PostgreSQL Backups with pgBackRest: Full, Differential and PITR Restore

pgBackRest gives PostgreSQL reliable physical backups by combining scheduled full, differential and incremental backups with continuous WAL archiving to a repository, so you can restore the cluster to the end of the archive or to any point in time covered by your retention. A working setup needs four things: archive_command pointing at pgbackrest archive-push, a stanza and repository in /etc/pgbackrest/pgbackrest.conf, a backup schedule with retention, and a restore you have actually run. This guide walks through each one on Debian or Ubuntu and ends with a tested point-in-time recovery.

Who this is for and what you will have at the end

This is for DBAs and Linux administrators running self-managed PostgreSQL on virtual machines or bare metal, including clusters managed by Patroni, who need backups that can survive both hardware failure and human error. Managed services such as Azure Database for PostgreSQL have their own backup system and aren't covered.

At the end you will have:

• pgBackRest installed with an encrypted local repository and an optional second repository in Azure Blob Storage.
• WAL archiving verified with the check command.
• A weekly full and daily differential schedule with retention that matches it.
• Monitoring of the last successful backup and last archived WAL segment.
• A rehearsed point-in-time restore, and a list of the errors you are most likely to meet.

The examples follow the official pgBackRest user guide, which uses PostgreSQL 17 on Debian and Ubuntu with a stanza named demo. Here the stanza is called app; the commands are the same on any Unix system, although paths differ by distribution.

Concepts you need before configuring anything

Two facts shape everything else. First, a restore needs the backup files plus WAL segments, so archiving is not optional. Second, restore time depends on database size, not backup type: the pgBackRest FAQ states that full, differential and incremental backups take the same time to restore because files are retrieved from the manifest, which can reference earlier backups.

Prerequisites

• PostgreSQL running on Linux, with shell access as root and as the postgres user.
• The same pgBackRest version on every host that participates. The local and remote versions must match exactly, or archiving and backups stop with a protocol error.
• Disk or object storage for the repository, sized for the backups you plan to retain plus the WAL generated between them.
• For an Azure repository: a storage account and a blob container created in advance, with hierarchical namespace not enabled.

Step 1: Install pgBackRest and create its directories

Install from a package rather than building from source; Debian and Ubuntu packages are published in the PostgreSQL Apt repository at apt.postgresql.org.

sudo apt-get install pgbackrest
sudo -u postgres pgbackrest version

If the package didn't create the log and configuration paths with the right ownership, create them as the user guide does:

sudo mkdir -p -m 770 /var/log/pgbackrest
sudo chown postgres:postgres /var/log/pgbackrest
sudo mkdir -p /etc/pgbackrest/conf.d
sudo touch /etc/pgbackrest/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf

Create the local repository directory:

sudo mkdir -p /var/lib/pgbackrest
sudo chmod 750 /var/lib/pgbackrest
sudo chown postgres:postgres /var/lib/pgbackrest

Step 2: Write the configuration

pgBackRest reads an INI-style file. Sections are merged, and the most specific wins in this order: [stanza:command], [stanza], [global:command], [global]. End-of-line comments and quoting aren't supported.

Generate a long random passphrase for repository encryption first. The user guide suggests openssl rand -base64 48. Encryption is always performed client-side, even when the object store also encrypts, and the cipher settings can't be changed once the stanza is created, so store the passphrase in your secrets vault now.

[app]
pg1-path=/var/lib/postgresql/17/main
 
[global]
repo1-path=/var/lib/pgbackrest
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=<output of openssl rand -base64 48>
repo1-retention-full=2
repo1-retention-diff=6
repo1-bundle=y
repo1-block=y
compress-type=zst
start-fast=y
process-max=2
 
[global:archive-push]
compress-level=3

Notes on each choice:

• pg1-path must be exactly equal to PostgreSQL's data_directory, or the backup returns an error.
• repo1-bundle combines small files, which saves space and speeds up backup and restore, especially on object stores. repo1-block stores only the changed parts of files in differential and incremental backups and requires bundling. The user guide recommends both for new repositories, with at least v2.52.1 for block incremental.
• compress-type defaults to gz; the user guide recommends zst as much faster with similar compression.
• start-fast=y forces a checkpoint so the backup begins immediately instead of waiting for the next scheduled checkpoint.
• process-max defaults to 1, which the documentation describes as almost never the appropriate value. Don't set it so high that it affects database performance.
• Encryption settings sit in [global] so the info command can read every stanza.

