Archive for December, 2007

template-postgis database 0

Posted December 16, 2007

Create a template_postgis database
Some might find this useful for creating PostGIS databases without having to be PostgreSQL
super users. The idea is to create a template_postgis database, install plpgsql and postgis into
it, and then use this database as a template when creating new PostGIS databases.

$ psql template1
\c template1
CREATE DATABASE template_postgis WITH TEMPLATE = template1 ENCODING = 'UTF8';
 
-- next set the 'datistemplate' record in the 'pg_database' table for
-- 'template_postgis' to TRUE indicating its a template
UPDATE pg_database SET datistemplate = TRUE WHERE datname =
'template_postgis';
\c template_postgis
CREATE LANGUAGE plpgsql;
\i /usr/share/postgresql/contrib/lwpostgis.sql;
\i /usr/share/postgresql/contrib/spatial_ref_sys.sql;
 
-- windows 
-- C:\Program Files\PostgreSQL\8.2\share\contrib\lwpostgis.sql
-- C:\Program Files\PostgreSQL\8.2\share\contrib\spatial_ref_sys.sql
 
GRANT ALL ON geometry_columns TO PUBLIC;
GRANT ALL ON spatial_ref_sys TO PUBLIC;
 
-- vacuum freeze: it will guarantee that all rows in the database are
-- "frozen" and will not be subject to transaction ID wraparound
-- problems.
VACUUM FREEZE;

Now non-super user’s can create PostGIS databases using template_postgis:

$ createdb my_gisdb -W -T template_postgis