Postgres Point-in-Time Recovery: WAL Archiving, Base Backups, and Restore Drills

pg_dump backups are simple and portable, and for many small apps they're enough. (How to Back Up a Postgres Database covers them.) Their limitation is granularity: a nightly dump means a failure at 17:00 loses everything since 02:00, and restoring "to just before the DELETE without a WHERE" is impossible — you get last night or nothing.

Point-in-time recovery (PITR) removes that limitation. With it, you can restore a cluster to any moment covered by your archive — typically to within seconds of a chosen timestamp or transaction. This article explains how it works, how to configure it, and how to make sure it actually works when you need it.

The mechanism: base backup + WAL

Postgres writes every change to the write-ahead log (WAL) before applying it to data files. WAL is a sequential record of every modification, stored in segment files (16 MB each by default).

PITR combines two things:

A base backup — a physical copy of the cluster's data directory, taken while the database runs.
A continuous WAL archive — every WAL segment generated since (and during) that base backup, copied to durable storage.

To recover, Postgres starts from the base backup and replays WAL forward until it reaches your chosen target. Because WAL records every change in order, you can stop anywhere.

Consequences worth internalising:

Recovery time scales with how much WAL must be replayed since the base backup. More frequent base backups → faster recovery.
Your archive must be gap-free. A single missing segment means you can't recover past that point.
PITR is physical and cluster-wide: you restore the whole cluster, not one database or table. (For extracting one tenant's or one table's data, restore to a side instance and copy out — see Multi-Tenant SaaS on Postgres.)

Configuration

The essentials in postgresql.conf:

wal_level = replica            # default on modern versions; needed for archiving
archive_mode = on              # requires restart
archive_command = '...'        # or archive_library, see below
archive_timeout = 60           # force a segment switch at least every 60s

archive_command runs for each completed segment and must return success only once the file is durably stored. Postgres retries on failure and never recycles an unarchived segment — so a broken archive command causes WAL to accumulate on the primary until the disk fills. Monitor it.
archive_timeout bounds your RPO on quiet systems: without it, a low-traffic database might not fill a segment for hours, and that data sits unarchived. The trade-off is more (mostly empty) segment files.
Since Postgres 15, archive_library allows archiving via a loadable module instead of a shell command.

Don't hand-roll archive_command with cp to a local directory for production: you want compression, encryption, parallelism, checksums, retention management, and off-host storage. That's what the dedicated tools are for.

Tooling

pgBackRest — full-featured: full/differential/incremental backups, parallel compression, encryption, retention policies, object storage (S3-compatible, Azure, GCS), backup verification, and delta restore.
WAL-G — lightweight, object-storage-oriented, widely used in cloud and Kubernetes setups.
Barman — backup server model, popular in some enterprise environments.

All three manage both base backups and WAL archiving and provide restore commands that handle the details below.

Postgres 17 also added native incremental backups: with summarize_wal = on, pg_basebackup --incremental captures only changed blocks since a prior backup, and pg_combinebackup reconstructs a full backup for restore. Useful when you want to stay within core tools.

Taking base backups

With pg_basebackup (or your tool's equivalent):

pg_basebackup -D /backups/base-2026-09-24 -Ft -z -Xs -P -c fast

Base backups are taken online, without blocking writes.
Schedule them often enough that WAL replay for a restore fits within your recovery time objective. Daily full plus the WAL archive is common; large, busy databases add incrementals.
Retention must be coherent: keeping WAL only makes sense back to the oldest base backup you retain.

Restoring to a point in time

The restore procedure (tools automate most of it):

Provision a clean data directory — ideally on a separate instance, not over the primary. You usually want to inspect before cutting over, and you must not destroy the evidence.
Restore the base backup taken before your target time.

Configure recovery in postgresql.conf:

restore_command = '...'                     # fetch archived segments
recovery_target_time = '2026-09-24 14:31:59+00'
recovery_target_inclusive = false
recovery_target_action = 'pause'

Create recovery.signal in the data directory (Postgres 12+; replaces recovery.conf).
Start Postgres. It replays WAL up to the target and pauses.
Inspect. Connect read-only and confirm the data is as expected — the dropped table exists, the bad update hasn't happened.
Promote with SELECT pg_wal_replay_resume(); or pg_ctl promote once satisfied — or adjust the target and restart recovery if you overshot or undershot.

Other targets: recovery_target_xid (a transaction ID), recovery_target_lsn (a WAL position), and recovery_target_name (a named restore point you created earlier with pg_create_restore_point() — worth doing before risky operations like large migrations).

Finding the right target

"Just before the bad statement" is easier if you know when it happened. Statement logs with timestamps, application logs with request IDs, and pg_waldump on the relevant segments can pin down the exact transaction. (Structured Logging.)

Timelines

When a recovered cluster is promoted, it starts a new timeline: WAL generated afterwards is tagged with a new timeline ID, and a .history file records where it branched. This prevents the new history from being confused with the original one that continued past the recovery point. Practical implications: keep .history files in the archive, and understand recovery_target_timeline (default latest) if you ever recover repeatedly.

Managed Postgres

Most managed providers implement PITR for you, with a configurable retention window, and restore to a new instance at a chosen timestamp. Know the specifics of yours:

Retention window, and whether it's long enough to notice a problem that surfaces days later
Whether restore creates a new instance (connection strings change) and how long it takes for your data size
Whether you can also export backups off-provider, for provider-level failures and account compromise

RPO and RTO

RPO (how much data you can lose): bounded by WAL archive lag — archive_timeout and archive health. With continuous archiving, typically seconds to a minute. For near-zero RPO, add synchronous streaming replication.
RTO (how long recovery takes): base backup restore time + WAL replay time + verification. Measure it; don't estimate it.

Note that replicas are not backups: a DROP TABLE replicates instantly. PITR is what protects against logical mistakes.

Monitoring the archive

SELECT archived_count, last_archived_wal, last_archived_time,
       failed_count, last_failed_wal, last_failed_time
FROM pg_stat_archiver;

Alert on: failed_count increasing, last_archived_time older than a few multiples of archive_timeout, and disk usage growth in pg_wal. A silently failing archive is the most common way PITR turns out not to exist. (How to Know When Your App Is Down covers heartbeat-style checks.)

Restore drills

A backup system you haven't restored from is a hypothesis. Automate proof:

On a schedule, restore the latest base backup plus WAL to a scratch instance, targeting a recent timestamp.
Run verification queries: row counts on key tables, the most recent rows' timestamps near the target, application smoke checks.
Record the measured RTO.
Tear it down; alert on any failure.

Periodically drill the scenario that matters: "restore to just before event X," with a human following the runbook, timed.

Agents and PITR

AI agents running migrations and data scripts raise the value of fine-grained recovery. Before any agent-driven schema or data change touches production, create a named restore point, keep agents' credentials away from production, and route changes through reviewed migrations. (How to Stop an AI Agent From Deleting Your Production Database.)

The checklist

archive_mode = on, off-host archive via pgBackRest / WAL-G / Barman
archive_timeout set to bound RPO on quiet systems
Base backups on a schedule consistent with RTO; retention coherent with WAL
pg_stat_archiver and pg_wal growth monitored with alerts
Restores go to a separate instance, paused at target for inspection
Named restore points before risky operations
Automated restore drills with measured RTO
Off-provider copies for provider/account failure

EasySpawn provisions managed Postgres for every project with daily backups, and on-demand database backups on the Team plan — so you can snapshot before a risky change. See pricing or join the waitlist.