An accidental `DELETE`, a faulty update, an attack: last night's backup only helps so much, because everything that happened since would be lost. Point-in-time recovery (PITR) solves this. It lets you roll a database back to a specific moment in the past – for example, the minute before the mistake.[1]
Contents
What is point-in-time recovery?
PITR lets an administrator restore a set of data from a backup to its state at a particular point in time.[1] The principle is the same in every system: you need a complete starting point (a base backup) and an unbroken log of every change made after it. Vendors give that log different names – write-ahead log, binary log or transaction log.[4]
A closely related idea is the point-in-time snapshot: a copy of data, files or databases as they were at one moment. Such snapshots are usually read-only.[1]
PostgreSQL: base backup and write-ahead log
PostgreSQL records every change in its write-ahead log (WAL). PITR combines a base backup with the archived WAL files; the feature has been available since version 8.0.[2] After a recovery, PostgreSQL starts a new timeline so that the database's new history does not get mixed up with the old one.[2]
MySQL: binary log by time or position
MySQL writes changes to its binary log. After the base backup has been restored, the binary logs are replayed up to the chosen point – either a timestamp or a log position.[3][5] The tool for this is `mysqlbinlog`.
MongoDB: oplog and mongorestore
MongoDB records changes in the oplog. To restore to a point in time you need a full backup and the matching oplog dump; you restore with `mongorestore`, which reads the logical backups created by `mongodump`.[1]
Advantages
- Less data loss: restore to just before the error instead of to the last backup.
- Targeted fixes: undo a single mistake without losing a whole day's work.
- Verifiable: if you regularly test that backups and logs can rebuild the database, you can also prove it in an audit.[3]
Disadvantages
Severalnines names two limits: the database is unavailable during recovery, and if the base backups are stored elsewhere, bringing all the files together takes a long time.[1]
Conclusion
Point-in-time recovery is the difference between "we have yesterday's backup" and "we're back to 14:59". It only works if the change logs are archived without gaps – and if the restore is rehearsed regularly.
Sources
- Severalnines – Database Backups 101: What is Point in Time Recovery? (Sarojini Devi Nagappan), retrieved 15 Nov 2026
- Hironobu Suzuki – The Internals of PostgreSQL, ch. 10: Online Backup and PITR, retrieved 15 Nov 2026
- Severalnines – ClusterControl: Database Point-in-time Recovery, retrieved 15 Nov 2026
- Vinchin – What Is Point-in-Time Recovery for Databases?, retrieved 15 Nov 2026
- Severalnines – Backup Management for MySQL, MariaDB, PostgreSQL & MongoDB (webinar slides, 2018), retrieved 15 Nov 2026