Repairing postgresql after upgrade to OSX Maverick

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 install via the .dmg you can just reinstalled from the Postgres installer (postgresql-9.1.3-1-osx.dmg in my case). A reboot might be required too. I did this just yesterday - my actually databases didn't get deleted and all is fine now.

A similar thing happened going from Snow Leopard to Mountain Lion too:

Mountain Lion Postgres could not connect

Answer 2

In case you have found this page after upgrading from Yosemite, then a different approach is needed to solve the problem. Essentially the upgrade to Yosemite destroys some files. You can find the problem and solution here!.

Answer 3

I recently had this issue happen to me when I upgraded to 10.9.4

After a day of trial and error, I found this post by Joho which fixed my problems: https://gist.github.com/joho/3735740

Make sure you change the versions to match your installation versions. If you have multiple versions installed like I did, you can see which versions by accessing cd /usr/local/Cellar/postgresql Then do the steps below but change the version numbers to match your installs.

In Summary:

$ launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
$ mv /usr/local/var/postgres /usr/local/var/postgres9.2
$ brew update
$ brew upgrade postgresql
$ initdb /usr/local/var/postgres -E utf8
$ pg_upgrade -b /usr/local/Cellar/postgresql/9.2.1/bin -B /usr/local/Cellar/postgresql/9.3.4/bin -d /usr/local/var/postgres9.2 -D /usr/local/var/postgres
$ cp /usr/local/Cellar/postgresql/9.3.4/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/
$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

If you're using ruby, also:

$ gem pristine pg

I hope this helps! It did for me

Answer 4

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 5

Had the same problem. Found that the upgrade killed the processes hard and left the postgres pid file out there. So, don't be a dumbazz like me and click on the restart to upgrade your OSX. Make sure you shutdown everything cleanly before the upgrade.

Answer 6

After installing PostgreSQL using homebrew for OS X Mavericks, this worked for me:

sudo ARCHFLAGS="-arch x86_64" gem install pg

If that's not the case, then this command has fixed the issue for in my case once it occurred again after upgrade OS to OS X Yosemite and OS X El Capitan

sudo PATH=$PATH:/Applications/Postgres.app/Contents/Versions/[Version-Number]/bin gem install pg -v [gem version]

Also, Make sure you have updated your Xcode and command line tools for Xcode.