Question 47103 in Debian Question posted in
The oficial documentation can be found here.

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

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?

Tags:

10

Answers


  1. 0

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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
  4. 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:

    Login or Signup to reply.
  5. 0

    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.

    Login or Signup to reply.
  6. 0

    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

    Login or Signup to reply.
  7. 0

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

    $ brew postgresql-upgrade-database
    Login or Signup to reply.
  8. 0

    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

    Login or Signup to reply.
  9. 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

    Login or Signup to reply.
  10. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search