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

Point-in-time recovery (PITR) rebuilds a cluster as it existed at a specific moment. It starts from a physical base backup and replays archived write-ahead log (WAL) on top of it until it reaches a target you choose. The target can be a timestamp, a transaction ID, an LSN, or a named restore point created earlier with pg_create_restore_point().

Replay is strictly sequential. Each WAL segment depends on the one before it, so the archive must form an unbroken chain from the end of the base backup onward. If a single segment is missing, replay stops there. Everything after the gap is unrecoverable from that backup, even if later segments sit safely in the archive. Your real recovery window ends at the first gap, not at the newest file in storage.

 ┌──────────────┐  archive_command:          ┌──────────────────────────┐
 │  Primary     │  pgbackrest archive-push   │  Repository              │
 │  PostgreSQL  │ ─────────────────────────► │  (backup host or         │
 │              │  backups: pgbackrest backup│   object storage)        │
 └──────────────┘ ─────────────────────────► │  base backups + WAL      │
                                             └────────────┬─────────────┘
                                                          │ pgbackrest restore
                                                          │ (backup + WAL to target)
                                                          ▼
                                             ┌──────────────────────────┐
                                             │  Recovery host           │
                                             │  replays WAL, stops at   │
                                             │  time / xid / LSN / name │
                                             └──────────────────────────┘

pgBackRest manages each part of this chain. It takes base backups, receives WAL through archive-push, enforces retention so backups and the WAL they need expire together, and restores the files and recovery settings on the target host.

This setup suits teams running self-managed Postgres. A replica protects against hardware loss, but it applies a bad deploy or an accidental DELETE within seconds. PITR lets you return to the moment just before the mistake.

┌─────────────────────┐   archive_command:          ┌──────────────────────┐
│ Primary PostgreSQL  │   pgbackrest archive-push   │ Repository           │
│                     │ ──────────────────────────► │ (backup host or      │
│ writes WAL segments │   pgbackrest backup         │  object storage)     │
│                     │ ──────────────────────────► │                      │
└─────────────────────┘   (full / diff / incr)      │ base backups         │
                                                    │ WAL archive          │
                                                    │ retention policy     │
                                                    └──────────┬───────────┘
                                                               │
                                            pgbackrest restore │
                                      --type=time|xid|lsn|name │
                                              --target=<value> │
                                                               ▼
                                                    ┌──────────────────────┐
                                                    │ Recovery host        │
                                                    │ restore base backup, │
                                                    │ replay WAL in order, │
                                                    │ stop at target       │
                                                    └──────────────────────┘

WAL replay is sequential; the first missing segment ends recovery:
[base]──►[seg 1]──►[seg 2]──►[seg 3]──►[ missing ]  [seg 5]──►[seg 6]
◄──────────── recoverable window ──►                ◄─ unreachable ─►

The primary pushes WAL and base backups to a separate pgBackRest repository, and a restore to a recovery host replays WAL in order up to a time, xid, LSN or named target, but never past the first gap in the WAL chain.

When should you use pgBackRest PITR, and what are the alternatives?

Each tool below covers a different failure, so the useful question is which failures you need to survive and how much data you can afford to lose.

Approach Protects against Recovery granularity Operational cost
pg_dump / pg_restore Data loss, corruption, migrations between major versions Time of the last dump Low setup; dump and restore time grow with size
Streaming replica Hardware or host loss None; mirrors the primary A second server, monitoring lag
Delayed replica Mistakes caught within the delay window Any point inside the delay A second server; useless once the delay passes
Filesystem or volume snapshot Disk loss, broad corruption Snapshot interval Low, but needs a consistent snapshot of data and WAL
Managed service backups Most of the above, handled by the provider Usually any moment within retention Low; limited control over storage and retention
pgBackRest PITR Hardware loss, bad deploys, accidental deletes Any transaction within retention Repository storage, WAL monitoring, restore drills

A replica is not a backup. It copies mistakes as faithfully as it copies good writes, so a dropped table disappears on the replica too.

