64

I have downloaded Portainer onto my server and created a PostgreSQL database in a container there. Today I could no longer get access to the database. The log shows a message that there is a version problem.

I already read into some similar issues like Postgres container crashes with `database files are incompatible with server` after container's image has been updated to the latest one and Postgres container crashes with `database files are incompatible with server` after container's image has been updated to the latest one

and the solutions brew postgresql-upgrade-database did not work.

What can I do?

LOG

2021-10-03  [1] FATAL:  database files are incompatible with server
2021-10-03  [1] DETAIL:  The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.0 (Debian 14.0-1.pgdg110+1).

PostgreSQL Database directory appears to contain a database; Skipping initialization

I also found this https://www.postgresql.org/docs/14/upgrading.html but the commands didn't work. Do I need to do this in the container somehow, or what commands will work to keep it running in the container?

Mr. Hankey
  • 954
  • 1
  • 5
  • 12
  • 1
    Do you also have something like watchtower to update the containers to their latest version? I would use `postgres:13` image (if your are getting postgres from docker.io) to stay on version 13. – Steeeve Oct 03 '21 at 12:18
  • I don't think I have one of those. Can you tell me what I should do? – Mr. Hankey Oct 03 '21 at 13:31
  • You need to upgrade the database to v14. Please read the documentation. – Laurenz Albe Oct 04 '21 at 10:55
  • In my case kubernetes mounted an old postgres volume which already had some data. I just had to delete the old volume to fix the issue. – ns15 Aug 17 '22 at 17:42

10 Answers10

34

You need to update the data file to the new format with this command:

$ brew postgresql-upgrade-database
ouflak
  • 2,458
  • 10
  • 44
  • 49
Hank Li
  • 341
  • 3
  • 3
  • 4
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Feb 12 '22 at 14:05
  • 1
    This method worked great. It automatically installed postgres@13 and ran upgrade procedure. – Dmitry Serebrennikov Jun 12 '22 at 21:01
  • 1
    Agree with the bot, this needs more detail. Is this not a reference to Homebrew, thus assuming that the user is on macOS? Why would you think that `brew` is the best option? – Excel Help May 09 '23 at 05:43
19

I resolved it by

  1. Removing the postgres image
  2. Remove the volume
  3. Pull the image again

Assuming you know the docker commands for above step.

Nyaga Kennedy
  • 453
  • 4
  • 11
7

On top of the answer suggesting removing all volumes/images/containers - if you have a shared volume for the DB in docker-compose.yml, like:

db:
  image: postgres:14.1-alpine
  volumes:
    - ./tmp/db:/var/lib/postgresql/data

You will also need to remove the postgres mapped volume data which lives in ./tmp/db. Otherwise those conflicting files would still be there.

If you don't care about the data - you use the database for development purposes and you'll be able to re-create the db easily, just run rm -r ./tmp/db. Than you can just docker-compose up and re-create your database.

In case you care about the data, use pg_dumpall to dump you data before removing the files and restore after you run docker-compose up and your postgres service is ready again.

Jacka
  • 2,270
  • 4
  • 27
  • 34
5

In docker-compose.yml you could change

db-data:/var/lib/postgresql/data:rw

to this

db-data:/var/lib/postgresql@14/data:rw

Delete image and run

docker-compose up -d

one more time

FFinn
  • 2,615
  • 2
  • 13
  • 6
4

had a problem after running

brew update
brew upgrade

Brew upgraded postgresql to 14 which gave me

psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

I have also seen the same error you have by inspecting

tail /usr/local/var/postgres/server.log

I have downgraded to version 13 running

brew uninstall postgresql
brew install postgresql@13
echo 'export PATH="/usr/local/opt/postgresql@13/bin:$PATH"' >> ~/.zshrc

and the command I've used to start the DB again is

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

copser
  • 2,523
  • 5
  • 38
  • 73
3

I think because of the difference postgresql13 with 14 in initialization. You can make a backup of your database before the new version, delete it and migrate database to the new version

for example in django:

dump: ./manage.py dumpdata -o mydata.json
load: ./manage.py loaddata mydata.json

django: Of course, if you have important information, do this separately for each model and pay attention to the dependencies when loading.

Mojtaba Jahannia
  • 39
  • 2
1

I resolved it by

  1. Removing the postgres container
    docker rm "container name"
  2. Removing the postgres image
    docker rmi "image name"
  3. docker-compose stop docker-compose down
  4. list all volumes
    docker volume ls
  5. remove volume
    docker volume rm "volume name"

Pull the image again Assuming you know the docker commands for above step.

Abakar
  • 21
  • 2
  • Ideally we want to persist whatever data is in the volume between version upgrades. I suggest considering the answers that upgrade db reusing the existing data dir. – Patrick Mutuku Aug 05 '23 at 00:16
0

I was also facing the same issue with postgres in keycloak. Downgrading the version to 13 resolved my issue.

Rahith R R
  • 129
  • 2
  • 13
0

for me the database directory was in /tmp/db and since no important data was there I remove all and everything worked well ...

but if your data is important then read these:

nullqube
  • 2,959
  • 19
  • 18
0

I had the same issue after home brew update on my mac - Install @14 of postgresql

This is what help to me:

Install old binaries v13

brew install postgresql@13

Backup Your old DB folder

Initdb with new name

now use pg_upgrade like this:

pg_upgrade --old-datadir [Your old DB folder] --new-datadir [Your new DB folder] --old-bindir [link to old bin (v13)]

for example:

pg_upgrade --old-datadir PSQ-data --new-datadir PSQ-data_new --old-bindir /usr/local/Cellar/postgresql@13/13.9/bin

Source for old bin You will have after install @13, just look what you receive on terminal after install "Summary".

After this operation just start sever with new DB

takelbery
  • 19
  • 1