Psql: could not connect... Oh fuck you!
Posted on 04/24/2012
My wife was out of town and I wasn't in the mood for Xbox, so I thought I'd resume work on a personal project that I hadn't touched in a while. I cd'd into the project directory, started the rails server, and hit up localhost:3000 in Chrome. That's when I was hit with this lovely little gem:
psql: 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"?
Ahh, fuck. I knew, or at least I thought I knew, that Postgres was running. I had a launch agent set up for it, so it should be running. I consulted the process list...
ps aux | grep postgres
...which returned nothing. Well shit. I tried a quick reboot. No luck. Still not running. Tried to start it manually with:
$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
pg_ctl: another server might be running; trying to start server anyway
server starting
Sure it says "server starting", but what does that really mean? Nothing evidently. After a bit more console hackery and rabid googling, I determined that Postgres was probably in a bad state after one of several recent kernel panics as a result of simultaneously using POW and Cisco's AnyConnect VPN client. This would explain why there was a postmaster pid file, but Postgres wasn't running.
Google told me that my transaction logs were probably in shit-shape, which meant they would need to be cleaned up using pg_resetxlog
like so:
$ pg_resetxlog /usr/local/var/postgres
pg_resetxlog: lock file "/usr/local/var/postgres/postmaster.pid" exists
Is a server running? If not, delete the lock file and try again.
$ rm /usr/local/var/postgres/postmaster.pid
$ pg_resetxlog /usr/local/var/postgres
The database server was not shut down cleanly.
Resetting the transaction log might cause data to be lost.
If you want to proceed anyway, use -f to force reset.
$ pg_resetxlog -f /usr/local/var/postgres
Transaction log reset
Finally, after a manual start up:
$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
server starting
Things were in good working order, and I was able start up the dbconsole and connect without a problem.