Upgrade a PostgresSQL container

Export the current data

Stop the stack and any containers that depend on the database

docker compose down

Start the database container only

docker container start pg_sql

Backup the database to a SQL file

docker exec -it pg_sql pg_dumpall -U postgres > $HOME/backup.sql

Write a script to extract the desired database

#!/bin/bash
[ $# -lt 2 ] && { echo "Usage: $0 <postgresql dump> <dbname>"; exit 1; }
sed "/connect.*$2/,\$!d" $1 | sed "/PostgreSQL database dump complete/,\$d"

Run the script

chmod +x ./pg_extract.sh

./pg_extract.sh ./backup.sql mydb >> ./backup-mydb.sql

Update PostgreSQL

Either run a different container, or edit the compose file accordingly.

Start the container

Connect to the database and check the new version is correct

select version()

Import the data

Import the data into the new database

cat $HOME/backup.sql | docker exec -i pg_sql psql -U postgres

Finally, restart any contains which depend on this database.

References

Thomas Bandt