What It Solves
A nightly backup only lets you restore to last midnight. If someone runs a destructive query at noon, you lose twelve hours. Point in time recovery, often written PITR, lets you restore to any moment between backups, such as one second before the bad statement.
How It Works
PITR combines two ingredients:
- A base backup, a full snapshot taken periodically.
- A continuous archive of the transaction log, the ordered record of every change.
To recover, you restore the base backup, then replay log records up to your chosen recovery target, a timestamp or a transaction id. Replaying stops exactly there, so changes after that point never reappear.
What You Need In Place
PITR only works if the log is being archived continuously and safely off the primary. If a log segment is missing, replay cannot cross the gap. You also need to know roughly when the damage happened, which is why audit timestamps and good logging matter.
Key idea
Point in time recovery restores a base backup then replays the archived transaction log up to a chosen instant, letting you rewind to just before a mistake instead of losing everything since the last backup.