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.

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. — Doon, Oct 24 '13 at 12:46
`netstat -na | grep 5432` return nothing. I'm don't remember how I installed it. Probably homebrew. — Ryan King, Oct 24 '13 at 13:08
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. — the Tin Man, Oct 24 '13 at 14:31

score 23 · Accepted Answer · edited Jan 05 '14 at 17:33

23

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.

edited Jan 05 '14 at 17:33

Ken

184
1
2
15

answered Oct 24 '13 at 13:14

Doon

18,294
3
35
42

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? – Ryan King Oct 24 '13 at 13:20
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. – Doon Oct 24 '13 at 13:33
1

I get no such file or directory for the unload and load commands. – Ryan King Oct 24 '13 at 13:38
brew info: postgresql: stable 9.3.1 http://www.postgresql.org/ Conflicts with: postgres-xc /usr/local/Cellar/postgresql/9.2.3 (2824 files, 39M) * From: https://github.com/mxcl/homebrew/commits/master/Library/Formula/postgresql.rb – Ryan King Oct 24 '13 at 13:39
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 Oct 24 '13 at 14:19
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. – Ryan King Oct 24 '13 at 14:21
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. – Doon Oct 24 '13 at 14:27
Ah I see now. Thanks. – Ryan King Oct 24 '13 at 14:30
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 Oct 24 '13 at 14:41
turns out the dump is empty anyway – Ryan King Oct 24 '13 at 14:46
1

well you need to install /start the new server. – Doon Oct 24 '13 at 15:44
ok finally got it. Thanks. I had to resuse an old dump but not too much was lost in the process.. – Ryan King Oct 25 '13 at 09:14

Peter Alfvin · Answer 2 · 2013-12-14T01:20:45.313

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

score 6 · Answer 3 · answered Dec 17 '13 at 16:22

6

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.

answered Dec 17 '13 at 16:22

GregD

61
1

The Postgres PID file when installed with homebrew is `/usr/local/var/postgres/postmaster.pid` – Stephen Kiningham Oct 09 '16 at 17:10

jewilmeer · Answer 4 · 2013-10-24T14:12:03.887

5

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

edited Oct 24 '13 at 14:12

answered Oct 24 '13 at 12:43

jewilmeer

1,836
1
12
10

Cool, can it find my previous databases? – Ryan King Oct 24 '13 at 12:49
Probably not, especially since your server won't start. – jewilmeer Oct 24 '13 at 14:10
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. – the Tin Man Oct 24 '13 at 14:34
So how do I revert back to the old installation once I find it? – Ryan King Oct 24 '13 at 14:58

score 4 · Answer 5 · edited May 23 '17 at 11:55

4

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

export PGHOST=localhost

edited May 23 '17 at 11:55

Community

1
1

answered Oct 25 '13 at 10:02

Daniel Ristic

710
6
16

score 2 · Answer 6 · answered Jul 22 '14 at 04:54

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

score 1 · Answer 7 · edited May 23 '17 at 10:34

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

edited May 23 '17 at 10:34

Community

1
1

answered Nov 01 '13 at 11:18

Neil Billingham

2,144
4
22
32

bronson · Answer 8 · 2014-06-26T17:02:39.847

If you upgraded using Homebrew, the problem is probably the database format. Check it by running the postgres command directly:

$ postgres
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.2, which is not compatible with this version 9.3.4.

Since my development databases are all seed data, I just blew them away.

$ rm -rf /usr/local/var/postgres
$ initdb

Of course, since this kills everything, you'll need to re-create your roles before rake db:create can succeed.

$ createuser -s MyApp

If you don't know the role name, just run rake db:create. It will tell you.

Lahiru · Answer 9 · 2015-11-21T07:25:19.073

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.

score 0 · Answer 10 · edited May 23 '17 at 11:55

0

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

edited May 23 '17 at 11:55

Community

1
1

answered Nov 11 '14 at 19:37

Obromios

11,158
11
58
98

Repairing postgresql after upgrade to OSX Mavericks

10 Answers10

Linked