Why does PostGIS installation not create a template_postgis?

Question

I went this route for installation of PostgreSQL and PostGIS:

• PostgreSQL using the standalone KyngChaos,
• PostGIS install from KyngChaos as well...
• pgAdmin3 from PostgreSQL Tools

Now when I launch pgAdmin and try to create a new PostGIS database, there is no template_postgis for use in Template.

---

I have tried going another route, that is downloading the PostgreSQL package directly from PostgreSQL. This installs the server and allows you to use the StackBuilder to download and install PostGIS. This doesn't fix my problem.

Answer 1

You can quite easily create the template if it is not there automatically. Here is a description for ubuntu: http://obsessivecoder.com/2010/02/01/installing-postgresql-8-4-postgis-1-4-1-and-pgrouting-1-0-3-on-ubuntu-9-10-karmic-koala/

This is the essential part:

sudo su postgres
createdb template_postgis
createlang plpgsql template_postgis
psql -d template_postgis -f /usr/share/postgresql/8.4/contrib/postgis.sql
psql -d template_postgis -f /usr/share/postgresql/8.4/contrib/spatial_ref_sys.sql

In Precise Pangolin:

sudo su postgres
createdb template_postgis
psql -d template_postgis -f /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql
psql -d template_postgis -f /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql

• Michael Markieta: Strange.. at step 'createlang plpgsql postgistemplate' I get a $ "plpgsql" is already installed in database "postgistemplate"... and then on the next step, psql: command not found
• Michael Markieta: Update - There is no postgis directory in the /usr/share/ path
• underdark: Then I guess it's hidden somewhere else on OSX. Have you tried searching for the files?
• Michael Markieta: Found the files, all lines of code run without errors. But pgAdmin3 doesn't show the template still... Hrmm.. Restarted my Mac to see if that would change anything, but still nada. (strange... I had this working on my Mac before the switch to Lion)
• underdark: Which user did you use to create the template and which user are you using in pgAdmin?

Answer 2

From version 2 Postgis is enabled by using the extension system. To spatially enable a database, log to your database and then:

 CREATE EXTENSION postgis;
 CREATE EXTENSION postgis_topology;

source: http://postgis.net/docs/postgis_installation.html

Note: Only SUPERUSERS roles have the ability to create EXTENSIONS

• matt wilkie: from pgAdmin4: Databases >> {select db} >> Extensions >> -r-click >> Create >> Select from list

Answer 3

I had similar issues and ended up installing the package found at:

entreprisedb.com

you can select installing postgis during the installation phase or call 'Application Stack Builder' later on. If the installation doesn't work, select another folder to download the postgis installer (which will be called something like edb_postgis_1_5_pg91.app.zip ). Unpack the zip and install. If it doesn't work you may want to reboot and try again. In pgAdmin III template_postgis should appear.

Answer 4

The question may be outdated but I ran into the same error on OS X Lion. Maybe my answer could help another user.

The default installation of PostGIS with "stack builder" will fail. But after the first installation step, you can choose the download folder for PostGIS. Just download it to your desktop and unzip it by hand. By doubleclicking the file, it will install properly. The postgis_template is now available in the pgAdmin.

Answer 5

According to the PostGIS documentation, only

Some packaged distributions of PostGIS ... load the PostGIS functions into a template database called template_postgis.

So, not every distribution comes with template_postgis.

As said in existing answers, in PostGIS 2.x, it is easy to create or customize the template yourself by creating a regular database named template_postgis as superuser, and then creating the required and optional extensions (such as pgRouting). Per PostGIS documentation:

sudo su postgres
createdb template_postgis
psql -d template_postgis -c "CREATE EXTENSION postgis;"
psql -d template_postgis -c "CREATE EXTENSION postgis_topology;"
-- if you built with sfcgal support --
psql -d template_postgis -c "CREATE EXTENSION postgis_sfcgal;"

In addition, you can mark this newly created database as a template database by setting the datistemplate flag in the system table pg_database to ture.

psql -d template_postgis -c "UPDATE pg_database SET datistemplate = 'true' WHERE datname = 'template_postgis';"

This will, e.g., prevent the template database from being accidentally dropped or altered by other users or yourself. (You will need to set the flag to false if you want to make changes to the template.)

Then you can create spatial database based on whatever you put into the template:

createdb -T template_postgis my_spatial_db