Point-in-time recovery

Point-in-time recovery (PITR) is a database recovery method that restores a database to its exact state at a chosen moment, such as one second before an accidental DELETE, by taking a full base backup and replaying the transaction log recorded after it up to the target time. In backup & recovery for databases, it closes the gap between periodic backups: instead of losing everything since the last nightly backup, an organization can recover to almost any instant within its retention window.

PITR relies on the fact that relational engines already write every change to a sequential log for crash safety. PostgreSQL archives its write-ahead log (WAL), MySQL and MariaDB keep the binary log, and SQL Server and Oracle use their own transaction or redo logs. Archiving that log continuously turns it into a complete history of changes that can be replayed on demand.

How point-in-time recovery works

Every PITR process combines two ingredients: a consistent base backup and an unbroken chain of log files from the moment that backup started. Recovery restores the base backup and then applies the logged changes in order until it reaches the target.

1. Take a base backup, a physical copy of the data files or a storage snapshot, while the database runs.
2. Archive the log continuously to separate, durable storage, segment by segment.
3. When recovery is needed, restore the base backup to a new or cleaned server.
4. Replay archived log records from the backup start point forward.
5. Stop at the recovery target, then open the database for normal use on a new timeline.

The recovery target can be expressed in several ways. A timestamp is the most common, but a timestamp is only as precise as the clock and the commit records. A transaction identifier or log position is exact, and a named restore point created before a risky change gives a clean, predefined target.

Recovery point objective and recovery time objective

PITR is evaluated against two business measures. The recovery point objective (RPO) is how much recent data may be lost, and the recovery time objective (RTO) is how long the restore may take.

With continuous log streaming, the RPO can fall to seconds. The RTO, however, grows with the size of the base backup and the amount of log that follows it: replaying a week of heavy write traffic can take many hours, which is why teams balance backup frequency against storage cost.

Point-in-time recovery compared with other backup methods

PITR is not a single backup type but a combination of a physical backup and log archiving. The table compares it with the neighboring methods in backup and recovery.

A logical dump cannot serve as the base for PITR in PostgreSQL, because WAL records refer to physical block locations. In MySQL, a dump that records its binary log coordinates can be followed by binary log replay, which gives a form of PITR at the logical level.

Engine-specific implementations

Each major engine provides PITR with its own log format and tooling, and managed cloud database services usually wrap the same mechanisms behind a restore-to-time option.

After a PostgreSQL recovery stops at a target, the server starts a new timeline. The timeline history keeps the old and new branches of WAL apart, which makes it possible to try several targets and to avoid mixing logs from different branches.

Pitfalls that defeat point-in-time recovery

PITR fails most often for operational reasons rather than technical ones. A backup strategy that has never been tested is an assumption, not a guarantee.

• A gap in the log chain makes every later moment unrecoverable; alert when archiving fails or falls behind.
• Logs stored on the same server as the database disappear with it; archive to separate storage, ideally in another region.
• Untested restores hide missing permissions, keys and configuration; rehearse a full restore regularly.
• Unknown target time wastes hours; record the time of risky operations or create named restore points.
• Restoring over production destroys evidence and later valid data; restore to a separate server and copy back what is needed.

In plain words

Point-in-time recovery works like rewinding a recording to an exact second. The database keeps a full copy taken now and then. It also keeps a diary of every change made since.

If someone deletes important data by mistake at 14:05, the team takes the last full copy. They replay the diary up to 14:04 and stop. The data comes back exactly as it was one minute before the mistake.

A real-world example

A subscription service runs PostgreSQL with a nightly base backup and continuous WAL archiving to object storage in another region, retained for 14 days. At 15:42 on a Tuesday, a maintenance script with a missing WHERE clause deletes about 80,000 customer addresses. The mistake is noticed eleven minutes later.

The operations team restores the previous night’s base backup to a separate server and sets the recovery target to 15:41:30. Replaying the day’s WAL takes about 25 minutes. They then export the restored addresses and load them back into production, where all other changes made after the incident remain intact. No customer data is lost, and the post-incident review adds named restore points to every maintenance script.