How to Set Up PostgreSQL Point-in-Time Recovery With pgBackRest (2026)

This guide is for anyone running self-managed PostgreSQL who wants to be able to say "restore the database to 14:31:59 yesterday" and have it work. By the end you will have pgBackRest archiving every WAL segment to an encrypted S3 repository, a schedule of full, differential and incremental backups, a tested restore to a timestamp, and the monitoring that tells you when archiving breaks. Target versions: PostgreSQL 18.6 and pgBackRest 2.60.0 from the PostgreSQL APT repository on Ubuntu 24.04. The output shown in code comments is the expected output based on the pgBackRest user guide, abridged and illustrative, not a captured run. You need root on the database host, an S3 bucket, and an IAM role (or keys) that can read and write one prefix in it. If you are still choosing between tools, read Postgres Backups in 2026: pg_dump vs pgBackRest vs WAL-G vs Barman first.

How pgBackRest point-in-time recovery works

PITR needs two things: a base backup taken before the moment you want, and every WAL segment written between that backup and the target. PostgreSQL calls archive_command each time it completes a WAL segment; we point that at pgbackrest archive-push, which compresses, encrypts and uploads it. Backups go to the same repository. On restore, pgBackRest picks a backup that ended before your target time, lays it down, and writes restore_command and recovery_target_time into postgresql.auto.conf. PostgreSQL then replays WAL until it hits the target and promotes.

Step 1: Install PostgreSQL 18 and pgBackRest 2.60 from the PGDG repository

Ubuntu's own archive lags behind, so use the official PostgreSQL APT repository and pin both packages. The version strings below are the ones published in the noble-pgdg repository at the time of writing.

# Ubuntu 24.04 (noble). Add the PostgreSQL Global Development Group repository.
sudo apt-get update
sudo apt-get install -y postgresql-common ca-certificates
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y

# Pin exact versions so a routine apt upgrade cannot change your backup tool
sudo apt-get install -y \
  postgresql-18=18.6-1.pgdg24.04+2 \
  pgbackrest=2.60.0-1.pgdg24.04+1
sudo apt-mark hold pgbackrest

pgbackrest version
# pgBackRest 2.60.0

PostgreSQL 18 enables data checksums by default in initdb (release notes), which matters here: pgBackRest validates page checksums during backup, and from 2.60.0 a restore stops with an error if a restored file contains an invalid page checksum. Check yours:

sudo -u postgres psql -Atc "show data_checksums"
# on

Step 2: Create the S3 bucket permissions for pgBackRest

Give pgBackRest one prefix in one bucket and nothing else. This policy follows the shape used in the pgBackRest user guide: list access limited to the prefix, object access limited to objects under it. Attach it to the instance role so no keys live on disk. Turn on bucket versioning too, so a compromised host cannot silently overwrite history.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": ["s3:ListBucket"],
      "Resource": ["arn:aws:s3:::acme-pg-backups"],
      "Condition": {
        "StringEquals": { "s3:prefix": ["", "pgbackrest"], "s3:delimiter": ["/"] }
      }
    },
    {
      "Effect": "Allow",
      "Action": ["s3:ListBucket"],
      "Resource": ["arn:aws:s3:::acme-pg-backups"],
      "Condition": { "StringLike": { "s3:prefix": ["pgbackrest/*"] } }
    },
    {
      "Effect": "Allow",
      "Action": [
        "s3:PutObject", "s3:PutObjectTagging",
        "s3:GetObject", "s3:GetObjectVersion", "s3:DeleteObject"
      ],
      "Resource": ["arn:aws:s3:::acme-pg-backups/pgbackrest/*"]
    }
  ]
}

If you use EKS, pgBackRest also supports repo1-s3-key-type=web-id and pod-id, and on GCS 2.60 added Workload Identity Federation. Background on why role-based credentials beat keys is in Workload Identity Federation Explained.

Step 3: Write /etc/pgbackrest/pgbackrest.conf

One stanza, named main to match the Debian cluster name. Every option here is from the configuration reference.

# /etc/pgbackrest/pgbackrest.conf  (owner postgres, mode 0640)
[global]
repo1-type=s3
repo1-s3-bucket=acme-pg-backups
repo1-s3-endpoint=s3.eu-west-2.amazonaws.com
repo1-s3-region=eu-west-2
repo1-s3-key-type=auto          # temporary credentials from the instance role
repo1-path=/pgbackrest

# Client-side encryption; S3 never sees plaintext. Store the passphrase in a
# secrets manager as well: without it the repository is unreadable.
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=REPLACE_WITH_OUTPUT_OF_openssl_rand_-base64_48

