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.

  • 1
    Which version of PostGIS are you using?
    – R.K.
    Commented Dec 6, 2012 at 9:55

5 Answers 5


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/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 Commented Nov 19, 2012 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.) Commented Apr 26, 2018 at 22:41

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.

  • 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
    Commented May 28, 2013 at 4:14
  • 1
    you probably also want to add CREATE EXTENSION postgis_topology; Commented Sep 11, 2018 at 21:07
  • 1
    @monkut in pgAdmin4 this is available via right-click on 'Extensions' in the navigation tree Commented Sep 11, 2018 at 21:09
  • CREATE EXTENSION needs superuser privileges, so actually creating/using a template might be very good idea.
    – Cromax
    Commented Dec 3, 2019 at 23:23

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...

  • 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
    Commented May 5, 2014 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: 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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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