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 initializationI also found this 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?
411 Answers
You need to update the data file to the new format with this command:
$ brew postgresql-upgrade-database I resolved it by
- Removing the postgres image
- Remove the volume
- Pull the image again
Assuming you know the docker commands for above step.
5On 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/dataYou 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.
In docker-compose.yml you could change
db-data:/var/lib/postgresql/data:rwto this
db-data:/var/lib/postgresql@14/data:rwDelete image and run
docker compose up -done more time
1had a problem after running
brew updatebrew 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 postgresqlbrew install postgresql@13echo '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
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.jsondjango: Of course, if you have important information, do this separately for each model and pay attention to the dependencies when loading.
Step-by-step guide to update Postgres and keep the data when using Docker.
Example below was tested while upgrading from v14 to v15 and then to v16.
Database user is joplin, using following docker-compose:
version: '3.5'
services: db: image: postgres:15 container_name: postgres volumes: - ./postgres-data:/var/lib/postgresql/data# while container is running, make a database dump
docker exec postgres pg_dumpall -U joplin > dump.sql
# stop the container
docker-compose down
# make a backup of the database data
mv postgres-data postgres-data.bak
# create a new directory to store database
mkdir postgres-data
# step up db version to `16` or `latest`
# nano ./docker-compose.yml
# update the image and start the `db` container
docker-compose pull && docker-compose up -d db
# copy recently created dump
cp ./dump.sql ./postgres-data/dump.sql
# get into the `db` container
docker exec -it postgres bash
# inside `db` container
cd /var/lib/postgresql/data/
# restore the database dump
psql -U joplin < dump.sql
# that's it, get out of the container
exitI was also facing the same issue with postgres in keycloak. Downgrading the version to 13 resolved my issue.
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:
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
I resolved it by
- Removing the postgres container
docker rm "container name" - Removing the postgres image
docker rmi "image name" - docker-compose stop docker-compose down
- list all volumes
docker volume ls - remove volume
docker volume rm "volume name"
Pull the image again Assuming you know the docker commands for above step.
1
