What is Postgres point-in-time recovery and how does it work with pgBackRest?

Point-in-time recovery (PITR) works only when WAL archiving, retention, and a tested restore are treated as one system. If any of the three is missing, the recovery you are counting on is an untested assumption.

PITR combines two things. The first is a base backup, a physical copy of the cluster's data files taken while the server runs. The second is the continuous stream of write-ahead log (WAL) segments generated after that backup started. PostgreSQL writes every change to WAL before applying it to data files, so a base backup plus every WAL segment since then contains everything needed to rebuild the cluster at any later moment. During recovery, PostgreSQL restores the base backup and replays WAL forward until it reaches a recovery target. The target can be a timestamp, a transaction ID, a log sequence number (LSN), or a named restore point created earlier.

pgBackRest manages both halves in a single repository:

  • PostgreSQL's archive_command calls pgBackRest's archive-push each time a WAL segment completes. pgBackRest then copies the segment into the repository.
  • pgBackRest writes full, differential, and incremental backups into the same repository. It tracks which WAL each backup depends on.
  • At restore time, pgBackRest copies the chosen backup back into place. It configures restore_command to call archive-get, so PostgreSQL can fetch WAL segments on demand during replay.

Keeping backups and WAL in one repository matters because retention decides both. If a backup expires, the WAL that only that backup needed expires with it.

PITR also has a hard limit: it rewinds the entire cluster. Every database and every table returns to the target point. You cannot use PITR to recover one dropped table while keeping later writes elsewhere. To get that result, restore to a separate host and copy the table back.

+---------------------+    archive-push      +-------------------------+
| PostgreSQL cluster  |---------------------->| pgBackRest repository   |
| (running server)    |  via archive_command |                         |
|                     |                      |  +-------------------+  |
| data files          |  pgbackrest backup   |  | base backups      |  |
| pg_wal/ segments    |---------------------->|  | full/diff/incr    |  |
+---------------------+                      |  +-------------------+  |
                                             |  +-------------------+  |
                                             |  | archived WAL      |  |
                                             |  | segments          |  |
                                             |  +-------------------+  |
                                             | retention expires both  |
                                             +-------------------------+
                                                |           |
                             copy base backup   |           | archive-get
                                                |           | restore_command
                                                v           v
+------------------------------------------------------------------------+
| Restored cluster: base backup restored, then WAL replayed forward      |
| until recovery target: time | xid | LSN | named restore point          |
| scope: the entire cluster rewinds, not individual tables               |
+------------------------------------------------------------------------+

pgBackRest stores base backups and archived WAL in one repository, and restore brings both back so PostgreSQL can replay WAL to a recovery target for the whole cluster.

Who needs PITR, and when should you avoid relying on it alone?

PITR earns its operational cost when a nightly pg_dump would lose too much data. If your recovery point objective is minutes rather than a day, you need continuous WAL archiving. It also fits teams whose likely failures are self-inflicted, such as a migration that drops the wrong column or a deploy that corrupts rows. Replication copies those mistakes to every standby within seconds, while archived WAL lets you stop just before them.

PITR is the wrong primary tool in three cases:

  • Fixing a single bad change while keeping later writes. You restore a separate instance and copy the affected rows back by hand. Plan for this workflow before you need it, because it takes time under pressure.
  • High availability. A restore takes as long as copying the backup and replaying WAL. A streaming standby can be promoted far faster.
  • Host loss with a local repository. If the pgBackRest repository sits on the database host, losing the host loses both. Put at least one repository on separate storage.
Approach Recovers from Granularity Restore time Operational cost
pg_dump logical dumps Logical errors, migrations, single-table loss Last dump; per table or schema Slow on large databases because indexes are rebuilt Low
Streaming replica Primary or host failure Current state, including mistakes Fast promotion Medium, needs failover tooling
Delayed replica Operator error noticed within the delay window Any point inside the window Fast if caught in time Medium, extra server
Storage/volume snapshots Host or disk loss Snapshot time only Fast for large volumes Low to medium, requires consistent snapshots
pgBackRest PITR Corruption, bad deploys, host loss with a remote repository Any point covered by archived WAL Backup restore plus WAL replay Medium, needs archive monitoring and restore drills

