Log Shipping and the Recovery Point Objective

A recovery point objective is a duration, usually stated as the maximum amount of work that may be lost in a failure. It is not a number of backups, a replication factor, or a claim about how often a job runs. A system that copies data every five minutes does not have a five-minute recovery point objective unless the copy is consistent with the transaction stream and the copy’s own durability is known.

Log shipping is the mechanism that makes the objective expressible. Instead of copying the database’s files, which change constantly and inconsistently, it copies the ordered record of changes, which is append-only and self-describing. Restoring means applying a base copy of the files and then replaying the log up to a chosen point. The precision of that point is the recovery point objective.

A time budget, not a copy count

The gap between what a client was told and what survived a failure is the only quantity that matters. If a client received an acknowledgement for a transaction that no longer exists after failover, the system lost committed data, and no amount of replication topology makes that acceptable. The recovery point objective is the bound on that gap, and it is measured from the last durable commit, not from the last backup.

This is why the number is hard to hold. The gap is produced by several independent delays stacked on top of each other: the time between the commit and its log record reaching stable storage on the primary, the time for that record to reach a replica, the time for the replica to make it durable, and, for archive-based recovery, the time until the archive has it. Each of those is a separate mechanism with its own failure mode, and the objective is only met if every one of them is bounded.

The commit acknowledgement ladder

Most write-ahead-logged databases expose a setting that controls how much of that chain the commit waits for. The settings form a ladder, and each rung trades latency for a smaller loss window.

At the bottom, the commit returns before the log record is flushed locally. A process crash does not lose the transaction, because the record is in the operating system’s page cache, but an operating system crash or a power loss does. This is appropriate for data that can be recomputed and is a poor choice for anything a client was told succeeded.

The next rung waits for the local log to be flushed to the storage device. This is the common default: a commit survives a power loss of that machine, and the loss window for a single-node failure is zero for acknowledged transactions. The wait includes the device’s own cache flush, which is why disabling write barriers or using a virtual disk that ignores flush requests silently removes the guarantee.

The higher rungs wait for a standby. There are two variants, and the difference between them is the difference between receiving and being able to prove receipt. One waits until the standby has written the record to its operating system, which survives the primary’s failure but not the standby’s. The other waits until the standby has flushed it to its own storage, which survives both. A further rung waits until the standby has replayed it, which is what makes a read on the standby see the committed transaction; that is a visibility property more than a durability one, and it costs the most.

The configuration usually also allows a quorum: wait for any k of a named set of standbys, rather than a specific one. Quorum converts the latency of the slowest required replica into the latency of the k-th fastest, at the cost of accepting that any k of the set may be the survivors.

Each rung adds a network round trip and a remote flush to every commit. That is the trade: a synchronous commit is slower by at least the round-trip time to the standby plus the standby’s flush latency, and the client sees that latency on every transaction. Asynchronous replication removes the wait and leaves the loss window equal to the replication lag, which is unbounded under load and becomes infinite when the link is down.

Why the archive lags by a segment

Archive-based shipping does not stream individual records. The primary writes its log in fixed-size segments, and the archiving mechanism is invoked when a segment is complete. Segment size is a build-time constant, 16 MiB in the common default configuration, so a segment takes as long to fill as the write rate dictates. On a busy system that is seconds; on an idle one it can be hours.

The consequence is a floor on the recovery point objective that has nothing to do with the archive’s reliability. If the primary fails with a partially filled segment that has not been archived, the transactions in it are lost even though the archiving command was configured correctly and never failed. The records exist on the primary’s disk, and if the primary’s disk survives, they can be recovered manually; if it does not, they are gone.

The standard mitigation is a timeout that forces a segment switch after a period of low activity, producing a short segment rather than waiting for a full one. The timeout bounds the wait, at the cost of archiving many nearly empty segments and of adding switch records that consume log space. The setting is a direct expression of the recovery point objective: a timeout of five minutes means no acknowledged transaction waits more than five minutes to be archived, assuming the archive itself keeps up.

The archive’s own failures matter more than they appear to. When the archive command fails, the primary does not discard the segment. It retains it and retries, and the retained segments accumulate. On a busy system that accumulation fills the log directory in hours, and a full log directory stops the primary from writing, which converts a backup failure into a write outage. Alerting on archive failure is therefore not a backup concern; it is an availability concern, and the alert has to fire on the retry state rather than on the eventual disk-full.

Replication copies corruption faithfully

Streaming replication ships the physical log, which means it ships whatever the primary wrote, including pages that are wrong. A storage controller that returns corrupt data without an error, an application bug that writes a malformed page, or an operator error such as a mistaken mass update all reach the replica at the speed of the log.

This is not a defect of physical replication; it is what physical replication means. The replica is a copy, and a copy of a bad state is a bad state. What protects against this class is not replication but history: the ability to restore to a point before the damage, which requires a base backup and an archive that extends back past the event.

A delayed replica is the cheap version of that history. It applies the log with a fixed time offset, so a destructive statement takes effect there only after the delay has passed, leaving a window in which the delayed replica holds the pre-damage state. The window is bounded by the delay and by the log retention needed to keep the replica caught up; increasing the delay increases the storage and the recovery time for everything else. It is a trade of recovery point objective against protection from logical error, and the two objectives pull in opposite directions.

Measuring the gap instead of asserting it

The gap between the last durable commit and the last shipped log is measurable, and stating it without measurement is guesswork. On the primary, the current log position is known exactly. The last position the archive has confirmed is also known, because the archive command’s success is recorded by the retention of segments and by the tooling that tracks them. The difference in bytes, divided by the recent log generation rate, is the archive lag in seconds.

For streaming replication, the same subtraction between the primary’s current position and the standby’s replayed position gives the byte lag; the same division gives time. Under a steady write rate the estimate is good. Under a bursty one it is misleading in the dangerous direction, because the rate during the burst is higher than the average, and the time to drain the backlog is longer than the average predicts.

The failure that no measurement catches is a standby that reports positions it has not made durable. A replica whose storage lies about flush completion, or whose configuration disables the flush, advances its reported position while the data sits in a volatile cache. The lag metric reads zero, the commit path reports success, and a power failure at the wrong moment loses transactions that every monitoring system believed were replicated. The only defence is to verify the durability settings at both ends, which is a configuration property rather than a runtime measurement.

Recovering to a point in time also requires a base copy to replay onto, and the age of that base copy sets the floor on recovery time even when the log is complete; that relationship is covered in incremental backup chains and restore depth. What happens when two nodes both believe they are the primary is the subject of fencing and quorum in automated failover.