PITR is overkill in two cases. The first is a small database where a nightly dump restores quickly enough and losing a day of writes fits your RPO. The second is a managed service that already provides PITR.

PITR also has a limit you should plan for. It restores the entire cluster to a single moment. To recover one damaged table without rolling back everything else, you restore to a separate instance and copy the rows back, for example with pg_dump on that one table.

How do you configure WAL archiving and pgbackrest.conf?

On the database host, three settings in postgresql.conf turn on archiving. wal_level = replica (or logical) makes PostgreSQL write enough information to WAL to replay changes on another server. archive_mode = on tells it to hand each completed segment to an external command. Changing archive_mode requires a restart, so plan for one. archive_command = 'pgbackrest --stanza=main archive-push %p' is that command. PostgreSQL substitutes %p with the segment's path and recycles the segment only after the command exits with zero.

That last behaviour is the main operational risk. A failing archive_command never stops the database. PostgreSQL retries indefinitely, and unarchived segments pile up in pg_wal until the disk fills and the server stops accepting writes.

In pgbackrest.conf, a stanza such as [main] names one cluster, and pg1-path points at its data directory. repo1-path sets where backups and archived WAL live. Pair it with repo1-host for a dedicated backup server, or repo1-type=s3 for object storage. repo1-retention-full sets how many full backups to keep. Expiring an old full backup also expires the WAL that depended on it.

compress-type trades CPU for storage and transfer size. repo1-cipher-type=aes-256-cbc with repo1-cipher-pass encrypts the repository. Store the passphrase outside the repository. If you lose it, every backup becomes unrecoverable.

Repository placement involves its own trade-off:

  • Local repository: protects against a dropped table but not against losing the host.
  • Remote repository: survives host loss but adds network latency, credentials to rotate, and storage costs.
# --- postgresql.conf (database host; restart required for archive_mode) ---
wal_level = replica
archive_mode = on
# PostgreSQL recycles a segment only after this exits 0; failures retry forever
archive_command = 'pgbackrest --stanza=main archive-push %p'

# --- /etc/pgbackrest/pgbackrest.conf (database host) ---
[global]
# Repository in object storage so backups survive loss of this host
repo1-type=s3
repo1-s3-bucket=example-pg-backups
repo1-s3-endpoint=s3.us-east-1.amazonaws.com
repo1-s3-region=us-east-1
repo1-path=/pgbackrest
# Credentials: prefer env vars (PGBACKREST_REPO1_S3_KEY, PGBACKREST_REPO1_S3_KEY_SECRET)
repo1-s3-key=PLACEHOLDER_ACCESS_KEY
repo1-s3-key-secret=PLACEHOLDER_SECRET_KEY

# Keep two full backups; expiring a full also expires the WAL it depended on
repo1-retention-full=2

# Encrypt the repository. Store this passphrase outside the repo:
# if it is lost, every backup in the repo is unrecoverable
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=PLACEHOLDER_LONG_RANDOM_PASSPHRASE

# Trade CPU for smaller uploads and storage
compress-type=zst

[main]
# One stanza per cluster; must match --stanza in archive_command
pg1-path=/var/lib/postgresql/16/main

A minimal postgresql.conf archiving block and a pgbackrest.conf that sends encrypted, compressed backups and WAL for the 'main' stanza to an S3 repository with two full backups retained.

How do you verify that WAL is actually reaching the repository?

Before anything can be archived, the repository needs a stanza: run pgbackrest stanza-create once per cluster. It records the cluster's system identifier and version in the repository, which is why later archive pushes fail loudly if they come from a different cluster.

Next, run pgbackrest check. It goes further than a config parse:

  1. It confirms PostgreSQL settings like archive_mode and archive_command.
  2. It forces a WAL switch.
  3. It waits until that exact segment appears in the repository, up to archive-timeout.

A clean exit means the whole path works: PostgreSQL to archive_command to pgBackRest to repository storage. That is the only thing worth trusting.

