PostgreSQL Point in Time Recovery with pgBackRest and Restore Drills

What PostgreSQL point in time recovery is and how it works

PostgreSQL point in time recovery (PITR) rebuilds a cluster as it existed at a chosen moment. It needs two inputs. The first is a base backup, which is a physical copy of the data directory taken while the server runs. The second is a continuous WAL archive, which holds every write-ahead log segment produced since that backup. During recovery, PostgreSQL starts from the base backup and replays archived WAL until it reaches a recovery target. The target can be a timestamp, a transaction ID, an LSN, or a named restore point created earlier with pg_create_restore_point().

Both inputs are required. A base backup taken during activity is internally inconsistent, and only WAL replay makes it consistent. WAL without a base backup has nothing to apply to. A gap in the archive ends replay at the last segment before the gap, so you can recover only up to that point.

PITR works on the whole cluster. You cannot use it to roll back a single table or database in place. To recover one dropped table, restore the cluster elsewhere and copy the data back.

A recovery procedure that has never been run end to end is an assumption, not a plan. Missing segments, a wrong passphrase, or a mistyped target show up only during a real restore. The diagram includes a drill host for this reason.

Data flow:

• Primary: runs archive-push for each completed WAL segment and sends periodic full, differential, and incremental backups. Both go to the pgBackRest repository.
• Repository: stores the backups and the WAL archive. It sits on separate storage from the primary.
• Drill host: isolated from production. Runs a restore from the repository, replays WAL to a target, and the result is verified.

Copy+---------------------------+          +-------------------------------+
| PRIMARY (production)      |          | pgBackRest REPOSITORY         |
|                           |          | (separate storage)            |
| PostgreSQL cluster        |          |                               |
| archive_command runs      |  WAL     |  +-------------------------+  |
|   pgbackrest archive-push |--------->|  | WAL archive             |  |
|                           |          |  | continuous segments     |  |
|                           |          |  +-------------------------+  |
| pgbackrest backup         | backups  |  +-------------------------+  |
|   full / diff / incr      |--------->|  | Base backups            |  |
+---------------------------+          |  | full / diff / incr      |  |
                                       |  +-------------------------+  |
                                       |                               |
                                       +-------------------------------+
                                  restore, replay WAL  |
                                                       v
                                       +-------------------------------+
                                       | DRILL HOST (isolated)         |
                                       |                               |
                                       | pgbackrest restore            |
                                       |   --type=time|xid|lsn|name    |
                                       | WAL replay to recovery target |
                                       | verify data, then discard     |
                                       +-------------------------------+

The primary pushes each completed WAL segment and its full, differential and incremental backups to a pgBackRest repository on separate storage, and an isolated drill host restores from that repository and replays WAL to a recovery target to show that recovery works.

Who needs it, when to use it and when to avoid it

Who should run it

pgBackRest PITR fits teams running PostgreSQL themselves, on VMs, bare metal or Kubernetes, who have written down a recovery point objective (RPO, how much data you can lose) and a recovery time objective (RTO, how long you can be down). Without those targets you can't choose an archive frequency, a full-backup schedule or a retention window, and you can't tell whether a drill passed.

Where it pays off

PITR exists for damage that replicates instantly. A DELETE without a WHERE clause, a migration that rewrites a column incorrectly, or an application bug that corrupts rows will reach every streaming replica within seconds. Only the WAL archive still records the state just before the mistake, so you can replay to that moment and stop.

Where it is the wrong tool or not enough

• Single-object recovery. PITR restores a whole cluster. To recover one dropped table, you restore to a separate host, export the table, and copy the rows back into production by hand, resolving any writes made since then.
• High availability. A restore takes time proportional to data size and WAL volume. For failover in seconds or minutes you need replicas. PITR and HA cover different failures, and most teams need both.
• Managed services. If your provider already offers PITR, a self-run pgBackRest setup duplicates it and adds operational burden.
• Small databases. If a logical dump restores within your RTO and its schedule meets your RPO, it is simpler to operate.

Configuring WAL archiving and the pgBackRest stanza

Archiving is controlled by three settings in postgresql.conf. Set wal_level to replica (or logical), because minimal omits information that recovery needs. Set archive_mode = on, and set archive_command to pgbackrest --stanza=main archive-push %p. PostgreSQL substitutes the path of each completed WAL segment for %p. Changing wal_level or archive_mode requires a restart, so make these changes during a maintenance window.

A minimal pgbackrest.conf needs two sections. The [global] section holds the repository settings, such as repo1-path and repo1-retention-full. A stanza section, such as [main], holds pg1-path, which points at the data directory. Run pgbackrest --stanza=main stanza-create to initialize the repository. Then run pgbackrest --stanza=main check, which forces a WAL switch and confirms that the segment reaches the repository. Seeing "archive_mode = on" in the config does not show that segments are being stored. A passing check does.

A failing archive_command is dangerous. PostgreSQL keeps every segment that has not been archived, so pg_wal grows without limit. If that volume fills, the primary shuts down. Alert on failed_count and last_failed_time in pg_stat_archiver, and on free space in pg_wal.

