CODEWITHSUNDEEP
macospostgresqlhomebrew
klaffenboeck
klaffenboeck

Reputation: 6377

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

Upvotes: 370

Views: 223059

Answers (13)

Sameer
Sameer

Reputation: 86

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.

Upvotes: 1

Meekohi
Meekohi

Reputation: 10887

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.

Upvotes: 250

Gowtham
Gowtham

Reputation: 12180

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.

Upvotes: 789

Anna
Anna

Reputation: 123

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

Upvotes: 11

FrankyHollywood
FrankyHollywood

Reputation: 1773

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

Upvotes: 9

olliezhu
olliezhu

Reputation: 796

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

Upvotes: 8

Alessandro
Alessandro

Reputation: 573

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

Upvotes: 1

stevec
stevec

Reputation: 52728

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.

Upvotes: 2

MaJeD BoJaN
MaJeD BoJaN

Reputation: 291

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

Upvotes: 3

anuj kumar
anuj kumar

Reputation: 41

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

Upvotes: 3

Gianluca Musa
Gianluca Musa

Reputation: 805

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.

Upvotes: 14

peresleguine
peresleguine

Reputation: 2403

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.

Upvotes: 12

gdurelle
gdurelle

Reputation: 2030

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.

Upvotes: 42

Related Questions