pitpoints.com is for sale

Point-in-Time Recovery: Restoring a Database to the Moment Before the Mistake

Published · updated · 3 min read · PITPoints Analytics

Key takeaways
  • Point-in-time recovery restores a database to any chosen moment in the past.
  • The principle is always the same: a base backup plus an unbroken log of every change made after it.
  • PostgreSQL uses the write-ahead log, MySQL the binary log, MongoDB the oplog.
  • The downside: the database is unavailable while it is being restored.

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

  1. Severalnines – Database Backups 101: What is Point in Time Recovery? (Sarojini Devi Nagappan), retrieved 15 Nov 2026
  2. Hironobu Suzuki – The Internals of PostgreSQL, ch. 10: Online Backup and PITR, retrieved 15 Nov 2026
  3. Severalnines – ClusterControl: Database Point-in-time Recovery, retrieved 15 Nov 2026
  4. Vinchin – What Is Point-in-Time Recovery for Databases?, retrieved 15 Nov 2026
  5. Severalnines – Backup Management for MySQL, MariaDB, PostgreSQL & MongoDB (webinar slides, 2018), retrieved 15 Nov 2026

More on this topic