What is Point-in-Time Recovery (PITR)?

Point-in-time recovery is a restore method that replays a change log over a base backup to rebuild a database as of any chosen second. The scenario it exists for is not hardware failure — replication handles that — but the human one: a migration that corrupted rows at 14:32, a script that deleted the wrong tenant. PITR answers with the only thing that helps: the database exactly as it was at 14:31:59.

Key takeaways

The mechanism, in one working session

-- The change log is the raw material: every committed write, in order.
-- Before risky work, you can drop a named marker into it:
SELECT pg_create_restore_point('before_pricing_migration');

-- Recovery = base backup + replay, stopped where you say:
--   restore the base backup taken at 02:00
--   replay the log forward…
--   …and stop just before the damage:
--     recovery_target_time = '2026-08-05 14:31:59+00'
--     (or recovery_target_name = 'before_pricing_migration')

-- Everything committed before the target exists; nothing after it does.

This is the write-ahead log doing double duty: the same ordered record that gives transactions their durability becomes, when archived continuously, a time machine. Document databases run the identical play with the oplog — capture the operation stream, replay onto a snapshot, stop on demand (PostgreSQL calls it continuous archiving; MongoDB documents the same architecture over filesystem snapshots).

After any restore, the drill’s verification step is application-level — confirm the timeline landed where intended:

// JavaScript / Node.js — Back4app JS SDK
// Restore drill: verify the restored data lines up with the incident timeline
const incident = new Date('2026-08-05T14:32:00Z'); // when the bad deploy hit

const latest = new Parse.Query('Order');
latest.lessThan('createdAt', incident);
latest.descending('createdAt');
const lastGood = await latest.first();   // newest order before the incident

const after = new Parse.Query('Order');
after.greaterThanOrEqualTo('createdAt', incident);
const leaked = await after.count();      // must be 0 on a clean PITR restore

console.log(`last good write: ${lastGood?.get('createdAt')}`);
console.log(`rows past the recovery target: ${leaked}`);

// Flutter / Dart — Back4app Flutter SDK
// Restore drill: verify the restored data lines up with the incident timeline
final incident = DateTime.parse('2026-08-05T14:32:00Z'); // the bad deploy

final latest = QueryBuilder<ParseObject>(ParseObject('Order'))
  ..whereLessThan('createdAt', incident)
  ..orderByDescending('createdAt')
  ..setLimit(1);
final lastGood = await latest.query();   // newest order before the incident

final after = QueryBuilder<ParseObject>(ParseObject('Order'))
  ..whereGreaterThanOrEqualsTo('createdAt', incident);
final leaked = await after.count();      // must be 0 on a clean PITR restore

print('last good write: '
    '${(lastGood.results?.first as ParseObject?)?.createdAt}');
print('rows past the recovery target: ${leaked.count}');

// iOS / Swift — Back4app Swift SDK
// Restore drill: verify the restored data lines up with the incident timeline
let incident = ISO8601DateFormatter()
  .date(from: "2026-08-05T14:32:00Z")!   // when the bad deploy hit

let latest = Order.query("createdAt" < incident)
  .order([.descending("createdAt")])
  .limit(1)
latest.first { result in                 // newest order before the incident
  if case .success(let lastGood) = result {
    print("last good write: \(String(describing: lastGood.createdAt))")
  }
}

let after = Order.query("createdAt" >= incident)
after.count { result in                  // must be 0 on a clean PITR restore
  if case .success(let leaked) = result {
    print("rows past the recovery target: \(leaked)")
  }
}

// Android / Kotlin — Back4app Android SDK
// Restore drill: verify the restored data lines up with the incident timeline
val incident = Date(1754404320000L)      // 2026-08-05T14:32:00Z, the bad deploy

val latest = ParseQuery.getQuery<ParseObject>("Order")
latest.whereLessThan("createdAt", incident)
latest.orderByDescending("createdAt")
latest.limit = 1
latest.findInBackground { lastGood, _ -> // newest order before the incident
    println("last good write: ${lastGood?.firstOrNull()?.createdAt}")
}

val after = ParseQuery.getQuery<ParseObject>("Order")
after.whereGreaterThanOrEqualTo("createdAt", incident)
after.countInBackground { leaked, e ->   // must be 0 on a clean PITR restore
    if (e == null) println("rows past the recovery target: $leaked")
}

How a restore reaches 14:31:59

Two properties fall out of the mechanism. First, RPO is set by capture frequency: logs shipped every few seconds mean seconds of maximum loss, regardless of when the last snapshot ran. Second, RTO is set by replay distance: restoring to 23:00 from a 02:00 base means replaying 21 hours of writes — which is why PITR systems still take frequent snapshots, not for granularity but to shorten the runway.

Snapshots vs. PITR

Which recovery guarantee do you actually need?

Common use cases

• The bad deploy. A migration or hotfix corrupts data mid-afternoon — restore to the second before it shipped.
• Fat-finger deletions. The wrong tenant, collection, or WHERE clause — recover to just before the statement ran, often into a scratch database to extract only what was lost.
• Ransomware and account compromise. Rewind to before the intrusion touched data — with encryption at rest guarding the archive copies themselves.
• Compliance retention. Regulated products must prove recoverability — documented windows, tested restores, auditable drills.
• Pre-launch rehearsal. The restore drill as a feature gate: teams that have restored to a timestamp once do it calmly the night it matters.

Should you rely on default backups? A decision matrix

Whatever the platform provides, three decisions stay yours: the RPO your product can survive, the RTO your users will tolerate, and the drill cadence that keeps both numbers honest — the discipline NIST’s contingency-planning guidance formalizes as: define objectives, then test against them.

Limitations and trade-offs

• Replication is not recovery. Replicas replicate the mistake within seconds; only backups reach before it. Different tools, different failure classes.
• The window is finite. PITR reaches any second — inside retention. Damage discovered after the window closes is permanent; size retention to detection time, not storage price alone.
• Replay takes time. Log-heavy restores can run long; your real RTO is measured by drills, not quoted by dashboards.
• Restores land whole. Classic PITR rebuilds the database at a timestamp; extracting one table’s lost rows means restoring to a scratch instance and copying across — plan for that workflow.
• An untested backup is a rumor. Silent export failures, expired credentials, missing runbooks — every one is invisible until a drill or a disaster finds it. Drills are cheaper.

PITR and backups on Back4app

Back4app is an open-source Backend-as-a-Service (BaaS) platform that combines a managed database, auto-generated REST and GraphQL APIs, authentication, file storage, and Cloud Code serverless functions. Backups of the managed database run on schedule without setup, and restore is a dashboard operation rather than a night of log archaeology — the snapshots-and-mechanics half of this article, absorbed by the platform. The half that stays with you is the deciding: your RPO and RTO targets, your retention needs, and the quarterly drill where the code tabs above verify that the restored timeline is exactly the one you asked for.