PostgreSQL Point-in-Time Recovery: Undo a Bad DELETE Step by Step

PostgreSQL point-in-time recovery (PITR) is what saves you when the damage comes from inside: a DELETE without a WHERE, a migration that dropped the wrong column, a bug that has been overwriting rows since this morning. Replicas don't help, because they faithfully copy the mistake. A nightly pg_dump means choosing between losing a day of data and keeping the damage. PITR lets you rebuild the cluster as it was one minute before the mistake. But only if two things were set up before the incident, and only if you know the restore procedure well enough to run it under pressure.

How it works

PostgreSQL writes every change to the write-ahead log (WAL) before it changes the data files. PITR combines two pieces, described in the continuous archiving chapter of the documentation:

• A base backup: a physical copy of the whole data directory, taken for example with pg_basebackup.
• A continuous WAL archive: every completed WAL segment copied somewhere safe as soon as it is written.

To recover, you restore the base backup and let PostgreSQL replay archived WAL on top of it, stopping at the moment you choose. Two limits follow from this design. First, it works for the whole cluster only, not for a single database or table. Second, pg_dump and pg_dumpall produce logical dumps, not file-system-level backups, so they cannot be used for PITR at all.

Before the incident: archiving and base backups

The minimal configuration looks like this. The archive_command is the example from the documentation, which itself calls it "an example, not a recommendation":

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
archive_timeout = 60

• wal_level must be replica or higher, and both wal_level and archive_mode only change on a server restart. Plan that restart; don't discover it during an incident.
• %p is the path of the WAL file to archive, %f its file name. The command must return zero only if the copy succeeded. On zero, PostgreSQL treats the segment as safe and recycles it. The test ! -f part refuses to overwrite an existing file in the archive.
• archive_timeout forces a segment switch after that many seconds if there was activity, which bounds how much recent data sits only in an unarchived segment. The documentation says a minute or so is usually reasonable; very short values bloat the archive, because forced segments are still full size.
• Take base backups regularly. The stop point of a recovery must be after the end of the base backup it starts from, so a backup can't take you back to a moment while it was still running, or before it.

In production many teams use a tool like pgBackRest, WAL-G or Barman rather than cp. They handle compression, retention, parallelism and object storage, but the mechanism underneath is the same.

Then watch the archive. pg_stat_archiver shows whether archiving is keeping up:

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

A growing failed_count or an old last_archived_time on a busy server means your PITR window is not what you think it is. Failed segments also stay in pg_wal until they are archived, so a broken archive can eventually fill the disk; we cover that situation in our guide to a full pg_wal disk. Note that the documentation lists cases where a failure is not reported in pg_stat_archiver, for example when the command is killed by a signal, so an alert on the age of the last archived segment is a useful second check.

During the incident: find the moment

Before you touch any server, establish when the damage started. Application logs, deployment timestamps and the PostgreSQL log (if statements are logged) are the usual sources. Write the time down with an explicit UTC offset. The documentation recommends a numeric offset such as +02; a full zone name like Europe/Riga also works, but abbreviations such as EET generally don't.

If you are not sure about the exact second, pick a time slightly earlier. You can always roll forward further; you cannot go back without starting over.

Restore to a separate server

Because PITR restores the whole cluster, rolling production back means losing every legitimate write since the mistake. Usually the better plan is to recover onto a separate machine or instance, check the data there, and copy back only the affected rows or tables. The production server keeps running in the meantime.

The documented procedure, adapted to a fresh target server:

1. Restore the base backup into an empty data directory, owned by the database system user (not root), and check that tablespace symlinks in pg_tblspc/ point to the right places. For an incremental backup chain (PostgreSQL 17 and later), combine it with pg_combinebackup.
2. Remove any files left in pg_wal/ from the backup; the documentation calls them "probably obsolete". Recovery will fetch WAL from the archive.
3. Configure recovery in postgresql.conf:

   restore_command = 'cp /mnt/server/archivedir/%f "%p"'
   recovery_target_time = '2026-11-02 14:31:00+02'
   recovery_target_action = 'pause'

4. Create an empty recovery.signal file in the data directory.
5. Restrict access in pg_hba.conf so applications cannot connect to the recovering server by accident.
6. Start the server and watch the log. It will ask for files that aren't in the archive, including .history files; that is normal, and restore_command must simply fail for them.

SELECT pg_is_in_recovery();
-- inspect the data, then end recovery:
SELECT pg_wal_replay_resume();

Two settings decide exactly where replay stops. recovery_target_inclusive (default on) stops just after the target; set it to off to stop just before. Only one of the target settings (recovery_target_time, _name, _xid, _lsn or recovery_target = 'immediate') can be set at a time. If you plan risky changes, pg_create_restore_point() creates a named point you can later target with recovery_target_name, which is much easier than guessing a timestamp.

If recovery runs out of archived WAL before reaching the target, the server shuts down with a fatal error. That is how you find out the archive has a gap, which is why the monitoring above matters.

After recovery: timelines

Every completed archive recovery starts a new timeline. The timeline ID is the first part of each WAL file name, and a small .history file records where it branched off. This is what lets PostgreSQL keep the old and new histories apart in the same archive. By default recovery follows the latest timeline in the archive (recovery_target_timeline = 'latest'), so if you run several trial recoveries that archive into the same location, be deliberate about which timeline you want. Also remember that configuration files such as postgresql.conf and pg_hba.conf are not part of WAL replay; back them up separately.

Test it before you need it

A PITR setup that has never been restored is a hope, not a plan. Run a recovery to a chosen timestamp on a test server regularly, time it, and compare the result with what you expected. Our guide to backup and restore testing covers how to turn that into a routine.