Backup, Snapshots, and Recovery Semantics

At 09:41, a Harbor Point maintenance job changes thousands of open CA-MUNI reservations to released. The base rows change. The global index removes the same reservations. Every replica faithfully copies the mistake.

Replication is working exactly as designed: it preserves the current history, including a bad but committed command. The team needs to recover the state just before that command without discarding all legitimate trading activity since last night's backup.

“Restore the backup” is too small a plan. A base backup is one starting state. Recovery needs the continuous history after that state and a precise point at which to stop. The useful model is a time machine with a foundation and an unbroken journal.

Core Insight

Point-in-time recovery (PITR) combines:

restorable base backup
  +
continuous archived log history
  +
chosen recovery target
  =
consistent database state at a selected point in time

A base backup alone restores history from around its own capture point. A log archive alone has no database state on which to replay records. Together, the engine can reconstruct a consistent state and stop before a destructive commit.

This is recovery of a whole storage history, not a way to pick a few old rows out of a file. That scope is both its strength and its operational boundary.

The Recovery Ingredients

Harbor Point's primary database writes each durable change to an ordered write-ahead log (WAL). PostgreSQL documents that a file-system-level backup plus archived WAL can be recovered by replaying the log; replay repairs any internal inconsistency in the copied files. The equivalent names differ across database engines, but the roles are stable.

The phrase “physical backup” matters. It copies storage-engine files, not merely table rows. A coordinated live base backup can be taken while pages are changing because WAL replay supplies the missing transitions. A logical dump has useful portability and selective-restore roles, but it is not the foundation used by WAL-based PITR.

A Worked Recovery Trace

The following times and log positions are illustrative. The goal is to make the decision boundaries visible.

02:00  base backup begins; its required WAL range is recorded
02:18  base backup completes and its required history is safely archived
09:41:20  normal reservation activity continues
09:41:23  destructive maintenance transaction commits
09:42:00  team declares an incident and freezes further writes

The restore path is not “copy the 02:00 files and open the database.” It is:

1. Select a verified base backup and its manifest/history metadata.
2. Restore it to isolated recovery hosts, not over the live authority.
3. Fetch the continuous archived WAL from that backup's required start through 09:41:23.
4. Replay records in order.
5. Stop just before the destructive commit, using a tested target rule.
6. Validate the recovered state and dependent-system plan.
7. Fence the old authority, then promote and route clients deliberately.

Suppose the destructive transaction has an exact commit marker X_bad, or the incident procedure has a named restore point immediately before the job. A target tied to that known boundary is usually easier to defend than a remembered wall-clock second. Time targets can still be appropriate, but operators must use a documented timezone, understand whether the chosen target is inclusive, and verify the transaction boundary they intend to include or exclude.

The result should contain the legitimate reservations committed before the selected boundary and exclude the erroneous release operation. If the team continues writes after detecting the mistake, it must separately decide whether to replay, reconcile, or re-enter those post-incident commands. PITR does not magically classify which later changes were good.

Why Replicas Are Not This Recovery Plan

A standby protects against some primary failures and can reduce recovery time. It is not automatically a backup.

bad transaction on primary
  -> replicates to standby
  -> replicates to read copies
  -> appears in derived views

Failing over to a healthy replica after logical corruption merely chooses another copy of the same bad history. A delayed replica can be a useful additional safeguard, but it has its own lag, routing, retention, and promotion risks. Treat it as a separately specified recovery tool, not proof that the organization has a recoverable point before every error.

The same distinction applies to global secondary indexes and external systems. If gsi_open_by_issuer is transactionally maintained inside the restored database, physical recovery replays it with the base rows. If an external search index or dashboard cache is fed asynchronously, restoring the database does not rewind it automatically. Classify it before the incident:

This inventory is the difference between a database that starts and a service that is coherent. Do not declare recovery complete while user-facing derived systems present a later, incompatible history as current.

Recovery Objectives Are Costed Choices

RPO and RTO become useful only when tied to this mechanism.

