Postgres - FATAL: database files are incompatible

Question

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.

Answer 1

If you're on Mac and recently upgraded to 11 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

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.