Some might find this useful for creating PostGIS databases without having to be postgresql superusers. 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
CREATEDATABASE template_postgis WITH template = template1;
-- set the 'datistemplate' record in the 'pg_database' table for-- 'template_postgis' to TRUE indicating its a templateUPDATE pg_database SET datistemplate = TRUE WHERE datname ='template_postgis';
\c template_postgis
CREATELANGUAGE plpgsql ;
\i /usr/share/postgresql/contrib/lwpostgis.sql;
\i /usr/share/postgresql/contrib/spatial_ref_sys.sql;
-- in a production environment you may want to -- give role based permissions, but granting all for nowGRANTALLON geometry_columns TO PUBLIC;
GRANTALLON 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:
postgis template database
Some might find this useful for creating PostGIS databases without having to be postgresql superusers. 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.
Now non-superuser’s can create postgis db’s using template_postgis: