Skip to content

CONCEPT Cited by 1 source

Point-in-time recovery

Definition

Point-in-time recovery (PITR) is the database operation of restoring state to a specific historical timestamp — not just to the time of the most recent backup, but to any intermediate point between backups. Concretely: restore the nearest backup-before-the-target-time, then replay the binlog / WAL forward up to the target timestamp.

PITR requires two things: - A base backup (full, periodic) — gives you a usable starting state. - A change log (binlog / WAL) retained for long enough to replay forward to the target time.

Without either, PITR is impossible: no base backup means nothing to start from; no retained binlog means no way to replay forward.

Why it matters

PITR is the primary production tool for recovering from logical corruption — data that was valid as of some past time but has been accidentally modified, deleted, or corrupted by an application bug, operator error, or malicious actor. Unlike disaster recovery (replace a dead primary with a replica or backup), PITR lets you rewind the clock selectively.

In Vitess

Vitess supports PITR natively via the VTBackup flow + binlog retention. Per the PlanetScale post (Source: sources/2026-04-21-planetscale-faster-backups-with-sharding): "Backups are also needed to perform point in time recovery in Vitess."

The mechanism: restore the most recent full backup taken before the target time, then replay binlog events from the backup's captured GTID position forward to the target time.

The PlanetScale production backup cadence (every 12 hours) sets an upper bound on replay-forward duration: PITR to any point requires replaying at most ~12 hours of binlog.

Relationship to shard-parallel backup

Under concepts/shard-parallel-backup, each shard has its own backup series and its own binlog. PITR runs per-shard: each shard independently restores its nearest backup and replays forward. This means PITR wall-clock also scales shard-parallel — a 20 TB PITR takes a few hours on 32 shards, not days.

Operational uses

  • Recover from accidental deletes (UPDATE / DELETE with bad WHERE clause). Customer case study at PlanetScale: Dub's customer accidentally deleted data; PITR (via backup + cherry-pick) restored it.
  • Recover from logical corruption (application bug writing bad data).
  • Audit / forensic restoration — restore a branch at the state of a specific historical time.
  • Testing / debugging — spin up a dev branch at the state of a production incident.

Caveats

  • Bounded by binlog retention. PITR to a target older than the earliest retained binlog is impossible. Retention is a capacity-planning trade-off (longer retention = more storage).
  • Expensive for large replay deltas. Replaying 12 h of binlog takes time proportional to write volume, not DB size. High-write workloads have slower PITR.
  • Cross-shard PITR consistency. Each shard's GTID is independent; PITR targeting the same wall-clock timestamp across shards produces per-shard-consistent but not cross-shard-transaction-consistent state, unless a cross-shard freeze-point mechanism is used.

Seen in

Last updated · 347 distilled / 1,201 read