# Retention: keep 2 full backups (and the WAL needed by them), 7 diffs
repo1-retention-full-type=count
repo1-retention-full=2
repo1-retention-diff=7

# Options the user guide recommends for new repositories
compress-type=zst
repo1-bundle=y
repo1-block=y                   # repo1-bundle must be on first

process-max=4
start-fast=y                    # force a checkpoint so backups start now

# Asynchronous archiving: batches and parallelises archive-push
archive-async=y
spool-path=/var/spool/pgbackrest

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

[main]
pg1-path=/var/lib/postgresql/18/main

sudo install -d -o postgres -g postgres -m 750 /var/spool/pgbackrest /var/log/pgbackrest
sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf

A note on archive-push-queue-max: if you set it, pgBackRest will tell PostgreSQL that WAL was archived and then drop it once the queue exceeds the limit. The docs say plainly that PITR past that point becomes impossible. It protects the disk at the cost of the backup. I leave it unset and alert on disk space instead.

Step 4: Turn on WAL archiving in postgresql.conf

# /etc/postgresql/18/main/conf.d/archiving.conf
wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
archive_timeout = 60            # seconds; forces a segment switch on quiet databases
max_wal_senders = 3

archive_mode needs a restart, not a reload. archive_timeout bounds how much committed data can sit in an unarchived segment; the docs warn that very short values bloat the archive, and suggest a minute or so.

sudo systemctl restart postgresql@18-main
sudo -u postgres psql -Atc "show archive_command"
# pgbackrest --stanza=main archive-push %p

Step 5: Create the stanza and run the check

stanza-create initialises the repository; check forces a WAL switch and confirms the segment arrives. Run both as postgres. Since 2.59.0 only restore is allowed to run as root by default.

sudo -u postgres pgbackrest --stanza=main stanza-create
# P00   INFO: stanza-create for stanza 'main' on repo1
# P00   INFO: stanza-create command end: completed successfully

sudo -u postgres pgbackrest --stanza=main check
# P00   INFO: check repo1 configuration (primary)
# P00   INFO: check repo1 archive for WAL (primary)
# P00   INFO: WAL segment 000000010000000000000001 successfully archived to '...' on repo1
# P00   INFO: check command end: completed successfully

If check fails, stop here and use the troubleshooting table. A backup taken while archiving is broken will not be restorable.

Step 6: Take full, differential and incremental backups

The backup --type is full (no dependencies), diff (changes since the last full) or incr (changes since the last backup of any type, the default). The first backup is always promoted to full.

sudo -u postgres pgbackrest --stanza=main --type=full backup
sudo -u postgres pgbackrest --stanza=main --type=diff backup
sudo -u postgres pgbackrest --stanza=main --type=incr backup

sudo -u postgres pgbackrest --stanza=main info
# stanza: main
#     status: ok
#     cipher: aes-256-cbc
#     db (current)
#         wal archive min/max (18): 000000010000000000000002/000000010000000000000009
#         full backup: 20261110-020001F
#         diff backup: 20261110-020001F_20261110-030001D
#         incr backup: 20261110-020001F_20261110-040001I

Schedule them as postgres. Expiry runs automatically at the end of each backup.

# /etc/cron.d/pgbackrest
# m  h  dom mon dow user     command
30   1  *   *   0   postgres pgbackrest --stanza=main --type=full backup
30   1  *   *   1-6 postgres pgbackrest --stanza=main --type=diff backup
15   *  *   *   *   postgres pgbackrest --stanza=main --type=incr backup
0    5  *   *   0   postgres pgbackrest --stanza=main verify

Step 7: Restore to a point in time with --type=time

Rehearse this on a copy before you need it. Record a timestamp, make a mistake, then go back. Always take the time from PostgreSQL itself and include the time zone offset; the pgBackRest guide stresses this to avoid unintended time zone conversion.

sudo -u postgres psql -c "create table important (msg text); insert into important values ('keep me');"
sudo -u postgres psql -Atc "select current_timestamp"
# 2026-11-10 09:41:07.512334+00
sudo -u postgres psql -c "drop table important;"

Stop PostgreSQL and restore. --delta reuses files whose checksums still match, which is far faster than wiping the data directory. Do not pass --set: with --type=time pgBackRest selects a backup that finished before the target on its own, and choosing a later backup by hand makes recovery fail because PostgreSQL can only replay forwards.

sudo systemctl stop postgresql@18-main

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

sudo -u postgres cat /var/lib/postgresql/18/main/postgresql.auto.conf
# restore_command = 'pgbackrest --stanza=main archive-get %f "%p"'
# recovery_target_time = '2026-11-10 09:41:07.512334+00'
# recovery_target_action = 'promote'

sudo systemctl start postgresql@18-main
sudo -u postgres psql -c "select * from important;"
#    msg
# ---------
#  keep me

