Repairing postgresql after upgrade to OSX Mavericks

Question

A recent upgrade to OSX Mavericks has broken my database connection for my Rails app.

When I try to fetch from the database the server returns the following error:

PG::ConnectionBad (could not connect to server: Connection refused     Is the server running on host "localhost" (::1) and accepting     TCP/IP connections on port 5432? could not connect to server: Connection refused     Is the server running on host "localhost" (127.0.0.1) and accepting     TCP/IP connections on port 5432? could not connect to server: Connection refused     Is the server running on host "localhost" (fe80::1) and accepting     TCP/IP connections on port 5432? 

When try to run psql I get:

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"? 

---

I've tried many of the solutions available on the internet. Such reinstalling the pg gem and setting host: localhost in my database.yml. My /usr/local/var/postgres/pg_hba.conf file says:

# TYPE  DATABASE        USER            ADDRESS                 METHOD  # "local" is for Unix domain socket connections only local   all             all                                     trust # IPv4 local connections: host    all             all             127.0.0.1/32            trust # IPv6 local connections: host    all             all             ::1/128                 trust # Allow replication connections from localhost, by a user with the # replication privilege. #local   replication     RyanKing                                trust #host    replication     RyanKing        127.0.0.1/32            trust #host    replication     RyanKing        ::1/128                 trust 

which psql returns: /usr/local/bin/psql

---

Any solutions on this one? Some solutions suggest I need to change my $PATH to my previous postgres installation as a new version of postgres would be added with Mavericks. How do I find where that is located? It's quite possible it was installed with homebrew but I'm not certain.

Answer 1

if you installed it via homebrew, try this (from brew info postgresql)

launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist 

Which will reload it. Postgress.app my default will not find your databases (you would need to point it to the PGDATA directory, and you might run into version conflicts (if you where running 9.2 and postgress.app is 9.3, a dump /restore would be in order.

pulling from comments.

ok so it looks like you have 9.2.3 installed, what I would do is this.

postgres -D /usr/local/var/postgres  

Then back them all up. pg_dumpall > ~/mydatabases.dump

kill postgres

a brew reinstall postgresql , but warning this will bring you from 9.2.3 to 9.3.1. Then reimport all your databases psql < mydatabaes.dump. Making sure to follow the directions on the unload/load for the launchctl stuff, and at that point you should be good. You could also look at using postgress.app and importing your databases into that app, but you would need to backup/dump your database anyway.

Answer 2

tl;dr Just start up the Postgres application!

FWIW, I had the exact same experience after my Mavericks upgrade and after:

• Installing and starting up 9.3 (appeared as Postgres93 in my Application folder)
• Moving 9.2 to the trash
• Seeing Rails fail because my databases weren't migrated

I then went back and:

• Quit 9.3
• Restored 9.2 from the trash
• Started up 9.2

and everything worked fine!

I then realized that

• The problem was that Postgres hadn't been run after my Mavericks install
• I'd never done anything explicit before to cause Postgres to be run at startup, but ...
• Mac OS was just always restarting Postgres after a reboot because it was running previously