Postgresql Backup and Restore in Docker

Make pg backup #

docker exec -e PGPASSWORD='<SuperSecure>' <ContainerName> \
  pg_dump -U <PostgresRole> -d <PostgresDB> -Fc -f /tmp/database.dump

Copy backup to root #

docker cp <ContainerName>:/tmp/database.dump /root/database.dump

Stop and remove old container #

docker stop <ContainerName>
docker rm <ContainerName>

Remove old database files #

rm -rf /mnt/<PostgresDBDatadir>/
mkdir /mnt/<PostgresDBDatadir>/
chown 999 /mnt/<PostgresDBDatadir>/
chmod 700 /mnt/<PostgresDBDatadir>/

Start new container #

docker run \
  --name <ContainerName> \
  --detach \
  --restart unless-stopped \
  --network <ContainerNetwork> \
  --volume /mnt/<PostgresDBDatadir>:/var/lib/postgresql/data \
  --env POSTGRES_DB=<PostgresDB> \
  --env POSTGRES_USER=<PostgresRole> \
  --env POSTGRES_PASSWORD=<SuperSecure>
  docker.io/library/postgres:17

Copy backup to new container #

docker cp /root/database.dump <ContainerName>:/tmp/database.dump

Verify role and db #

docker exec -e PGPASSWORD='<SuperSecure>' <ContainerName> \
  psql -U <PostgresRole> -c "CREATE ROLE <PostgresRole> WITH LOGIN PASSWORD '<SuperSecure>';"
docker exec -e PGPASSWORD='<SuperSecure>' <ContainerName> \
  psql -U <PostgresRole> -c "CREATE DATABASE <PostgresDB> OWNER <PostgresRole>;"

Restore backup #

docker exec -e PGPASSWORD='<SuperSecure>' <ContainerName> \
  pg_restore -U <PostgresRole> -d <PostgresDB> -Fc /tmp/database.dump