Replication Copies Corruption Too

The primary answers SELECT 1. So does its streaming standby. Their WAL positions show that the standby has caught up, and PostgreSQL promotes it without error. shipment-100 is missing from both.

Every infrastructure signal in that sequence can be true at the same time. The OPS-04 fixture makes it happen with PostgreSQL 18.1: a valid DELETE commits on the primary, reaches the physical standby, and survives promotion. Failover restores a writable endpoint. It does not restore the deleted shipment.

Replication protects the availability of current state, including current mistakes. Recovering correct historical state needs a different capability: an independently selectable point from before the mistake, followed by a test of the business behavior that point must support.

A current copy answers an availability question

Physical streaming replication sends WAL records from a primary to a standby. The standby replays those records to stay close to the primary’s current state. PostgreSQL’s streaming-replication documentation describes this as the standby continuously applying WAL received from the primary.

That mechanism is exactly why replication helps after an instance failure. A standby which has received the necessary WAL can take over without rebuilding the cluster from an older backup. Depending on the topology and application, it may also serve read traffic before promotion.

The same mechanism explains why it cannot undo an accepted logical operation. PostgreSQL does not know that deleting shipment-100 was a mistake. The transaction was valid, committed, and written to WAL. Replaying it is the standby’s job.

The fixture observes these positions after the delete:

destructive transaction LSN  0/5000910
standby replay LSN           0/6000000
primary connection probe     pass
standby connection probe     pass
shipment-100 on standby      absent

The exact LSNs belong to one local run; their relationship is the evidence. The standby had replayed beyond the destructive transaction. Lag was not the problem.

This gives two axes which should not be collapsed into a single “redundancy” claim:

A standby can be excellent availability infrastructure while being the wrong source for logical recovery. That is not a weakness in replication. It is a boundary around the problem replication solves.

Promotion preserves the state it received

Promotion is an attractive response when the primary host or process is gone. It changes the standby from recovery mode into a server which can accept writes. It does not inspect the data and choose which transactions deserved to survive.

OPS-04 promotes the caught-up standby with pg_promote(). PostgreSQL reports that the server is no longer in recovery, and the connection probe remains green. The application probe still fails because shipment-100 has no row.

This negative control matters because “failover succeeded” is easy to let expand into “the database recovered.” The first statement concerns service continuity. The second needs a recovery point and an acceptance boundary.

The row-count control exposes another false comfort. Before the delete, the fixture has two shipments. It then commits shipment-102 and deletes shipment-100. The standby still has two rows after replay. A count-based check passes while the identity required by the application is gone.

An acceptance probe should therefore name behavior, not merely database activity. This fixture loads shipment-100 by its stable external identity, expects its in_transit state, and checks its two ordered events. A real system might instead verify a known account balance, order, entitlement, or ledger position. The useful probe is the smallest one which distinguishes available data from the business state that must be recovered.

History needs a base and an unbroken WAL chain

Before the destructive transaction, the fixture takes a physical base backup. It also enables continuous WAL archiving and creates a named restore point:

before_destructive_delete  0/5000090

After that point, shipment-102 is booked. The later transaction deletes shipment-100. The WAL archive retains both changes.

Point-in-time recovery starts from the base backup, replays archived WAL, and stops at the named point. PostgreSQL’s continuous-archiving documentation is precise about the dependency: successful recovery requires a continuous sequence of archived WAL extending back to the start of the backup. A base backup without its required chain is not a selectable history.

The fixture removes the WAL segment containing the named point and tries the same recovery. The target cannot become ready. The retained base is real; the requested recovery path is still broken.

At the other extreme, the end-of-WAL control supplies the complete archive but does not select the earlier target. PostgreSQL replays through the delete. The server becomes available and the shipment probe fails again. More retained history does not help if the recovery instruction chooses the wrong end of it.

The working path uses both pieces: a verified base backup and the WAL needed to reach an explicitly chosen target. It starts a clean data directory, stops at before_destructive_delete, promotes on reaching that point, and runs the application probe.

The recovered target contains shipment-100 and its event history. It does not contain shipment-102.

Restoring correctness has an explicit cost

Excluding shipment-102 is not incidental damage hidden by the fixture. It is the observed recovery-point loss: one valid booking committed after the selected target.

That cost is why “restore to before it broke” is incomplete as an operational instruction. Before which transaction? How confidently can the bad interval be bounded? Which valid writes fall inside it? Can they be replayed from an independent ledger, queue, or external system? Who decides that losing or reconstructing them is preferable to retaining the bad change?

The laboratory chooses a named point immediately before the later booking, so the answer is inspectable:

These are synthetic records, not a production RPO. A real recovery decision needs a declared tolerance expressed in the units the business loses: orders, payments, messages, bookings, or time. The mechanism can produce a target. It cannot decide whether the target’s loss is acceptable.

Recovery also creates a new PostgreSQL timeline. The PITR documentation explains why timeline history must be retained when recovery branches from earlier state. That detail becomes operationally important as soon as another standby or later recovery must follow the new primary rather than the abandoned history.

Replicas, snapshots, and WAL archives have different jobs

A physical standby is the natural choice when a current endpoint must survive an instance failure or serve compatible reads. It brings authentication, WAL retention, replay monitoring, promotion, and timeline obligations. Reconsider it as the recovery source once the unwanted transaction has replayed.

An independent storage or provider snapshot can preserve a historical point without operating the WAL chain directly. Its value depends on whether the snapshot is consistent, old enough, retained long enough, restorable with the available permissions, and granular enough for the acceptable data loss. Restoring it still needs application acceptance.

A physical base backup plus continuous WAL can select points between base backups. It also requires PostgreSQL compatibility, a complete archive, correct timeline handling, enough storage, and an exercised procedure. The fixture’s missing-segment control exists because “we archive WAL” is not proof that a particular target can be reached.

None of these mechanisms dominates without the system’s interruption budget, write rate, data-loss tolerance, retention, restore duration, provider boundary, and operator capability. Many systems need more than one: a standby for current availability and independent history for deletion, corruption, or a bad deployment which changes durable state.

The operational split is simple to state and worth enforcing in tests:

availability test
  fail the primary, promote the standby, accept current application behavior

historical recovery test
  choose an earlier point, restore without current state, measure excluded
  writes, and accept the required application behavior

Calling both exercises “database recovery” hides the decision each one proves. A green replica tells you that another server has the current state. If the question is whether yesterday’s correct state still exists, make the recovery path answer that question directly.