Monday, July 25, 2011

Repairing PostgreSQL after upgrading to Mac OSX Lion

I upgraded to Mac OSX 10.7 (Lion) today and had some issues with PostgreSQL. It
would seem that PostgreSQL is now bundled with OSX, and the upgrade process appears to have
caused some issues with my previous version.

The problem is this: I'd try to connect to PostgreSQL via the unix socket during (python
manage.py syncdb
), and I'd get the following error:

psycopg2.OperationalError: could not connect to server: Permission denied
 Is the server running locally and accepting
 connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

After a little trial and error the following seems to work reliably.
  1. Edit PostgreSQL's config to define the unix_socket_directory setting: (for me this was line 68)
$ sudo vim /Library/PostgreSQL/9.0/data/postgresql.conf

In my case I changed the setting to unix_socket_directory = '/var/pgsql_socket/'.
  1. Exceute the following commands.
$ sudo dscl . append /Groups/_postgres GroupMembership postgres
$ sudo chmod g+w,o+rx /var/pgsql_socket/

And finally restart PostgreSQL to apply the config changes:

$ sudo -u postgres /Library/PostgreSQL/9.0/bin/pg_ctl -D /Library/PostgreSQL/9.0/data/ restart