Photo-1 Photo-2 Photo-3 Photo-4 Photo-5 Photo-6

@brentmc79

@brentmc79

Full-time web developer. Part-time smart ass.

I'm Brent Collier.

After a year and a half as an engineer on Twitter's Trust & Safety team, I'm looking for my next gig. Contact me if you know of something interesting.

#

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.