pgBackRest Point in Time Recovery: WAL Archiving and Restore Tests

How does point-in-time recovery work in PostgreSQL with pgBackRest?

Point-in-time recovery (PITR) has two parts: a base backup, which is a physical copy of the data directory taken at one moment, and the write-ahead log (WAL) generated after it. PostgreSQL writes every change to WAL before applying it to data files. If you restore the base backup and replay archived WAL segments in order, you can stop at any moment you choose, such as a timestamp, a transaction ID, or a named restore point.

Replay is strictly sequential. Each segment depends on the one before it, so a single missing segment stops recovery at the gap. Every target after that point becomes unreachable, no matter how many later segments you have. For this reason the WAL archive, not the backup command, is the real foundation. A nightly backup with a broken archive gives you nightly recovery points and nothing in between.

pgBackRest divides the work into four roles:

• The PostgreSQL primary generates WAL and calls archive_command each time a segment is complete.
• archive-push is the pgBackRest command set as archive_command. It compresses each segment and stores it in the repository. PostgreSQL recycles a segment only after archive-push reports success.
• The repository holds the base backups and archived WAL, on local disk, a remote host, or object storage.
• restore writes a base backup back to disk and configures recovery so PostgreSQL fetches WAL through archive-get and replays it up to your target.

With this setup you can recover from a bad DELETE, a broken migration, or silent corruption, and land just before the damage occurred. PITR suits teams running self-managed Postgres who need a recovery point objective measured in minutes rather than the 24 hours that nightly pg_dump allows.

Copy+------------------------+                  +-------------------------------+
| PostgreSQL primary     |                  | Repository                    |
|  generates WAL         |   archive-push   |  local / remote / object store|
|  archive_command ------+----------------->|                               |
|                        |  (compress, then |  +-------------------------+  |
|  recycles segment only |   ack success)   |  | base backups            |  |
|  after ack             |                  |  +-------------------------+  |
+-----------+------------+                  |  | archived WAL            |  |
            |                               |  | 001 002 003 gap 005 006 |  |
            +--- backup ------------------->|  +-------------------------+  |
                                            |                               |
                                            +---------------+---------------+
                                                            |  archive-get
                                                            v
                             +----------------------------------------------+
                             | restore                                      |
                             | write base backup, replay WAL in order,      |
                             | stop at target: time, xid or restore point   |
                             +----------------------------------------------+

  replay:  backup --> 001 --> 002 --> 003 --X gap   (005, 006 unreachable)

The primary hands each completed WAL segment to archive-push, the repository stores base backups and archived WAL, and restore replays segments in order to the target, so one missing segment makes every later target unreachable.

How does it work, step by step?

The roles above interact in a fixed sequence. Knowing it tells you where to look when something breaks.

1. WAL is archived continuously. PostgreSQL fills WAL segment files, 16 MB each by default. When a segment is complete, or when archive_timeout forces a switch, PostgreSQL runs archive_command with the segment's path. pgBackRest's archive-push copies the segment into the repository and exits with success. If the exit code is non-zero, PostgreSQL keeps the segment and retries.
2. A backup is taken while the database runs. pgbackrest backup tells PostgreSQL that a backup is starting, copies the data files while writes continue, and then tells PostgreSQL that the backup has finished. Files copied while they were being modified are inconsistent on their own. They become consistent once the WAL generated between the backup's start and stop positions is replayed. pgBackRest records those positions in the backup manifest along with a checksum for every file. By default it also waits until the WAL needed for consistency has reached the archive before it marks the backup complete.
3. Restore rebuilds the data directory. pgbackrest restore selects a backup, copies its files back, and verifies them against the manifest. It then writes the recovery configuration. On current PostgreSQL versions this is a restore_command that calls pgbackrest archive-get, plus the recovery target settings and a recovery.signal file.
4. PostgreSQL replays WAL on startup. The server finds the signal file and enters recovery. It asks archive-get for each segment in turn and applies the changes. It stops at the recovery target and then pauses, promotes, or shuts down according to --target-action.

Steps 1 and 2 run all the time in production. Steps 3 and 4 run only during an incident or a restore test, so the regular restore tests described below are the only proof that they work.

Why is pgBackRest PITR used?

Teams adopt pgBackRest PITR to close specific gaps that simpler backup methods leave open:

• Fine-grained recovery points. A dump or snapshot can only take you back to the moment it was taken. Continuous WAL archiving lets you recover to just before a specific bad transaction, so you lose only the work that came after the damage, not everything since the last nightly job.
• Restore speed on large databases. A physical restore copies files and replays WAL. It does not re-execute SQL or rebuild indexes, which is where logical restores of large databases spend most of their time. pgBackRest can also compress and transfer files in parallel.
• Integrity you can check. File checksums in the manifest and the archive check at backup time catch corrupted or incomplete backups before you need them, not during an incident.
• Retention that stays consistent. Expiry removes old backups together with the WAL that only they need. Doing this by hand with scripts is a common way to end up with a backup that cannot reach a consistent state.
• Off-host copies. Repositories on a remote host or in object storage, with optional encryption, keep backups available when the database host is lost.

In short, pgBackRest turns "we have backups" into a repeatable process with a known recovery point and a recovery time you can measure.

Who should use pgBackRest PITR?

pgBackRest is aimed at the engineers who are responsible for a PostgreSQL cluster's data when no provider is doing it for them:

• DBAs and SREs running self-managed PostgreSQL on bare metal, virtual machines, or containers they operate themselves, where backup and recovery are their responsibility.
• Teams with large or busy databases, where a logical dump takes too long to restore or a nightly recovery point loses too much data.
• Teams with stated RPO and RTO commitments, such as internal SLAs or audit requirements that call for a defined retention window and evidence that restores work.
• Operators of several clusters who want one repository and one consistent procedure for all of them, instead of separate scripts per host.

It is a weaker fit for teams on a managed PostgreSQL service with built-in PITR, for small databases where pg_dump meets the RTO, and for teams with no one available to own and monitor another piece of infrastructure. The next section covers those trade-offs in more detail.

When should you use pgBackRest PITR, and when should you avoid it or choose an alternative?

Each alternative covers part of what pgBackRest does. The question is which gaps you can live with.

pgBackRest is worth the operational cost when backups and restores take long enough that parallel compression and transfer matter. It also fits when you need incremental backups that skip unchanged files, or retention that expires old backups together with the WAL they depend on.

It is overkill for a database small enough that a nightly pg_dump restores within your RTO. It also adds little on a managed service that already provides PITR. It is the wrong tool for recovering one dropped table. PITR rewinds the whole cluster, so you would restore to a scratch instance and copy the rows back.

Repository placement is the main trade-off. A repository on the database host protects against bad queries but not against losing the host or its disk. A remote host or object store survives those failures. In exchange, archiving now depends on network reachability, and you have to rotate and protect the storage credentials.

How do you configure a pgBackRest stanza and WAL archiving?

pgbackrest.conf has two kinds of sections. The [global] section describes the repository: repo1-path (or object store settings), retention, compression via compress-type, and encryption via repo1-cipher-type and repo1-cipher-pass. A stanza section such as [main] describes one PostgreSQL cluster, mainly pg1-path pointing at its data directory. Keeping these separate lets one repository serve several clusters.

On the PostgreSQL side, set wal_level to replica or higher so WAL contains enough information to replay. Set archive_mode = on, and set archive_command to pgbackrest --stanza=main archive-push %p. Changing archive_mode or wal_level requires a restart. A reload is not enough.

Next, run stanza-create. It initializes the repository layout and records the cluster's system identifier, so a misconfigured host cannot later push WAL into the wrong stanza. Then run pgbackrest check. It forces a WAL segment switch and confirms that the segment actually reaches the repository. This is the first real evidence that archive_command works end to end, including permissions and network paths. Until check passes, assume no WAL is being saved.

After the first backup, pgbackrest info lists each backup with its type, timestamps, and size. It also shows the minimum and maximum archived WAL segment, which tells you the earliest and latest points you can recover to.

