pgBackRest Point in Time Recovery: WAL Archiving to S3 and a Full Restore

How PostgreSQL PITR works and who needs it

Point-in-time recovery (PITR) restores a PostgreSQL cluster to any moment you choose. It needs two things: a base backup, which is a physical copy of the data directory, and a continuous, gap-free archive of write-ahead log (WAL) segments produced since that backup. On restore, PostgreSQL starts from the base backup and replays archived WAL until it reaches your recovery target, which can be a timestamp, a transaction ID, or a named restore point.

A base backup on its own only gets you to the moment it finished. Files are copied while the database keeps writing, so the copy is not consistent until the WAL generated during the backup has been replayed. Every change after that point exists only in WAL. If one segment is missing from the archive, replay stops there. Everything after the gap cannot be recovered.

Copy+-------------+  archive-push   +-----------------+   restore    +--------------+
|  Primary    | --------------> |  S3 repository  | -----------> | Restore host |
|  PostgreSQL |  (each WAL seg) |  backups + WAL  |  backup, then| (separate VM)|
|  pgBackRest | --------------> |                 |  archive-get | replays WAL  |
+-------------+  full/diff/incr +-----------------+  per segment +--------------+

Teams use PITR to undo damage that replication faithfully copies to every replica. Typical cases are a DELETE without a WHERE clause, a migration that drops the wrong column, or a bad batch job. You restore to just before the mistake and extract or replace the affected data.

pgBackRest suits teams running self-managed PostgreSQL on VMs or bare metal whose nightly pg_dump would lose up to a day of writes. It is the right tool when you need recovery to arbitrary points across a retention window measured in days or weeks, and you are prepared to monitor the archive continuously.

Copy+------------------------+                    +------------------------+
| Primary host           |  archive-push      | S3 repository          |
|  PostgreSQL            |  (each WAL segment)|                        |
|  pgBackRest            |------------------->|  archive/: WAL segments|
|                        |  pgbackrest backup |                        |
|                        |  (full/diff/incr)  |  backup/: base backups |
|                        |------------------->|                        |
+------------------------+                    +------------------------+
                                                 (1)              (2)
                                                  |                |
                                                  v                v
(1) restore copies a base backup              +------------------------+
(2) restore_command runs archive-get          | Restore host (separate)|
    once per WAL segment needed               |  pgBackRest restore    |
                                              |  PostgreSQL replays WAL|
                                              |  to recovery target    |
                                              +------------------------+

[base backup]==seg 1==seg 2==seg 3==seg 4==> recovery target
               a missing segment stops replay; all later WAL is lost

The primary pushes every WAL segment and periodic base backups to an S3 repository, and a separate restore host pulls a base backup, then fetches WAL one segment at a time and replays it up to the chosen recovery target.

Configuring pgBackRest and WAL archiving to S3

The configuration lives in /etc/pgbackrest/pgbackrest.conf and has two parts. A global section describes the repository: repo1-type=s3, repo1-s3-bucket, repo1-s3-endpoint, repo1-s3-region, and repo1-path, which sets the prefix inside the bucket. A stanza section named after the cluster, such as [main], sets pg1-path to the data directory. Any S3-compatible store works, provided the endpoint and region match what that provider expects.

The same file usually holds repo1-s3-key and repo1-s3-key-secret. If you enable repository encryption with repo1-cipher-type=aes-256-cbc, it also holds repo1-cipher-pass. Make the file owned by postgres with mode 640 or stricter, so no other local user can read it. Treat the passphrase as part of the backup itself. An encrypted repository is unrecoverable without it, so store it somewhere that survives losing this host. Rotating it, and recording who can read it, belong in your backup runbook.

In postgresql.conf, set archive_mode = on and archive_command = 'pgbackrest --stanza=main archive-push %p'. Changing archive_mode requires a server restart, not a reload. Then run stanza-create to initialize the repository. Follow it with check, which forces a WAL switch and confirms that the segment actually arrives in the bucket. A successful check is the only evidence that archiving works end to end.

A failing archive_command does not lose data right away. PostgreSQL keeps every unarchived segment in pg_wal and retries until archiving succeeds. While it retries, pg_wal keeps growing until the disk fills and the primary stops accepting writes, so a broken credential can turn into an outage.

Copy# Run as root. Write the config with credentials and passphrase (placeholders only).
install -d -o postgres -g postgres -m 750 /etc/pgbackrest
cat > /etc/pgbackrest/pgbackrest.conf <<'EOF'
[global]
repo1-type=s3
repo1-s3-bucket=pg-backups-example
repo1-s3-endpoint=s3.eu-central-1.example.com
repo1-s3-region=eu-central-1
repo1-s3-key=EXAMPLEACCESSKEYID
repo1-s3-key-secret=example-secret-key-do-not-use
repo1-path=/prod/main
# Encrypt the repository; losing this passphrase makes every backup unreadable
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=example-long-random-passphrase
log-level-console=info

[main]
pg1-path=/var/lib/postgresql/data
EOF

# Only postgres can read the secrets; no other local user can
chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
chmod 640 /etc/pgbackrest/pgbackrest.conf

