PostgreSQL point-in-time recovery: how it works and how to test it

PostgreSQL point-in-time recovery (PITR) works by combining a file-level base backup with a continuous stream of archived write-ahead log (WAL) segments. To restore, you bring back the base backup and replay WAL up to a chosen moment. A logical dump from pg_dump cannot do this.

The two ingredients

PostgreSQL writes every change to the WAL. Continuous archiving copies each completed WAL segment somewhere safe. A base backup, taken with pg_basebackup or the low-level backup API, gives you the starting point. According to the PostgreSQL documentation, every WAL segment from the backup start onward must be archived, and the sequence must be unbroken.

The archive side is set in postgresql.conf: wal_level at replica or higher, archive_mode on, and an archive command or archive library. The documentation's example uses a command that refuses to overwrite an existing file. The command must return zero only on success so PostgreSQL knows to retry.

To limit how much data sits unarchived, archive_timeout forces a segment switch periodically. The documentation suggests a value around a minute is typical. That setting is effectively your RPO lever.

Restoring to a point

1. Stop the server and keep the old data directory if you have space.
2. Clear the data directory and restore the base backup with correct ownership and permissions.
3. Set restore_command so PostgreSQL can fetch archived segments, and create the recovery.signal file.
4. Optionally set a recovery target such as a time, a named restore point or a transaction ID.
5. Start the server and let it replay.

Without a target, recovery runs to the end of the available WAL. The target must be later than the end of the base backup. Each completed recovery creates a new timeline, which matters if you try several targets.

Things that catch people

• Archiving can stall. If it does, WAL piles up in pg_wal and a full disk causes a PANIC shutdown. Monitor archive failures and disk space, and alert on them.
• Config files are not in the WAL. The docs say postgresql.conf, pg_hba.conf and pg_ident.conf must be backed up separately.
• PITR restores the whole cluster, not one database.
• pg_dump and pg_dumpall are logical dumps and cannot be used for PITR.
• Restore pg_hba.conf only after you verify the data, so nobody connects to a half-checked database.

A restore drill

Run this on a scratch host every quarter.

1. Pick a target time ten minutes ago.
2. Restore the latest base backup and WAL to the scratch host.
3. Recover to the target and confirm a row you inserted before it exists and one you inserted after it does not.
4. Record elapsed time and the age of the data. Those are your real RTO and RPO. See setting RPO and RTO.

Also store the archive where the database host's credentials cannot delete it, as described in replication is not a backup.

Safeguard's compliance and evidence automation can hold the dated drill result as evidence. A free dependency scan is at gold.safeguard.sh/scan.

Quick check

Sources

• Continuous Archiving and Point-in-Time Recovery, PostgreSQL documentation

Last reviewed: 19 October 2026.