Homebrew installed PostgreSQL could not connect, database files are incompatible with server

If you don't have any data, then it seems like a problem with Brew. Did you run brew upgrade, there are two answers on StackOverflow that address this, I've merged both of them together to try the best of both worlds.

  1. Backing up

    launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
sudo mv /usr/local/var/postgres /usr/local/var/.postgres_bak;

  2. Making sure you have the latest version

    brew update
brew upgrade postgresql

  3. Either, not both reinitialize or try to upgrade your data-dir

    1. Nuke it (start from scratch, leaving the old back up)

      initdb /usr/local/var/postgres -E utf8

    2. Try to upgrade it

      pg_upgrade -b /usr/local/Cellar/postgresql/$myOldVersion/bin/ -B /usr/local/Cellar/postgresql/$myNewVersion/bin -d /usr/local/var/.postgres_bak -D /usr/local/var/postgres

  4. Restart server.

    brew services start postgresql

  5. Delete the backup, if the server restarts and everything works remove the backup

    sudo rm -rf /usr/local/var/.postgres_bak

Hopefully you haven't run brew cleanup postgresql yet. brew info gives all the relevant info, including if you still have the previous version and the fact you might need to run pg_upgrade (shortened output below).

This will tell you what version your db is on: cat /usr/local/var/postgres/PG_VERSION

If you didn't mean to upgrade to PostgreSQL 10.1, and assuming your previous version is 9.6.6, this could be enough: brew switch postgresql 9.6.6

If you need to reinstall an older version, this could work: https://stackoverflow.com/a/9832084/279726

It looks like the data directory is /usr/local/var/postgres by default (as I haven't changed mine), see last line of output below.

$ brew info postgresql
postgresql: stable 10.1 (bottled), HEAD
[...]
==> Caveats
To migrate existing data from a previous major version of PostgreSQL, see:
  https://www.postgresql.org/docs/10/static/upgrading.html

  You will need your previous PostgreSQL installation from brew to perform
  `pg_upgrade` or `pg_dumpall` depending on your upgrade method.

  Do not run `brew cleanup postgresql` until you have performed the migration.

To have launchd start postgresql now and restart at login:
  brew services start postgresql
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/postgres start
$

But if you do want to use 10.1, basically you could run something like this, still assuming you were running 9.6.6 before (change directory name based on your actual previous PostgreSQL version). Run one command at a time and check the output before proceeding with the next:

mv -i /usr/local/var/postgres /usr/local/var/postgres96
mkdir /usr/local/var/postgres
pg_upgrade -b /usr/local/Cellar/postgresql/9.6.6/bin -B /usr/local/Cellar/postgresql/10.1/bin -d /usr/local/var/postgres96 -D /usr/local/var/postgres

See here for details: https://www.postgresql.org/docs/10/static/pgupgrade.html

********************************************************
* PLEASE MAKE BACKUPS BEFORE DOING ANY OF THE ABOVE!!! *
********************************************************

The funny thing is, if I try to install the latest PostgreSQL I get:

$ brew install postgresql
Error: postgresql 9.5.5 is already installed
To upgrade to 10.1, run `brew upgrade postgresql`

So I'm not sure how you got from 9.6.x to 10.1 without seeing that message...

Tags:

Postgresql

Psql

Mac Os X

Recent Posts