How do you configure WAL archiving and a pgBackRest stanza?

Start in postgresql.conf. Set wal_level = replica (or logical), because minimal omits the information needed to replay WAL onto a base backup. Set archive_mode = on. Both settings require a server restart. Then set archive_command = 'pgbackrest --stanza=main archive-push %p'. The stanza name must match the section in pgbackrest.conf, or every push fails.

Postgres treats a zero exit code as proof that a segment is safely stored, and it recycles the segment after that. If a wrapper script swallows errors, you lose WAL without any warning. With a correct non-zero exit, Postgres retries indefinitely and keeps the segment. If the repository stays unreachable, segments accumulate in pg_wal until the primary's disk fills and the server stops.

A minimal pgbackrest.conf has a [global] section with repo1-path=/var/lib/pgbackrest and a [main] section with pg1-path pointing at the data directory. Run pgbackrest --stanza=main stanza-create to create the stanza. Then run pgbackrest --stanza=main check, which forces a WAL switch and confirms that the segment reaches the repository.

Repository trade-offs

  • Location: A local repository is fast but is lost along with the host. Offsite or object storage survives host loss. The cost is a network dependency on every archive-push, added latency per segment, and credentials you have to rotate.
  • Compression and encryption: Both cost CPU on every push and backup.
  • Encryption passphrase: If you lose repo1-cipher-pass, the backups cannot be recovered. Store it separately from the repository.

On busy systems, archive-async=y batches pushes through a spool-path. This gives you one more directory to size and monitor. It also widens the window of WAL that exists only on the primary.

#!/usr/bin/env bash
set -euo pipefail
PGDATA=/var/lib/postgresql/data   # adjust to your data directory

# 1. Keep enough WAL detail for replay and hand each segment to pgBackRest.
#    wal_level and archive_mode only take effect after a restart.
cat >> "$PGDATA/postgresql.conf" <<'EOF'
wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
EOF

# 2. Minimal repository config. The [main] section must match --stanza=main.
cat > /etc/pgbackrest/pgbackrest.conf <<'EOF'
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
# Optional encryption. Set it before stanza-create, and store the passphrase
# outside the repository. If it is lost, the backups cannot be restored.
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=REPLACE_WITH_LONG_RANDOM_PASSPHRASE
# Optional asynchronous archiving for busy primaries. The spool needs sizing and monitoring.
#archive-async=y
#spool-path=/var/spool/pgbackrest

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

# 3. Restart Postgres (the service name varies by distro), then create the stanza.
systemctl restart postgresql
sudo -u postgres pgbackrest --stanza=main stanza-create

# 4. Force a WAL switch and confirm the segment reached the repository.
sudo -u postgres pgbackrest --stanza=main check

# 5. Ongoing signal: a rising failed_count means WAL is piling up in pg_wal.
sudo -u postgres psql -c "SELECT archived_count, failed_count, last_failed_wal, last_failed_time FROM pg_stat_archiver;"

This script sets up WAL archiving in postgresql.conf, writes a minimal pgbackrest.conf with optional encryption and async settings, then creates the stanza and verifies archiving with pgbackrest check and pg_stat_archiver.

Which backup types and retention settings match your recovery objectives?

pgBackRest offers three backup types, and each one depends on a different earlier backup. You run them with pgbackrest --stanza=main --type=full backup, or with --type=diff or --type=incr in place of full.

Type Contains Depends on Restore cost
Full Entire cluster Nothing Copy one backup
Differential Changes since last full That full Full plus one diff
Incremental Changes since last backup of any type The whole prior chain Full plus every link

An incremental chain is only as strong as its weakest link. If one incremental in the middle is missing or corrupt, every later backup in that chain is unrestorable. Run pgbackrest verify periodically to catch damaged files before you need them.

