Point-in-Time Recovery

Point-in-time recovery (PITR) restores a datastore to a supported historical state by combining a usable base backup with retained changes. In PostgreSQL physical PITR, archived WAL must extend back to the base backup’s start and continue through the target, including records needed to make the backup consistent. The earliest consistent state of an online backup is at its end; a target while that backup was running requires an earlier base backup. A crash-recovery log can be recycled and is not automatically a retained history. Verify the backup, log continuity, timeline, and restore procedure together.

A note on the name, because the phrase is overloaded in data work: this is not the same as a point-in-time join, which is about computing features using only what was known at a given moment. Same adjective, unrelated problem.

What it changes about recovery

With snapshots alone, the recovered position depends on the latest usable snapshot’s data cutoff, not simply its completion time. The SRE book’s 40-minute database-export example illustrates one possible cost. If a 10:00 snapshot finishes copying at 10:40 and remains the newest copy at an 11:00 failure, it is an hour behind. Copy frequency, consistent snapshot position, and transfer delay jointly determine the exposure; the times in this example are illustrative.

PITR removes that quantization. The base copy can be hours old while the recoverable moment is seconds ago, because the log fills the gap. Two things that does not change are worth stating up front, and the second is the one that surprises people.

• It shortens the recovery point, not the recovery time. Replaying a long log takes time, and “restoring probably takes as long as backing up, so actually loading the data might take hours.” A recovery point of five seconds and a recovery time of four hours is an ordinary PITR outcome.
• It is not protection against the log’s own storage. If the base copy and the log live on the same medium and under the same credentials as the primary, they share its failure domain. That is the same objection that applies to replication — “replication and redundancy are not recoverability.”

Mechanically it is the recovery-side use of the write-ahead log: a durable record of intended changes, replayed against a consistent base. The logging mechanism is write-ahead logging, and the general shape of resuming from a saved position and replaying is checkpoint and replay.

Why it matters for the failures that actually happen

The case for PITR is strongest against the corruption nobody noticed at the time. Google’s observation: “low-grade data mutation or deletion bugs within application code demand the furthest reaches back in time, as some of those bugs were noticed months after the first data loss began.”

Against that failure a snapshot is a blunt instrument. You want the state at a particular moment — ideally just before the bad deploy — and the nearest snapshot is on a schedule that knows nothing about your incident. The source also names the harder version of the same problem: “it is challenging to recover different subsets of data to different restore points, because doing so would involve multiple backups. Yet, that is exactly the sort of recovery effort demanded by low-grade data corruption or deletion scenarios.“

Which is why the recommendation is to have both mechanisms rather than to choose: “Trade point-in-time recovery against a tiered strategy if necessary, but don’t resort to not using either! If you can have both features, use both features. Each of these features (or both) will be valuable at some point.”

And a limit that applies even to the most expensive architectures, which is the reason PITR is not made redundant by running everywhere: with multi-site active/active recovery time approaches zero for most disasters, “however data corruption may need to rely on backups, which usually results in a non-zero recovery point,” and “backup and recovery are still required.” Topology answers the loss of a place. It does not answer wrong data, because wrong data is faithfully present in every place.

The hard part is choosing the moment

Replaying a log is a solved engineering problem. Knowing where to stop is not, and it is where real recoveries stall. If a bug has been corrupting a fraction of writes for three weeks, there is no single instant that is simply “before” — and every candidate trades lost good data against retained bad data. Three ways to find a defensible answer, which are my suggestions rather than documented procedure:

• Work from a change. If the corruption began with a deploy, a migration or a configuration change, its timestamp is your candidate — which is one more reason to record what changed in production, as incident practice already recommends.
• Work from validation. A last passing check and a first failing check provide evidence about the data and invariants those checks actually covered. They bound the onset only if the checks would reliably detect the relevant fault throughout that period. Sampling, missing checks, or a newly introduced rule can leave older corruption unseen. Preserve the check version, scope, and results when selecting a candidate restore point.
• Search. Restore to a candidate moment in an isolated environment, run the check, and move the candidate. Expensive in time, and the only option when neither of the above is available — which is itself an argument for having them.

For PostgreSQL online backups, distinguish the start of the required WAL chain from the earliest recovery target. WAL must be retained from the backup’s start through its completion and onward to the target, but a time target must be after the backup’s ending time. A backup taken from 10:00 to 10:40 cannot restore 10:20; use an earlier backup for that target. With a valid backup completed Monday and continuous archived WAL through Wednesday, a gap on Thursday leaves Tuesday recoverable but prevents reaching Friday from that backup, even if archiving resumed Friday. A later valid base backup can open another recoverable interval. Preserve the appropriate timeline history too. These are illustrative timelines. Compatibility remains a separate constraint: retain or reconstruct the database version, application code, and schema path needed to read and use an older restored state.

What it cannot undo

PITR rewinds one datastore. It does not rewind the world, and the four gaps below are where recoveries produce new problems. These are my observations; the underlying caution about uncoordinated restores across stores is the source’s.

• Effects that already left. Emails sent, payments captured, webhooks delivered, events published to other teams. Rewinding your table does not recall any of it, so a restore has to be paired with a decision about what those consumers now believe.
• Other stores that cannot reach the same instant. An object store, a search index, a cache and a queue will not all rewind to 14:07. The source warns about exactly this complication for uncoordinated restores — sorting out “restored blobs versus live” data and the interactions between them.
• Good work done after the moment. Everything between the restore point and now is discarded. For a busy system that can be a large amount of legitimate user activity, which turns a technical decision into a product one.
• Corruption older than the window. If no available state predates the relevant bad changes, PITR cannot return the entire dataset to a state before those changes. It may still recover other data lost more recently: an old damaged row does not prevent extracting a different row from a copy made before yesterday’s deletion. State which losses can be repaired and which need older retained copies, reconstruction, or an accepted loss.

A selective repair can preserve legitimate work performed after the target, but it is not the same operation as physical PITR. PostgreSQL’s WAL-based restore recovers the whole database cluster, not selected rows or tables. Restore to a separate, isolated instance, extract the required data, and compare it with current records before applying an authorized repair. Check identities, versions, concurrent changes, and invariants across the repaired and untouched data. Other products may offer native object-level restore under different conditions. Mixing moments can still leave balances inconsistent with transactions or children without parents, so name the checks before starting.

Before an incident, inventory base backups with their start and end positions, required WAL and timeline history, and the target intervals each can actually support. Verify retention and continuity, rehearse retrieval and replay at representative volume, and check database and application compatibility. A catalog entry alone cannot prove that the bytes and permissions will support recovery. A recovery drill tests those assumptions. Replication Is Not Recovery explains how PITR fits alongside other recovery layers.

References

• “Data Integrity: What You Read Is What You Wrote,” Google SRE Book
• AWS, Disaster recovery options in the cloud
• PostgreSQL Documentation, Continuous Archiving and Point-in-Time Recovery (PITR)
• PostgreSQL 18 — Recovery Target