Point-in-Time Recovery: Rewind Your Database to a Specific Second
Point-in-Time Recovery (PITR) is a database time machine, restoring data to a specific second, not just the last snapshot. It's crucial for reversing application-level errors.
Why it exists
Traditional backups restore a database to the time of the backup, like midnight. If a critical failure happens at 3 PM, you lose 15 hours of data. PITR was invented to drastically shrink this data loss window (the Recovery Point Objective) from hours to mere seconds.
The mental model
Think of PITR like a security camera system. A full backup is a single photo of a room taken once a day. The transaction log is a continuous video recording of every change. To see how the room looked at 2:35:15 PM, you don't scrub through days of video. You grab the last daily photo and play the video forward from there until you reach the exact second you need.
How it works
PITR is a two-step process. First, you restore the most recent full database backup taken before your target recovery time. This provides a stale but consistent state. Second, you apply a continuous log of all subsequent transactions (like PostgreSQL's Write-Ahead Log or MySQL's binlog) up to your precise point in time. The database replays these transactions, rolling forward to the exact state it was in at that moment.
When to use it
PITR is your "undo" button for logical data corruption. Use it to recover from human error, like an UPDATE query without a WHERE clause, a buggy code deployment that corrupts records, or an accidental DROP TABLE. It saves you from losing all work done since the last nightly backup.
When not to use it
PITR is not a high-availability solution. For surviving hardware failure, you should fail over to a hot standby replica, which is nearly instantaneous. A full PITR process can take hours for large databases. It's also overkill for systems where some data loss is acceptable, like caches or non-critical analytics data.
One canonical example
PostgreSQL implements PITR using base backups and continuous archiving of its Write-Ahead Log (WAL). An admin takes a base backup, then configures the server to archive every WAL file to durable storage. To recover, they restore the base backup and provide a command that tells Postgres how to fetch and replay the archived WAL files until a specified timestamp.
Interview question
What primary problem does Point-in-Time Recovery (PITR) address that traditional full backups cannot?
- a.Minimizing data loss from logical errors by restoring to a precise moment.Correct
- b.Ensuring continuous application uptime during database maintenance.
- c.Preventing data loss due to hardware failures by providing immediate failover.
- d.Reducing the overall storage footprint required for database backups.
Why? this is the answer
PITR's core purpose is to restore a database to a specific second, drastically shrinking the data loss window from logical errors like accidental data corruption. Option C describes high-availability solutions, which the card explicitly states PITR is not designed for.
Just read this? Test yourself on what you have been reading.
Read the original → en.wikipedia.org
- #databases
- #disaster recovery
- #backups
- #architecture
Put your scrolling time to good use
Learn one idea, try a quiz and save useful cards for revision. Tezvyn makes it easy to learn and stay current in your tech field, a few minutes at a time.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on databases — each one lists the topics its interview covers.
See open roles