Installing Ltree and PostGIS with PostgreSQL 9.2

It has been a while since I upgraded the key components of my application stack. This decision was driven by a desire to use PostGIS is a current project, and it appears that MacPorts has designed the latest version of PostGIS to work with PostgreSQL 9.2.

Since I have all my local databases backed up, I decided to clear all my previous PosgreSQL installs first (it turns out I had quite a bit of debris, and it necessitated uninstalling my PHP port as well).

PostgreSQL 9.2

In any case, with all those previous installations gone, I can now proceed:

sudo port -k install postgresql92
sudo port -k install postgresql92-server

As usual, there are a couple of followup steps to get PostgreSQL up and running, and have it automatically load when Mac OS X restarts.

sudo mkdir -p /opt/local/var/db/postgresql92/defaultdb
sudo chown postgres:postgres /opt/local/var/db/postgresql92/defaultdb
sudo su postgres -c '/opt/local/lib/postgresql92/bin/initdb -D /opt/local/var/db/postgresql92/defaultdb'
sudo su postgres -c '/opt/local/lib/postgresql92/bin/postgres -D /opt/local/var/db/postgresql92/defaultdb'

sudo launchctl load -w /Library/LaunchDaemons/org.macports.postgresql92-server.plist

Ltree

The reason I used the -k switch for the postgresql installation was to ensure that the contrib directory remained present (and was not cleaned up after installation). Now I can get ltree properly installed, making it possible to add it as an extension in my databases.

cd `port work postgresql92`
cd postgresql-9.2.1/contrib/ltree
make all
sudo make install

PostGIS

PostGIS has its own port.

sudo port install postgis2

With that out of the way, I can now start rebuilding my databases, and they can use these extensions. For example, I can do:

psql iw postgres
CREATE EXTENSION ltree;
CREATE EXTENSION postgis;
ALTER TABLE spatial_ref_sys OWNER TO database_owner_role;

As always, I will update my /etc/paths to list /opt/local/lib/postgresql92/bin first.