Step 3: Turn on WAL archiving

Edit postgresql.conf for the cluster:

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

Restart PostgreSQL; archive_mode needs a restart before the first backup.

sudo pg_ctlcluster 17 main restart

Leave hot_standby at its default of on. pgBackRest's archive-timeout option (default 60 seconds) is how long a backup or check waits for each WAL segment to reach the repository; it is unrelated to PostgreSQL's archive_timeout, which forces segment switches on idle systems.

If the cluster generates WAL quickly, enable asynchronous archiving, which pushes segments in batches and in parallel:

[global]
archive-async=y
spool-path=/var/spool/pgbackrest
 
[global:archive-push]
process-max=2

Create /var/spool/pgbackrest owned by postgres with mode 750. The spool path should be on a local filesystem, not NFS or CIFS, and not inside pg_wal.

Step 4: Create the stanza and check it

sudo -u postgres pgbackrest --stanza=app --log-level-console=info stanza-create
sudo -u postgres pgbackrest --stanza=app --log-level-console=info check

check forces a WAL switch and confirms the segment arrives in every configured repository. A successful run ends with a line such as WAL segment ... successfully archived to ... and check command end: completed successfully. Don't schedule backups until this passes.

Step 5: Take the first backups

sudo -u postgres pgbackrest --stanza=app --type=full --log-level-console=info backup
sudo -u postgres pgbackrest --stanza=app --type=diff --log-level-console=info backup

If you run a backup without --type before any full backup exists, the log shows WARN: no prior backup exists, incr backup has been changed to full. That is expected.

Each backup gets a label: full backups end in F (for example 20261011-020000F), differentials in D and incrementals in I, with the parent full label as a prefix.

Step 6: Schedule backups and set retention

pgBackRest has no built-in scheduler. The user guide's pattern is a full backup every Sunday and a differential on the other days, in the postgres user's crontab:

#m h  dom mon dow  command
30 06 * * 0   pgbackrest --type=full --stanza=app backup
30 06 * * 1-6 pgbackrest --type=diff --stanza=app backup

If the crontab is installed mid-week, the first differential run becomes a full backup automatically.

Retention then follows from the schedule:

The expire command runs automatically after each successful backup. To preview what a manual expire would remove, run it with --dry-run.

Step 7: Add an off-host copy in Azure Blob Storage

A repository on the same host doesn't survive the loss of that host. pgBackRest can write to several repositories; a common design is a local repository for fast restores and an object store for redundancy.

[global]
repo2-type=azure
repo2-azure-account=contosopgbackup
repo2-azure-container=pgbackrest
repo2-azure-key=<storage account key or SAS token>
repo2-path=/app-repo
repo2-retention-full=4
repo2-cipher-type=aes-256-cbc
repo2-cipher-pass=<separate passphrase>
repo2-bundle=y

To use a shared access signature instead of the account key, also set repo2-azure-key-type=sas. Then run stanza-create again; it initializes every configured repository.

Points that catch people out with multiple repositories:

• archive-push sends WAL to all repositories. If one is unreachable, the others only stay ahead of it when archive-async=y is enabled. If WAL can't reach any repository, PostgreSQL keeps it in pg_wal, which can fill the volume.
• Backups are scheduled per repository with --repo=2, and each repository has its own retention.
• restore defaults to the latest backup from the first repository that contains backups. Pass --repo=2 to restore from the second repository instead.

45 07 * * 0 pgbackrest --type=full --stanza=app --repo=2 backup

Step 8: Monitor backups and archiving

sudo -u postgres pgbackrest info
sudo -u postgres pgbackrest --output=json --stanza=app info | jq '.[0] | .backup[-1] | .timestamp.stop'
sudo -u postgres pgbackrest --output=json --stanza=app info | jq '.[0] | .archive[-1] | .max'

The text output is for people and may change between releases; the JSON output is kept stable for tooling. Alert when the last backup stop time is older than your schedule allows, or when the last archived WAL stops advancing. A stanza status of mixed means at least one repository is unhealthy.

Run verify periodically to check that backups and archive files in a repository are valid:

sudo -u postgres pgbackrest --stanza=app --output=text verify