When check fails, the error usually points to one of these causes:

  • Timeout waiting for the segment. archive_command is wrong, failing, or not using the right --stanza. PostgreSQL's log shows the failing archive attempts.
  • Data directory mismatch. pg1-path does not match the directory PostgreSQL reports, often after a move or on a host with several clusters.
  • Permission denied. The repository path or lock directory is not writable by the user running pgBackRest, usually postgres.
  • Remote errors. These include missing SSH host keys, a passphrase-protected key, or wrong object-store credentials. They tend to work interactively and fail under the service account.

Once backups exist, pgbackrest info shows each backup's type, timestamps, and WAL start/stop, plus the archived WAL min/max range. Together these give your recovery window. The earliest point is the end of the oldest backup, and the latest is the newest archived segment. Write both down now, so you are not working them out during an outage.

#!/usr/bin/env bash
# Bootstrap and verify a pgBackRest stanza named 'main'.
# Run as the same OS user that archive_command runs as (usually postgres),
# so that permission and SSH/credential problems show up here, not in production.
set -euo pipefail
STANZA=main

# Confirm what PostgreSQL will actually execute when a segment is ready.
# The --stanza in archive_command must match $STANZA.
sudo -u postgres psql -Atc "SHOW archive_mode"
sudo -u postgres psql -Atc "SHOW archive_command"
sudo -u postgres psql -Atc "SHOW data_directory"   # must equal pg1-path

# One-time per cluster: record system identifier and version in the repo.
sudo -u postgres pgbackrest --stanza=$STANZA --log-level-console=info stanza-create

# End-to-end test: forces a WAL switch and waits for that segment to land in
# the repository (up to archive-timeout). Non-zero exit means the path is broken.
if ! sudo -u postgres pgbackrest --stanza=$STANZA --log-level-console=info check; then
  echo "check failed: inspect the PostgreSQL log for archive_command errors" >&2
  exit 1
fi

# Human-readable backup list and archived WAL min/max.
sudo -u postgres pgbackrest --stanza=$STANZA info

# Record the recovery window now, not during an incident.
# Earliest point: stop time of the oldest backup. Latest: newest archived segment.
sudo -u postgres pgbackrest --stanza=$STANZA --output=json info | jq -r '
  .[0] |
  "oldest backup stop: \(.backup[0].timestamp.stop | todate)",
  "newest backup stop: \(.backup[-1].timestamp.stop | todate)",
  "archived WAL range: \(.archive[-1].min) .. \(.archive[-1].max)"'

Creates the stanza, runs check as the postgres user to prove WAL reaches the repository, and uses info (text and JSON) to record the earliest and latest recoverable points ahead of time.

Should you use full, differential or incremental backups?

pgBackRest offers three backup types, and each one depends on earlier backups in a different way. A full backup copies every file and stands alone. A differential copies the files changed since the last full, so it needs only that full. An incremental copies the files changed since the previous backup of any type, so it needs that whole chain back to the full. If any backup in the chain is missing or corrupt, a restore from a later backup fails.

Retention follows the same dependencies. When the full-backup retention limit expires an old full, pgBackRest also expires its dependent backups and the WAL that only they needed. The oldest point you can recover to therefore moves forward with each expiry.

Illustrative estimate using these assumed inputs: a 500 GB database, 25 GB of changed files per day, 30 GB of WAL per day, and compression ignored.

Schedule Storage per week (excl. WAL) Worst-case WAL replay Must be intact to restore
Daily full 3,500 GB ~30 GB One full
Weekly full + daily diff ~1,025 GB ~30 GB Full + one diff
Weekly full + daily incr ~650 GB ~30 GB Full + up to six incrs
Weekly full only 500 GB ~210 GB One full

Replay starts from the backup you restore, so the time between backups of any type limits how much WAL you replay. Infrequent fulls make chains longer and more fragile. For production, choose the longest replay time you can accept, then the cheapest schedule that meets it. For development, a weekly full with short retention is usually enough.