Encryption limits exposure if the repository leaks, and compression shrinks storage and transfer. Both cost CPU on every push and restore. Encryption also adds a hard dependency: without the passphrase, the backups cannot be read. Store the passphrase somewhere you can still reach when the primary host is gone. Encryption must also be configured before running stanza-create.

Copysudo tee /etc/pgbackrest/pgbackrest.conf >/dev/null <<'EOF'
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
compress-type=zst
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=CHANGE-ME-long-random-passphrase

[main]
pg1-path=/var/lib/postgresql/16/main
EOF
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf

sudo -u postgres psql -c "ALTER SYSTEM SET wal_level = 'replica';"
sudo -u postgres psql -c "ALTER SYSTEM SET archive_mode = 'on';"
sudo -u postgres psql -c "ALTER SYSTEM SET archive_command = 'pgbackrest --stanza=main archive-push %p';"
sudo systemctl restart postgresql   # unit name varies by distro and version

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 info

Sets up an encrypted, compressed pgBackRest repository and a main stanza, turns on PostgreSQL WAL archiving through archive-push, and then checks the setup with stanza-create, check, a first full backup, and info.

Which backup types and retention settings should you choose?

pgBackRest supports three backup types, and they differ in what each one depends on:

The manifest lets pgBackRest copy each file from whichever backup holds it, so a long chain does not slow the copy much. It does make restores more fragile. A daily incremental six days after a full needs seven backup sets intact and readable. Losing any one of them breaks the restore.

Retention works on whole chains. repo1-retention-full sets how many full backups to keep. Expiring a full also expires the differentials and incrementals that depend on it, along with the WAL that only that chain needed. Size retention from the recovery window you promise. To recover to any point in the last 14 days, the oldest retained backup must start before that window, and the WAL from that backup forward must still exist. Do not set repo1-retention-archive lower than this window. Longer retention increases storage cost and can also mean replaying more WAL.

Illustrative estimate (assumed inputs): assume a weekly full, no differentials or incrementals, 50 GB of WAL per day, and a replay rate of 60 GB/hour. Recovering to a point six days after the full means replaying about 300 GB of WAL, which takes roughly five hours on top of the file copy. Adding daily differentials cuts the replay to at most one day of WAL, about 50 minutes. In both cases no data is lost, but the recovery time objective (RTO) is very different.

Copy[global]
repo1-path=/var/lib/pgbackrest

repo1-retention-full=3

repo1-retention-diff=7

repo1-retention-archive-type=full

archive-async=y
spool-path=/var/spool/pgbackrest

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

A pgbackrest.conf with weekly fulls and daily differentials. It keeps enough full backups and WAL to cover a 14-day recovery window, keeps each restore chain to at most two backup sets, and limits WAL replay to about one day.

How do you restore PostgreSQL to a specific point in time with pgBackRest?

Stop PostgreSQL on the target host, then run pgbackrest restore with --type=time and a --target timestamp that includes an explicit UTC offset, such as +00. Add --delta to reuse files that already match the backup instead of copying everything. pgBackRest picks the most recent backup that finished before the target, writes the recovery settings, and PostgreSQL replays WAL on startup.

The target is inclusive by default: recovery stops just after the first transaction that committed at or after the target time. Add --target-exclusive to stop just before it. --target-action controls what happens next:

• pause (default): the server stays in recovery, read-only if hot_standby is on. Check the data, then call pg_wal_replay_resume() to promote. If hot_standby is off, pause behaves like shutdown.
• promote: the server opens for writes immediately.
• shutdown: PostgreSQL stops at the target, which is useful for scripted pipelines.

Guessing a target from application logs is error-prone. Log timestamps record when the app wrote a line, not when the transaction committed, and clock skew and missing time zones shift the result further. Before a risky migration, record now() from the database itself, or call pg_create_restore_point('pre_migration') and restore with --type=name.

Promotion creates a new timeline. Later restores from the same repository follow the latest timeline by default. To reach a point on the old history, pass --target-timeline.

