PostgreSQL Backups You Can Actually Restore: Setting Up pgBackRest

Most of the small Postgres setups I inherit from clients share the exact same version of a backup strategy. It is usually a cron job running pg_dump every night, piping the output to gzip, dropping the file in a random folder, and maybe syncing it off-site with rclone.

It looks exactly like a backup. It runs every night without throwing errors. The problem is that nobody has actually tried to restore from it in a very long time.

pg_dump is perfectly fine as a baseline, but it hits very real limits the second a database grows past a few gigabytes. The dump takes longer and longer every single month. It adds load to the server for the entire duration of the run. Most importantly, a single dump file gives you exactly one recovery point per day. If something goes catastrophically wrong at 3:00 PM, you are rolling back to last night and permanently losing everything that happened since. There is no point-in-time recovery, and dump-based backups simply do not scale gracefully once you start dealing with tens of gigabytes.

Moving to pgBackRest

pgBackRest solves almost all of this without requiring much extra infrastructure. It handles full, differential, and incremental backups effortlessly. It archives WAL (Write-Ahead Logs) continuously so you get true point-in-time recovery. It supports parallel processing for vastly faster backups on multi-core machines, and it can push archives directly to local disk, S3-compatible storage, or both simultaneously.

For a single, small server, the setup I default to 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

You then enable WAL archiving inside postgresql.conf:

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

Create the stanza and manually trigger the very first full backup:

sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main --type=full backup

After that initial setup, scheduling one full backup a week and incrementals for the rest of the days keeps your repository size extremely manageable. It still gives you a solid recovery point for every single day, plus the continuous WAL archiving for anything that requires finer-grained recovery.

# 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 configuration above matters if you have never actually restored from it.

I keep a small secondary VPS around specifically for this one task. Once a month, it pulls the absolute latest backup, runs pgbackrest --stanza=main --delta restore, and starts Postgres. I then run a couple of basic sanity queries against it: row counts on the biggest tables, and checking a recent updated_at timestamp. It takes maybe twenty minutes end-to-end, and I explicitly write down exactly how long the restore process itself took.

That specific number—the actual real-world restore time—is your true Recovery Time Objective (RTO). It is not whatever hypothetical number is written in a runbook somewhere. The very first time I ran this drill for a client, the restore took noticeably longer than I had assumed, purely because of the physical disk throughput limits on the target VPS. It is infinitely better to discover that harsh reality on a quiet Wednesday afternoon than during an active incident.

Where this leaves off-site copies

repo1 can be configured to point directly at S3-compatible object storage. Backblaze B2 and Wasabi both work exceptionally well and are cheap enough that cost is never a valid reason to skip them.

For any database I would actually lose sleep over, I configure a second repo definition pointed exclusively at off-site storage. That way, a full host failure—not just a bad deploy—does not take the only copy of the backups down into the void with it.

A backup you have never restored from is just a hope, not a backup. pgBackRest easily gets you most of the way to "actually restorable" with a simple config file and a couple of cron entries. The monthly restore drill is what completely closes the gap.