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.
- 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/'.
- 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
Brad,
ReplyDeleteIt appears that I've spent a couple of days struggling with the same thing. After migrating from Leopard to Lion with a new computer, my python cgi script using psycopg2.connect() failed with the same error. The procedure you've worked out here works like a charm and saved me a lot of trouble, thanks!
A little odd though is your cryptic parenthetical expression "(Change brad to your username)". This doesn't appear to be necessary :-)
Also, like you I was unaware that postgres came bundled with Lion, and now I'm puzzled: where is it? Has my migrated installation completely shadowed the factory installation? Can the factory installation be invoked independently? Just idle curiosity now that I've discovered your solution.
Thanks again,
Jon
Actually after these steps `psql` command shows the following:
ReplyDeletepsql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
alexey,
ReplyDeleteI had the same problem but I discovered that psql needs the host specified thus:
psql -U username -d database -h localhost
I don't know why this should be so, since the config file has:
listen_addresses = '*' which is supposed to be 'all' and defaults to 'localhost'. But anyway, it works for me.
Jon
Thanks so much for this. Oddly, I did a reboot and had to repeat the steps for my group membership and permissions. Will this happen on every reboot or was it some fluke?
ReplyDeleteThat did not work for me. What did work was setting HOST = 'localhost' in local_settings.py, instead of not specifying it or setting it to '127.0.0.1'
ReplyDeleteFor any of you setting PGHOST or HOST in your python scripts, you're using a workaround, rather than fixing the root cause of the issue. Postgres uses the unix_socket_directory setting to locate the local unix domain socket to which to connect directly. Postgres clients switch to TCP when you specify a hostname, so your client is now connecting to port 5432 over TCP, rather than reading and writing to the local socket.
ReplyDeleteThanks for the post. It just worked in my case :)
ReplyDeleteThanks for working through this. Your post solved my problem. :)
ReplyDeleteWow, so glad I found this post. I've been modifying permissions and playing with all sorts of settings files. Didn't realize that I needed to do this.
ReplyDelete