Introduction
pg_dump is fine for small databases. For serious prod, use pgBackRest:
- Full, differential, incremental backups
- PITR: restore to a precise moment (timestamp or LSN)
- Parallelism (multi-CPU for backup and restore)
- LZ4 / Zstd / Gzip compression
- Native AES-256 encryption
- Storage: local, S3, Azure, GCS
- Integrity verification (checksums)
- Backup from replica (no primary load)
Prerequisites
- PostgreSQL 12+
- Linux VPS Debian / Ubuntu
- Root access
- Storage space (local or S3)
Step 1: Installation
sudo apt update
sudo apt install -y pgbackrest
pgbackrest version
Step 2: Prepare local backup repo
sudo mkdir -p /var/lib/pgbackrest
sudo chown postgres:postgres /var/lib/pgbackrest
sudo chmod 750 /var/lib/pgbackrest
Step 3: pgBackRest configuration
sudo nano /etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=4
process-max=2
compress-type=zst
compress-level=3
log-level-console=info
log-level-file=detail
log-path=/var/log/pgbackrest
[main]
pg1-path=/var/lib/postgresql/16/main
pg1-port=5432
pg1-user=postgres
sudo mkdir -p /var/log/pgbackrest
sudo chown postgres:postgres /var/log/pgbackrest
Step 4: Configure PostgreSQL archive_command
/etc/postgresql/16/main/postgresql.conf:
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
archive_timeout = 60
max_wal_senders = 3
wal_level = replica
sudo systemctl restart postgresql
Step 5: Initialize stanza
sudo -u postgres pgbackrest --stanza=main --log-level-console=info stanza-create
sudo -u postgres pgbackrest --stanza=main check
Step 6: First full backup
sudo -u postgres pgbackrest --stanza=main --type=full --log-level-console=info backup
First backup is long (reads entire database). Subsequent ones are incremental.
Step 7: List backups
sudo -u postgres pgbackrest --stanza=main info
Step 8: Differential and incremental
# Differential: since last full
sudo -u postgres pgbackrest --stanza=main --type=diff backup
# Incremental: since last backup (full or diff)
sudo -u postgres pgbackrest --stanza=main --type=incr backup
Good strategy:
- 1 full per week (Sunday)
- 1 diff per day
- 1 incr per hour
Step 9: Automate with cron
sudo crontab -u postgres -e
# Full on Sunday 1am
0 1 * * 0 pgbackrest --stanza=main --type=full backup
# Diff other days 1am
0 1 * * 1-6 pgbackrest --stanza=main --type=diff backup
# Incremental hourly
0 * * * * pgbackrest --stanza=main --type=incr backup
Step 10: Full restore
⚠️ Test server or after confirmation.
sudo systemctl stop postgresql
sudo -u postgres rm -rf /var/lib/postgresql/16/main/*
sudo -u postgres pgbackrest --stanza=main --log-level-console=info restore
sudo systemctl start postgresql
PG replays WAL and reopens the database.
Step 11: Point-In-Time Recovery (PITR)
Restore to a precise timestamp (before an accidental DROP TABLE):
sudo systemctl stop postgresql
sudo -u postgres rm -rf /var/lib/postgresql/16/main/*
sudo -u postgres pgbackrest --stanza=main \
--type=time \
--target="2026-05-17 14:45:00" \
--target-action=promote \
restore
sudo systemctl start postgresql
SELECT now() - pg_last_xact_replay_timestamp();
Step 12: S3 remote storage
For S3 repo (AWS, Wasabi, Backblaze B2, MinIO):
[global]
repo1-type=s3
repo1-s3-bucket=my-backups-bucket
repo1-s3-endpoint=s3.eu-west-3.amazonaws.com
repo1-s3-region=eu-west-3
repo1-s3-key=AKIA...
repo1-s3-key-secret=secret...
repo1-path=/pgbackrest
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=very-long-passphrase
[main]
pg1-path=/var/lib/postgresql/16/main
Re-initialize:
sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main backup
Backups go direct to S3, AES-256 encrypted.
Troubleshooting
"archive command failed"
sudo -u postgres pgbackrest --stanza=main check
Check archive_command correct, /var/log/pgbackrest exists and writable.
"WAL segment missing"
WAL couldn't be archived. Check disk space and logs:
sudo tail -f /var/log/pgbackrest/main-archive-push.log
Very slow backup
Increase process-max (parallelism). If I/O-bound, add compression HW acceleration or use NVMe.
Restore fails with "pg_control file is corrupt"
Datadir not properly wiped. Recheck rm -rf and permissions.
S3 access denied
Check IAM permissions (s3:GetObject, s3:PutObject, s3:ListBucket, s3:DeleteObject).
Useful commands
sudo -u postgres pgbackrest --stanza=main info
sudo -u postgres pgbackrest --stanza=main --type=full backup
sudo -u postgres pgbackrest --stanza=main --type=diff backup
sudo -u postgres pgbackrest --stanza=main --type=incr backup
sudo -u postgres pgbackrest --stanza=main check
sudo -u postgres pgbackrest --stanza=main verify
sudo -u postgres pgbackrest --stanza=main restore
sudo -u postgres pgbackrest --stanza=main --type=time --target="2026-05-17 14:00:00" restore
sudo -u postgres pgbackrest --stanza=main --set=20260517-143000F expire
sudo tail -f /var/log/pgbackrest/main-backup.log
sudo tail -f /var/log/pgbackrest/main-archive-push.log
Conclusion
pgBackRest is essential for production:
- Efficient backups (compression, parallelism)
- Reliable PITR
- Automatic integrity verification
- Encrypted remote storage
Going further:
- Combine with streaming replication for HA
- Regular restores on test server to validate
- Monitor execution with Prometheus + node_exporter + textfile collector
Resources
- Official docs: https://pgbackrest.org/user-guide.html
- GitHub: https://github.com/pgbackrest/pgbackrest
- Example configs: https://pgbackrest.org/configuration.html
- PG backup tools comparison: https://pgbackrest.org/