PostgreSQL Backup and Recovery#

A backup you have never tested restoring is not a backup. This covers the main backup tools, when to use each, point-in-time recovery, and automation.

Logical Backups: pg_dump and pg_dumpall#

pg_dump exports a single database as SQL or a compressed binary format. It takes a consistent snapshot without blocking writes.

# Custom format (compressed, supports parallel restore)
pg_dump -U postgres -Fc -d myapp -f myapp.dump

# Directory format (parallel dump)
pg_dump -U postgres -Fd -j 4 -d myapp -f myapp_dir/

pg_dumpall exports every database plus cluster-wide objects. In practice, dump roles separately and per-database for flexibility:

pg_dumpall -U postgres --roles-only > roles.sql
pg_dump -U postgres -Fc -d myapp -f myapp.dump

Restoring:

# From custom format with parallel workers
createdb -U postgres myapp_restored
pg_restore -U postgres -d myapp_restored -j 4 myapp.dump

Logical backups are ideal for databases under ~100 GB and for cross-version migrations. They are too slow for multi-terabyte databases.

Physical Backups: pg_basebackup#

pg_basebackup copies the entire data directory at the filesystem level. Much faster for large databases and required for streaming replication setup.

pg_basebackup -U replicator -h primary-host -D /backup/base \
  --checkpoint=fast --wal-method=stream -P

Prerequisites on the primary: wal_level = replica, max_wal_senders = 5, a replication role, and a pg_hba.conf entry for replication.

Point-in-Time Recovery (PITR)#

PITR restores to any specific moment by combining a base backup with continuous WAL archiving.

Configure the primary to archive WAL:

archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

To restore to a point in time: stop PostgreSQL, replace the data directory with the base backup, create recovery.signal, and configure recovery:

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-02-22 14:30:00 UTC'
recovery_target_action = 'promote'

Start PostgreSQL. It replays WAL up to the target time, then promotes to read-write.

pgBackRest#

pgBackRest is the standard tool for production backup management – incremental backups, parallel compression, rotation, and remote storage.

Configure /etc/pgbackrest/pgbackrest.conf:

[mydb]
pg1-path=/var/lib/postgresql/16/main

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=7
compress-type=zst

Set PostgreSQL to archive through pgBackRest:

archive_command = 'pgbackrest --stanza=mydb archive-push %p'

Initialize and take backups:

pgbackrest --stanza=mydb stanza-create
pgbackrest --stanza=mydb --type=full backup    # full
pgbackrest --stanza=mydb --type=diff backup    # differential
pgbackrest --stanza=mydb --type=incr backup    # incremental

Restore (including PITR):

pgbackrest --stanza=mydb --delta restore
pgbackrest --stanza=mydb --delta --type=time --target="2026-02-22 14:30:00" restore

Automated Backup Schedule#

Full weekly, differential daily, WAL archiving continuous:

0 2 * * 0 pgbackrest --stanza=mydb --type=full backup >> /var/log/pgbackrest.log 2>&1
0 2 * * 1-6 pgbackrest --stanza=mydb --type=diff backup >> /var/log/pgbackrest.log 2>&1

For pg_dump setups with 7-day retention:

0 3 * * * pg_dump -U postgres -Fc -d myapp -f /backup/myapp_$(date +\%Y\%m\%d).dump && find /backup -name "myapp_*.dump" -mtime +7 -delete

Testing Your Restores#

Restore into a temporary instance at least monthly:

docker run -d --name pg-test -v /backup:/backup -e POSTGRES_PASSWORD=test postgres:16
docker exec pg-test pg_restore -U postgres -d postgres --create /backup/myapp.dump
docker exec pg-test psql -U postgres -d myapp \
  -c "SELECT COUNT(*) FROM users; SELECT MAX(created_at) FROM orders;"
docker rm -f pg-test

Script this on a schedule. If row counts or timestamps are wrong, alert immediately.

Choosing Your Strategy#

For production, use pgBackRest with continuous WAL archiving. Logical backups supplement for portability.