Upgrading postgresql can’t be done in-place, you have to setup a new DB with the expected version and restore your content in this DB.
I had a few containers running older versions of postgres lying around (13 and 14), and I wanted to upgrade them to postgresql 15. Here’s the script I came up with.
app_name="app" db_user="user" db_name="db" # Stop the running app(s) # to prevent modifications during the backup & restore docker compose stop $app_name # Dump DB & roles # I'm using custom scripts for this part in order to run # them regularly and export the backups. # Here's what they basically do. docker compose exec -it db \ pg_dump -Fc -U $db_user $db_name > ./db_export/backup.dump docker compose exec -it db \ pg_dumpall --globals-only -U $db_user > ./db_export/roles.sql # Shutdown everything docker compose down # Move current data dir, don't remove it yet mv db_data db_data_OLD # edit docker-compose.yaml - upgrade psql vim docker-compose.yaml # Up the new database & check version docker compose up -d db docker compose exec -it db psql -U $db_user -c 'select version();' # Restore roles # Skip for simple DB where only 1 user, # managed by the docker image, is used docker compose cp ./db_export/roles.sql db:/tmp/roles docker compose exec -it db psql -U $db_user -d $db_name -f /tmp/roles # Restore content docker compose cp ./db_export/backup.dump db:/tmp/backup docker compose exec -it db pg_restore -U $db_user -d $db_name /tmp/backup # Check if content is properly restored: # list tables, make a few queries docker compose exec -it db psql -U $db_user -d $db_name -c "\dt" # Launch app again docker compose up -d docker compose logs -ft # check app still works and has data # Finally remove the old data dir rm -rf db_data_OLD
If your database is not in a container, then
pg_upgrade may be a better option: faster, less hassle and doesn’t require manually dumping and restoring.