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

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

$ brew postgresql-upgrade-database 

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

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.

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.

Step-by-step guide to update Postgres and keep the data when using Docker.

Example below was tested while upgrading from v14 to v15, then to v16 and to v17. 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
exit

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

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.

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:

• https://www.postgresql.org/docs/13/upgrading.html
• https://www.postgresql.org/docs/13/pgupgrade.html
• https://www.kostolansky.sk/posts/upgrading-to-postgresql-14/

I had the same issue after home brew update on my mac. Install v14 of postgresql.

This is what helped me:

Install old v13 binaries:

brew install postgresql@13

Backup Your old DB folder:

Initdb with new name

now use pg_upgrade:

pg_upgrade --old-datadir [Your old DB folder] --new-datadir [Your new DB folder] --old-bindir [link to old bin (v13)]

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 v13, just look at your terminal "Summary" after install.

After this operation just start the sever with new DB.

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.

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