A PostgreSQL Backup Strategy with pgBackRest

The day I stopped wrestling with pg_dump is the day I think I got more serious as a systems engineer. pg_dump is an investigation tool — not a production backup tool.

A production backup has to deliver three things:

1. Point-in-time recovery (PITR). Being able to say “it was deleted at 02:13 last night.”
2. Integrity verification. Testing that the backup file actually works when you open it.
3. Retention governed by policy. 6-hourly/daily/weekly rotation.

pg_dump covers none of the three.

pgBackRest setup

I prefer — and recommend — taking both a local and an S3 backup. Local backups are fast; S3 backups are fire-resistant.

Off-site copy

If the repository lives on a single disk, one VPS fire wipes out everything. pgBackRest can write to an S3 or S3-compatible (R2, MinIO) bucket:

Preferably, protect the bucket with a policy that locks versions and forbids deletion. Critical in a ransomware scenario.

To keep costs under control over the long term, set the bucket lifecycle:

30 Days → Glacier Instant Retrieval → 120 Days → Delete

/etc/pgbackrest.conf:

[global]

###################################
# Local Repository
###################################
repo1-path=/var/lib/pgbackrest
repo1-retention-full=4
repo1-retention-diff=14
repo1-bundle=y
repo1-block=y
repo1-cipher-pass=...
repo1-cipher-type=aes-256-cbc

###################################
# AWS S3 Repository
###################################
repo2-type=s3
repo2-path=/main
repo2-s3-bucket=...
repo2-s3-endpoint=s3.{region_name}.amazonaws.com
repo2-s3-region={region_name}
repo2-s3-key=...
repo2-s3-key-secret=...
repo2-bundle=y
repo2-block=y
repo2-cipher-type=aes-256-cbc
repo2-cipher-pass=...

###################################
compress-type=zst
compress-level=6
process-max=4
start-fast=y
log-level-console=info
log-level-file=detail
log-path=/var/log/pgbackrest

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

Tighten the permissions:

sudo chown postgres:postgres /etc/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest.conf

Create the stanza:

sudo -u postgres pgbackrest --stanza=main stanza-create

On the postgresql.conf side, we route WAL archiving to pgBackRest:

archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
archive_timeout = 60
wal_level = replica

archive_timeout = 60 — the last 60 seconds of transactions can be lost; enough for most applications, not for financial transactions.

Backup plan

On the cron side:

# Local repository (repo1)
0 2 * * 0          pgbackrest --stanza=main --repo=1 --type=full backup
0 2 * * 1-6        pgbackrest --stanza=main --repo=1 --type=diff backup
0 0,6,12,18 * * *  pgbackrest --stanza=main --repo=1 --type=incr backup

# S3 repository (repo2)
0 4 * * 0          pgbackrest --stanza=main --repo=2 --type=full backup
0 4 * * 1-6        pgbackrest --stanza=main --repo=2 --type=diff backup
0 3,9,15,21 * * *  pgbackrest --stanza=main --repo=2 --type=incr backup

With two repositories configured, --repo is mandatory: without it pgBackRest backs up only to the highest-priority repository (repo1), so S3 would hold nothing but archived WAL — a copy that cannot be restored on its own.

Weekly full, daily diff, 6-hourly incremental on each repository. Storage consumption is surprisingly low — the repoN-bundle/repoN-block lines in the config enable block incremental backup, so pgBackRest copies only the changed blocks of a file instead of the whole file; both options are off by default.

Integrity: if there’s a backup, can it be restored?

Taking the backup is not enough. A weekly restore drill:

pgbackrest --stanza=main --delta restore --pg1-path=/tmp/pg_restore_test

Then I start that instance on a temporary port and run a few sanity queries. Skip this step and the backup becomes false confidence.

PITR rehearsal

Rolling back to a specific point in time:

pgbackrest --stanza=main \
  --type=time \
  --target='2026-05-07 02:13:00+03' \
  restore

Running this command for the first time during a real incident is a disaster. Rehearse it beforehand — write the steps into the runbook.

Retention

Cleaning up unused backups in the repository with the expire command:

pgbackrest --stanza=main expire

With repo1-retention-full=4 the last 4 full backups are kept; older ones (and the diff/incr backups depending on them) are cleaned up automatically.

Conclusion

For copying a schema or moving a small table, pg_dump is still the right tool. But on any system that needs PITR, the backup software has to be pgBackRest (or barman, or your database provider’s native tool). The moment a backup is untested, saying “I have a backup” is a sophisticated way of lying.