PostgreSQL Backup and Restore

Use PostgreSQL's logical dump tools to protect data and move it between major versions. The examples assume the source container is named postgres, the database superuser is postgres, and commands run on the Docker host. Replace those names to match your setup.

If the official image was initialized with a different POSTGRES_USER (for example, cubis in the Compose example), use that initial superuser in place of postgres for dump and restore commands.

Back up the whole clusterCopy Anchor Link

pg_dumpall writes one plain SQL script containing every database plus cluster-wide roles and tablespaces. Run it as a database superuser. The shell redirection writes the dump on the host, not inside the container.

backup="postgres-full-$(date +%F).sql"
docker exec --user postgres postgres \
  pg_dumpall --username=postgres --quote-all-identifiers > "$backup"
chmod 600 "$backup"
shasum -a 256 "$backup" > "${backup}.sha256"

Keep the dump and checksum in protected storage outside the Docker host. A dump contains application data and may contain sensitive information. Store encryption keys separately and restrict access to both the dump and its backups. This SQL format is convenient for migration but can take longer to restore than PostgreSQL's archive formats for a single database.

For a consistent whole-cluster migration, put applications into maintenance mode or otherwise stop writes before taking the dump. pg_dumpall takes a separate snapshot for each database, so writes across different databases are not synchronized in one point-in-time snapshot.

Restore into a clean clusterCopy Anchor Link

Restore a full-cluster dump into a newly initialized PostgreSQL cluster. The destination must have enough disk space, and the restoring database role must be a superuser because the script creates roles and databases. Do not restore over a live cluster that contains data you need to keep.

Set backup to the dump you intend to restore; the date expression below is a same-day example and should match the chosen backup's filename.

backup="postgres-full-$(date +%F).sql"
shasum -a 256 -c "${backup}.sha256"
docker exec -i --user postgres postgres18 \
  psql -X --username=postgres --dbname=postgres \
  --set=ON_ERROR_STOP=1 < "$backup"

-X ignores local psql startup settings, and ON_ERROR_STOP makes the restore stop at the first SQL error. If a restore fails, inspect the error and restore into a fresh destination rather than blindly replaying the script against a partially populated cluster.

Check the server version and confirm that expected databases and roles exist:

docker exec --user postgres postgres18 \
  psql -X --username=postgres --dbname=postgres \
  --command='SHOW server_version;'
docker exec --user postgres postgres18 \
  psql -X --username=postgres --dbname=postgres --list
docker exec --user postgres postgres18 \
  psql -X --username=postgres --dbname=postgres --command='\du'

Also verify application health and run representative read/write checks before routing production traffic to the restored cluster. Keep the old cluster and verified backup until the migration has been accepted and the rollback window has closed.

Migrate PostgreSQL 16 to 18 with dump and restoreCopy Anchor Link

A PostgreSQL major-version upgrade requires a migration; changing the image tag alone does not upgrade the data files. This procedure uses a full logical dump and restore, which requires downtime while writes are stopped and the dump is restored.

1. Test the procedure on a recent backup in a non-production environment. Confirm application and extension compatibility with PostgreSQL 18.
2. Stop application writes and scheduled jobs, then create and checksum a final full-cluster dump from PostgreSQL 16 using the backup procedure above.
3. Start PostgreSQL 18 with a new, empty volume. Keep the PostgreSQL 16 container and volume intact. For the official PostgreSQL 18 image, mount the volume at /var/lib/postgresql; its default PGDATA is /var/lib/postgresql/18/docker.
4. Restore the dump into the PostgreSQL 18 cluster and verify the server version, databases, roles, application health, and representative queries.
5. Point the application at PostgreSQL 18 and monitor it. Retain the old cluster and backup until the rollback window ends.

For cross-version migrations, PostgreSQL recommends using dump utilities from the newer major version when possible. The pg_dumpall command above runs the client included in the source container; for a production migration, use PostgreSQL 18 client utilities against the PostgreSQL 16 server if available. PostgreSQL documents the supported version range and migration details in its upgrade guide.

Do not copy PostgreSQL 16's data directory into the PostgreSQL 18 volume. A filesystem-level copy is not a major-version migration. PostgreSQL's official image also changed its recommended mount target in version 18; see the official image documentation before changing an existing Compose deployment.

ReferencesCopy Anchor Link

• PostgreSQL 18: Upgrading a PostgreSQL Cluster
• PostgreSQL 18: pg_dumpall
• PostgreSQL 18: SQL Dump
• Official PostgreSQL Docker image