JOSHUA WONG
Menu
Course contents
On this page
Lesson 37 of 59

Recover yesterday’s data after today’s mistake

Distinguish a running copy from a recoverable point in history.

An operator accidentally deletes the orders table. A second live copy receives the same deletion. Switching to that copy keeps the service running, but does not restore the missing orders.

A replica is another copy receiving database changes; replication is developed in the next stage. A backup preserves a recoverable state under a specified procedure. They can serve different failure cases. An up-to-date copy may faithfully contain the mistake.

Restore to the point before the error

Suppose a valid base backup represents 01:00, and a complete archived change log covers the period afterward. The mistaken transaction commits at 10:05. We want to restore to just before that transaction, using the engine’s supported recovery target semantics.

  1. Restore the 01:00 baseline

    Use the backup, its required metadata, and the correct software and keys.

  2. Replay retained history

    Apply valid changes in order until the chosen safe target before the mistake.

  3. Validate before reopening

    Check the orders, required constraints, access, and the application's ability to operate.

Point-in-time recovery combines a valid baseline with enough continuous history to reach the target.

A missing log segment can break the chain. Having a recent-looking backup filename is not proof that the target is reachable. A restore test exercises the complete dependency set, including encryption keys and permissions.

State loss and downtime separately

A recovery point objective, or RPO, specifies tolerated data loss under a named failure. A recovery time objective, or RTO, specifies tolerated recovery duration to a defined service boundary. These are objectives that the procedure must demonstrate, not labels that make a backup sufficient.

Suppose copying a 300 GB backup at a sustained 100 MB/s takes 3,000 seconds, or 50 minutes, using decimal units. If replay takes 20 minutes and validation takes 10, those stages alone require 80 minutes. Detection, provisioning, and traffic switching may add more.

An objective of 15 minutes cannot be justified by the existence of this backup. The recovery path must change or the objective must acknowledge the measured limit.

Stop the history early

The backup is valid, but the available logs end at 09:00. Can this procedure guarantee recovery to 10:04?

Require every needed part of the history

No. It lacks the changes between 09:00 and the target. Another complete source might supply them, but this backup-and-log set cannot. A successful earlier restore would not establish the requested recovery point.

An engine is operable when recovery has been practiced with the data and dependencies it actually needs. Recovery from user mistakes belongs beside crash recovery, not inside an assumption that every failure is a process restart.

PostgreSQL’s continuous-archiving documentation specifies its base-backup and WAL requirements. The times above are illustrative capacity calculations.

Extended reference

The extended chapter preserves the original technical coverage, additional examples, and citations. The explanation above supplies the concepts needed for this page; the reference develops the specialist cases.

Find a section from the original chapter