Postgis template

From MapbenderWiki

PostGIS is the spatial extension of PostgreSQL.

Creating the postgis_template Database

If you want to comfortably create new database including PostGIS by default you can create a template with the required functionality.

Create the template database with postgis spatial extension and add PLPGSQL language support.

createdb -E UTF8 -T template0 template_postgis
createlang -d template_postgis plpgsql

Load PostGIS SQL scripts postgis.sql (old: lwpostgis.sql) and spatial_ref_sys.sql.

psql -d template_postgis -f /usr/share/<postgresql-version>/postgis.sql 
psql -d template_postgis -f /usr/share/<postgresql-version>/spatial_ref_sys.sql 

Allow users to alter spatial tables, garbage-collect and freeze and allow non-superusers the ability to create from this template.

psql -d template_postgis -c "GRANT ALL ON geometry_columns TO PUBLIC;"
psql -d template_postgis -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;"
psql -d template_postgis -c "VACUUM FULL;"
psql -d template_postgis -c "VACUUM FREEZE;"
psql -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='template_postgis';" 
psql -d postgres -c "UPDATE pg_database SET datallowconn='false' WHERE datname='template_postgis';"