# /etc/pgbackrest/pgbackrest.conf
# Production schedule: weekly full + daily incremental.
# Restore needs the full plus every incr up to the target.

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

# Keep 2 full backups. When a third full completes, the oldest full
# expires along with the diffs/incrs that depend on it and the WAL
# only they needed. The oldest recovery point moves forward.
repo1-retention-full-type=count
repo1-retention-full=2

# Start the backup without waiting for a scheduled checkpoint.
start-fast=y

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

# Schedule (crontab for the postgres user):
#   Sunday: full backup, starts a new chain
#   30 1 * * 0   pgbackrest --stanza=main --type=full backup
#   Mon-Sat: incremental, depends on the previous backup
#   30 1 * * 1-6 pgbackrest --stanza=main --type=incr backup
#
# To use differentials instead (shorter chain: full + one diff),
# replace --type=incr with --type=diff. Each diff depends only on
# the last full, so it grows through the week but tolerates the
# loss of an earlier diff.
#
# Development: run weekly fulls only with repo1-retention-full=1
# to minimize storage. Expect longer WAL replay late in the week.
#
# Check which backups exist and what each one depends on:
#   pgbackrest --stanza=main info

A pgBackRest configuration and cron schedule for weekly full plus daily incremental backups, with count-based full retention that expires dependent backups and their WAL together.

How do you perform a point-in-time restore with pgBackRest?

Stop PostgreSQL on the target host first. pgBackRest refuses to restore into a data directory while a postmaster is running, because overwriting files under a live server would corrupt it. Then run pgbackrest restore with your stanza, --type=time, and --target set to a timestamp that includes a time zone. A timestamp without a zone is interpreted in the server's local zone, which is how restores land an hour off.

Add --delta when restoring over an existing data directory. Without it, pgBackRest requires an empty directory and copies every file. With it, pgBackRest checksums the files already present and fetches only the ones that differ. On a large cluster where little has changed, this cuts restore time substantially.

--target-action controls what PostgreSQL does when replay reaches the target:

  • pause holds the server read-only so you can inspect the data before committing to it. If hot_standby is off, pause behaves like shutdown.
  • promote ends recovery and opens the server for writes.
  • shutdown stops the server, which is useful for scripted restores.

Prefer pause when you are unsure of the exact target. If the data is wrong, you can restart with a different target without having opened a divergent history.

Promotion starts a new timeline. WAL from the old timeline stays in the archive, and the backups you took before the restore belong to that old history. Take a fresh full backup right away so that the next recovery has a clean base on the current timeline.

You often only need one dropped table back. In that case, do not roll back production. Restore to a separate host, or to a different --pg1-path on a different port, and pause at the target. Then pg_dump that table and load it into production.

How do you keep PITR from silently breaking?

Archiving can stop, a credential can rotate, or a retention change can delete the base backup your target needs. None of these show up until someone tries to restore. Treat a scheduled restore drill as the real health check and everything else as early warning.

The restore drill

Run it on a separate host on a schedule, at least weekly. Restore with --type=time to a timestamp a few minutes old, let recovery finish, then run sanity queries. Check row counts on a few key tables and compare them against expected ranges. Check the newest transaction timestamp to confirm the restore actually replayed WAL close to the target. Alert if the restore fails, recovery stalls, or any query returns an implausible result. The drill exercises the full path: repository access, decryption, the WAL chain, file permissions and the restore host's configuration. Drift in any of these breaks the drill rather than an incident.

Early-warning signals

Signal What it catches
pg_stat_archiver failed_count, last_failed_time archive_command failing
Backup job exit status Missed or aborted backups
Repository disk usage Backups and WAL about to stop landing
pg_wal growth on the primary WAL piling up when archiving falls behind, risking a full disk
Oldest restorable point vs. required window Retention expiring backups you still need

The drill turns a discovery you would otherwise make at 3 a.m. during an incident into a routine alert during working hours. Budget for a small, always-on restore host, plus engineer time to keep the sanity queries matched to the current schema. Each alert also needs an owner who triages it.

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.