Point-in-Time Recovery in PostgreSQL: WAL Archiving, pgBackRest and Restore Traps

A nightly pg_dump will, at best, give you yesterday's data back when someone runs the wrong DELETE in the afternoon. Every order, signup and payment from the fifteen hours in between is gone. Point-in-time recovery (PITR) closes that gap: you can bring the database back to the state it was in one second before the bad query. That requires a physical backup and an unbroken WAL archive, not a logical dump. This post compares the common approaches, walks through a working pgBackRest setup, and covers the traps that hurt the most.

How PITR works

PostgreSQL writes every change to the write-ahead log (WAL) before touching the data files. With two things in hand you can rebuild any moment in the past:

1. A physical copy of the data directory (a base backup)
2. Every WAL segment produced from the start of that backup up to the moment you want

During recovery PostgreSQL opens the base backup, replays WAL records in order and stops at the target you give it. If a single segment is missing from the chain, you cannot get past that point. So the real subject of PITR is not taking backups, it is keeping the WAL archive continuous.

pg_dump sits outside this model. A logical dump is the SQL form of one instant, and nothing can be replayed on top of it. It remains very useful for major version upgrades, moving a single table or seeding another environment, but it is not a substitute for PITR.

Three approaches

Hand-rolled archive_command plus pg_basebackup. You can do it with PostgreSQL's own tools: a script copies WAL segments somewhere, and pg_basebackup takes the base backup. It is a good way to learn the mechanics and a poor way to run production. Retention, pruning old backups, compression, encryption, parallel transfer and, above all, verifying archive integrity are all left to your script. A plain cp inside archive_command does not know what to do when the target file already exists or when the disk is full.

WAL-G. A fast, single-binary tool designed around object storage (S3-compatible, GCS, Azure). It is very light to deploy on Kubernetes or anywhere storage is purely cloud based, and it is configured through environment variables, which fits container workflows well.

pgBackRest. Full, differential and incremental backups, parallel compression, backup verification, retention policies, multiple repositories and encryption, all in one tool. Its check command tests end to end that archiving actually works.

If we have to pick: for a database on a VM or bare metal that is past a few tens of gigabytes, choose pgBackRest. Having retention and verification built in removes the silent failure modes of a home-grown script. If your database runs on Kubernetes under an operator, use the operator's own backup mechanism; the operator already made the tooling choice, and bolting a second mechanism on top of it invites conflicts.

Setting up pgBackRest

Start with the repository and cluster definition. In pgBackRest each PostgreSQL cluster is called a "stanza":

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=a-long-random-passphrase
compress-type=zst
process-max=4
start-fast=y

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

repo1-retention-full=2 keeps the last two full backups and the WAL they depend on, and expires anything older on its own. Lose the cipher passphrase and your backups cannot be opened, so keep it in a secret store that does not live on the database server.

Then enable archiving in postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
archive_timeout = 60

Changing archive_mode needs a restart. After that, create the stanza, test the setup and take the first full backup:

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

A weekly full plus daily differential is a common and sensible starting schedule. A differential holds everything changed since the last full; an incremental holds what changed since the previous backup of any type. The longer an incremental chain grows, the more pieces a restore depends on.

Restoring

Say a table was dropped by mistake in the afternoon and the logs tell you the exact minute. Stop the server and give pgBackRest a target time:

sudo systemctl stop postgresql
sudo -u postgres pgbackrest --stanza=main --delta \
  --type=time "--target=2026-03-14 14:31:00+03" \
  --target-action=promote restore
sudo systemctl start postgresql

--delta replaces only the files that differ instead of wiping the data directory, which saves hours on large databases. pgBackRest writes the restore_command setting and the recovery signal file that PostgreSQL 12 and later expect.

Traps

A broken archive fills the disk. As long as archive_command returns failure, PostgreSQL will not remove that WAL segment. If the repository becomes unreachable, a credential expires or the cipher passphrase changes, the WAL directory keeps growing until the database stops on a full disk. Watch failed_count and last_failed_time in the pg_stat_archiver view and alert when the age of the last successful archive passes a threshold.

Quiet databases stretch your RPO. A segment is archived only when it fills up or when archive_timeout elapses. On a low-write system without archive_timeout, the latest changes can sit on local disk for hours. Setting it very low has a cost too, since every switch produces a full-size segment and bloats the repository; one minute is a reasonable middle ground.

Give the target time a time zone. A timestamp without one is interpreted in the server's local setting. If application logs are in UTC and the server is not, your recovery point drifts by hours. Always write the target with an explicit offset.

The default is to pause. If you do not set recovery_target_action and hot standby is enabled, PostgreSQL pauses recovery when it reaches the target and stays read-only. That is actually a useful feature: you inspect the data and, if the target is right, call pg_wal_replay_resume(). Someone who does not know it will spend hours debugging "the database is up but refuses writes".

A restore starts a new timeline. After promotion the cluster moves to a new timeline, while WAL from the old one stays in the repository. If you accidentally bring up a second server writing to the same repository, such as the old primary, two different histories try to land in one archive. After a restore, make sure the old server is not archiving, and take a fresh full backup.

A backup on the same machine is not a backup. Keeping the repository on the database's own disk saves nothing when the hardware dies. pgBackRest supports multiple repositories; keep one local and fast, and another in object storage at a different location, and you cover both quick rollbacks and real disasters.

An untested restore is a hope. check tells you archiving works, not that restoring works. On a regular schedule, do an actual PITR onto a separate machine, go back to a specific time, query a row you know should exist, and record how long it took. Your RTO is the number you measured in that drill, not the one on paper.

When you do not need it

If the database is a few gigabytes and losing a day of data is acceptable to the business, a nightly pg_dump that is regularly restored and tested is enough; PITR's operational cost would exceed its benefit. On a managed database service PITR usually comes built in, so manage retention and restore drills with the provider's tooling instead of building your own. Finally, remember that PITR is a time machine for the whole cluster: it rewinds everything even when you only need one table back. In that case, restore to a separate server and copy just the rows you need into production. It is far safer than rewinding it all.