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?
I was also facing the same issue with postgres in keycloak. Downgrading the version to 13 resolved my issue.
I resolved it by
Assuming you know the docker commands for above step.
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.
You need to update the data file to the new format with this command:
$ brew postgresql-upgrade-database
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:
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.
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