PostgreSQL physical backups with pgBackRest

A logical dump is fine until the database gets big. This is how I set up physical backups with pgBackRest, including point-in-time recovery and the restore test that proves the backups work.

##Why physical backups

pg_dump reads every table and writes SQL. That is simple, but the time it takes grows with the data, and restoring means replaying every statement and rebuilding every index.

A physical backup copies the data files themselves, plus the write-ahead log (WAL). That gives three things a dump cannot:

• Restores that run at disk speed, not at the speed of INSERT

• Incremental and differential backups, so only changed blocks are copied

• Point-in-time recovery: restore to any second covered by the archived WAL

##Install and configure

On Debian or Ubuntu the package is in the default repositories:

$ sudo apt install pgbackrest$ sudo mkdir -p /var/lib/pgbackrest$ sudo chown postgres:postgres /var/lib/pgbackrest

The whole setup lives in one file, /etc/pgbackrest/pgbackrest.conf:

[global]repo1-path=/var/lib/pgbackrestrepo1-retention-full=2repo1-retention-diff=6compress-type=zststart-fast=y [main]pg1-path=/var/lib/postgresql/16/main

Then tell PostgreSQL to hand every finished WAL segment to pgBackRest:

archive_mode = onarchive_command = 'pgbackrest --stanza=main archive-push %p'wal_level = replica

##Create the stanza and take a backup

$ sudo -u postgres pgbackrest --stanza=main stanza-create$ sudo -u postgres pgbackrest --stanza=main check$ sudo -u postgres pgbackrest --stanza=main --type=full backup$ sudo -u postgres pgbackrest --stanza=main infostanza: main    status: ok    db (current)        wal archive min/max (16): 000000010000000000000004/000000010000000000000006        full backup: 20261002-020001F

check is the command to trust. It forces a WAL switch and confirms the archive is really receiving segments, which catches a wrong archive_command immediately.

##Schedule it

A full backup on Sunday and a differential on every other night is enough for most databases:

0 2 * * 0   postgres  pgbackrest --stanza=main --type=full backup0 2 * * 1-6 postgres  pgbackrest --stanza=main --type=diff backup

##Test the restore

A backup nobody has restored is a hope, not a backup. Restore to a scratch machine, to a moment just before a known change:

$ sudo systemctl stop postgresql$ sudo -u postgres pgbackrest --stanza=main --delta \    --type=time --target="2026-10-02 09:15:00+00" \    --target-action=promote restore$ sudo systemctl start postgresql

##What I would remember

1. Run check after every change to the archive settings.

2. Keep at least two full backups, so one bad backup is not the only copy.

3. Put the repository on a different disk, and ideally a different machine, from the database.

4. Restore on a schedule. Put the date of the last successful restore somewhere people can see it.