How to create PostGIS template?

Question

I am following a tutorial to install pgrouting. However, I didn't get the part when it says

Start pgAdmin and create a new database based on your PostGIS template

Where can I get this postgis template? I know I have installed PostGIS using homebrew. But I couldn't find the template. I am running macos now.

Which version of PostGIS are you using? – R.K. Dec 06 '12 at 09:55 — R.K., Dec 06 '12 at 09:55

score 16 · Answer 1 · edited Apr 13 '17 at 12:34

New Method With PostgreSQL 9.1

Thanks to the advice of R.K. below, I took a look at this tutorial and found that for PostgreSQL 9.1 all you need to do is add the extensions postgis and postgis_topology to an existing database using the pgAdmin context menus. To create a postgis template, I created a new database called template-postgis and then added these extensions. I then created my other database using this template. When using pg_dump I found the size of the export was much smaller, as it seems to just include these lines and not dump the extension functions:

CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology;

Old Redundant Method:

I ended up using the .sql files here:

/Applications/Postgres.app/Contents/MacOS/share/contrib/postgis-2.0/postgis.sql
/Applications/Postgres.app/Contents/MacOS/share/contrib/postgis-2.0/spatial_ref_sys.sql
/Applications/Postgres.app/Contents/MacOS/share/contrib/postgis-2.0/topology.sql

Also, I got this error when I imported an existing PostGIS database into a new database made from this template:

ERROR:  type "spheroid" already exists

So I followed the instructions here and used ON_ERROR_ROLLBACK=on to set up the template, after creating a blank database called "template_postgis":

psql -U postgres -d template_postgis -1 -f /Applications/Postgres.app/Contents/MacOS/share/contrib/postgis-2.0/postgis.sql -v ON_ERROR_ROLLBACK=on
psql -U postgres -d template_postgis -1 -f /Applications/Postgres.app/Contents/MacOS/share/contrib/postgis-2.0/spatial_ref_sys.sql -v ON_ERROR_ROLLBACK=on
psql -U postgres -d template_postgis -1 -f /Applications/Postgres.app/Contents/MacOS/share/contrib/postgis-2.0/topology.sql -v ON_ERROR_ROLLBACK=on

And then imported my backup db, e.g.:

psql -U someuser -d somedb -1 -f somefile.sql -v ON_ERROR_ROLLBACK=on

Even Older Method:

I did this:

createdb -E UTF8 -T template0 template_postgis
createlang -d template_postgis plpgsql
psql --quiet -d template_postgis -f /Applications/Postgres.app/Contents/MacOS/share/extension/postgis--2.0.1.sql

The path to postgis--2.0.1.sql will be different depending on your setup.

To complete your answer (which works on my Mac): on a Mac, if you installed postgis from Kyngchaos ports, the postgis installation file is: /usr/local/pgsql/share/extension/postgis--2.0.1.sql — Bruno von Paris, Nov 19 '12 at 23:20
For the newest method, is 'SCHEMA' needed? What's it for? (Yours is the only example I've seen so far that do that.) — matt wilkie, Apr 26 '18 at 22:41

score 15 · Answer 2 · answered Dec 06 '12 at 10:07

15

If you are running PostgreSQL 9.1+, you don't need to bother with the template database. Just create a database and then on the SQL view in pgAdmin, type and run

CREATE EXTENSION postgis;

That will spatially enable your database.

answered Dec 06 '12 at 10:07

R.K.

17,405
3
59
110

Can the template method still be used? Or, is there a way that to add this info to the "new database" dialog in pgAdminIII? – monkut May 28 '13 at 04:14
1

you probably also want to add CREATE EXTENSION postgis_topology; – matt wilkie Sep 11 '18 at 21:07
1

@monkut in pgAdmin4 this is available via right-click on 'Extensions' in the navigation tree – matt wilkie Sep 11 '18 at 21:09
CREATE EXTENSION needs superuser privileges, so actually creating/using a template might be very good idea. – Cromax Dec 03 '19 at 23:23

urcm · Answer 3 · 2012-05-31T07:03:46.510

3

in fact this is the foundation of postgis installation.

if you have postgis template you can create a new database from it as :

 createdb -T postgistemplate -O gis gisdb

if you not have it, you can do this with this way:

sudo su postgres
createdb postgistemplate
createlang plpgsql postgistemplate
psql -d postgistemplate -f /usr/share/postgresql-8.2-postgis/lwpostgis.sql
psql -d postgistemplate -f /usr/share/postgresql-8.2-postgis/spatial_ref_sys.sql

you can use bash scripts Debian/Ubuntu - create_template_postgis-debian.sh, here

i hope it helps you...

edited May 31 '12 at 07:03

answered May 31 '12 at 06:51

urcm

22,533
4
57
109

It is probably the case that the user postgres does not have permissions to use a terminal, in which case the sudo su postgres command will silently fail. To get past this you can precede all these commands with sudo -u postgres to run the command as the postgres user without switching to the postgres user. – Ring May 05 '14 at 19:52

score 2 · Answer 4 · answered Jan 18 '15 at 02:35

#!/bin/sh
instance_name=$1
psql << EOF
create database $instance_name;
\connect $instance_name
-- Enable PostGIS (includes raster)
CREATE EXTENSION postgis;
-- Enable Topology
CREATE EXTENSION postgis_topology;
-- fuzzy matching needed for Tiger
CREATE EXTENSION fuzzystrmatch;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;
EOF

I use this bash script to execute creation of new database with postgis enabled and name given on stdin.

Postgres 9.3

PostGIS 2.1.3

score 2 · Answer 5 · answered May 30 '12 at 20:12

As far as I remember there is some script available for the workshop to create those template databases. It's also available here: https://github.com/pgRouting/pgrouting-contrib/blob/master/scripts/create_templates.sh

Eventually you need to change a few things here and there, because it was written to work for Debian/Ubuntu. But you can just pick what you need.

How to create PostGIS template?

5 Answers5

Linked