Postgres - FATAL: database files are incompatible with server

After restarting my MacBook Pro I am unable to start the database server:

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 checked the logs and the following line appears over and over again:

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

9.0.4 was the version that came preinstalled on the mac, 9.2[.4] is the version I installed via Homebrew. As mentioned, this used to work before the restart, so it can't actually be an compiling issue. I also re-ran initdb /usr/local/var/postgres -E utf8 and the file still exists.

Unfortunately, I am pretty new to Postgres, so any help would be very much appreciated.

If you recently upgraded to 11 or 12 from 10.x you can run the below command to upgrade your postgres data directory retaining all data:

brew postgresql-upgrade-database

The above command is taken from the output of brew info postgres

update: this should work for upgrading to postgres 14 as well.

If you are looking for the nuclear option (delete all data and get a fresh database), you can do:

rm -rf /usr/local/var/postgres && initdb /usr/local/var/postgres -E utf8

and then you'll need to rake db:setup and rake db:migrate from your Rails app to get setup again.

Try this : https://gist.github.com/joho/3735740

It worked perfectly for me. In the end it also generates you 2 bash scripts to check your DB and remove the old cluster. Really Awesome.

see: http://www.postgresql.org/docs/9.2/static/pgupgrade.html to understand more.

Found on internet, this solution work fine for me.

When I tried to start postgresql server after upgrade to OS X 10.10 Yosemite, I encountered with a next problem:

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

could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

Okay, lets take a look into server logs:

cat /usr/local/var/postgres/server.log

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

So, we need to follow a few steps after upgrade postgresql:

launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

mv /usr/local/var/postgres /usr/local/var/postgres92

brew update

brew upgrade postgresql

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

pg_upgrade -b /usr/local/Cellar/postgresql/9.2.3/bin -B /usr/local/Cellar/postgresql/9.3.5_1/bin -d /usr/local/var/postgres92 -D /usr/local/var/postgres

cp /usr/local/Cellar/postgresql/9.3.5_1/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/

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

rm -rf /usr/local/var/postgres92

That's it.

If you want to keep the previous version of postgres, use brew switch:

$ brew info postgresql

postgresql: stable 10.5 (bottled), HEAD
Object-relational database system
https://www.postgresql.org/
Conflicts with:
  postgres-xc (because postgresql and postgres-xc install the same binaries.)
/usr/local/Cellar/postgresql/9.6.3 (3,259 files, 36.6MB)
  Poured from bottle on 2017-07-09 at 22:15:41
/usr/local/Cellar/postgresql/10.5 (1,705 files, 20.8MB) *
  Poured from bottle on 2018-11-04 at 15:13:13

$ brew switch postgresql 9.6.3
$ brew services stop postgresql
$ brew services start postgresql

Otherwise, consider this brew command to migrate existing data: brew postgresql-upgrade-database. Check out the source code.