PostgreSQL Backups You Can Actually Restore: Setting Up pgBackRest
Most small Postgres setups I inherit from clients have some version of the same backup: a cron job running pg_dump every night, piping to gzip, dropping the file in a folder, maybe synced off-site with rclone. It looks like a backup. It runs every night without errors. Nobody has tried to restore from it in a long time.
That's the problem. pg_dump is fine as a baseline, but it has real limits once a database grows past a few GB: the dump takes longer every month, it adds load for the whole duration, and a single dump file gives you exactly one recovery point per day. If something goes wrong in the afternoon, you're rolling back to last night and losing everything since. There's no point-in-time recovery, and dump-based backups don't scale gracefully once you're talking tens of GB.
Moving to pgBackRest
pgBackRest solves most of this without much extra infrastructure. It does full, differential, and incremental backups, archives WAL continuously so you get point-in-time recovery, supports parallel processing for faster backups on multi-core boxes, and can push to local disk, S3-compatible storage, or both.
For a single small server, the setup I use looks roughly like this:
# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=4
[main]
pg1-path=/var/lib/postgresql/16/main
pg1-port=5432
Then enable WAL archiving in postgresql.conf:
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
Create the stanza and run the first full backup:
sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main --type=full backup
After that, a schedule of one full backup a week and incrementals the rest of the days keeps the repository size manageable while still giving you a recovery point for every day, plus continuous WAL for anything finer-grained:
# Sunday: full backup
0 2 * * 0 postgres pgbackrest --stanza=main --type=full backup
# Mon-Sat: incremental
0 2 * * 1-6 postgres pgbackrest --stanza=main --type=incr backup
The part almost everyone skips
None of the above matters if you've never restored from it. I keep a small secondary VPS around specifically for this: once a month, it pulls the latest backup, runs pgbackrest --stanza=main --delta restore, starts Postgres, and I run a couple of sanity queries against it, row counts on the biggest tables, a recent updated_at timestamp. It takes maybe twenty minutes end to end, and I write down how long the restore itself took.
That number, the actual restore time, is your real recovery time objective, not whatever's written in a runbook somewhere. The first time I did this for a client, the restore took noticeably longer than I'd assumed, purely because of disk throughput on the target VPS. Better to find that out on a quiet afternoon than during an actual incident.
Where this leaves off-site copies
repo1 can point at S3-compatible storage directly, Backblaze B2 and Wasabi both work well and are cheap enough that cost isn't a reason to skip it. For anything I'd actually lose sleep over, I run a second repo definition pointed at off-site storage, so a full host failure, not just a bad deploy, doesn't take the backups down with it.
A backup you haven't restored from is a hope, not a backup. pgBackRest gets you most of the way to "actually restorable" with a config file and a couple of cron entries; the restore drill is what closes the gap.