Two settings deserve attention. pgBackRest's --target-action defaults to pause, as does PostgreSQL's recovery_target_action. Pause is useful when you are unsure of the time: query the paused server, and if it is too early, stop, move the target later and start again. --target-exclusive stops just before the target instead of after it. Once promoted, the server is on a new timeline, so take a fresh full backup straight away.

For a restore onto a new host, install the same versions, copy pgbackrest.conf, create an empty data directory owned by postgres, and run the same command without --delta.

Step 8: Verify backups and monitor WAL archiving

# Check every backup and archived WAL file in the repository is readable and intact
sudo -u postgres pgbackrest --stanza=main verify

# Machine-readable status for your monitoring system
sudo -u postgres pgbackrest --stanza=main --output=json info | jq '.[0].status'

-- Alert if failed_count rises or last_archived_time is older than ~5 minutes
SELECT archived_count, last_archived_wal, last_archived_time,
       failed_count, last_failed_wal, last_failed_time
FROM pg_stat_archiver;

pg_stat_archiver comes straight from the statistics views, so any Prometheus exporter that scrapes Postgres can alert on it. I would page on archiving failures before I would page on a missed backup, because a missed backup lengthens recovery while broken archiving shrinks the PITR window to zero.

Troubleshooting

Symptom	Likely cause	Fix
ERROR: [082]: WAL segment ... was not archived before the 60000ms timeout on check or backup	archive_command wrong (often the --stanza name), archiving not reloaded, or stanza stopped	Compare show archive_command with the stanza in the config, read the PostgreSQL log, run pgbackrest --stanza=main start if it was stopped; raise archive-timeout only for slow links
ERROR: [087]: archive_mode must be enabled	archive_mode = off or set without a restart	Set archive_mode = on and restart, not reload
pg_wal filling the disk, pg_stat_archiver.failed_count rising	Archive push failing (S3 permissions, DNS, expired credentials)	Run pgbackrest --stanza=main check as postgres, check IAM for the prefix, fix before the disk fills
ERROR: [044] archive mismatch on stanza-create or archive-push	Repository path reused for a different cluster (different system identifier or version)	Use a new repo1-path, or stanza-upgrade after a major version upgrade
FATAL: recovery ended before configured recovery target was reached	A backup taken after the target was used, or WAL is missing for the gap	Drop --set and let pgBackRest pick the backup; check info shows WAL archive covering the target
Server starts read-only after restore and stays that way	Default --target-action=pause	Run select pg_wal_replay_resume(); or restore again with --target-action=promote
Restore stops with a page checksum error (2.60+)	Corrupt page in a backup file	Restore an older backup set; investigate storage; checksum-page-error=n only warns and is a last resort
ERROR: [040]: unable to restore to path ... because it contains files	Restoring into a non-empty data directory without --delta	Add --delta, or empty the directory
Commands refuse to run as root	Root restricted since 2.59.0	Run as postgres with sudo -u postgres; avoid allow-root

FAQ

How far back can pgBackRest restore?

As far back as the oldest full backup you retain, plus its WAL. With repo1-retention-full=2 and weekly fulls, that is roughly two weeks. Use repo1-retention-full-type=time if you want a retention window in days instead of a backup count.

Can I restore a single table with pgBackRest?

Not directly. pgBackRest restores whole clusters, with --db-include to restore selected databases. To recover one table, restore to a separate host at the right time, then pg_dump -t the table and load it into production.

Do I need archive-async?

Not for correctness, but it batches WAL uploads and uses process-max for parallelism, which matters on busy databases writing many segments a minute to S3. It also lets archiving continue to other repositories when one is unreachable.

Should I use recovery_target_time or a named restore point?

Time is the usual choice after an accident. If you know a risky change is coming, call pg_create_restore_point('before_migration') first, then restore with --type=name --target=before_migration. pgBackRest cannot pick the backup automatically for name targets, so add --set with the backup taken before the restore point if newer backups exist.

What I check first on any Postgres someone hands me is pg_stat_archiver, because a failing archive is silent until the day you need it. Then I look for evidence that a restore has actually been run in the last month. If there is none, the first job is a timed restore drill like Step 7 on a scratch host, with the duration written down; that number is the RTO you can honestly promise. Official docs for everything above: the pgBackRest user guide, command reference, configuration reference, and PostgreSQL's continuous archiving and PITR chapter.

Related: Postgres Backups in 2026: pg_dump vs pgBackRest vs WAL-G vs Barman, Backup and Disaster Recovery in 2026: RPO vs RTO, 3-2-1-1-0, Real Costs and How to Run Zero-Downtime Postgres Migrations: Step by Step (2026).