• Recovery point objective (RPO): How much accepted history can the business lose in the failure being planned for? For PITR, archival continuity and off-site durability determine whether that point is available.
• Recovery time objective (RTO): How long can the service take to restore, replay, validate, rebuild dependencies, and cut over? A completed file copy is only one part of that time.

The trade-off is explicit. More frequent base backups reduce the amount of WAL that must be replayed, which can improve RTO, but consume backup I/O and storage. Longer log retention gives more target choices, but costs archival storage and retention operations. Rebuilding derived indexes keeps the authoritative backup scope smaller, but can extend service recovery. A shorter stated RPO may require faster and more reliable archival transport.

Choose the policy from the business boundary. An issuer-search index might be allowed to rebuild after the reservation service returns. A risk ledger required for legal evidence may need an aligned restore and validation before any trading resumes.

The Archive Is a Live Durability Pipeline

PITR depends on an unbroken sequence of WAL segments starting at least as far back as the selected base backup requires. An archive command that succeeds most of the time is not enough if one missing segment lies in the recovery path.

base backup needs WAL: 0001, 0002, 0003, 0004, 0005
archive contains:      0001, 0002,       0004, 0005

result: cannot replay across the gap in 0003

The archive also needs integrity and idempotency rules. A retry after a crash may attempt to archive an already stored segment. A safe archive accepts an existing segment only when its contents are the same; it must not overwrite a different segment with the same name. Monitor the age of the last successful archival, missing segments, archive retries, storage capacity, and the actual ability to restore from the remote archive.

Archived history protects database changes, not necessarily configuration files, credentials, DNS routing, or external dependencies. A recovery runbook needs those components too, but they are separate backup and deployment concerns. Keep that boundary visible rather than promising that WAL restores an entire application platform.

Check Your Understanding

Check: Harbor Point has a valid 02:00 base backup but its archive lost one required WAL segment from 06:12. Can it recover to 09:41?

Answer: No. The base backup is not sufficient and replay cannot safely skip an ordered history gap. The team may recover only to an earlier usable boundary or use another independent recovery source.

Check: The restored database excludes the bad reservation release, but the external search index still shows it as released. Is recovery complete?

Answer: Not for an endpoint that relies on that search index. The team must rebuild or align the derived system, or keep the endpoint unavailable or clearly stale until its contract is true again.

Practice: Write the Restore Evidence

Write a small recovery checklist for the CA-MUNI incident. Include one check before replay, one check for the chosen stop point, one validation query after replay, and one rule for the external issuer-search index.

A strong answer verifies the base backup manifest and continuous archive range before starting; records an exact restore point or commit boundary and its inclusivity; confirms that reservations expected just before the bad job exist while the erroneous releases do not; and rebuilds or replays the external index from the restored base history before allowing it to answer current-state searches.

Connections

• Replication Flow Control and Backpressure shows why retained logs, archive throughput, and replay capacity are active operational pressure rather than passive backup details.
• Observability for Replicated Data Systems turns backup age, archive continuity, replay rate, restore duration, and validation success into monitored promises.

Resources

• [DOC] PostgreSQL: Continuous Archiving and PITR — Focus: Connect a physical base backup, required WAL sequence, recovery target, timeline, and archive monitoring.
• [DOC] PostgreSQL: pg_basebackup — Focus: Inspect a coordinated physical-backup tool and the metadata that makes later recovery possible.
• [DOC] Percona XtraBackup: Point-in-Time Recovery — Focus: Compare the same base-backup-plus-log-replay pattern in a MySQL ecosystem.

Key Takeaways

• PITR needs a restorable base backup, a continuous log archive, and a deliberately chosen stop point.
• A healthy replica can faithfully copy corruption, so it is not automatically a recovery plan for operator error or bad data.
• Recovery scope must classify base data, in-database indexes, external projections, and independent authorities before an incident.
• RPO and RTO are costed choices about archive continuity, backup cadence, replay time, rebuild work, validation, and cutover.