Compression (compress-type, for example lz4 or zst) shrinks the repository at the cost of CPU on the host doing the push. Faster algorithms keep that cost modest. Encryption (repo1-cipher-type=aes-256-cbc with repo1-cipher-pass) also adds CPU cost. If you lose the passphrase, every backup in that repository is unrecoverable. Store the passphrase in a secrets manager separate from the database hosts.

Copy# 1. postgresql.conf (wal_level and archive_mode need a restart):
#   wal_level = replica
#   archive_mode = on
#   archive_command = 'pgbackrest --stanza=main archive-push %p'

# 2. Minimal pgbackrest.conf. Set the cipher before stanza-create,
#    because encryption cannot be enabled later on an existing repository.
sudo tee /etc/pgbackrest/pgbackrest.conf > /dev/null <<'EOF'
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
# zst gives good compression at moderate CPU; lz4 is cheaper but larger
compress-type=zst
repo1-cipher-type=aes-256-cbc
# Placeholder only. Fetch the real value from a secrets manager.
# If this passphrase is lost, every backup in repo1 is unrecoverable.
repo1-cipher-pass=REPLACE_WITH_PASSPHRASE_FROM_SECRETS_MANAGER

[main]
pg1-path=/var/lib/postgresql/16/main
EOF
# The file contains the passphrase, so keep it readable only by postgres
sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
sudo chmod 600 /etc/pgbackrest/pgbackrest.conf

# 3. Initialize the repository, then prove a WAL segment actually arrives
sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main check

# 4. Values to alert on: archive failures and free space under pg_wal
sudo -u postgres psql -c "SELECT archived_count, failed_count, last_failed_wal, last_failed_time FROM pg_stat_archiver;"
df -h /var/lib/postgresql/16/main/pg_wal

Enables WAL archiving through pgbackrest archive-push, defines a compressed and encrypted repository with a single stanza, confirms that archiving works with stanza-create and check, and shows the pg_stat_archiver and disk-space values to alert on.

Repository placement, backup types and retention

The repository holds your only copy of history, so place it where the event that destroys the primary cannot reach it.

pgBackRest supports three backup types. A full backup copies every file. A differential copies the files changed since the last full. An incremental copies the files changed since the previous backup of any type. A differential needs its full. An incremental needs every backup back to the full. If one link in that chain is expired or corrupted, every backup that depends on it is unusable. When pgBackRest expires a full backup, it also expires the backups that depend on it.

Backup type affects how long backups take, not how much a restore writes. A restore always writes the whole cluster and then replays WAL to the target. Illustrative estimate: assume a 500 GB cluster restored at 250 MB/s, which takes about 35 minutes. Add the replay of 80 GB of WAL generated since that backup. Taking full or differential backups more often reduces the WAL to replay.

Set repo1-retention-full (and repo1-retention-archive if you set it) so that the WAL from your oldest retained backup forward is never expired. Without that WAL, the oldest backup cannot reach any recovery point. Write the recovery window down as an explicit decision, for example "14 days." Record who approved it and what it costs in storage.

Copy# /etc/pgbackrest/pgbackrest.conf on the database host
# Recovery window: 14 days (record the approver and the storage cost alongside this file)

[global]
# Repository in object storage, in a different region and account from the database
repo1-type=s3
repo1-s3-bucket=example-pg-backups-dr
repo1-s3-endpoint=s3.us-west-2.amazonaws.com
repo1-s3-region=us-west-2
repo1-path=/pgbackrest
# Take credentials from the instance role so no keys are stored in this file
repo1-s3-key-type=auto

# Encrypt the repository, because it sits outside the database's trust boundary
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=CHANGE_ME_long_random_passphrase

# Retention is time-based: keep enough full backups to cover 14 days of history.
# A full backup is expired only when a newer full is at least 14 days old, so the
# oldest retained backup always predates the start of the window.
repo1-retention-full-type=time
repo1-retention-full=14

# Keep differentials only for the most recent fulls to limit chain length
repo1-retention-diff=7

# repo1-retention-archive is deliberately unset. pgBackRest then keeps WAL from the
# oldest retained backup forward, so that backup can still reach a recovery point.

# Throughput settings: more processes help when network bandwidth is the bottleneck
process-max=4
compress-type=zst
start-fast=y

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

A pgBackRest configuration that keeps the repository in object storage in a separate region and uses time-based full retention, with archive retention left unset so WAL is kept from the oldest retained backup forward to support a documented 14-day recovery window.

Restoring to a target time, LSN or named restore point

Run the restore with the cluster stopped. For a time target, pass --type=time and --target="2024-05-01 14:29:00+00". Always include an explicit UTC offset, because a bare timestamp is interpreted in the server's time zone. Recovery stops at the first transaction whose commit timestamp reaches the target. By default the target is inclusive. --target-exclusive stops just before it. Use --type=lsn when you have an exact WAL position, and --type=name for a named restore point.

--target-action controls what happens once the target is reached:

• pause holds the server read-only so you can check that the bad change is absent.
• promote opens the server for writes immediately.

Prefer pause. A wrong guess can then be corrected by restoring again, with no history forked yet. When you are restoring onto an existing data directory, add --delta. It compares file checksums and copies only what differs, which is much faster than wiping the directory and copying everything.

