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 a compiling issue. I also re-ran initdb /usr/local/var/postgres -E utf8 and the file still exists.

Pleader

How do you start postgres? Are you sure your startscript points to the new version? Because based on the error messages I think both versions are installed side by side now. – Pyrochemical

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start - and the response is server starting – Pleader

When you search for files named pg_ctl I'm pretty sure you're going to find 2 copies. And the one that matches which pg_ctl will be the old version, and the other will be the new version. – Pyrochemical

running pg_ctl --version gives pg_ctl (PostgreSQL) 9.2.4 – Pleader

And yet that copy of pg_ctl seems to be starting a 9.0.4 postmaster... Did you stop postgres before upgrading via homebrew? As it stands you should probably ensure all postgres processes are stopped and redo the installation of 9.2.4 – Pyrochemical

ps aux | grep postgres yields nothing, and as I mentioned, it used to work just fine before - so I doubt that I messed up the installation. You think a reinstallation is the best way to go? – Pleader

yes, as right now a 9.2.4 pg_ctl appears to be starting a 9.0.4 backend, and that's definitely a bad situation! You could try to untangle the mess manually, but reinstalling on top of the preexisting mix of versions somehow seems easier and more bulletproof to do (and as you seem to have just initialized you datadir, your db is empty anyways, so nothing will be lost). – Pyrochemical

the error exists even after reinstallation... I am at my wits end :-( – Pleader

Apple's insane decision to pre-install PostgreSQL and put their pre-installed version on the default PATH just seems to get worse with time... – Ardenia

@CraigRinger really? Apple preinstalls postgresql? – Stephanistephania

@EvanCarroll Sure did then. I think now they've moved it to bundle it inside Server.app and hidden it to use a non-default port and unix socket directory. Finally! – Ardenia

Answer 1

If you recently upgraded postgres to latest version, 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

Note: this won't work for upgrading from 14 to 15 as of recent testing. For postgres version > 14, you should use pg_upgrade directly to upgrade your clusters.

Pentad

This worked like a charm, however, after it worked, it introduced a second error, remember to upgrate the db created by initdb using brew postgresql-upgrade-database then restart postgres – Rothenberg

Worked on 9.5 to 11 as well. Kudos for also showing how to use brew info. – Scramble

This worked like a charm for me after upgrade my postgres from 10 to 11 – Ad

From 10 to 11.5. You are a life saver. <3 – Myasthenia

Works for 11 to 12 as well. – Torsi

Works from 9.5 to 12 !! – Vinosity

Works for 10 to 12.2 as well :) – Hughhughes

Upgrading from 9.2 to 12.3 I’m getting this error when running the above command (which Homebrew also showed to me): Error: No postgresql 9.2.* version installed! – Compel

Can verify that this works fine when moving from 12 to 13 as well. – Fizz

This is one of those "So we meet again" answers I have to look up every 6-12 months. – Mcgary

Confirmed this still worked great upgrading from version 11 to 13. – Besiege

This worked for me with only one caveat: I had to make sure that the postgresql homebrew process was off first. (brew services stop postgresql). Then I would execute, brew postgresql-upgrade-database -- and then of course I would run brew services start postgresql. If I don't do this I get this error, "lock file "postmaster.pid" already exists", which I see in these logs "cat /usr/local/var/log/pg_upgrade_server.log" – Yeeyegg

This is upgrading the entire world of packages from my mac which I'm not even aware of. Woah! I hope that my actual problem is solved though. – Tangible

Works for 13 => 14 as well. – Alfredalfreda

Oh and don't forget to brew services stop postgresql before – Dragonet

"Upgrading postgresql data from 13 to 14 failed" : why might that be? :( When I try to run the server, I get the following errors: "FATAL: database files are incompatible with server", "DETAIL: The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.1.", "FATAL: lock file "postmaster.pid" already exists" – Laevorotation

@Laevorotation - try deleting the lock file "postmaster.pid" and try again? before doing this, please have a backup – Pentad

For me this only worked in a normal terminal window, not my iTerm2 session. Go figure. – Greenway

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.

Nucleate

If the above still doesn't work (which was the case for me), try giving a new data directory name to initdb, e.g. /usr/local/var/postgres95. – Latten

btw, immediately after this you'll probably need to run createuser -s your_rails_app to create the rails' postgres user. See #11919891 – Nucleate

Or if you're nervous like me and want to go nuclear but keep a backup in the bunker just in case, do mv /usr/local/var/postgres /usr/local/var/postgres_backup && initdb /usr/local/var/postgres -E utf8 instead! – Predator

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.

Eldred

Worked perfectly for me with migration from 9.4 to 9.5. – Harney

Also worked for me with migration from 9.3.4 to 9.5.2. – Eustis

Here are the steps for updating 9.5.5 to 9.6.1 using Homebrew (macOS): gist.github.com/giannisp/b53a76047b07751ed3ade3c1db1d2c51 – Georgetown

That should definitely be the best answer!! And no data loss. – Appassionato

Link only answers are frowned upon – Electroscope

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.

Monogamy

I have used the above steps to upgrade from 9.53 to 10.0. The pg_upgrade command has upgraded a bit. The new command is pg_upgrade -b /usr/local/Cellar/postgresql/9.5.3/bin/ -B /usr/local/Cellar/postgresql/10.0/bin -d /usr/local/var/postgres95 -D /usr/local/var/postgres – Axial

better solution below – Erl

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.

Hocuspocus

Answer 6

As @Gowtham mentioned, you can solve this problem by executing a brew command

brew postgresql-upgrade-database The above command is taken from the output of brew info postgres

However, don't forget to stop the postgres service before executing it, use the following command: `brew services stop postgresql

you may have to start the service again.

So the final order of commands are:

brew services stop postgresql
brew postgresql-upgrade-database
brew services start postgresql  

Formulate

Thanks, that was very helpful – Benzyl

PS, don't forget to CLOSE your virtual environment first, if you're using one – Centrum

Answer 7

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

Damaris

what if you need to keep your data? – Hedgehog

Answer 8

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

Eddington

Answer 9

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

Thanatopsis

Answer 10

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

Bibber

Answer 11

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.

Odoric

Answer 12

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

Oppugn

This was useful for me, turns out my issue had to do with conflicting versions of Postgres installed on my Mac – Consciencestricken

Answer 13

It's 2024 and this answer is for some non-computer nerds who are trying to install Postgres again.

if you're receiving this message that means there's already folder in you programFiles with name Postgress exist because you might install before and didn't uninstall properly.

what you can do here is go to C --> Program files --> Postgress and delete and then again try to install it'll work remember to delete anything nfrom c you need to have administrator consent.

Papotto