Adding “contrib” Extensions to MacPorts PostgreSQL

PostgreSQL is my database of choice. The main reason is that a standard distribution comes with just about everything one could ask for in a free RDBMS, and if what you want isn’t already installed, it’s often in the “contrib” directory. In particular, now that I’m doing a lot of iOS development, and location awareness is a big part of mobile apps, it’s useful to be able to work with coordinates. PostgreSQL does this using the cube data type and earthdistance functions, both in “contrib”.

MacPorts, however, doesn’t include much of the contents of “contrib” in a standard install, including both of these extensions. This makes it a lot harder to develop code using them unless I want to set up a development database on my production server, something I am not eager to do.

Thankfully, there is a solution: disable auto-clean, and after installing the postgresql83 (or 84, depending on the version you want), you can install the extra “contrib” extensions by hand.

1. Install PostgreSQL (sudo port -k install postgresql83) or, if you’ve already installed it, re-install it (sudo port -nk upgrade --force postgresql83).
2. Change into MacPorts working directory: cd `port work postgresql83`/postgresql-8.3.11/contrib. Your specific version will differ from mine if you’re reading this more than a few months after I wrote it.
3. For each extension you want installed, cd into the directory, make all, then sudo make install. This compiles C code, if any, into a shared library, then copies the library and supporting SQL files (to create types, functions, etc., and also to drop them) to /opt/local/share/postgresql83/contrib.
4. Change into /opt/local/share/postgresql83/contrib and run the SQL files associated with the extensions you want. This probably needs to be done as the PostgreSQL super-user. For example, psql -U postgres dbname <cube.sql.
5. Clean the postgresql83 port: sudo port clean postgresql83.