Timestamps are the most common way to get this wrong. Recovery compares against commit time, not the time a statement started. A migration that began at 14:20 but committed at 14:31 is still present at a 14:30 target. A target taken from an application log with a skewed clock can also land after the damage, which replays the change you meant to undo. Before any risky migration, run pg_create_restore_point('pre_migration_x') so the target is a name rather than a guess.

Promotion starts a new timeline. Later restores must follow the intended timeline, which --target-timeline selects. Take a fresh full backup right after recovery so that future restores have a clean base on the new history.

Copy#!/usr/bin/env bash
set -euo pipefail
STANZA=main
PGDATA=/var/lib/postgresql/16/main   # placeholder data directory

# 1. Before a risky migration, record an unambiguous target (run on the primary).
psql -d appdb -c "SELECT pg_create_restore_point('pre_migration_x');"

# --- later, after the migration went wrong ---

# 2. Restore requires the cluster to be stopped.
pg_ctl -D "$PGDATA" stop -m fast

# 3. Restore to the named point. --delta reuses matching files in the
#    existing data directory instead of copying the whole cluster again.
#    pause keeps the server read-only so the result can be inspected.
pgbackrest --stanza=$STANZA --delta \
  --type=name --target=pre_migration_x \
  --target-action=pause \
  restore

# Alternative with no restore point. Use an explicit UTC offset. Recovery
# compares against commit time, and --target-exclusive stops just before
# a transaction committed exactly at the target.
# pgbackrest --stanza=$STANZA --delta --type=time \
#   --target="2024-05-01 14:29:00+00" --target-exclusive \
#   --target-action=pause restore

pg_ctl -D "$PGDATA" start

# 4. While paused, confirm the bad change is absent before forking history.
psql -d appdb -c "SELECT count(*) FROM orders;"   # replace with a query that checks your change

# 5. If the result is correct, end recovery. This promotes the server and
#    starts a new timeline. If it is wrong, stop the server and restore again.
psql -d appdb -c "SELECT pg_wal_replay_resume();"

# 6. Take a fresh full backup so later restores have a base on the new timeline.
pgbackrest --stanza=$STANZA --type=full backup

This drill creates a named restore point before a migration, restores to it with --delta and --target-action=pause, checks the data, promotes, and then takes a fresh full backup on the new timeline.

Running a scheduled restore drill on an isolated host

That fresh full backup is only useful if you can prove it restores, so treat the restore as a scheduled job rather than an emergency procedure.

Drill outline

1. Provision an isolated host with no network route to production clients or replicas.
2. Pick a target timestamp. A good choice is a recent value from a heartbeat table that production updates every minute.
3. Record the start time, then run pgbackrest restore with --type=time, the chosen --target, and --target-action=promote.
4. Start PostgreSQL and wait until pg_is_in_recovery() returns false.
5. Run validation queries and record the elapsed time against your RTO.
6. Destroy the host.

Validation queries

Check data whose state you can predict from the target time:

• max(ts) in the heartbeat table should fall just before the target, and no rows should appear after it.
• Row counts in append-only tables should match counts captured at that timestamp.
• A known order or account balance should hold its expected value.

Generic checks such as "the server started" or "a table exists" catch little. Assertions about specific data catch silent WAL gaps.

Keep the drill path identical to production

A drill can pass on a convenient setup and still hide a broken production restore. Common examples are a local repository copy, a hand-edited config, or broader credentials than the real recovery host would have. To avoid this:

• Deploy pgbackrest.conf from the same configuration management that production uses.
• Read from the real repository with the same credential type the production recovery host would use, scoped to read-only.
• Match the PostgreSQL major version, extensions and instance size.
• Disable archive_mode on the restored instance so it never pushes WAL into the production stanza.

Illustrative estimate. Assume a 500 GB backup restored at 400 MB/s, which takes about 21 minutes. Add 50 GB of WAL replayed at 100 MB/s, about 9 minutes. The total is roughly 30 minutes before validation begins.

Monitoring, alternatives and impact on cost and engineering time

That restore time is only trustworthy while the inputs behind it stay healthy, so monitor those inputs directly.

Signals worth alerting on

• Archive failures: a rising failed_count in pg_stat_archiver, or a last_failed_time newer than last_archived_time. While archiving fails, pg_wal keeps growing and your recovery window stops advancing.
• Age of the last archived WAL segment: if now() - last_archived_time exceeds your RPO, alert. A quiet database may archive rarely, so set archive_timeout to bound the gap.
• Age of the last successful backup, read from pgbackrest info rather than from the scheduler.

Exit codes catch only jobs that ran and failed. A job that never ran produces no exit code, so alert on how old the newest backup or segment is.

Alternatives

Cost

Illustrative estimate (assumed numbers, not a benchmark): monthly drill cost = host hours × hourly rate + engineer hours × loaded rate. With 2 h × $1 and 1 h × $100, that is about $102. Compare that with the hours an incident spends confirming whether backups work, plus the hours an audit spends producing restore evidence. A logged drill answers both questions in advance.

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 postgresql 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.