thoughtherder

Creating a Spatially-Enable Database in Postgres after Upgrading

View Comments

Wednesday, May 25, 2011 3:47 p.m.

This post is a reminder to myself on how to rebuild my template_postgis because I never seem to remember how to do it.

First things first, stop the db if it’s running. For me, that’s pg_ctl -D /usr/local/var/postgres stop -s -m fast, which I have aliased as pg-stop.

So, you’ve successfully upgraded Postgres. Congrats! Now you want to re-install Postgis. In OS X, that’s brew install postgis. Even if it’s already there, remove it first. Failing to do so will result in errors down the line. Do it now.

Start up Postgres, pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start, or for me pg-start. Next, kill your old template_postgis. Since it’s a template, you’ll need to do:

psql template1
template1=# update pg_database set datistemplate = FALSE
template1=# where datname = 'template_postgis';
UPDATE 1
template1=# \q
dropdb template_postgis

Now we’re ready to create it:

createdb template_postgis -T template1
createlang plpgsql template_postgis
cd /usr/local/Cellar/postgis/1.5.2/share/postgis/
psql -d template_postgis -f postgis.sql
psql -d template_postgis -f spatial_ref_sys.sql
psql template_postgis
template_postgis=# pg_database set datistemplate = TRUE
template_postgis=# where datname = 'template_postgis';
UPDATE 1
template_postgis=# grant all on geometry_columns to PUBLIC;
GRANT
template_postgis=# grant all on spatial_ref_sys to PUBLIC;
GRANT
template_postgis=# VACUUM FREEZE;
VACUUM
template1=# \q

And that should be that. We now have a spatially-enabled template that we can use to create other databases with, as such createdb my_db_name -T template_postgis.

References:

• Installing postgresql, postgis and more on os-x leopard
• Create a spatially-enabled database — Postgis Manual

permalink | tagged as: postgresql osx postgis template