What is Point-in-Time Recovery (PITR)?
Learn what point-in-time recovery is, how transaction log replay works, and how PITR minimizes data loss after an incident.
Expected Interview Answer
Point-in-time recovery restores a database to its exact state at a specific past moment by combining a full backup with the replay of transaction logs up to that chosen timestamp or transaction, rather than only to the moment of the last backup.
A plain backup only lets you restore to when the backup was taken, which loses everything written afterward. PITR instead restores the last full backup and then replays the write-ahead or transaction log records generated after that backup, stopping exactly at the target timestamp, log sequence number, or named transaction the operator specifies. This lets teams undo a bad deploy or an accidental mass delete by rolling forward to just before the damaging statement, rather than losing every legitimate change made since the last full backup too.
- Restores to any moment, not just the last backup time
- Lets teams recover from accidental deletes or bad migrations precisely
- Minimizes data loss compared to restoring only the last full backup
- Relies on continuous log archiving rather than frequent full backups
AI Mentor Explanation
A plain backup is like only being able to replay the match from the start-of-innings scorecard, ignoring every ball bowled after that snapshot was taken. Point-in-time recovery is like having ball-by-ball commentary archived, so you can rebuild the exact match state at any specific over and ball, say right before a controversial no-ball was called. You start from the innings snapshot and replay commentary up to that exact ball, not a moment further.
Step-by-Step Explanation
Step 1
Enable continuous log archiving
Configure the database to retain write-ahead or transaction logs beyond the last full backup.
Step 2
Take periodic full backups
Establish a base restore point that logs can be replayed on top of.
Step 3
Choose a recovery target
Pick a timestamp, log sequence number, or named transaction just before the incident.
Step 4
Restore the base backup and replay logs
Load the last full backup, then apply archived log records up to, but not past, the target point.
What Interviewer Expects
- Understanding that PITR needs continuous log archiving, not just periodic backups
- Ability to explain restoring to an arbitrary timestamp rather than only backup time
- A concrete use case such as recovering from an accidental DELETE or DROP
- Awareness of the storage and retention cost of keeping logs long enough
Common Mistakes
- Confusing PITR with simply restoring the most recent full backup
- Forgetting that PITR requires transaction logs to be archived continuously
- Not specifying an exact recovery target, causing over- or under-recovery
- Assuming PITR is instantaneous when replaying a long log history takes real time
Best Answer (HR Friendly)
“Point-in-time recovery lets you restore a database to an exact moment in the past, not just to when the last backup was taken. It works by restoring the last full backup and then replaying the transaction log up to just before something went wrong, like an accidental delete, so you recover almost everything except the bad change itself.”
Code Example
-- postgresql.conf: enable continuous WAL archiving
wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/%f'
-- recovery.signal + postgresql.conf on the restored instance:
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-07-17 14:32:00'
-- The server replays archived WAL files up to, but not past,
-- the specified recovery_target_time before opening for writes.Follow-up Questions
- How does PITR differ from restoring a full backup alone?
- What role does the write-ahead log play in point-in-time recovery?
- How would you determine the exact recovery target after an incident?
- What are the storage implications of retaining logs for PITR?
MCQ Practice
1. Point-in-time recovery restores a database to:
PITR restores the last full backup and replays archived transaction logs up to a precisely chosen target moment.
2. What must be continuously retained for PITR to work?
PITR depends on archived transaction logs covering the period after the last full backup was taken.
3. A common use case for PITR is:
PITR lets teams roll back to the moment right before a destructive statement, recovering nearly all legitimate data.
Flash Cards
What is point-in-time recovery? — Restoring a database to an exact past moment by replaying logs after the last full backup.
What does PITR require besides full backups? — Continuous archiving of the transaction or write-ahead log.
Why use PITR over a plain restore? — It recovers data up to just before an incident instead of losing everything since the last backup.
What defines the recovery target in PITR? — A timestamp, log sequence number, or named transaction to stop replay at.