# Enable archiving (archive_mode needs a full restart, not a reload)
cat >> /var/lib/postgresql/data/postgresql.conf <<'EOF'
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
EOF
systemctl restart postgresql   # service name varies by distribution

# Initialize the repository, then prove a WAL segment reaches the bucket
sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main check

# Ongoing signal: a rising failed_count means pg_wal is growing on disk
sudo -u postgres psql -c "SELECT archived_count, failed_count, last_failed_wal, last_failed_time FROM pg_stat_archiver;"

Writes a permission-restricted pgbackrest.conf for an encrypted S3-compatible repository, enables WAL archiving, verifies it end to end with stanza-create and check, and shows how to watch pg_stat_archiver for archive failures.

Running backups, retention, and reading pgbackrest info

Each backup type is a run of pgbackrest --stanza=main --type=full backup, with full, diff or incr as the type. If no full backup exists yet, pgBackRest takes a full even when you request incr, because an incremental needs a base. A common schedule is a weekly full, daily differentials and hourly incrementals. The schedule decides how much WAL a restore must replay, since replay starts from the most recent backup before your target.

Retention is set with repo1-retention-full, which is the number of full backups to keep. Expiry runs automatically after each backup. When a full backup expires, pgBackRest also removes the differentials and incrementals built on it, along with the archived WAL that only those backups needed. Retention therefore defines your recovery window. With repo1-retention-full=2 and weekly fulls, you can recover to points from roughly one to two weeks back, and nothing earlier.

pgbackrest info shows that window. For the stanza it lists the archive's minimum and maximum WAL segment. For each backup it lists the label, type, start and stop timestamps, the WAL start and stop segments, database size and repository size. The earliest point you can recover to is the stop time of the oldest backup still listed. Recovery can target any time from that point up to the newest WAL that has been archived.

A missing or expired full breaks every differential and incremental that depends on it.

Copy# Retention lives in /etc/pgbackrest/pgbackrest.conf on the database host:
#   [global]
#   repo1-retention-full=2   # keep two fulls; older fulls, their diff/incr
#                            # backups and the WAL only they need are expired

# Run as the postgres OS user. Expire runs automatically after each backup.
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

# Example schedule (postgres user's crontab): weekly full, daily diff, hourly incr
#   30 1 * * 0    pgbackrest --stanza=main --type=full backup
#   30 1 * * 1-6  pgbackrest --stanza=main --type=diff backup
#   15 * * * *    pgbackrest --stanza=main --type=incr backup

# Human-readable view: archive min/max WAL plus every backup set
sudo -u postgres pgbackrest --stanza=main info

# Machine-readable view for scripts and monitoring
sudo -u postgres pgbackrest --stanza=main --output=json info > /tmp/pgbr-info.json

# Oldest backup still in the repo: its stop time is the start of the recovery window
jq -r '.[0].backup[0] | "\(.label) \(.type) stop=\(.timestamp.stop | todate)"' /tmp/pgbr-info.json

# WAL range in the archive: recovery can target up to the newest archived segment
jq -r '.[0].archive[] | "wal min=\(.min) max=\(.max)"' /tmp/pgbr-info.json

Shows how to set repo1-retention-full, run full, differential and incremental backups on a schedule, and read the recovery window from pgbackrest info in both text and JSON output.

Restoring to a timestamp on a separate host, step by step

Start with a scratch host that runs the same PostgreSQL major version as production. Physical backups cannot be restored across major versions. Copy the production pgBackRest configuration so the stanza name and repository settings match. Ideally, give this host repository credentials that can read but not write. If PostgreSQL is running there, stop it first. pgBackRest refuses to restore into a data directory used by a live server.

Run the restore

Call pgbackrest restore with your --stanza, --type=time and a --target timestamp. Always include an explicit UTC offset, such as +00, in the timestamp. Without one, PostgreSQL interprets the value in the restored server's TimeZone setting. That setting may differ from the zone used by whoever reported "the table was dropped at 14:05", and an offset mismatch of a few hours either replays the damage or stops well before it.

If the data directory already holds a previous restore, add --delta. pgBackRest then checksums the existing files and fetches only those that differ, instead of requiring an empty directory.

Pause, inspect, then commit

Set --target-action=pause for investigative restores. When replay reaches the target, the server stays in recovery and accepts read-only connections as long as hot_standby is on. Query the affected tables and confirm that the rows you expect are present and the damage is absent. If the target was wrong, adjust it and restore again with --delta. Nothing has been committed yet. When the data looks right, call pg_wal_replay_resume(). Recovery ends, the server promotes, and it starts a new timeline. Use --target-action=promote only when you already trust the target.

Before you start the server, make sure the restored instance cannot push WAL into the production stanza. Pass --archive-mode=off to the restore, or set archive_mode = off yourself. Without this, the promoted copy archives its new timeline into the production archive alongside production's own WAL.

Copy#!/usr/bin/env bash
# Point-in-time restore on a scratch host (same PostgreSQL major version,
# same stanza and repo settings as production, read-only repo credentials).
set -euo pipefail

