Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
Which version of PostGIS are you using? – R.K. Dec 6 '12 at 9:55

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:


Old Redundant Method:

I ended up using the .sql files here:


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/ -v ON_ERROR_ROLLBACK=on
psql -U postgres -d template_postgis -1 -f /Applications/ -v ON_ERROR_ROLLBACK=on
psql -U postgres -d template_postgis -1 -f /Applications/ -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/

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

share|improve this answer
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

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


That will spatially enable your database.

share|improve this answer
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 4:14

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 -, here

i hope it helps you...

share|improve this answer
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 5 '14 at 19:52

As far as I remember there is some script available for the workshop to create those template databases. It's also available here:

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.

share|improve this answer
psql << EOF
create database $instance_name;
\connect $instance_name
-- Enable PostGIS (includes raster)
-- Enable Topology
CREATE EXTENSION postgis_topology;
-- fuzzy matching needed for Tiger
CREATE EXTENSION fuzzystrmatch;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;

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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.