Baseline pgBackRest Setup With Point-in-Time Recovery

A backup you have never restored is a hope, not a control. pgBackRest turns full, differential and WAL-level backups into the raw material for point-in-time recovery — but only if the stanza, the archive command and the retention policy are configured to agree with each other.

This is the baseline I put on PostgreSQL 17 clusters that have to survive a bad deployment, a dropped table or a ransomware event: pgBackRest 2.5x writing compressed, encrypted backups to S3-compatible object storage, with every WAL segment archived so recovery can land on any moment in the retained window. It is the config that makes the companion PITR restore a routine operation rather than an incident. Get the pieces below right and you own a recoverable database. Get them nearly right and you own a directory of files that cannot be replayed.

What point-in-time recovery actually needs

PITR rests on two things existing together. The first is a base backup — a consistent physical copy of the cluster taken at some known point. The second is an unbroken chain of write-ahead log (WAL) segments from that base backup forward to the moment you want to recover to. pgBackRest produces the base backups; PostgreSQL’s archive_command produces the WAL chain by handing each completed segment to pgBackRest as it is filled.

If either half is missing, PITR is impossible. A base backup with no archived WAL can only be restored to the instant the backup finished. A pile of WAL with no base backup to apply it to is inert. The single most common failure I see is an archive_command that silently fails for weeks — the base backups keep running, everyone assumes they are covered, and the recoverable window has quietly collapsed to the last full backup. The config below is built to make that failure loud.

The stanza and the S3 repository

A stanza is pgBackRest’s name for one PostgreSQL cluster and its associated repository configuration. Everything lives in /etc/pgbackrest/pgbackrest.conf. The [global] section defines where and how backups are stored; the [main] section names the cluster.

# /etc/pgbackrest/pgbackrest.conf

[global]
repo1-path=/pg-backups-prod
repo1-type=s3
repo1-s3-bucket=pg-backups-prod
repo1-s3-endpoint=s3.eu-central-1.amazonaws.com
repo1-s3-region=eu-central-1
repo1-s3-uri-style=host
repo1-s3-key=AKIAEXAMPLEKEY
repo1-s3-key-secret=wJalrXUtnFEMIexampleSECRETkey

# Client-side encryption: the repository is unreadable without this passphrase
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=REPLACE_WITH_A_LONG_RANDOM_PASSPHRASE

# Compress on the host, before anything leaves it
compress-type=zst
compress-level=6

# Keep two full backups; dependent diffs and WAL expire with the full they hang off
repo1-retention-full=2

# Parallelism for both backup and restore
process-max=4
start-fast=y

# Archive WAL through a local spool so a slow upload never stalls the primary
archive-async=y
spool-path=/var/spool/pgbackrest

log-level-console=info
log-level-file=detail

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

Two options do the security work. repo1-cipher-type=aes-256-cbc with a long repo1-cipher-pass means the object store holds only ciphertext — an attacker or a cloud misconfiguration that exposes the bucket gets nothing usable. Store that passphrase somewhere separate from the repository; lose it and the backups are as unrecoverable as if they had been deleted. compress-type=zst compresses before upload, which cuts both storage cost and the volume of data crossing the wire.

archive-async=y with a spool-path lets WAL archiving queue locally instead of blocking the primary when object storage is briefly slow — important, because a synchronous archive_command that hangs will eventually stop the database.

Wiring WAL archiving into PostgreSQL

PostgreSQL has to be told to emit enough WAL and to hand each segment to pgBackRest. In postgresql.conf:

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
max_wal_senders = 3

Changing archive_mode requires a full restart; archive_command alone can be applied with a reload. With PostgreSQL restarted, create the stanza and verify the whole chain end to end:

# Initialise the repository for this cluster (run once)
sudo -u postgres pgbackrest --stanza=main stanza-create

# Confirm archiving and the repository are both working
sudo -u postgres pgbackrest --stanza=main check

The check command is the one that earns its keep. It forces a WAL switch and confirms the segment actually arrives in the repository, so it fails immediately if the archive_command is wrong, the credentials are stale or the bucket is unreachable. Run it after any change to the archiving path, and run it from your monitoring on a schedule — a green check is the closest thing to proof that PITR is currently possible.

Running and scheduling backups

The first backup of a stanza is always promoted to a full backup regardless of the type you request. After that, differentials capture everything changed since the last full, which keeps day-to-day backups small while the weekly full bounds how much WAL any recovery has to replay.

# Weekly anchor: a full backup
sudo -u postgres pgbackrest --stanza=main --type=full backup

# Between fulls: differentials (changes since the last full)
sudo -u postgres pgbackrest --stanza=main --type=diff backup

# See what you actually hold
sudo -u postgres pgbackrest --stanza=main info

Do not drive this from cron and hope. A systemd timer gives you a service unit whose success or failure is visible to systemctl and your log pipeline. One service, one timer, for the weekly full:

# /etc/systemd/system/pgbackrest-full.service
[Unit]
Description=pgBackRest full backup of the main stanza
After=network-online.target
Wants=network-online.target

[Service]
Type=oneshot
User=postgres
ExecStart=/usr/bin/pgbackrest --stanza=main --type=full backup

# /etc/systemd/system/pgbackrest-full.timer
[Unit]
Description=Weekly pgBackRest full backup

[Timer]
OnCalendar=Sun *-*-* 01:00:00
Persistent=true

[Install]
WantedBy=timers.target

Enable it, then create an analogous pgbackrest-diff pair with --type=diff and OnCalendar=Mon..Sat *-*-* 01:00:00 for the daily differential:

sudo systemctl daemon-reload
sudo systemctl enable --now pgbackrest-full.timer
sudo systemctl enable --now pgbackrest-diff.timer

Persistent=true matters: if the host is down at 01:00, the backup runs when it next boots rather than being silently skipped.

Retention, verification and the restore you owe yourself

repo1-retention-full=2 keeps the two most recent full backups. When a third full completes, pgBackRest expires the oldest full together with its dependent differentials and — crucially — the archived WAL that only that expired backup needed. This is what stops WAL accumulating without limit in object storage while still guaranteeing you can recover to any point within the window covered by the retained fulls. Set the number to the recovery window your resilience objectives actually require, not the one that happens to be cheap.

Backups feed the recovery-time engineering that sits underneath any serious continuity plan, from ransomware recovery-time targets to the change-failure and restore metrics you should already be tracking as part of instrumenting the DORA Four. None of that is worth anything until you have proved the restore. A retention count is a claim; a completed restore is evidence. Worth noting too that pgBackRest’s physical, WAL-based recovery is a different discipline from the point-in-time correctness that data teams chase in feature stores — same phrase, unrelated problem.

The config above is the standing half of the exercise. The other half — restoring to a chosen timestamp, promoting the recovered cluster and confirming the data is what you expected — is the companion PITR walkthrough. Do not treat this post as finished until you have run that restore against these backups at least once. A stanza that has never been restored is a directory of encrypted files you are paying to store and hoping you will never have to open.