PostgreSQL is running locally but I cannot connect. Why?
Recently updated my machine from Mac OS X Lion (10.7.4) to Mountain Lion (10.8) and I think it borked my PostgreSQL installation. It was installed originally via Homebrew. I'm not a DBA, but hoping someone can tell me how to troubleshoot this.
I am unable to connect (but was able to before pre-Mountain Lion):
$ psql -U rails -d myapp_development psql: 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"?But Postgres is still clearly running:
$ ps aux | grep postgres meltemi 2010 0.0 0.0 2444124 5292 ?? Ss Wed01PM 0:00.02 postgres: rails myapp_development [local] idle meltemi 562 0.0 0.0 2439312 592 ?? Ss Wed12PM 0:02.28 postgres: stats collector process meltemi 561 0.0 0.0 2443228 1832 ?? Ss Wed12PM 0:01.57 postgres: autovacuum launcher process meltemi 560 0.0 0.0 2443096 596 ?? Ss Wed12PM 0:02.89 postgres: wal writer process meltemi 559 0.0 0.0 2443096 1072 ?? Ss Wed12PM 0:04.01 postgres: writer process meltemi 466 0.0 0.0 2443096 3728 ?? S Wed12PM 0:00.85 /usr/local/bin/postgres -D /usr/local/varpostgres -r /usr/local/var/postgres/server.logAnd it's responding to queries (both to a test db and the development db) from a local Rails app
User Load (0.2ms) SELECT "users".* FROM "users" Rendered users/index.html.haml within layouts/application (1.3ms)There appears to be no
/var/pgsql_socket/directory, let alone the/var/pgsql_socket/.s.PGSQL.5432socket file mentioned above!?! Maybe the install of Mountain Lion wiped that out?$ ls -l /var/ | grep pg drwxr-x--- 2 _postgres _postgres 68 Jun 20 16:39 pgsql_socket_altHow can I troubleshoot this?
Related question on SO. Seems like Apple didn't do a great job with the upgrade.
is there any mention of a socket file being created in the log file `/usr/local/var/postgres/server.log` ?
@ErwinBrandstetter How do you expect Apple to do a "good job" of upgrading manually installed 3rd party *nix apps?
@Phil- no mention. I'm starting to think this might be a path variable problem. I think my `$PATH` got changed with the upgrade `/usr/bin` is ahead of `/usr/local/bin` and I think Mountain Lion may come with PostgreSQL pre-installed!?! Investigating...
I found that I had an extremely similar problem, namely that postgres was opening a socket in
/var/pgsql_socket_altwhere none of my software expects to look, but the solution to my problem was not only a problem with my$PATH.I had to create the directory
/var/pgsql_socket, chown it to myself, and setunix_socket_directoryinpostgresql.conf(located in/usr/local/var/postgres) to that directory, then use thepg_ctlbinary in/usr/local/binto start the right postgres server successfully (which is where$PATHcomes in – make surewhich pg_ctlresolves to/usr/local/bin/pg_ctl, or just always call it explicitly).This might help other users who find this question via the
/var/pgsql_socket_altmention.Interesting. Are you also on Mountain Lion? Did you install PostgreSQL with Homebrew? If so, wonder if anyone else can verify this solution as opposed to changing my `$PATH` as I have done.
Yes, yes, and I hope so!
@wolftron your solution was bang-on for me (Mountain Lion, homebrew/postgres, /var/pgsql_socket_alt, the whole nine). Is this a new issue on Mountain Lion with homebrew? I'll open a ticket with them if you think so.
Looks like we've got verification from @Jamie.
I can confirm this problem and the solution on OS X 10.8.2 / brew installation of postgresql 9.2.1.
I spent a while trying to "chown the folder", and felt like sharing this because it didn't throw an error finally: `sudo chown _postgres pgsql_socket`
A plausible and typical explanation would be that the
psqlthat comes with homebrew is in/usr/local/bin/psqlwhich is different from the one that would be in your $PATH, like/usr/bin/psql(bundled with OS X). You may want to try with the full path:$ /usr/local/bin/psql -U rails -d myapp_developmentAlso, there's something rather unusual in the
psoutput of your question: the postgres server is running under ameltemiUnix user, whereas generally, the dedicatedpostgresUnix user is used for that.Also, `_postgres` (with underscore) for user / group are unknown to me. Is that an artifact or expected?
Yes, it appears to be a `$PATH` problem as you stated. Things work as before when I use `/usr/local/bin/psql` to access the database. Either Lion didn't have a system PostgreSQL or my $PATH was set up differently. It's been a year since I last muddled with this so I can't remember exactly. As for the Unix user...with a Homebrew install of PostgreSQL the server is launched by launchd and the user is set to the local user who installed it, as a default. Things are set up differently on **Mac OS X Server** which starts up PostgreSQL automatically under `postgres`.
I don't know of any config file for the psql client. However psql does respect a number of environment variables that correlate to command line options.
So to have the psql automatically use the socket of your choice you can set the PGHOST variable to the directory containing the socket. i.e.
PGHOST=/var/pgsql_socket_alt psql -d mydatabsaseIt's not documented anywhere I can find that you can set PGHOST to the directory where the socket files live. But that does in fact work. Thanks!
Try:
psql -U rails -d myapp_development -h localhostor
psql -U rails -d myapp_development -h 127.0.0.1Late, but I found this helpful: http://tammersaleh.com/posts/installing-postgresql-for-rails-3-1-on-lion
That was for Lion, but I was having same issues as the one in this thread after upgrading from 10.6.8 to Mountain Lion and having installed PostgreSQL via HomeBrew prior while on 10.6.8. I also had the mysterious
/var/pgsql_socket_altfolder post-upgrade, but I just removed it and created/var/pgsql_socketas suggested by @wolftron. However, that wasn't the final solution.If I left
unix_socket_directoryblank/commented out inpostgresql.conf, any projects existing prior to the upgrade would complain that the socket in/var/pgsql_socketwas missing. But if I changed conf and hard-codedvar/pgsql_socket, any new projects would complain that the socket in/tmpwas missing. Very frustrating...until I re-installedpg gemin an pre-10.8 project (gem uninstall pg && gem install pg) and leftunix_socket_directorycommented out inconffile. After a quickpg_ctlto restart the server, both new and old projects worked. My pgsql socket lives in/tmpnow, fwiw.Sidenote: if you are using
activerecord-postgresql-adaptergem, uninstall it first, then re-install pg, then installactiverecord-postgresql-adapteragain.I've only just signed up to the dba SE, so don't seem to be able to comment on the relevant post (what a crock!).
However, I was confident that I was in the same boat as @thure. I'd made sure /usr/local/bin was earlier in my PATH than /usr/bin, had checked which binaries the shell had hashed with
whichandtype, etc.I saw the same symptoms as @thure. Then I had an epiphany; I realised I'd rebuilt the
pggem (I'm using Ruby) in a shell whose PATH had been adversely affected by Mac's path_helper (which is run from /etc/profile and puts /usr/bin before /usr/local/bin).I uninstalled pg and reinstalled it in a shell whose PATH was correct. All of a sudden I could connect!
So make sure you recompile your language bindings people, and let them find the correct copy of (presumably)
pg_config.I can't quickly find the link where I found this nugget, but it worked for me.
export PGHOST=localhost
Oh, here's the link. https://stackoverflow.com/questions/13868730/socket-file-var-pgsql-socket-s-pgsql-5432-missing-in-mountain-lion-os-x-ser
Here it is, 2016, El Capitan is out there, and Apple keeps changing things up. Postgres is installed as part of the OS, and the postgres config file sets the unix_socket_directories property in postgresql.conf to /tmp. The socket is in /tmp/.s.PGSQL.5432. I was able to get around the problem by executing the following:
sudo ln -s /tmp /var/pgsql_socketHope this helps someone.
Look for the correct socket file
find / -name .s.PGSQL.5432 -lsFrom the result get the path to the file and use the path with the "-h" parameter within the psql command
For example, this is the way I connect to the macOS Server Calendar and Contacts database (within a ssh session to the server):
sudo psql -U _calendar -h /private/var/run/caldavd/PostgresSocket/ -d caldavThen, the socket file at the path would be used to connect.
By default postgres seems to be trying to connect via unix-domain-sockets. UNIX DOMAIN SOCKET
This happened to me when I was running postgres instance on docker. You have to see what kind of connection is your server accepting. For me it was clearly TCP and not unix domain socket.
Adding flag to accept host redirected the connection to correct path and fixed the issue.
psql -U username -p port -h hostPS: Unix domain sockets work on kernel level and the connection doesn't have to go through all the jazz required for TCP connections. They are pretty fast and efficient when you want to make connection to your own machine from a different process as a part of Interprocess Communication.
License under CC-BY-SA with attribution
Content dated before 6/26/2020 9:53 AM
Derek Downey 8 years ago
Not a postgres admin, but a missing socket file sounds about right. Create the /var/pgsql_socket directory (with your user having write permissions) and restart the server. See if that fixes it