Skip to content
Navigation

Type at least two characters. Search covers page titles, headings, tags and database names.

↑ ↓ to navigateEnter to openEsc to close0 pages
Operationsadvanced

Point-in-Time Recovery

Recovering to a chosen moment across engines, and the preconditions that must exist beforehand.

3 min readAdvancedUpdated Edit this page

Point-in-time recovery (PITR) restores a base backup and replays the transaction log up to a chosen instant. It is what turns "we lost the last 24 hours" into "we lost 30 seconds".

Preconditions

PITR is only possible if it was configured before the incident:

  • Continuous archiving of the transaction log, to storage independent of the primary.
  • A base backup taken after archiving began.
  • An unbroken log chain from that backup to the target time.
  • Enough retention that the target time is still within the window.

Per engine

EngineBaseLogTarget selection
PostgreSQLBase backupArchived WALrecovery_target_time, _xid, _lsn, _name
MySQL / MariaDBPhysical or logical backupBinary logmysqlbinlog --stop-datetime or GTID exclusion
MongoDBSnapshot or dump with oplogOplog--oplogLimit timestamp
ClickHouseBACKUPNo general logBackup granularity only
CassandraSnapshotCommit log (per node)Snapshot granularity in practice
YugabyteDBSnapshot scheduleInternalRestore to timestamp

Note the pattern: engines with a logical, ordered, cluster-wide log support fine-grained PITR. Engines without one recover to a backup boundary — which is a design fact to plan around, not a gap to work around.

Procedure

  1. Establish the target. Find the moment just before the damaging operation, from application logs, the audit log, or the transaction log itself. Pick a target slightly before it.
  2. Restore the base backup into an isolated environment.
  3. Configure recovery with the target and, where the engine supports it, a pause on reaching it.
  4. Replay and inspect. Connect read-only and verify the data is what you expect before promoting.
  5. Extract or promote. For a partial recovery — one table, one collection — export from the recovered copy and apply to production. For a full recovery, promote and redirect traffic.
  6. Reconcile. Everything written to the damaged system after the target time is not in the recovered copy. Decide explicitly what happens to it.

Named restore points

Creating a marker before a risky operation removes the guesswork later:

-- PostgreSQL
SELECT pg_create_restore_point('before_release_42');

Make this a step in every migration and deploy runbook. It costs nothing and turns "roughly 14:20" into an exact target.

Practise it

The two facts that matter — how long a full restore actually takes, and how far back the archive really reaches — are only knowable by doing it. See Restore Testing.