Mac OSX Lion Postgres does not accept connections on /tmp/.s.PGSQL.5432

9
votes

I'm getting a common Mac OSX error for Homebrew installations of Postgres,

No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

This is apparently due to there being multiple installs on the system. However, the usual steps are not resolving things.

I can successfully start Postgres and running:

ps auxw | grep post

returns ..

euanmillar       847   0.0  0.0  2432768    588 s000  R+   11:19am   0:00.00 grep postgres
euanmillar       845   0.0  0.0  2439324    448   ??  Ss   11:19am   0:00.00 postgres:
stats collector process
euanmillar       844   0.0  0.0  2443176   1520   ??  Ss   11:19am   0:00.00 postgres:
autovacuum launcher process
euanmillar       843   0.0  0.0  2443044    544   ??  Ss   11:19am   0:00.00 postgres: wal writer process
euanmillar       842   0.0  0.0  2443044    584   ??  Ss   11:19am   0:00.00 postgres: writer process
euanmillar       841   0.0  0.0  2443044    596   ??  Ss   11:19am   0:00.00 postgres: checkpointer process
euanmillar       839   0.0  0.0  2443044   3616 s000  S    11:19am   0:00.02 /us

so it looks like postgres is running.

I have commented in the following lines in postgresql.conf

port = 5432
unix_socket_directory = '/var/pgsql_socket'
unix_socket_permissions = 0777

and in var/pgsql_socket/ I do have a hidden file .s.PGSQL.5432

I have tried solutions to many of the posts here. This one especially seems similar:

https://dba.stackexchange.com/questions/21587/postgresql-is-running-locally-but-i-cannot-connect-why

But the difference is that I do have a '/var/pgsql_socket' directory which is CHOWN-ed to everyone. I have used Homebrew to completely remove and re-install Postgres and still I have the same issue.

6
postgresqlosx-lionhomebrew
The ps output shows that postgres is not running. See another question for a sample output when it's running. Your first step is to actually launch the server with the homebrew instructions. – Daniel Vérité
Your description is confusing /var/postgresql and /var/pgsql_socket. – Peter Eisentraut
Thanks for the help and apologies if my description is a bit confusing. – Euan Millar
I ran pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start and I get the message "server starting" then ps auxw | grep post returns a lot of processes. But when I try to createdb I get the same socket error. – Euan Millar
A reminder that createdb should be run as postgres user. Have you used sudo -u postgres createdb? Although, that might not be related to the socket issue. – Atmaram Shetye

6 Answers

33
votes

You need to remove the postmaster.pid, which should be in the following path: /usr/local/var/postgres/postmaster.pid

14
votes

For me, i removed postmaster.pid in /usr/local/var/postgres. fixed me right up.

9
votes

A simpler solution is to locate where the socket actually is vs where it's expected to be. In my case, I ran:

$ locate PGSQL.5432
/private/var/pgsql_socket/.s.PGSQL.5432
/private/var/pgsql_socket/.s.PGSQL.5432.lock

Then just symlink the expected socket location to the actual socket location.

$ ln -s /private/var/pgsql_socket/.s.PGSQL.5432 /tmp/.s.PGSQL.5432
2
votes

The system client is being used and not your brew version. The symlink approach only lasts until the next restart. This approach fixes the root issue I believe

  1. sudo edit /etc/paths
  2. Move the line containing /usr/local/bin to the top of the file. (Before /usr/bin)
  3. Uninstall your postgres gem(s) (gem uninstall pg)
  4. Start up new shell to load the new environment settings
  5. bundle
1
votes

That is not an OS X specific issue. You will need to clean up your postgres installation and then reinstall it again. I have faced this issue on my ubuntu 12.04. While cleaning your previous installation, you will need to remove all packages starting with postgres (postgresql, postgresql-common, postgresql-client etc), in other words, postgres*. I have not used brew, have used port on OS X Lion. I guess the equivalent command should be sudo brew remove postgres*. A sudo brew install postgresql should then do the trick.

Also, if you feel the server is already running, you can try sudo -u postgres createuser. If that fails, you will need to reinstall.

However, the output of your ps aux | grep post is actually the grep command itself. Not of the postgres server running.

EDIT: Looks like the following link could be of help https://dba.stackexchange.com/questions/21587/postgresql-is-running-locally-but-i-cannot-connect-why If yes, then this question might be a duplicate.

0
votes

I had the same problem on Mac Serria. In Mac Serria you can find postmaster.pid inside /Users/<user_name>/Library/Application Support/Postgres/var-9.6 if you used GUI installer rather than homebrew.

Once you delete this file then everything should work fine.