Postgres - FATAL: database files are incompatible with server

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

Answer 2

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 3

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 4

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.

Answer 5

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 6

brew info postgres will give you hints Like To migrate existing data from a previous major version of PostgreSQL run:

So in my case removing the old one rm -rf /usr/local/var/postgres.old and upgrading the DB brew postgresql-upgrade-database

Answer 7

Similar to these answers (1, 2), my Postgres database files were incompatible to my Postgres version after upgrading to postgresql 13.3.

Unfortunately, upgrading my Postgres data directory failed.

$ brew postgresql-upgrade-database
...
Setting next OID for new cluster
*failure*

Consult the last few lines of "pg_upgrade_utility.log" for
the probable cause of the failure.
Failure, exiting
Error: Upgrading postgresql data from 12 to 13 failed!
==> Removing empty postgresql initdb database...
==> Moving postgresql data back from /usr/local/var/postgres.old to /usr/local/var/postgres...
Error: Failure while executing; `/usr/local/opt/postgresql/bin/pg_upgrade -r -b /usr/local/Cellar/postgresql@12/12.7/bin -B /usr/local/opt/postgresql/bin -d /usr/local/var/postgres.old -D /usr/local/var/postgres -j 8` exited with 1.

My workaround for this was to reinstall postgresql 12.7.

$ brew reinstall postgresql@12
$ brew services start postgresql@12

Answer 8

It happened for me when I was trying to start Postgres12 with postgres11 mounted volume. Just deleting the mounted volume for postgres11 and restart worked for me.

Previously I was using:

docker run -d --name my_database -v /Users/champ/postgres:/var/lib/postgresql/data -p 54320:5432 postgres:11

I deleted /Users/champ/postgres and restarted postgres 12, using

docker run -d --name my_database -v /Users/champ/postgres:/var/lib/postgresql/data -p 54320:5432 postgres:12

Answer 9

A stale postmaster.pid file caused this for me.

Simply navigate to your postgres directory /Users/st/Library/Application Support/Postgres/, for me, that's:

cd '/Users/<username>/Library/Application Support/Postgres/var-10'

Then delete the file postmaster.pid. Restart postgres and it will work.

Answer 10

This can also occur when running a new Postgres in Docker, and your old volume isn't updated.

If you don't need to keep your data easiest is to clear the old volumes in the docker folder, in Linux that's here:

/var/lib/docker/volumes

Answer 11

To me works with this command:

brew install --build-from-source postgresql@12

Then started Postgres:

brew services start postgresql@12

You can also check the port 5432 is listen:

netstat -nl |grep 5432