'data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.0

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?

postgresqldockerdebianportainer


Solution 1:[1]

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.

Solution 2:[2]

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

$ brew postgresql-upgrade-database

Solution 3:[3]

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

Solution 4:[4]

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.

Solution 5:[5]

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.

Solution 6:[6]

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

Solution 7:[7]

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:

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Nyaga Kennedy
Solution 2 ouflak
Solution 3
Solution 4 Mojtaba Jahannia
Solution 5
Solution 6 Rahith RR
Solution 7 nullqube

Related Questions