Saturday, October 24, 2009

PostGIS template on macport postgresql84

We use postgresql and postgis for one of our websites we maintain: byways.org.

I use a mac with development environment installed via macports. I originally followed most of the instructions on: http://www.robbyonrails.com/articles/2008/01/22/installing-ruby-on-rails-and-postgresql-on-os-x-third-edition

Today I did an update to postgresql 8.4 and it was messier than I had hoped. After running:
sudo port selfupdate
sudo port upgrade outdated


I waited about 3 hours to download and compile all my macports.

Then:

port install postgresql84 postgres84-server
at the end there are instructions to modify your path and launchDeamon as mentioned in the robby on rails post.


However - here is where it gets fun -- I uninstalled postgresql83-server and modified my path to point to 84 not 83. You have to re-boot to get the database up and running again with the appropriate path etc.

Then, I wanted to build me a template_postgis database as outlined here:
http://geospatial.nomad-labs.com/2007/12/16/template-postgis-database/

except they moved some files, and I had to run it like this:
$ psql template1
\c template1
CREATE DATABASE template_postgis WITH TEMPLATE = template1 ENCODING = 'UTF8';

-- next set the 'datistemplate' record in the 'pg_database' table for
-- 'template_postgis' to TRUE indicating its a template
UPDATE pg_database SET datistemplate = TRUE WHERE datname =
'template_postgis';
\c template_postgis
CREATE LANGUAGE plpgsql;
\i /opt/local/var/macports/software/postgis/1.4.0_1+postgresql84/opt/local/share/postgresql84/contrib/postgis.sql
\i /opt/local/var/macports/software/postgis/1.4.0_1+postgresql84/opt/local/share/postgresql84/contrib/spatial_ref_sys.sql

GRANT ALL ON geometry_columns TO PUBLIC;
GRANT ALL ON spatial_ref_sys TO PUBLIC;

-- vacuum freeze: it will guarantee that all rows in the database are
-- "frozen" and will not be subject to transaction ID wraparound
-- problems.
VACUUM FREEZE;


Restore my database from backup and away we go.

Hope this helps someone out there.

No comments:

Post a Comment

I filter all comments heavily. Please be polite and G rated.