RPO: engineering an acceptable loss window · Backup & DR

Reading an estate produces a table of systems and of the mechanisms currently protecting each one. It does not tell you whether any of those mechanisms is adequate, because adequacy is a comparison and the survey supplies only one side of it. The other side is a pair of numbers the business owns: how much recent work it can afford to recreate, and how long it can afford to be without the system. This part derives both, starting with the one that is quoted constantly and derived almost never.

The window is a subtraction, not a setting

The recovery point objective is the amount of recent work an organisation has agreed it can afford to recreate or lose. Two words in that sentence do most of the work. Agreed makes it a business decision rather than a technical one. Recreate matters because the cost of a lost hour is not always the data itself: orders that still exist in an upstream queue, or on paper, can be re-entered, while sensor readings that were never written anywhere else cannot. An RPO that is expensive to meet is sometimes cheaper to absorb, and that judgement belongs to whoever owns the revenue.

What no part of that definition contains is a product. A vendor can tell you how often its software is capable of taking a copy; it cannot tell you how much data you will lose, because the loss depends on when the failure lands relative to the copies you chose to take. The number is a subtraction between two moments, and you own both of them.

Take the commonest arrangement in production: one full backup nightly at 01:00, nothing continuous, and a failure model of “the primary storage becomes unreadable at an arbitrary time”. A failure at 20:00 leaves the 01:00 copy as the newest thing that exists, so nineteen hours of work is gone. That number is a fact about one incident, and it is the wrong thing to write in a design document, because the next failure will not arrive at 20:00.

Exposure under this architecture is a sawtooth. It drops to roughly zero when a backup completes and climbs linearly until the next one. The honest statement of its RPO is therefore up to 24 hours, worst case at the moment before the next backup runs. Two shortcuts are commonly taken instead, and both mislead. Quoting the nineteen hours describes an incident that already happened. Quoting twelve hours — the mean loss if failures are uniformly distributed — describes an average outage, and nobody gets an average outage. They get the one they get.

“Up to 24 hours” is still generous, because it treats the backup as instantaneous. A file-level job that runs from 01:00 to 01:40 has read different files at different moments, so the recovery point of any given file is whenever the job reached it. The window is the interval plus the duration of the copy, and only a database backup that brackets itself and carries its own log has a single, defined instant it is consistent as of.

The failure model matters as much as the schedule. Twenty-four hours is the answer for loss of the primary copy. For an accidental deletion noticed nine days later, the relevant question is whether any surviving copy predates the mistake at all — and if retention holds seven days, the window for that failure mode is not 24 hours but unattainable. One system, one schedule, two entirely different numbers. An RPO stated without its failure model is not a number, it is a mood.

Measured: 45000 rows in the backup, 50000 after recovery

The capture below was taken on PostgreSQL 18.6 to make the subtraction visible rather than argued. A base backup was taken while a workload ran, business continued afterwards, and then somebody deleted every row.

Read-only / Safethe base backup, and the work that arrived after it— EXECUTION VALIDATED on PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2), from the application-consistency and point-in-time-recovery capture, Part C.

$ pg_basebackup -D /work/base -X stream -c fast

  >>> exit code: 0
rows contained in the base backup: 45000

--- business continues after the backup: 5,000 more orders arrive ---
rows now                      : 50000
checksum of the business data : sum(amount)=825025000
recovery target time          : 2026-08-28 13:34:40.077562+00

--- and then somebody runs an unqualified DELETE ---
rows after the mistake        : 0
pg_stat_archiver:
  archived=6 failed=0 last=000000010000000000000005
WAL segments in the archive   : 5
  000000010000000000000001
  000000010000000000000002
  000000010000000000000003
  000000010000000000000003.00000028.backup
  000000010000000000000004
  000000010000000000000005

The base backup holds 45000 rows and will hold 45000 rows forever. Everything between that figure and the 50000 rows the business actually had is work that exists nowhere inside the backup file, and the only reason it survives is that the write-ahead log segments carrying it were copied to the archive as they completed. Recovery replays them onto the base backup, and the transcript names each one it fetched:

  2026-08-28 13:35:12.707 UTC [631] LOG:  restored log file "000000010000000000000003" from archive
  2026-08-28 13:35:12.708 UTC [631] LOG:  starting point-in-time recovery to 2026-08-28 13:34:40.077562+00
  2026-08-28 13:35:12.708 UTC [631] LOG:  redo starts at 0/3000028
  2026-08-28 13:35:12.713 UTC [631] LOG:  restored log file "000000010000000000000004" from archive
  2026-08-28 13:35:12.713 UTC [631] LOG:  completed backup recovery with redo LSN 0/3000028 and end LSN 0/3000120
  2026-08-28 13:35:12.713 UTC [631] LOG:  consistent recovery state reached at 0/3000120
  2026-08-28 13:35:12.713 UTC [625] LOG:  database system is ready to accept read-only connections
  2026-08-28 13:35:12.726 UTC [631] LOG:  restored log file "000000010000000000000005" from archive
  2026-08-28 13:35:12.735 UTC [631] LOG:  recovery stopping before commit of transaction 836, time 2026-08-28 13:34:42.096745+00
  2026-08-28 13:35:12.735 UTC [631] LOG:  redo done at 0/52EBC90 system usage: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.02 s
  2026-08-28 13:35:12.735 UTC [631] LOG:  last completed transaction was at log time 2026-08-28 13:34:38.041366+00

