Create a POSTGIS template database

Create PostGIS template

-- From http://geospatial.nomad-labs.com/2006/12/24/postgis-template-database/

-- $ sudo su postgres
-- $ psql template1
\c template1
CREATE DATABASE template_postgis WITH template = template1;
 
-- 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/pgsql/contrib/postgis.sql;
\i /usr/share/pgsql/contrib/spatial_ref_sys.sql;
 
-- in a production environment you may want to 
-- give role based permissions, but granting all for now
GRANT ALL ON geometry_columns TO PUBLIC;
GRANT ALL ON geography_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-superuser’s can create postgis db’s using template_postgis:
-- $ createdb -h host-name my_gisdb -W -T template_postgis

postgis_template_homebrew.sql

-- $ psql template1
\c template1
CREATE DATABASE template_postgis WITH template = template1;
 
-- 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/local/share/postgis/postgis.sql;
\i /usr/local/share/postgis/spatial_ref_sys.sql;
 
-- in a production environment you may want to 
-- give role based permissions, but granting all for now
GRANT ALL ON geometry_columns TO PUBLIC;
GRANT ALL ON geography_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-superuser’s can create postgis db’s using template_postgis:
-- $ createdb -h host-name my_gisdb -W -T template_postgis

postgis_template_homebrew_postgis2.sql

-- $ psql template1
\c template1
CREATE DATABASE template_postgis WITH template = template1;
 
-- 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 EXTENSION postgis;
 
-- in a production environment you may want to 
-- give role based permissions, but granting all for now
GRANT ALL ON geometry_columns TO PUBLIC;
GRANT ALL ON geography_columns TO PUBLIC;
GRANT ALL ON raster_columns TO PUBLIC;
GRANT ALL ON raster_overviews 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-superuser’s can create postgis db’s using template_postgis:
-- $ createdb -h host-name my_gisdb -W -T template_postgis