Point-in-Time Recovery
also called PITR
Restoring a database to any moment within a retention window by replaying transaction logs onto a base backup, rather than only to a snapshot boundary.
Snapshot-only backup gives an RPO equal to the snapshot interval — nightly snapshots mean up to twenty-four hours of loss. Point-in-time recovery continuously archives the transaction log, so restoration can target any second within the window.
Its most valuable use is not hardware failure, which replication already handles, but logical
damage: a bad migration, an UPDATE without a WHERE clause, an application bug corrupting rows.
Replication faithfully propagates all of these to every replica within milliseconds. Restoring to the
moment before the mistake is the only remedy.
Practical realities. Restore time is not RPO — recovery means provisioning an instance, restoring the base backup and replaying logs, which for a large database is hours. The RPO is near zero and the RTO is not, and both belong in the plan.
Knowing the target time is the hard part. Recovering to just before a bad migration requires knowing when it ran, which is why deployment and migration timestamps are operationally valuable.
And the standard practice: restore to a new instance and verify, rather than restoring over the original. It preserves the evidence and lets you compare the two before committing.