What is point-in-time recovery in a relational database, and what two things must you have been collecting beforehand for it to be possible at all?
answer
- base backup + unbroken archived log chain
- fuzzy online copy made consistent by replay
- target = time / log position / marker / end of log
- missing segment caps recovery at the gap
- restores the whole instance, not one table
basics
~20 sPoint-in-time recovery restores a database to any chosen moment in the past. It needs a base backup of the data files plus an unbroken chain of archived transaction-log records covering everything from the start of that backup onward. You restore the backup, then replay the log and stop at the chosen point.
solid answer
~60 sPoint-in-time recovery (PITR) means restoring the database to an arbitrary moment - not just to whenever the last backup finished. It rests on two ingredients: 1. A **base backup**: a physical copy of the data files, normally taken while the database is running, which is therefore internally inconsistent on its own. 2. **Continuous archiving of the transaction log** (write-ahead log, redo log, binary log - the name varies) from at least the moment the base backup started, shipped somewhere durable and independent of the primary's storage. Recovery restores the base backup onto a target host, then **replays** archived log records forward. The first stretch of replay makes the inconsistent file copy consistent; replay then continues to the chosen recovery target and stops there. Because the target can be any point in that log chain, PITR is what turns 'we lost last night's data' into 'we lost the last few seconds'. The chain must be unbroken: a single missing log segment caps recovery at that gap, and your exposure to data loss is essentially the archiving lag.
go deeper
Say that PITR means restoring to a chosen moment, and that it needs a backup plus the transaction log recorded continuously since that backup.
Explain the fuzzy base backup made consistent by log replay, the recovery target, and why a gap in the archive caps the recoverable window.
Emphasise operational reality: alerting on archive failures, independent storage for archives, retention that never cuts the chain, and the link between backup cadence and replay duration.
Position PITR against replication in the failure taxonomy - node loss versus logical destruction - and reason about window design, archive durability, and instance-level granularity as a constraint on recovery strategy.
## The mechanism Every durable relational engine writes changes to a sequential **transaction log** before (or as) it applies them to data files. That log exists primarily for crash recovery - after a crash, replaying it reconstructs committed work and rolls back uncommitted work. Point-in-time recovery reuses the same machinery over a much longer horizon. The pieces: - **Base backup.** A physical copy of the data files. It is normally taken online, so while the copy is being made other sessions keep changing pages; different files, even different pages, are captured at different moments. That copy is therefore **fuzzy** - not a consistent snapshot in itself. This is expected and fine, because the log fixes it. - **Log archiving.** As the engine fills log segments, each completed segment is copied to an archive: object storage, a separate filesystem, a backup service. Archiving must begin no later than the start of the base backup and must never miss a segment. ## What recovery does 1. Provision a target and restore the base backup's files onto it. 2. Point the instance at the archive so it can fetch log segments. 3. Declare a **recovery target**: a timestamp, a log position, a transaction identifier, a named marker, or simply 'the end of the log'. 4. Start the instance in recovery mode. It replays log records in order, starting from the position recorded when the base backup began. 5. The first phase of replay brings the fuzzy copy to a consistent state - until that point is reached, the database cannot be opened for reading at all. 6. Replay continues to the target, stops, and then the instance is either paused for inspection or promoted to normal read-write operation. Because the target is chosen at recovery time, one backup plus its log chain gives you every instant in that window - hence 'point in time'. ## Why the chain matters more than the backup The usual mental model is that backups are the valuable asset. For PITR, the **log chain is the fragile asset**. A base backup can be re-taken tomorrow; a log segment that was never archived is gone forever, and its absence hard-caps recovery at the moment of the gap. Practical implications: - Archiving failures must be alerted on loudly. A silently failing archive command produces a system that appears backed up for weeks and can recover to nothing newer than the last base backup. - Archives must live on storage independent of the primary. Log segments on the same volume as the data files disappear with the data files. - Retention ties together: the recoverable window starts at the oldest base backup for which you still hold a complete, contiguous log chain. Deleting log segments older than a retention horizon must never cut into the chain that belongs to a base backup you intend to keep. ## Granularity and limits - Recovery always stops on a **transaction boundary**. You cannot land in the middle of a transaction; replay applies committed work up to the target and discards anything that had not committed. - PITR restores **the whole database instance**, not one table. Rolling the live system back to 14:31 discards every valid change made after 14:31 as well. Recovering a single object usually means restoring a copy elsewhere and extracting the rows. - The recovery is only as fast as restoring the base backup plus replaying the log volume in between, which is why backup cadence and recovery duration are directly linked: the longer since the last base backup, the more log there is to replay. - Non-logged operations, external files, and anything the engine deliberately excludes from the log are not reconstructed by replay; know what your engine leaves out. ## What it protects against PITR is the answer to **logical destruction** - a bad migration, an unqualified UPDATE, a buggy release, an application that corrupted rows over hours - and to loss of storage where the archive survived. It is not the answer to hardware failure of a single node; that is what replicas and failover are for. Replicas faithfully reproduce a destructive statement within milliseconds, which is exactly why a replicated system still needs PITR.
- If you have synchronous replicas, why do you still need point-in-time recovery?Replicas protect against loss of a node, not against logical damage. A DELETE without a WHERE clause is a perfectly valid transaction, so it is replicated to every replica almost immediately, including synchronous ones. PITR is the only mechanism that lets you go back to a moment before the damaging statement committed.
- What is your data-loss exposure with continuous archiving, and how would you reduce it?Roughly the amount of log written since the last segment was successfully archived, so it grows with segment size and with archiving lag. You reduce it by archiving more aggressively - streaming the log continuously rather than shipping only completed segments, forcing a segment switch on a timeout, or keeping a synchronous standby whose log is guaranteed durable elsewhere before commit acknowledgement.
The base backup is a photograph of the building; the archived log is the security-camera footage that follows. Replaying the footage from the photograph lets you rebuild the building as it stood at any minute you choose - but only if the footage has no gaps.
saying these in an interview costs you the question
- Believing a nightly full backup alone provides point-in-time recovery
- Thinking the online base backup is a consistent snapshot without log replay
- Storing archived log segments on the same volume as the data files
- Assuming replicas make PITR unnecessary
- Expecting to recover a single table without touching the rest of the database