Mountain Lion Postgres could not connect

Question

After my update to mountain lion my postgres doest work. It is still running but my applications cant connect to it anymore.

$ ps aux | grep postgres
postgres         204   0.0  0.0  2446960    836   ??  Ss    7:31AM   0:00.59 postgres: stats collector process    
postgres         203   0.0  0.1  2478732   2240   ??  Ss    7:31AM   0:00.41 postgres: autovacuum launcher process    
postgres         202   0.0  0.0  2478600    584   ??  Ss    7:31AM   0:00.34 postgres: wal writer process    
postgres         201   0.0  0.0  2478600    784   ??  Ss    7:31AM   0:00.48 postgres: writer process    
postgres          95   0.0  0.0  2446960    368   ??  Ss    7:31AM   0:00.11 postgres: logger process    
postgres          64   0.0  0.2  2478600   7972   ??  Ss    7:31AM   0:00.26 /Library/PostgreSQL/9.1/bin/postmaster -D/Library/PostgreSQL/9.1/data
anezio         10205   0.0  0.0  2432768    624 s000  R+    8:01AM   0:00.00 grep postgres

and my applications are returning this error:

could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

I still can connect to psql with the command /Library/PostgreSQL/9.1/bin/psql -U postgres

Seems like something is not pointing to the right place

sounds like you have some other build of postgres on that machine that you're using psql from. maybe you could tell us about your installation history too? — araqnid, Jul 27 '12 at 11:12
Ive installed it using the one click installer from postgresql.com. It was working fine before my update do mountain lion — Anezio Campos, Jul 27 '12 at 11:19
Ive solved this problem uninstalling and reinstalling Postgresql. Thanks — Anezio Campos, Jul 27 '12 at 12:54
This solved it for me: http://jaygoldman.com/2012/11/fixing-postgres-connection-errors-on-mountain-lion/ (summary: make a symbolic link from the actual location of the socket file to where pg is looking) — MBHNYC, Jan 15 '13 at 03:14

score 8 · Answer 1 · answered Jul 27 '12 at 13:39

8

I just had the same problem. Personally I just reinstalled from the Postgres installer (postgresql-9.1.3-1-osx.dmg in my case), rebooted my mac and all is fine again. p.s. re-installing didn't zap my databases :)

answered Jul 27 '12 at 13:39

Neil Billingham

2,235
4
23
34

score 5 · Answer 2 · answered Aug 02 '12 at 23:52

5

Check which psql you're using. I had the same problem while using the Postgres.app server from Heroku and found that I was using Apple's /usr/bin/psql client based on my $PATH setting. Either set your $PATH to use your Postgres library or use the full path to your installed psql.

answered Aug 02 '12 at 23:52

Rotten Ronny

71
3

2

I have postgres installed at the brew location /usr/local/bin. I had changed my /etc/paths to put /usr/local/bin at the top of the search paths so that brew installs were found first (before /usr/bin installs). The upgrade to Mountain Lion reset the contents of /etc/paths; consequently I too started to pick up the apple-installed psql in /usr/bin (and the nasty message "could not connect to server ...") after the upgrade. Putting /usr/local/bin back to the top of /etc/paths fixed everything. – Michael Bamford Oct 07 '12 at 00:05

score 2 · Answer 3 · answered Jul 27 '12 at 13:02

The default Unix domain socket path is hardcoded in libpq. What may have happen is that before the upgrade, your application used the libpq library installed by the Postgres one-click installer, while after the upgrade a different version of this library gets picked up.

To work around the problem, from a programmer's point of view, you could specify the socket directory instead of relying on the default. To locate the correct directory if you don't know it already, connect to any database as a superuser (generally postgres) and issue in SQL:

SHOW unix_socket_directory;

Then change or reconfigure your app with the path obtained in the host field of the connect call (e.g. in a connect string: host=/path/to/socket dbname=d user=u). Libpq will recognize it as a unix socket directory because it starts with a slash, as opposed to a hostname or IP address.

score 1 · Answer 4 · answered Aug 10 '13 at 21:22

Somehow I totally forgot that this socket file will be hidden because of the dot. Make sure you use ls -A /tmp/.s.PGSQL.5432 if you are checking to see if the socket is actually there.

My postgres app started accepting a connection again from psql after I did the following. I think it had something to do with step 3.

Exit the postgres app.
In terminal type postgres -D ~/Library/Application\ Support/Postgres/var. This is the data directory if you are using the postgres app. If you are not using the postgres app you need to find out what the data directory really is.
I got a prompt from OS X asking if I want to allow incoming traffic. I clicked yes.
In a different terminal tab type psql. You should connect successfully.
Type \q to exit psql.
Back on the tab running postgres, type ctrl c to stop postgres.
Start the postgres app. psql should work.

score 0 · Accepted Answer · answered Jun 26 '13 at 00:43

0

I've solved this problem by uninstalling and re-installing it

answered Jun 26 '13 at 00:43

Anezio Campos

1,555
1
10
18

score 0 · Answer 6 · answered Jul 03 '13 at 14:00

The problem is that mountain lion comes with it's own psql client (/usr/bin/psql) which apparently tries to connect postgres by searching the local socket file in a different location than the one from your installer. You could either change your PATH variable to include first your postgres bin folder or replace the default /usr/bin/psql with the one on your installation.

Mountain Lion Postgres could not connect

6 Answers6

Linked