Greymeister.net

PostgreSQL and PostGIS Cooperating on OSX Lion Server

I recently replaced my home server with some Apple hardware and instead of having to buy a rather costly Server version of Snow Leopard, Lion had just come out, as well as an App store Server upgrade option. I was delighted to hear that OSX Lion Server shipped with the PostgreSQL database that I use for my application. I know that some people are probably surprised by this, but I think it is an excellent choice over MySQL which I try to avoid.

Fingering postgres

My first problem was finding the postgres user which is the daemon user for the PostgreSQL software on a system. I’m used to having to find it on multiple systems using the finger command standard with modern *nix systems. However, on OSX Lion, the user is _postgres which seems draconian. I guess that is a security feature but nonetheless I was able to continue in my PostgreSQL setup. Oddly enough, the database username is still postgres, so it is likely that the underscore prefix is a standard way for Apple systems to designate daemon users, although I doubt you would have many people who wanted to log in to a machine as postgres unless they were just trying to break in via SSH. I suppose it would be just as easy to try _postgres as an SSH remote user, which is why Apple made the shell for _postgres /usr/bin/false reducing the risk of remote break in attempts. However, this just points out that the underscore naming scheme is completely superfluous.

PostGIS

One feature I always need to enable in my PostgreSQL installations is spatial indexes. Ever since I read the excellent introduction by Scott Davis on the subject I have used PostGIS to enable that in PostgreSQL. The installation instructions are pretty straightforward once you’ve created your database, however, the required scripts were not included in the PostgreSQL installation on OSX Lion Server.

To get the required scripts, I turned to my trusty friend MacPorts. I keep hearing about how I shouldn’t use MacPorts and how Homebrew is way better. Having used FreeBSD in the past, the MacPorts experience is very familiar to me and it does its job. Without going down a rat hole on this, MacPorts has worked very well for me in the past and I usually don’t switch something I’ve used for so long unless I have a really good reason.

That said, obviously with Lion being so new, I expected a few humps getting MacPorts set up with it.
MacPorts has a PostGIS install which I use on my development machine with PostgreSQL already. However, it looks like one of its dependencies is broken at least as of the time of this writing.
However, there is a workaround in the ticket and thusly I was able to move forward.

Remote Connections

Last but not least, I always like to enable remote connections to my PostgreSQL server. It makes pointing my development work at it to test for incompatibilities easier, as well as many other things. I obviously would not do this if the machine were Internet reachable, so I understand that the default option in OSX Lion’s install would be not to allow remote connections. I went to configure the pg_hba.conf file and made the appropriate configuration change and restarted PostgreSQL. This is slightly less convenient than Ubuntu because instead of running something like

1
sudo service postgres restart

I had to familiarize myself with launchd and launchctl again. I was able to find out the proper command to stop and start PostgreSQL was using the name org.postgresql.postgres. However, even after restarting, I was still unable to connect from my workstation. A little research in the form of

1
ps -ef | grep postgres

revealed that the process was getting launched with a command line option of listen_addresses= which I assume was overwriting the option in the postgresql.conf file. I had to comment this line out in the plist file, located in /System/Library/LaunchDaemons/org.postgresql.postgres.plist. To get launchd to respect these changes, I had to run

1
2
launchctl unload org.postgresql.postgres.plist 
launchctl load org.postgresql.postgres.plist  

After that, I was able to connect from my workstation.

I’m not sure that this is the proper way to get the functionality I needed, but it might help someone else who happens to be in a similar situation as I was.