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.

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. — fvu, Jul 24 '13 at 00:03
`pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start` - and the response is `server starting` — klaffenboeck, Jul 24 '13 at 00:05
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. — fvu, Jul 24 '13 at 00:08
running `pg_ctl --version` gives `pg_ctl (PostgreSQL) 9.2.4` — klaffenboeck, Jul 24 '13 at 00:19
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 — fvu, Jul 24 '13 at 00:23
`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? — klaffenboeck, Jul 24 '13 at 00:34
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). — fvu, Jul 24 '13 at 00:41
the error exists even after reinstallation... I am at my wits end :-( — klaffenboeck, Jul 24 '13 at 00:52
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... — Craig Ringer, Jul 24 '13 at 02:20
@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! — Craig Ringer, Dec 13 '17 at 04:09

Gowtham · Accepted Answer · 2023-01-16T15:24:41.590

784

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.

edited Jan 16 '23 at 15:24

answered Dec 12 '18 at 14:58

Gowtham

11,853
12
43
64

3

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 – Shemogumbe Jul 03 '19 at 10:50
Worked on 9.5 to 11 as well. Kudos for also showing how to use `brew info`. – Yacc Jul 08 '19 at 09:20
This worked like a charm for me after upgrade my postgres from 10 to 11 – Yudi Yohanes Septian Gotama Aug 09 '19 at 07:02
3

From 10 to 11.5. You are a life saver. <3 – Thomas Nov 22 '19 at 12:53
8

Works for 11 to 12 as well. – stevex Dec 11 '19 at 16:31
1

Works from 9.5 to 12 !! – Augusto Samamé Barrientos Jan 23 '20 at 18:35
Works for 10 to 12.2 as well :) – EMiDU Mar 12 '20 at 16:01
1

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!` – Frederik Jul 13 '20 at 08:31
10

Can verify that this works fine when moving from 12 to 13 as well. – Cyclonecode Oct 20 '20 at 19:14
95

This is one of those "So we meet again" answers I have to look up every 6-12 months. – seniorpreacher Nov 11 '20 at 13:27
Confirmed this still worked great upgrading from version 11 to 13. – ityler22 Jan 09 '21 at 19:53
6

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" – robskrob Apr 03 '21 at 14:45
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. – Amey Lokhande May 11 '21 at 05:06
8

Works for 13 => 14 as well. – jonschlinkert Oct 21 '21 at 05:11
3

Oh and don't forget to `brew services stop postgresql` before – Séraphin Oct 26 '21 at 19:51
"`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`" – payne Dec 09 '21 at 04:33
@payne - try deleting the lock file "postmaster.pid" and try again? before doing this, please have a backup – Gowtham Dec 09 '21 at 18:20
For me this only worked in a normal terminal window, not my iTerm2 session. Go figure. – David Corbitt Jan 12 '22 at 07:24

score 247 · Answer 2 · answered Dec 04 '15 at 16:08

247

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.

answered Dec 04 '15 at 16:08

Meekohi

10,390
6
49
58

3

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. – mpelzsherman Feb 01 '16 at 13:02
btw, immediately after this you'll probably need to run `createuser -s your_rails_app` to create the rails' postgres user. See https://stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist/25263322 – Meekohi Dec 20 '17 at 18:39
7

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! – lucas Sep 30 '20 at 15:27

gdurelle · Answer 3 · 2013-08-05T10:37:04.267

41

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.

edited Aug 05 '13 at 10:37

answered Aug 05 '13 at 10:18

gdurelle

2,079
22
38

2

Worked perfectly for me with migration from 9.4 to 9.5. – tftdias Feb 02 '16 at 11:05
Also worked for me with migration from 9.3.4 to 9.5.2. – Steve Jorgensen Apr 12 '16 at 02:33
1

Here are the steps for updating 9.5.5 to 9.6.1 using Homebrew (macOS): https://gist.github.com/giannisp/b53a76047b07751ed3ade3c1db1d2c51 – Giannis Nov 18 '16 at 06:09
That should definitely be the best answer!! And no data loss. – FlorianB Dec 04 '16 at 21:10
1

Link only answers are frowned upon – oligofren Jan 19 '21 at 21:14

score 13 · Answer 4 · edited Feb 12 '19 at 13:40

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.

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 — techvineet, Oct 27 '17 at 09:44

score 11 · Answer 5 · answered Dec 10 '18 at 11:50

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.

score 10 · Answer 6 · answered Dec 21 '21 at 19:05

10

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

answered Dec 21 '21 at 19:05

Anna

113
1
7

Thanks, that was very helpful – Vitor Reis Dec 21 '21 at 19:07
PS, don't forget to CLOSE your virtual environment first, if you're using one – Heartthrob_Rob Feb 09 '22 at 20:22

score 7 · Answer 7 · answered Jul 05 '21 at 08:18

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

answered Jul 05 '21 at 08:18

FrankyHollywood

1,497
19
18

3

what if you need to keep your data? – Dmitry Efimenko Jul 23 '22 at 19:44

score 6 · Answer 8 · answered May 27 '21 at 18:08

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

score 2 · Answer 9 · edited Oct 04 '19 at 08:57

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

score 2 · Answer 10 · answered Oct 27 '20 at 06:56

2

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

answered Oct 27 '20 at 06:56

MaJeD BoJaN

261
2
6

score 0 · Answer 11 · answered Feb 26 '21 at 08:41

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.

score 0 · Answer 12 · answered Feb 27 '21 at 17:30

0

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

answered Feb 27 '21 at 17:30

Alessandro

543
5
6

This was useful for me, turns out my issue had to do with conflicting versions of Postgres installed on my Mac – legel Oct 07 '21 at 19:44

Postgres - FATAL: database files are incompatible with server

12 Answers12

Linked