The verification afterwards reported rows recovered : 50000 (expected 50000) and sum(amount) : 825025000 (expected 825025000). Three segments were pulled from the archive. Without them the restore would have stopped where the base backup ended, returned the 45000 rows it contains, and reported no error at all. That is the entire lesson in one comparison: the backup determines the floor, the log determines the window above it.

Notice also what the achieved recovery point actually is. The target was 2026-08-28 13:34:40.077562+00, but the log records recovery stopping before commit of transaction 836 and last completed transaction was at log time 2026-08-28 13:34:38.041366+00. Recovery lands on the last commit at or before the target, not on the target itself. A window quoted to the second is quoted more precisely than the mechanism can deliver.

What each continuous mechanism buys, and what it costs

Continuous archiving is the first step away from the sawtooth. Instead of recovery depending on the last full copy, the server hands every completed log segment to an archive command, and recovery replays whatever the archive holds. The window stops being the backup interval and becomes the time between a commit and its segment landing in the archive. The cost is storage and a retention rule that must cover the whole chain from the base backup forward: losing one segment in the middle truncates recovery at that point no matter how many later segments survive.

A quiet cluster exposes the weakness of segment-sized units. If a segment takes six hours to fill, six hours of committed work sits on the primary and nowhere else. The upstream documentation covers this directly: archive_timeout forces a segment switch after a chosen interval so that unarchived data has a bounded age, and the price is archiving segments that are only partly used, which costs archive volume in exchange for a narrower window. That trade is the shape of every option in this section.

Feeding that same series of segments to a second machine already loaded with the base backup gives what PostgreSQL’s own documentation calls a warm standby: a copy that can be brought up holding a nearly-current database. That does not narrow the window, which is still bounded by what has been shipped, but the copy is already unpacked, so recovery time falls and the price is a second machine with its own operational surface. Below that rung the unit of movement stops being the segment: an architecture that moves records as they are generated narrows the window to however far behind the transport is running, and one that will not acknowledge a commit until a second copy holds it drives the window for committed transactions to zero by construction, charging for it in commit latency and in a coupling that has to be managed deliberately whenever the second copy is unreachable.

Every step down that ladder is paid for in money, in latency, or in the number of components that must keep working for the guarantee to hold, and no rung supplies an RPO by itself: choosing one is a business decision informed by what an hour of recreated work costs. Each rung also copies a mistaken deletion as faithfully and as quickly as it copies good work, so protection against that failure comes from retention rather than from speed.

Why the archive, and not the schedule, sets the bound

A committed transaction is durable on the primary as soon as its log record is flushed to that machine’s disk. That protects against the server process dying. It protects against nothing else, because the bytes are still in exactly one failure domain — and the failure model this lesson started from is the loss of that domain.

For a transaction to be recoverable elsewhere, its record must exist somewhere the failure does not reach. Under continuous archiving the unit of movement is the segment. The server fills a segment, marks it ready, hands it to the archive command, and only counts it archived when that command reports success. Everything committed into the current, partly filled segment exists on the primary and nowhere else.

The recoverable point at any instant is therefore the end of the last segment successfully archived, and three intervals stack up to produce it: the time to fill a segment, which the workload sets and archive_timeout can cap; the time for the archive command to run; and the time for the destination to make the bytes durable. Their sum is the RPO of the scheme. The base-backup schedule governs how much log has to be replayed, which is a recovery-time term, not a recovery-point one.

The capture makes the accounting checkable. pg_stat_archiver reported archived=6 failed=0 with last=000000010000000000000005, and the archive directory held five segments plus the .backup label file the base backup wrote — six objects, matching the counter exactly. That agreement, recorded before recovery was attempted, is what made the 5000 post-backup rows a prediction rather than a hope.

There is a second consequence worth holding on to. When an archive command fails, the server does not discard the segment; it keeps it and retries. So a broken archive produces two problems from one silence: the offsite recovery point stops advancing, and the primary’s log directory keeps filling until the filesystem holding it runs out, which the documentation says ends in a PANIC shutdown.

The archive is a moving part, and silence widens the window