STANZA=main
PGDATA=/var/lib/postgresql/16/main   # adjust to your layout

# pgBackRest will not restore over a running cluster.
sudo systemctl stop postgresql

# --type/--target: stop replay at this moment. Always give a UTC offset,
#   otherwise the value is read in the restored server's TimeZone.
# --target-action=pause: stay in read-only recovery so we can inspect first.
# --delta: reuse files from a previous restore, fetch only what differs.
# --archive-mode=off: restored server must not archive WAL into the
#   production stanza while we test.
sudo -u postgres pgbackrest --stanza="$STANZA" \
  --pg1-path="$PGDATA" \
  --type=time --target='2026-10-22 14:04:30+00' \
  --target-action=pause \
  --delta \
  --archive-mode=off \
  restore

sudo systemctl start postgresql

# Inspect while paused (requires hot_standby = on, the default).
sudo -u postgres psql -X <<'SQL'
SHOW TimeZone;
SHOW archive_mode;                         -- expect: off
SELECT pg_is_in_recovery();               -- expect: t
SELECT pg_last_xact_replay_timestamp();   -- last replayed commit
SELECT count(*) FROM public.orders;       -- confirm expected rows exist
SQL

# Target wrong? Stop, change --target, rerun the restore with --delta.
# Data correct? End recovery; the server promotes onto a new timeline:
#   sudo -u postgres psql -X -c 'SELECT pg_wal_replay_resume();'

A scratch-host restore to a UTC-qualified timestamp that pauses at the target with WAL archiving disabled, then verifies the data with read-only queries before any promotion.

Recovery targets, inclusive behavior, and timelines

Time is the most common recovery target, but rarely the most precise one. pgBackRest passes --type through to PostgreSQL's recovery settings, and each target type behaves differently.

Targets are inclusive by default. Recovery replays the transaction that matches the target and then stops. If you point --type=xid at the DROP TABLE itself, inclusive mode replays the drop. Add --target-exclusive in that case, or aim at the last good transaction instead. The opposite mistake also happens. If you set a time target a few seconds early "to be safe," every legitimate commit in that gap is lost. Use --target-action=pause and inspect the data before you promote.

Each promotion starts a new timeline. The new WAL segment names carry the next timeline ID, and a .history file goes into the archive to record where the fork happened. A restored copy has the same system identifier as its source, so pgBackRest cannot tell the two clusters apart. If both archive into one stanza, the archive ends up holding WAL from two diverged histories, and later restores can follow the wrong one. Stop or fence the old primary before you promote the replacement.

When to avoid pgBackRest PITR and the alternatives

pgBackRest restores a whole cluster, because it copies data files and replays WAL. It cannot put one dropped table or one damaged database back into a running server. Granular recovery means restoring to a side host, recovering to just before the mistake, then copying the rows back with pg_dump or COPY. That works, but it costs a full restore to get back a few megabytes.

Restore time has two parts. Fetching the backup from object storage is limited by network throughput. WAL replay runs mostly single-threaded on the startup process, so a long gap since the last full or differential backup can matter more than bandwidth.

Combining them

Use pgBackRest PITR for losing a cluster or for corruption you find hours later. Add nightly pg_dump runs of the tables that matter most, so restoring one table takes minutes instead of a full cluster restore. A delayed replica of an hour or so is a fast way to recover from an operator error spotted quickly, but it does not replace an off-host archive.

Making restores routine: drills, monitoring, cost and engineering time

Scheduled restore drills

A backup you have never restored is a hypothesis. Schedule a drill, weekly or monthly, that provisions a scratch host, runs the same restore script you would use in an incident against a recent timestamp, starts PostgreSQL, and runs a few sanity queries such as row counts on key tables. Record wall-clock time from start to first successful query. That figure is your measured RTO. Watch how it changes as the database grows.

Alerts, not logs

Query pg_stat_archiver on a schedule and alert on two conditions:

• failed_count increasing, or last_failed_time newer than last_archived_time.
• last_archived_time older than a threshold you choose.

A stuck archive_command causes two problems. WAL piles up in pg_wal until the disk fills, and your recovery window quietly stops advancing. Run pgbackrest check from the same job and route both results to your paging system.

Cost versus recovery window

pgBackRest expires WAL along with the backups it depends on. If you lower repo1-retention-full to save money, you also shorten how far back you can recover.

Illustrative estimate (assumed numbers, not a benchmark). Assumptions:

• Compressed full backup: 60 GB, taken weekly.
• Daily incrementals: 5 GB each.
• Compressed WAL: 8 GB/day.
• Retention: 2 full backups, which covers about 14 days.

Formula: storage ≈ (fulls × full size) + (incrementals × incremental size) + (days × WAL per day)

Result: (2 × 60) + (12 × 5) + (14 × 8) = 292 GB.

Differential backups use more storage than incrementals. In exchange, a restore needs fewer backups in the chain.

Engineering time

During an incident, a rehearsed script replaces decisions made under pressure with a single command and a timestamp. It also gives you a known duration you can tell stakeholders.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to pgbackrest point in time recovery, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.