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.

Solution 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.

Solution 2

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.

Solution 3

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

Solution 4

The simplest solution is to use http://postgresapp.com which just works

To install via homebrew, make sure to do brew update to get homebrew working again

Solution 5

Try to add this to your .bash_profile as stated here:

export PGHOST=localhost

View more solutions

Comments

[Doon]

how did you install postgresql on localhost? It doesn't appear to be running, (netstat -na | grep 5432 to check if it is listening. But knowing how you got it installed will help in figuring out how to get it started agai.

[Ryan King]

netstat -na | grep 5432 return nothing. I'm don't remember how I installed it. Probably homebrew.

[PJP]

PostgreSQL is supposed to be installed by Apple on Mac OS lately. You could be piggybacking on that installation, which seems likely if installing the OS upgrade broke it. It shouldn't break if you'd installed it ourself.

[Ryan King]

Cool, can it find my previous databases?

[Ryan King]

Awesome, looks like I did use homebrew - brew info postgresql returns postgresql: stable 9.3.1... How do I point it to the PGDATA driectory?

[Doon]

Well does it show as being installed something like usr/local/Cellar/postgresql/9.3.1 (2919 files, 39M) * Built from source or brew list | grep postgresql to make sure it is installed, if it is then just doing the load /unload on the launchctl should bring it back, no need to point the PGDATA, as it should already been done by the launch scripts.

[Ryan King]

I get no such file or directory for the unload and load commands.

[Ryan King]

brew info: postgresql: stable 9.3.1 postgresql.org Conflicts with: postgres-xc /usr/local/Cellar/postgresql/9.2.3 (2824 files, 39M) * From: github.com/mxcl/homebrew/commits/master/Library/Formula/…

[Posiew]

Probably not, especially since your server won't start.

[Ryan King]

This is what i get with postgres -D /usr/local/var/postgres LOG: database system was shut down at 2013-10-25 01:09:16 EST LOG: autovacuum launcher started LOG: database system is ready to accept connections

[Ryan King]

After this nothing happens. If I ctrl+c out of it I can't do the dump as it cant connect to the server. I'm leaving it running for now to see if there's any changes.

[Doon]

When it is running, open another command prompt and do the dump. Or you can you use postgres -D /usr/local/var/postgres & to make it run in the background.

[Ryan King]

Ah I see now. Thanks.

[PJP]

It's not hard to find the old server installation using find and/or locate. Look for the pg.conf file or pg_config and nose around in them.

[Ryan King]

I get this again when running psql < ~/mydatabases.dump : 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"?

[Ryan King]

turns out the dump is empty anyway

[Ryan King]

So how do I revert back to the old installation once I find it?

[Doon]

well you need to install /start the new server.

[Ryan King]

ok finally got it. Thanks. I had to resuse an old dump but not too much was lost in the process..

[Stephen Kiningham]

The Postgres PID file when installed with homebrew is /usr/local/var/postgres/postmaster.pid