pg_stat_archiver describes the data path rather than the job: archived_count and failed_count for successful and failed archiving attempts, and last_archived_wal and last_failed_wal for the files each of those counts last touched. The useful alerting question is not “did last night’s backup succeed” — it did, and it will keep doing so — but “is last_archived_wal still advancing, and has failed_count moved”. If failures have been accumulating for three days, the recovery point is three days old regardless of what any document claims, and every backup dashboard in the building is green, because the nightly job is genuinely running.

This is the mechanism by which an RPO degrades with no change request, no deployment and no alarm. Nobody widened the window; it widened itself when a credential expired or a mount went read-only, and the only signal was a counter nobody was watching.

Data-loss riska data-volume copy whose log directory was not included— EXECUTION VALIDATED on PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2), from Part B of the same capture, with pg_wal emptied to model a separate-volume snapshot.

$ pg_ctl -D /work/nowal start

  waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.
>>> exit code: 1

2026-08-28 13:34:37.879 UTC [132] LOG:  creating missing WAL directory "pg_wal/archive_status"
2026-08-28 13:34:37.879 UTC [132] LOG:  creating missing WAL directory "pg_wal/summaries"
2026-08-28 13:34:37.879 UTC [132] LOG:  invalid checkpoint record
2026-08-28 13:34:37.879 UTC [132] PANIC:  could not locate a valid checkpoint record at 0/2F20158
2026-08-28 13:34:37.941 UTC [126] LOG:  startup process (PID 132) was terminated by signal 6: Aborted
2026-08-28 13:34:37.941 UTC [126] LOG:  terminating any other active server processes
2026-08-28 13:34:37.942 UTC [126] LOG:  shutting down due to startup process failure
2026-08-28 13:34:37.943 UTC [126] LOG:  database system is shut down

The copy is complete, sized correctly and entirely present. It is also unusable, and the RPO of the arrangement that produced it is not a large number — it is undefined, which is a different and worse thing to discover during an incident.

Writing a number somebody else can check

A window is only engineering if the statement carries the assumptions that produced it. The form that survives review names the system, the failure it is stated against, the mechanism that produces it, the worst case rather than the average, and the evidence.

Applied to the cluster in the capture, that reads: for the orders database, against loss of the primary’s storage, using a base backup plus continuous archiving, the recovery point is the end of the last successfully archived segment; the exercise on 2026-08-28 recovered to a target of 2026-08-28 13:34:40.077562+00 and returned 50000 rows with sum(amount)=825025000, matching the pre-incident figures recorded independently. Against an unqualified DELETE, the same architecture gives the same window only while a copy older than the mistake still exists, so the answer is bounded by retention rather than by archive latency.

Written that way, the claim is falsifiable. Someone can check whether the archive counters are still advancing, whether retention still covers the chain, and whether anybody has recovered to a point inside the window recently. A bare figure on a slide supports none of those questions, which is precisely why it survives so long.

Production discipline

Derive the window from the schedule and the failure model, and treat any figure that arrives without both as unverified. The 45000 rows in the base backup were fixed by the schedule; the other 5000 were decided entirely by what the archive held.
Write the worst case, not the incident you remember. For nightly backups at 01:00 with nothing continuous, the statement is “up to 24 hours, worst case at the moment before the next backup”, never the nineteen hours a particular failure at 20:00 happened to cost.
Monitor the archive as a data path, not as a job. Alert on the failure count moving and on the last archived segment failing to advance; the capture recorded archived=6 failed=0 before recovery, and that reading is what made the outcome predictable.
State a separate window for each failure mode. Loss of storage, accidental deletion and loss of a site are answered by different copies, and the run with pg_wal emptied produced PANIC: could not locate a valid checkpoint record at 0/2F20158 rather than a merely older recovery point.
Prove the window by recovering to a point inside it. The claim here is supported because a recovery to 2026-08-28 13:34:40.077562+00 returned 50000 rows and sum(amount)=825025000, matching an independently recorded property of the data.

Cross-course references

PostgreSQL for Production Sysadmins — Part XIII (Backup, Archiving and Point-in-Time Recovery) teaches the archiving and recovery configuration whose output this lesson borrows; here the capture is used only to derive a loss window, while that part covers how to build and operate the archive that produces it.
Observability for Production Sysadmins — Part XVIII (Alerting Rules) is where the counters named above become alerts: a failure count that must stay flat and a last-archived segment that must keep advancing. Without those rules the archive latency that bounds this lesson’s RPO is simply unmeasured.
Ceph & Distributed Storage for Production Sysadmins — Part CXX (Multi-Site Concepts) has the identical shape at storage level, where the recovery point at the remote site is whatever last arrived, so replication lag bounds the window there exactly as archive latency bounds it here.