To recover one dropped table, restore to a separate host or data directory using --pg1-path, with --archive-mode=off so the scratch instance never pushes WAL into the repository. Recover to just before the DROP, export the table with pg_dump -t, and load it into production.

Copypsql -d appdb -c "SELECT pg_create_restore_point('pre_migration');"
psql -d appdb -c "SELECT now();"            # reference timestamp from the DB clock
psql -d appdb -c "SELECT pg_switch_wal();"

SCRATCH=/var/lib/postgresql/scratch
install -d -m 0700 "$SCRATCH"

pgbackrest --stanza=main \
  --pg1-path="$SCRATCH" \
  --type=time \
  --target='2026-11-03 14:05:00+00' \
  --target-exclusive \
  --target-action=pause \
  --archive-mode=off \
  restore
pg_ctl -D "$SCRATCH" -o '-p 5433' -w start

psql -p 5433 -d appdb -c "SELECT pg_is_wal_replay_paused(), pg_last_xact_replay_timestamp();"
pg_dump -p 5433 -d appdb -t public.orders -Fc -f /tmp/orders.dump

pg_restore -d appdb /tmp/orders.dump
pg_ctl -D "$SCRATCH" -m fast stop
rm -rf "$SCRATCH"

Recovering a single dropped table by restoring to a paused, read-only scratch instance with a time target, dumping the table with pg_dump, and loading it back into production.

How do you automate restore testing so backups are proven restorable?

A restore you have never run is a hypothesis. A scheduled job turns it into evidence.

What the job does

Run it nightly or weekly on a scratch host with the same PostgreSQL major version as production:

1. Run pgbackrest restore from the latest backup into an empty data directory, with --archive-mode=off. Use --type=time with a recent target so WAL replay is exercised too.
2. Start PostgreSQL and wait until pg_isready succeeds and recovery has finished.
3. Run sanity queries. Check row counts on key tables against a tolerance, and check that the newest created_at value is close to the recovery target. A stale timestamp means WAL replay stopped early.
4. Record the wall-clock time for each step, alert on any non-zero exit or failed check, then destroy the host.

Keep it cheap and fast, or it will be skipped

Each run needs storage for a full copy of the database and compute for WAL replay. An ephemeral instance that exists only for the test keeps that cost bounded. Slow or flaky tests get muted, then disabled, and that is how a broken backup goes unnoticed for months. Restore a representative subset if needed, make every failure point to a specific step, and never retry silently.

What you get back

You no longer need manual restore rehearsals. Every run produces a measured restore duration, so RTO planning uses real numbers instead of guesses. During an incident, the on-call engineer runs a procedure that has already succeeded many times.

How do you monitor WAL archive health and avoid a full pg_wal disk?

When archive_command fails, PostgreSQL does not reject writes. It keeps every unarchived segment in pg_wal and retries indefinitely. A broken repository credential or an unreachable object store therefore shows up first as a growing disk, and when that volume fills, the primary shuts down. Archiving failures need their own alerts.

What to watch

• pg_stat_archiver.failed_count: alert when it increases. Compare last_failed_time with last_archived_time. If the most recent failure is newer than the most recent success, archiving is currently broken, not just recovering from a transient error.
• Age of the last archived segment: now() - last_archived_time is your actual exposure. Set archive_timeout so idle databases still switch segments. Without it, a quiet database looks the same as a stalled archiver.
• pg_wal size: compare it to free space on the volume, not to a fixed number. Alerting on growth rate gives you time to act before the disk is full.
• pgbackrest info: check that the reported WAL archive range reaches the current segment and that every retained backup is covered.

Thresholds tied to RPO

With a 15-minute RPO, page when archive age passes about half of it (roughly 7 minutes). That leaves time to fix the problem before the RPO is breached. Page on pg_wal at about 70% of the volume. pgBackRest's archive-push-queue-max can protect the primary by discarding WAL once a limit is reached, but it breaks PITR continuity until the next backup, so only enable it as a deliberate choice.

The full setup takes a few days of engineering plus a small amount of ongoing alert maintenance. That cost is fixed and predictable. An unrecoverable data loss incident has no upper bound.

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.