Backup frequency trades storage and I/O against replay time. Recovery to a late target replays all WAL generated since the backup you restore from. Here is an illustrative estimate, assuming 20 GB of WAL per hour and replay at 100 GB per hour:

  • Daily full only: worst case is about 24 hours of WAL. That is 480 GB, or roughly 4.8 hours of replay.
  • Daily full plus hourly incrementals: worst case is about 1 hour of WAL. That is 20 GB, or roughly 12 minutes of replay, plus the time to copy the chain.

Retention is configured per repository, for example with repo1-retention-full=2 and repo1-retention-diff=6. When expire removes a full backup, it also removes the differentials and incrementals that depend on it. It then deletes archived WAL older than the oldest backup it kept. That WAL has no base backup to replay onto, so it is useless for PITR.

Your earliest recoverable point is therefore the start of your oldest retained backup. Set retention according to how far back you need to be able to recover.

# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/var/lib/pgbackrest

# Keep the 2 most recent full backups. Expiring a full also expires
# every diff and incr that depends on it, plus archived WAL older
# than the oldest backup that remains.
repo1-retention-full=2

# Keep the 6 most recent differentials. Incrementals built on an
# expired diff are removed along with it.
repo1-retention-diff=6

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

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

# postgresql.conf must ship WAL to the repo, or PITR has nothing to replay:
#   archive_mode = on
#   archive_command = 'pgbackrest --stanza=main archive-push %p'

# Example schedule (crontab for the postgres user):
# Weekly full on Sunday, daily diff Mon-Sat, hourly incr in between.
# Hourly incrementals cap worst-case WAL replay at about one hour.
#   0 1 * * 0    pgbackrest --stanza=main --type=full backup
#   0 1 * * 1-6  pgbackrest --stanza=main --type=diff backup
#   0 2-23 * * * pgbackrest --stanza=main --type=incr backup
#   0 5 * * 3    pgbackrest --stanza=main verify

# The expire command runs automatically after each backup. You can also
# run it by hand:
#   pgbackrest --stanza=main expire
# To list the backups and the WAL range that are still retained:
#   pgbackrest --stanza=main info

This pgBackRest config sets count-based full and differential retention, and the comments sketch a schedule of a weekly full, daily differentials and hourly incrementals that keeps worst-case WAL replay to about one hour.

How do you perform a PITR restore with pgBackRest?

Stop the cluster first. pgBackRest refuses to restore while Postgres is running against the data directory, and an unclean stop risks leaving the target in an unknown state. Then run pgbackrest restore with --stanza, --type=time and --target set to the timestamp just before the damage. pgBackRest selects the most recent backup that finished before that time, copies it into place, and writes the recovery settings so Postgres replays archived WAL up to the target.

Add --delta when restoring over an existing data directory. Without it, pgBackRest expects an empty directory. With it, pgBackRest compares checksums and rewrites only the files that differ, which is usually much faster on a large cluster.

--target-action decides what happens when replay reaches the target:

  • pause holds the server in read-only recovery so you can query it. This is the default.
  • promote ends recovery and opens the database for writes on a new timeline.
  • shutdown stops the server. This is useful when automation or a person should make the next decision.

Use pause, inspect the data, then promote. If the target turns out to be too late, or you have already promoted, the only fix is to restore again from the backup, which can take hours.

Other target types follow the same pattern. --type=xid targets a transaction ID, --type=lsn targets a WAL position, and --type=name targets a restore point created earlier with pg_create_restore_point().

Two details often go wrong:

  • Time zones. Always include an explicit UTC offset in the timestamp. Otherwise its interpretation depends on server settings.
  • Inclusive targets. Targets are inclusive by default, so the transaction at the target is replayed. Set --target-exclusive to stop just before it.
#!/usr/bin/env bash
set -euo pipefail

# Placeholder values: adjust for your environment
STANZA=main
PGDATA=/var/lib/postgresql/data

# 1. Stop the cluster cleanly. pgBackRest will not restore over a running server.
pg_ctl -D "$PGDATA" stop -m fast

