Upgrade postgres version from docker-compose

upgrade_postgres.md

Make sure postgres is running at the current old version

image: postgres:16-alpine

Pull new image if needed

docker pull postgres:17-alpine

Make sure to map tmp folder from docker postgres to the host

For example:

volumes:
  - ./database/postgres:/var/lib/postgresql/data
  - ./database/tmp:/tmp

From docker postgres old version, backup data with the following command

Entering the container

docker exec -it postgres bash

Exporting data

pg_dumpall -U postgres > /tmp/dump.sql

Make sure to have dump.sql in the host directory!

ls ./database/tmp

Stop database service

docker compose down

Rename as the old postgres binary for backup

For example, if the binary is located in /data/database/postgres

sudo mv -r  /data/database/postgres  /data/database/pg16

Edit docker-compose.yml to latest version, then up the compose

docker compose up -d

Restore your data

Enter the postgres container

docker exec -it postgres bash

Import data back to the database

psql -U postgres -d postgres < /tmp/dump.sql