Step 9: Restore and point-in-time recovery

Full restore after a failure

Stop PostgreSQL and restore with --delta, which keeps files whose checksum matches the backup and replaces or removes the rest, so you don't have to empty the data directory first.

sudo pg_ctlcluster 17 main stop
sudo -u postgres pgbackrest --stanza=app --delta --process-max=4 restore
sudo pg_ctlcluster 17 main start

The default recovery type replays all WAL to the end of the archive.

Point-in-time recovery after a mistake

When someone drops a table or deletes rows, recover to a moment just before it happened. Record times as PostgreSQL reports them, including the time zone offset, to avoid an unintended conversion.

sudo pg_ctlcluster 17 main stop
sudo -u postgres pgbackrest --stanza=app --delta \
  --type=time "--target=2026-10-11 09:41:00+00" \
  --target-action=promote restore
sudo pg_ctlcluster 17 main start

Omit --set: for --type=time and --type=lsn, pgBackRest checks the configured repositories in order for a backup that contains the requested target. pgBackRest writes restore_command, recovery_target_time and recovery_target_action into postgresql.auto.conf, and the PostgreSQL log shows where recovery stopped and the last transaction applied.

--target-action defaults to pause, which leaves the cluster in recovery so you can inspect the data before committing. Use promote when you are sure, or start with pause, check, then promote. Other recovery types are lsn, xid, name and immediate; pgBackRest can't choose a backup automatically for xid or name, so supply --set for those.

Rehearse on a separate host

A restore you haven't tested isn't a backup strategy. Restore to a separate server that can read the repository, and pass --archive-mode=off so the restored, promoted copy doesn't push WAL back into the production repository (the command reference notes this option isn't available on older PostgreSQL versions). To pull only one database, --db-include restores that database and zero-fills the others; the user guide warns that recovery may error unless --type=immediate is used, and the zero-filled databases must then be dropped.

If you run PostgreSQL under Patroni, the same stanza and archive_command apply on every node; see the PostgreSQL high availability guide with Patroni, etcd and HAProxy for where pgBackRest fits in that topology.

Troubleshooting

Point-in-time recovery didn't stop at the target time. You restored from a backup that ended after the target time. PostgreSQL can't replay backward, so it doesn't treat the target as valid and replays to the end of the archive instead. The pgBackRest FAQ calls this the most common time-based recovery mistake. Rerun the restore without --set, or choose a backup from pgbackrest info whose timestamp stop is before the target and pass it with --set.

could not find WAL segment or check timing out. Check archive_command, pgBackRest configuration on each host, permissions, network access to the repository, and whether a large WAL backlog is waiting. Run check with a higher --archive-timeout to see whether the queue just needs time, and enable asynchronous archiving on busy systems.

[ProtocolError] expected value '2.x' for greeting key 'version' but got '2.y'. pgBackRest versions differ between hosts. Upgrade all hosts together; upgrades within v2 don't change the repository format.

Backup fails because the data directory doesn't match. pg1-path must equal data_directory exactly as PostgreSQL reports it.

Expire errors on an Azure repository. Hierarchical namespace is enabled on the storage account. pgBackRest documents that it causes errors during expire; use an account without it.

The archive volume filled and archive-push-queue-max was set. When that limit is reached pgBackRest reports WAL as archived and drops it, which breaks the WAL stream. Point-in-time recovery isn't possible past that point; take a new full backup immediately.

Later commands fail with permission errors on repository files. A command was run as root and created files the postgres user can't read. Only restore may run as root by default; run everything else as postgres and fix the ownership of the affected files.

Checklist

• pgBackRest installed at the same version on every host.
• pg1-path matches data_directory; archive_mode on and archive_command set; check passes.
• Repository encrypted with a passphrase stored outside the server.
• Weekly full and daily differential backups scheduled; repo1-retention-full and repo1-retention-diff match the schedule.
• A second repository off the host, scheduled and retained separately.
• Alerts on last backup time and last archived WAL; verify runs on a schedule.
• A point-in-time restore rehearsed on a separate host with --archive-mode=off, and the steps written into your runbook.

References

• pgBackRest User Guide (Debian and Ubuntu)
• pgBackRest Configuration Reference
• pgBackRest Command Reference
• pgBackRest FAQ