# 2. Restore to just before the damage.
#    --delta rewrites only the files that differ from the backup.
#    The timestamp carries an explicit UTC offset (+00) so the server's
#    time zone setting cannot change its meaning.
#    --target-exclusive stops just before the target instead of replaying it.
#    --target-action=pause keeps the server in read-only recovery at the target.
pgbackrest --stanza="$STANZA" --delta \
  --type=time --target="2025-03-14 09:41:00+00" \
  --target-exclusive \
  --target-action=pause \
  restore

# Other target types use the same pattern, for example:
#   --type=xid  --target="5234719"
#   --type=lsn  --target="0/3A0F1C8"
#   --type=name --target="before_schema_migration"

# 3. Start Postgres. It replays archived WAL up to the target, then pauses.
pg_ctl -D "$PGDATA" start

# 4. Check that replay is paused, then inspect the data while it is read-only.
psql -X -c "SELECT pg_is_in_recovery(), pg_is_wal_replay_paused();"
psql -X -c "SELECT count(*) FROM orders;"

# 5. If the data is correct, end recovery. When replay is paused at the
#    recovery target, resuming ends recovery and opens a new timeline.
#    If the target is wrong, do not promote. Stop and restore again with a new target.
psql -X -c "SELECT pg_wal_replay_resume();"

This example stops the cluster and runs a delta, time-targeted pgBackRest restore that pauses at the target, so you can inspect the data before ending recovery.

How do you verify a restore and understand the new timeline?

Read the log before you open the cluster to traffic

The PostgreSQL log states where replay ended. Look for a line such as recovery stopping before commit of transaction … or recovery stopping after commit of transaction …, followed by last completed transaction was at log time …. Compare that timestamp and transaction with your target. If you see redo done with no "recovery stopping" line, replay ran to the end of available WAL without reaching the target. That usually means the target was later than the last archived segment, so the cluster is not in the state you asked for.

Check sentinel data

Log lines prove where replay stopped, not that the data is correct. Query rows you know should exist and rows you know should not, for example the last order written before an accidental DELETE and the deleted rows themselves. Choose these sentinels before the restore so the check is objective.

The new timeline

When the cluster is promoted, PostgreSQL logs selected new timeline ID and starts writing WAL on a new timeline. This keeps its WAL separate from the original history after the recovery point. The first eight hex digits of a WAL file name are the timeline, so 00000002… follows 00000001…. A 00000002.history file records the parent timeline and the LSN where the branch occurred. You can confirm the current timeline with SELECT timeline_id FROM pg_control_checkpoint();.

The restored cluster archives into the stanza on its own timeline. Do not leave the original primary archiving to the same stanza at the same time. Take a fresh full backup soon after promotion, so later restores do not depend on replaying across the timeline switch.

How can restore drills and archive monitoring save engineering time?

An untested restore is only a hope that recovery works. A scheduled drill turns it into a measured result. On a separate host, restore the latest backup to a recent target time, then record how long the restore and WAL replay took and compare that against your RTO. After the restore, run automated checks: the server accepts connections, critical tables have plausible row counts, and the newest rows are close to the target time. Fail the drill job loudly. Expired credentials, missing WAL segments or a repository that has outgrown its restore window then show up on a weekday afternoon instead of during an outage.

Between drills, watch pg_stat_archiver. Select failed_count, last_failed_wal, last_failed_time and now() - last_archived_time. Alert under either of these conditions:

  • last_failed_time is newer than last_archived_time. This means archiving is failing right now.
  • Time since the last successful archive exceeds your RPO while the database is taking writes.

Also track the total size of pg_wal, for example by summing pg_ls_waldir(). When archiving stalls, Postgres keeps WAL segments locally, and steady growth there means the disk is filling.

Illustrative estimate (assumed numbers, not a benchmark):

  • Assume setup takes 3 engineer-days and maintenance takes 2 hours per month.
  • Assume one incident per year that would otherwise involve an untested restore costing 4 engineers × 10 hours.

Annual cost of drills = setup + (monthly maintenance × 12) = 24 + 24 = 48 hours in the first year. The time saved per avoided incident is engineers × hours = 40 hours. Add the drill host and the extra repository storage to the cost side, then plug in your own incident frequency.

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 postgres point in time recovery pgbackrest, 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.