Creating a PostGIS template
The following is adapted from Rob Braswell’s instructions. This allows non-superusers to create spatial databases using a template.
-
Connect to the template database
$ psql template1
-
Execute the following commands:
template1=# create database template_postgis with template = template1;
template1=# UPDATE pg_database SET datistemplate = TRUE where datname = 'template_postgis';
-
Connect to the new template_postgis database:
template1=# \c template_postgis
-
Add PostGIS extensions and grant access to everyone to spatial tables:
template_postgis=# CREATE LANGUAGE plpgsql;
template_postgis=# \i /opt/local/share/postgis/lwpostgis.sql;
template_postgis=# \i /opt/local/share/postgis/spatial_ref_sys.sql;
template_postgis=# GRANT ALL ON geometry_columns TO PUBLIC;
template_postgis=# GRANT ALL ON spatial_ref_sys TO PUBLIC;
-
Prevent further modifications to the template_postgis database:
template_postgis=# VACUUM FREEZE;
-
Quit out of psql (^D or \q )
-
Create a test database using the new template_postgis template…
$ createdb test_gis_db -T template_postgis
…and drop it again.
$ dropdb test_gis_db
|