Point-in-Time Recovery In Depth
Point-in-time recovery, or PITR, is the capability that turns a single backup into an entire rewindable history of your cluster rather than one fixed snapshot.
This page explains the mechanism underneath that capability: how WAL replay works, why every recovery target is really just a stopping condition, and what happens to the cluster's identity once you rewind and resume it.
Summary
- Core Idea: PITR replays the write-ahead log forward from a base backup until it reaches a chosen stopping point, reconstructing the cluster exactly as it existed at that moment.
- Why It Matters: It is the only recovery method precise enough to undo a single bad transaction without discarding every change that happened around it.
- Key Concepts: base backup, WAL replay, recovery target, timeline, recovery point objective (RPO), promotion.
- When to Use: Reach for PITR when you know approximately when something went wrong and need surgical precision rather than a full restore to the last full backup.
- Limitations / Trade-offs: PITR can only rewind as far as your archived WAL reaches, and replaying a large WAL history back to an old point can take longer than a fresh restore.
- Related Topics: backup and restore foundations, disaster recovery RTO and RPO, streaming replication, WAL archiving.
Foundations
A base backup by itself only gives you one moment in time, the instant it finished, and nothing before or after it.
WAL archiving extends that single moment into a continuous history, because every change PostgreSQL makes is first written to the write-ahead log before it touches a data file.
Recovery works by replaying that log against the base backup, applying each recorded change in the exact order it originally happened.
Because replay is deterministic, the cluster passes through every intermediate state the original did, not just the final one, which is what lets you stop replay at any point along that path.
A recovery target is simply the instruction that tells replay when to stop, and PostgreSQL accepts several kinds: a wall-clock time, a transaction ID, a log sequence number (LSN), or a named restore point created in advance.
None of these targets are fundamentally different mechanisms, they are just different coordinate systems for pointing at the same underlying WAL stream.
The plain-text shape of the process looks like this:
[Base backup] --replay WAL segment 1--> --replay WAL segment 2--> ... --STOP at target-->
T0 T1 (chosen point)
Everything between the base backup and the target gets replayed; everything after the target is discarded from the recovered instance's perspective, even though it still exists in the archive.
Mechanics & Interactions
The recovery point objective that PITR can deliver is bounded by one simple fact: you can never recover past the last WAL segment that was successfully archived.
-- The number that actually defines your PITR ceiling
SELECT archived_count, last_archived_wal, last_archived_time, failed_count
FROM pg_stat_archiver;If failed_count is climbing, the archive chain has a gap, and any recovery target beyond that gap becomes unreachable no matter how recent your base backup is.
Promotion is the moment recovery ends and the cluster starts accepting writes again, and it has a consequence that surprises many operators: it forks a new timeline.
PostgreSQL numbers timelines starting at 1, and every promotion after a recovery increments that number, writing a .history file that records exactly where the fork happened and which timeline it branched from.
This exists because the WAL the recovered instance generates after promotion is different from the WAL the original instance would have generated past that same point, so PostgreSQL needs a way to tell those two futures apart.
That timeline mechanism is also why you cannot simply keep replaying an old archive past a promoted instance's fork point, because the archive on the new timeline diverges from the one the old primary was still writing.
A recovery target is evaluated against the timeline the recovering instance is following, so recovering a second time from the same base backup to a different, later target still works, but recovering forward past a previous promotion requires following the correct timeline history file.
WAL replay is also what makes PITR fundamentally different from restoring a replica or a snapshot: replica promotion moves you forward to the replica's current state, while PITR can move you to any state the WAL archive still remembers, including states earlier than what any live replica currently holds.
Advanced Considerations & Applications
Choosing how to recover from a given incident means picking the tool whose precision matches the problem, and PITR is not always the right one even when it is available.
| Recovery Approach | Precision | Typical Recovery Time | Best Fit |
|---|---|---|---|
| PITR (base backup + WAL replay) | Exact transaction or timestamp | Minutes to hours, scales with WAL volume | Undoing a specific mistake with minimal collateral loss |
| Replica promotion | Current replica state only | Seconds to minutes | Hardware or node failure, not logical error |
| Object-level restore (logical dump) | Whole objects as of dump time | Minutes | Single table or schema recovery when dump is recent enough |
| Full physical restore (no PITR) | Backup's consistency point only | Minutes to hours | Fast recovery when the base backup itself is recent enough |
Cloud-managed services expose PITR through a different interface but not a different mechanism: RDS, Aurora, and Neon all still combine a base image with a continuous change log, they simply hide the archive_command and manual recovery target configuration behind a console action that restores to a new endpoint.
Large clusters make WAL replay speed a first-class capacity concern, because replaying days of WAL against a multi-terabyte base backup can turn a "quick undo" into an hours-long operation unless replay is parallelized or the base backup is kept fresh enough to shorten the replay window.
Retention policy for the WAL archive is effectively the retention policy for your entire PITR capability, so a compliance requirement to recover from mistakes discovered weeks later translates directly into a WAL retention requirement measured in weeks, not days.
PITR precision also has a security dimension worth naming, because the ability to rewind to any point in a retained window is exactly the capability an incident responder needs after a compromised account made unauthorized changes, provided the archive itself was not also compromised.
Common Misconceptions
- "PITR restores the database in place." Recovery always operates on a separate copy of the data directory or a new instance, never on a running production cluster's live files.
- "Any recovery target works as long as WAL exists somewhere." A target beyond the last successfully archived segment is unreachable, so a failed or gapped archive silently shrinks your real recovery window.
- "Promoting after PITR just resumes normal operation." Promotion forks a new timeline, and understanding that fork matters for anyone who later needs to recover again or reconcile data across timelines.
- "PITR can be built from a logical dump if you also keep WAL around." WAL replay only makes sense against a physical base backup, because WAL records physical or logical changes relative to a known starting file state, not against an arbitrary SQL export.
- "A shorter time-to-target always means a faster recovery." Recovery time depends on how much WAL must be replayed to reach that target, not on how close the target is to "now," so an old target right after a fresh base backup can be faster than a recent target after a stale one.
FAQs
What is the actual mechanism behind point-in-time recovery?
PITR loads a physical base backup and then deterministically replays archived WAL segments in order until a chosen recovery target is reached, reconstructing the exact state at that moment.
Why does my recovery target sometimes get "clamped" to an earlier point than I asked for?
Recovery cannot proceed past the last WAL segment your archive actually has, so a requested target beyond that gap silently stops at the newest available segment instead.
What is a WAL timeline, in plain terms?
- A timeline is PostgreSQL's numbered record of one continuous line of WAL history
- Every promotion after a recovery increments the timeline number by one
- A
.historyfile records where and from which prior timeline the fork happened
Why does promoting a recovered instance matter so much?
Promotion is the point where the recovered instance starts generating its own new WAL on a new timeline, which is a different future than the one the original instance would have generated.
Can I recover to a point that's later than my current replica's state?
No, PITR can only reach as far as the WAL archive has recorded, so it can never move you past the most recent successfully archived change.
How is PITR different from just promoting a streaming replica?
Replica promotion moves you forward to whatever state the replica currently holds, while PITR can move you to any earlier state the WAL archive still retains, which makes it the tool for undoing mistakes rather than surviving hardware failure.
Does WAL replay speed actually matter in practice?
Yes, replaying a long WAL history against a large base backup can take hours, so operators with strict recovery time targets need to keep base backups fresh enough to shrink that replay window.
Is cloud-managed PITR (RDS, Aurora, Neon) a different technology?
No, it is the same base-backup-plus-change-log mechanism exposed through a console or API instead of manual archive_command and recovery target configuration.
Why can't a logical dump support PITR the way a physical backup does?
WAL records changes relative to a known physical starting state, so replay only makes sense on top of a physical base backup, not on top of an arbitrary SQL export with no corresponding WAL position.
What determines the actual recovery point objective PITR can deliver?
The last successfully archived WAL segment sets the ceiling, so monitoring archive failures is effectively monitoring your real-time RPO.
Is PITR always the right tool for an incident?
No, it trades speed for precision, so a full physical restore or replica promotion is often faster when the incident is a hardware failure rather than a specific logical mistake.
Does PITR have security implications beyond disaster recovery?
Yes, the same rewind capability that undoes an accidental deletion also lets an incident responder reconstruct or reverse unauthorized changes made by a compromised account, as long as the archive itself remains trustworthy.
Related
- PITR Basics - the concrete enable-and-restore recipe this page's mental model supports
- archive_mode & archive_command - how WAL actually reaches durable storage
- Recovery Target Options - the parameters that express a stopping condition
- PITR Drill Runbook - measuring real recovery time against this model
- Backup & Restore Foundations - the base backup layer PITR builds on
- RTO & RPO Demystified - how PITR's precision fits the wider recovery picture
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17).