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

Question

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?

Answer 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.

Answer 2

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

$ brew postgresql-upgrade-database 

Answer 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

Answer 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.

Answer 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.