Showing posts with label postgis. Show all posts
Showing posts with label postgis. Show all posts

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.