how to fix postgres after updating/upgrading brew

Question

I upgraded to mavericks and had some trouble installing/compiling new gems so I reinstalled xcode and did a brew update and upgrade. Gems work now, and even postgres continued to work for a while until a recent reboot. Now postgres seems to be having issues.

postgres:

postgres does not know where to find the server configuration file.
You must specify the --config-file or -D invocation option or set the PGDATA environment variable.


brew info postgres:

postgresql: stable 9.3.2 (bottled)
http://www.postgresql.org/
Conflicts with: postgres-xc
/usr/local/Cellar/postgresql/9.2.4 (2842 files, 39M)
  Built from source
/usr/local/Cellar/postgresql/9.3.2 (2924 files, 39M) *
  Poured from bottle

postgres -D /usr/local/var/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.2.

What should I do now to get my database working again?

score 26 · Answer 1 · edited Jul 22 '14 at 19:15

26

$ brew tap homebrew/versions
$ brew install postgresql92
$ brew switch postgresql 9.2.8 # might need to check this one, could be newer by the time you read this
$ pg_dumpall > ninedottwo-dump
$ brew uninstall postgresql92
$ brew switch postgresql 9.3.4 # again, check version
$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
$ createdb # i got an error about my database not existing, so i had to run this
$ psql < ninedottwo-dump
$ bundle exec rails s

Thanks to Peter for getting me started in the right direction.

edit: this'll teach me to upgrade everything all at once...

edited Jul 22 '14 at 19:15

Marius Butuc

17,781
22
77
111

answered Jan 30 '14 at 15:52

sent1nel

1,580
1
15
31

2

+1 for your edit comment. Also, all you really need if you upgraded by mistake and need to revert is: **rm -rf /usr/local/Cellar/postgresql/9.4...** followed by **brew switch postgresql 9.4....** – Lisandro Jan 25 '15 at 15:49
is this valid for 2016? – SuperUberDuper Feb 29 '16 at 20:48
It seemed to work to bridge the jump between 9.2 and 9.3, and 9.3 and 9.4... Why don't you try backing up your data files, try this procedure, and let us know how it turned out? – sent1nel Mar 02 '16 at 18:05
brew install postgresql92, No available formula with the name "postgresql92" – SuperUberDuper Jul 15 '16 at 14:26
Looks like postgresql92 has been removed from brew. Would you be willing to try 93 or 94 and report back here? https://github.com/Homebrew/homebrew-versions – sent1nel Jul 16 '16 at 20:48
solution is five years old – sent1nel Mar 25 '19 at 16:05

score 5 · Answer 2 · edited Nov 10 '14 at 17:28

You need to get a copy of PostgreSQL 9.2 installed somehow, to be able to access the existing data directory.

Options for installing 9.2 via Homebrew include:

Get a checkout of Homebrew from before the postgresql formula was upgraded to 9.3.
Install postgresql92 from homebrew/versions tap.
Install postgresql-9.2 from petere/postgresql tap (disclosure: that's my project).
Install from source.

Then you ought to upgrade that to 9.3 using either pg_dump or pg_upgrade.

pg_upgrade comes for this very purpose, and using it is straightforward. Just make sure you're specifying the right paths for the old data and binaries ( -d and -b ) and the right paths to the new data and binaries (-D and -B):

pg_upgrade \
-d /usr/local/var/postgres/9.2/ -D /usr/local/var/postgres/9.3/ \
-b /usr/local/Cellar/postgresql/9.2.4/bin/ -B /usr/local/Cellar/postgresql/9.3.2/bin/

If everything goes fine, you can then cleanup old versions with brew cleanup postgresql, and since a new version of PostgreSQL means new versions of the native libraries that are used by the Ruby pg gem, you may want to recompile it:

gem uninstall pg
ARCHFLAGS="-arch x86_64" gem install pg

score 3 · Answer 3 · edited May 23 '17 at 12:02

For 9.2 to 9.5, on El Capitan, I ran into a couple problems which might help others.

It seems now the name of the package used in the switch statement has changed to include the version number (postgresql92 vs postgresql). So, I needed to have the added step of unlinking the current version of postgres:

brew unlink postgresql
brew link postgresql92
brew switch postgresql92 9.2.13 # match version

In order to perform the dump I needed to start the postgres server,

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

But then I ran into the dreaded:

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 check out the logs and found this:

could not open directory "pg_tblspc": No such file or directory

According to this answer by Donovan, Yosemite and El Capitan remove some needed directories. So I also needed to add those directories back in using this awesome command by Nate

mkdir /usr/local/var/postgres/{pg_tblspc,pg_twophase,pg_stat,pg_stat_tmp,pg_replslot,pg_snapshots}/

So the full updated version is:

brew tap homebrew/versions
brew install postgresql92
brew unlink postgresql
brew switch postgresql92 9.2.13 # might need to check this one, could be newer by the time you read this
mkdir /usr/local/var/postgres/{pg_tblspc,pg_twophase,pg_stat,pg_stat_tmp,pg_replslot,pg_snapshots}/
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
pg_dumpall > ninedottwo-dump
pg_ctl -D /usr/local/var/postgres stop
brew unlink postgresql92
brew uninstall postgresql92
brew switch postgresql 9.5.2 # again, check version
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
createdb # i got an error about my database not existing, so i had to run this
psql < ninedottwo-dump
bundle install # need to make sure you have pg gem for 9.5
bundle exec rails s

All credit goes to sentinel, Nate, and Donovan. Thanks so much!

how to fix postgres after updating/upgrading brew

3 Answers3