Repairing postgresql after upgrade to OSX Maverick

问题:

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.

回答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.

回答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

回答3:

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.

回答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

回答5:

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

export PGHOST=localhost

回答6:

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

回答7:

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

回答8:

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.

回答9:

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

回答10:

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.