PostgreSQLintermediate

PostgreSQL Backup

Logical dumps with pg_dump and physical backups with pgBackRest — what each is for, and how to verify the result.

2 min readIntermediateUpdated 31 Jul 2026Edit this page

PostgreSQL has two backup families: logical dumps, which produce a portable representation of the data, and physical backups, which copy the data files and combine with archived WAL for point-in-time recovery.

Backup with pg_dump

pg_dump produces a consistent snapshot of one database, taken inside a single transaction, while the database keeps serving traffic.

# Custom format: compressed, and restorable selectively and in parallel.
pg_dump --host=db.internal --username=backup --dbname=shop \
        --format=custom --compress=9 --jobs=4 \
        --file=/backups/shop-$(date -u +%Y%m%dT%H%M%SZ).dump
 
# Roles and tablespaces are cluster-wide and are NOT in a per-database dump.
pg_dumpall --globals-only --file=/backups/globals.sql

Restore:

createdb shop_restore
pg_restore --dbname=shop_restore --jobs=4 --exit-on-error /backups/shop-20260731T020000Z.dump

Backup with pgBackRest

pgBackRest performs physical backups with full, differential and incremental modes, parallel compression, checksum verification and WAL archiving — the combination needed for point-in-time recovery.

/etc/pgbackrest/pgbackrest.confSmall productionAssumes S3-compatible object storage, one primary

[global]
repo1-type = s3
repo1-s3-bucket = acme-pg-backups
repo1-s3-region = eu-central-1
repo1-s3-endpoint = s3.eu-central-1.amazonaws.com
repo1-path = /pgbackrest
repo1-cipher-type = aes-256-cbc
repo1-cipher-pass = <from the secret manager, never in the repository>
 
repo1-retention-full = 4
repo1-retention-diff = 6
 
process-max = 4
compress-type = zst
start-fast = y
log-level-console = info
 
[main]
pg1-path = /srv/pgdata/17/main
pg1-port = 5432

On the database server, point archive_command at pgBackRest:

archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'

pgbackrest --stanza=main stanza-create
pgbackrest --stanza=main check          # verifies archiving works end to end
pgbackrest --stanza=main --type=full backup
pgbackrest --stanza=main --type=incr backup
pgbackrest --stanza=main info

Verification

A backup job that exits zero has proven only that a file was written.

# Confirm archiving is actually current on the primary.
psql -c "SELECT last_archived_wal, last_archived_time, failed_count FROM pg_stat_archiver;"

Then restore on a schedule into an isolated environment, check row counts and at least one application invariant, and record how long it took against your recovery time objective. See Restore Testing.