Skip to main content
RunBook Academy

← All break/fix scenarios in Backup & DR

intermediatebdr-pitr~40 min

The requested recovery point is earlier than the oldest base backup

Reported symptoms

  • ●A pricing migration deployed at 02:38 on 2026-08-19 rewrote the discount column on historical orders in rbdr-pg-01, and nobody notices until the finance reconciliation on 2026-08-28, nine days later
  • ●The recovery request is narrow and entirely reasonable: stand up a copy of the database as it was at 2026-08-19 02:30:00+00, ten minutes before the deployment, so the original values can be read out and compared
  • ●Every backup indicator is green and has been for eleven weeks: the nightly base backup succeeds, and pg_stat_archiver reports failed=0 for the whole period
  • ●The WAL archive is large and healthy, which is why the first assumption on the bridge call is that a point-in-time recovery here is a matter of choosing the timestamp
  • ●The oldest directory under /srv/rbdr-base is dated 2026-08-22 01:00, three days after the requested target
  • ●A recovery attempt from that oldest base backup reads the target back correctly into its log, begins replay, and then exits during startup with a FATAL that names the consistency point rather than a file it could not fetch
  • ●The second theory on the call is that the archive lost segments during the same window, because a startup that dies mid-recovery is the shape everyone associates with a missing WAL file

Evidence

  • · A dated listing of /srv/rbdr-base returns exactly seven base backup directories, 2026-08-22 through 2026-08-28, and nothing earlier
  • · The archive holds 4719 segments running from 0000000100000048000000C1 to 000000010000005B0000002F with no missing sequence number between them, every name on timeline 00000001, and no timeline history file present
  • · The earliest backup label in the archive is 0000000100000048000000C1.00000028.backup, written at 01:00:09 on 2026-08-22, which names the same segment the archive itself begins at
  • · The recovery attempt logs the target back as `starting point-in-time recovery to 2026-08-19 02:30:00+00`, then aborts with `FATAL: requested recovery stop point is before consistent recovery point`, having never logged a consistent recovery state and having named no file it failed to restore
  • · A captured PostgreSQL 18.6 point-in-time recovery shows where replay begins and in which direction it moves: `starting backup recovery with redo LSN 0/3000028`, then `redo starts at 0/3000028`, both values taken from the base backup and not from the archive
  • · The prune job sets KEEP_BASE_BACKUPS=7 and expires every archive segment older than the earliest base backup it decided to keep
  • · The change that set that value is titled as a 1.9 TiB saving on rbdr-backup-01 under the Q1 capacity review, records no recovery requirement, and carries no approval from a data owner
  • · The incident register lists fourteen logical data-error incidents over twenty-four months, nine of them found more than seven days after they were introduced, with a median detection latency of eleven days and a 95th percentile of thirty-one
Diagnosis and resolutionclick to reveal

Root cause

Two defects. The first one is why the recovery cannot be performed; the second one is why nobody knew that until the day it mattered. **The immediate cause.** A point-in-time recovery is a base backup replayed forward through archived WAL. Replay starts at the redo point recorded inside the base backup and moves in one direction only, so the earliest instant any recovery can reach is the instant the earliest surviving base backup became consistent. On 2026-08-28 that instant is 2026-08-22 01:00. The requested target is 2026-08-19 02:30, which is 2 days 22 hours and 30 minutes below the floor. No quantity of archived WAL changes that, because WAL is a forward journal and there is no earlier starting image to apply it to. The floor is 2026-08-22 because a prune job keeps seven base backups and expires archive segments older than the oldest one it kept. Seven days of base backups is a shorter window than the nine days this error went unnoticed. **The control that should have caught it.** The number seven was produced by a capacity review. The change record states a saving of 1.9 TiB and names the storage budget it serves; it states no recovery requirement, and no data owner approved it. Retention was reviewed as a cost line, and a cost review has no input that could have raised the objection. The number that should have set retention was already measured and sitting in the incident register: nine of fourteen logical data errors in two years were found more than seven days after they were introduced, median eleven days. Retention shorter than detection latency is a recovery window that exists only for the mistakes you happen to notice quickly. The alternative hypothesis on the call was a damaged archive, which the evidence eliminates. The segment sequence has no missing number between its first and last name, the archiver refused nothing over the period, and every name sits on one timeline. A lost-segment fault leaves a hole in the middle of a sequence; this archive has a clean edge, and that edge is the same segment named by the earliest backup label. The recovery log says the same thing from the other side: it names no file it could not restore, and it never reports a consistent recovery state. The archive is not damaged. It begins after the target because it was trimmed to match a base backup set that also begins after the target.

Remediation

Stop the floor from moving before anything else. The prune job runs nightly, and each run removes another base backup and another day of archive, so an investigation that takes two days costs two more days of reachable history. ```bash sudo systemctl disable --now rbdr-base-prune.timer sudo systemctl status rbdr-base-prune.timer ``` Then establish the floor as a fact rather than an assumption. Record the oldest base backup directory, the earliest backup label in the archive, and the first segment name, and write down the instant they agree on. That instant is the earliest recovery point the estate can produce, and it is the number to give the business. Say it plainly and early: the 2026-08-19 02:30 recovery point does not exist here, and no configuration change creates one. Search for it elsewhere before the call ends, because an earlier image somewhere else is the only thing that changes the answer. Candidates are a monthly or quarterly base backup held under a different retention, a logical dump taken for a migration or a developer refresh, a filesystem or volume snapshot on the database host, a replica that was detached before the migration, and any downstream system that carries the affected values with its own history. When no earlier image is found, change the question. A recovery to the floor at 2026-08-22 01:00 still holds the corrupted values, so it does not restore the data, but it does give an isolated copy against which the migration's effect can be characterised, and it establishes the population of rows the deployment touched. If the migration is deterministic and its inputs survive, reconstruction by recomputation is a repair path that a recovery cannot supply. Only then reset retention, from the detection-latency figure rather than from the storage budget, and rerun the prune with the new value.

Verification

The prune timer is disabled and its next scheduled run is gone from the timer listing, checked rather than assumed, because a disabled unit with an active companion still fires. The stated floor is reproduced by a recovery rather than read off a listing. A recovery to a target a few minutes after 2026-08-22 01:00 reaches `consistent recovery state reached`, then `archive recovery complete`, and the server accepts connections. Until a recovery has actually stopped at that target, the floor is a claim about a directory listing. The archive continuity number is recomputed after the retention change and compared against the new base backup set: the first segment in the archive is the segment named by the earliest backup label, and the count between first and last equals the expected count with zero gaps. Retention is verified against the number it is now derived from. The oldest base backup age is emitted as a metric, and the alert threshold is the 95th percentile of detection latency from the incident register, not a round number. The finding is closed only when a written statement of the earliest recoverable point, the requested point, and the distance between them has been given to the data owner and acknowledged.

Prevention

**Derive base backup retention from detection latency, not from capacity.** The question retention answers is "how long can an error hide here before we look for it", and that number is measurable from the incident register every organisation already keeps. Shortening a window to reclaim capacity is the move this scenario is made of; the saving was real and the recovery point is gone. **Give the retention figure a data owner and a recorded derivation.** A change that alters a recovery window needs the same class of approval as a change to the recovery objectives, and its description must state the detection-latency figure it is derived from. A change reviewed as a storage saving has no reviewer who is looking for the objection. **Publish the earliest recoverable point as a running number.** One line per protected database: the instant its oldest base backup became consistent. That is the number the recovery request will be measured against, and it is not visible in job success, archiver counters, or repository size. **Keep a long-retention tier that the nightly prune cannot reach.** A monthly base backup held for a year costs a fraction of the nightly set and moves the floor from weeks to months for exactly the class of error that takes months to surface. **Rehearse a recovery to a deliberately old target.** A restore test that always chooses yesterday exercises the newest base backup and proves nothing about the floor. Choose a target close to the oldest recovery point instead, at least quarterly, and record whether it was reachable. **Alert on the floor, not only on the ceiling.** A rule that fires when the oldest recovery point rises above the detection-latency threshold turns this incident into a ticket weeks before anybody needs the data.

Reported symptoms

At 02:38 on 2026-08-19 a release shipped a pricing migration that rewrote the discount column on historical orders in rbdr-pg-01. Nothing failed. The deployment was green and the numbers were wrong.

The finance reconciliation finds it on 2026-08-28, nine days later. The request is narrow: a copy of the database as it stood at 2026-08-19 02:30:00+00, ten minutes before the deployment.

Everything says this is easy. The nightly base backup has succeeded for eleven weeks, pg_stat_archiver reports failed=0, the archive is large, and the first assumption on the call is that this is a matter of typing the timestamp.

Then somebody lists the base backups. The oldest is dated 2026-08-22. An attempt from it reads the target back into the log, begins replay and then dies during startup — and a startup that dies mid-recovery is the shape everyone associates with a missing WAL file, so the second theory is a damaged archive.

Evidence provided

Read-only / Safeevery base backup the estate holds
$ ls -1dt --time-style=long-iso /srv/rbdr-base/*/
2026-08-28 01:00  /srv/rbdr-base/2026-08-28/
2026-08-27 01:00  /srv/rbdr-base/2026-08-27/
2026-08-26 01:00  /srv/rbdr-base/2026-08-26/
2026-08-25 01:00  /srv/rbdr-base/2026-08-25/
2026-08-24 01:00  /srv/rbdr-base/2026-08-24/
2026-08-23 01:00  /srv/rbdr-base/2026-08-23/
2026-08-22 01:00  /srv/rbdr-base/2026-08-22/

Illustrative output

Read-only / Safethe extent and the continuity of the archive
$ /usr/local/bin/rbdr-archive-continuity /srv/rbdr-wal-archive
first segment       : 0000000100000048000000C1   2026-08-22 01:00:09
last  segment       : 000000010000005B0000002F   2026-08-28 09:12:41
segments present    : 4719
segments expected   : 4719
gaps                : 0
timelines observed  : 00000001
timeline history    : none present
earliest label file : 0000000100000048000000C1.00000028.backup   2026-08-22 01:00:09

Illustrative output

Two facts there do the work: the sequence has no hole, and the archive’s first segment is the one named by the earliest backup label.

Service impact possiblethe recovery attempt from the oldest base backup
$ pg_ctl -D /srv/rbdr-recover -l /srv/rbdr-recover/startup.log start
2026-08-28 10:22:41.118 UTC [4417] LOG:  starting backup recovery with redo LSN 48/C1000028, checkpoint LSN 48/C1000098, on timeline ID 1
2026-08-28 10:22:41.124 UTC [4417] LOG:  restored log file "0000000100000048000000C1" from archive
2026-08-28 10:22:41.126 UTC [4417] LOG:  starting point-in-time recovery to 2026-08-19 02:30:00+00
2026-08-28 10:22:41.126 UTC [4417] LOG:  redo starts at 48/C1000028
2026-08-28 10:22:41.394 UTC [4417] FATAL:  requested recovery stop point is before consistent recovery point
2026-08-28 10:22:41.396 UTC [4411] LOG:  startup process (PID 4417) exited with exit code 1
2026-08-28 10:22:41.396 UTC [4411] LOG:  aborting startup due to startup process failure
2026-08-28 10:22:41.397 UTC [4411] LOG:  database system is shut down

Illustrative output

Here is a recovery that did work, measured, for comparison.

Service impact possiblewhere replay starts, and which way it goes
$ pg_ctl -D /work/base start
  2026-08-28 13:35:12.701 UTC [631] LOG:  starting backup recovery with redo LSN 0/3000028, checkpoint LSN 0/3000080, on timeline ID 1
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
Read-only / Safethe retention change, and the number nobody consulted
$ git show 3ac91e7 -- backup/rbdr-base-prune.env
Date:   Mon Feb 9 09:14:52 2026 +0000

  backup: reduce rbdr-base retention 30 -> 7 (Q1 capacity review, 1.9 TiB on rbdr-backup-01)

--- a/backup/rbdr-base-prune.env
+++ b/backup/rbdr-base-prune.env
@@ -2,4 +2,4 @@
-KEEP_BASE_BACKUPS=30
+KEEP_BASE_BACKUPS=7

--- logical data-error incidents, 24 months ---
incidents                          14
detected within 7 days              5
detected after 7 days               9
median days to detection           11
95th percentile                    31

Illustrative output

Work the evidence before reading on

  1. The archive has no gap, and its first segment is the one named by the earliest backup label. What does that coincidence say about why it begins where it does?
  2. In the measured recovery, which artefact supplied the LSN that redo started from — the archive, or the base backup?
  3. The retention change is justified by 1.9 TiB. What number would have had to appear in it for a reviewer to be able to object?
  4. Nine days is not unusual here. Where is the figure that says so, and who saw it when retention was set?

Root cause

The earliest reachable instant is a property of the oldest base backup

A point-in-time recovery is a base backup replayed forward through archived WAL. The measured log shows both halves in one line: redo starts at 0/3000028, a value taken from the base backup, after which segments were fetched in ascending order.

The earliest reachable instant is therefore the one at which the oldest surviving base backup became consistent: 2026-08-22 01:00. The target is 2026-08-19 02:30, 2 days 22 hours 30 minutes below that floor, with no earlier image for a forward journal to be applied to.

The floor sits there because a prune job keeps seven base backups and expires archive segments older than the oldest one kept. Seven days is shorter than the nine this error spent unnoticed.

The retention number came from the storage budget

Seven was produced by a capacity review. The change states a saving of 1.9 TiB and the budget it serves, no recovery requirement, and no approval from a data owner.

The number that should have set retention was already measured. Nine of fourteen logical data errors in twenty-four months were found more than seven days after introduction, median eleven. Retention shorter than detection latency is a window that exists only for the mistakes somebody notices quickly. The register said so before the change was raised; a storage review never reads it.

Resolution

Stop the floor from moving. The prune runs nightly, and each run takes another base backup and another day of archive with it.

sudo systemctl disable --now rbdr-base-prune.timer
sudo systemctl list-timers --all rbdr-base-prune.timer

Establish the floor as a measurement: the oldest base backup directory, the earliest label file and the first segment name, all three agreeing on one instant. That instant is what the business is told.

Say it early and plainly: the 2026-08-19 02:30 recovery point does not exist here, and no setting creates one. Then hunt an earlier image elsewhere, because nothing else changes the answer — a monthly backup on a different retention, a dump taken for a migration, a volume snapshot on the host.

If nothing earlier exists, change the question. A recovery to the floor still holds the corrupted values, so it restores nothing — but it gives an isolated copy from which the migration’s effect can be characterised and the affected rows identified. Where the migration is deterministic and its inputs survive, recomputation repairs what no recovery could.

Verification

The prune timer is disabled and absent from the timer listing, checked rather than assumed.

The floor is reproduced by a recovery, not read off a listing. A recovery to a target a few minutes after 2026-08-22 01:00 reaches consistent recovery state reached, then archive recovery complete, and accepts connections, with recovery_target_action set explicitly rather than left at its boot value of pause. Until a recovery has stopped there, the floor is a claim about filenames.

Archive continuity is recomputed after the retention change: first segment equal to the segment named by the earliest label file, zero gaps to the last.

The age of the oldest base backup is emitted as a metric whose threshold is the 95th percentile of detection latency from the register.

The finding closes when the floor, the requested point and the distance between them have reached the data owner in writing.

Prevention

Derive base backup retention from detection latency. Retention answers how long an error can hide before somebody looks, and the incident register holds that number. Shortening a recovery window to reclaim capacity is the move this scenario is made of: the saving was real, and the recovery point is gone.

Give the figure a data owner and a recorded derivation. A change that alters a recovery window is a change to a recovery objective and needs the same approval. Reviewed as a storage saving, it has no reviewer whose job it is to object.

Publish the earliest recoverable point, and alert on it — one line per protected database, firing when it rises past the detection-latency figure. That number is invisible in job success, archiver counters and repository size.

Keep a long-retention tier the nightly prune cannot reach. A monthly base backup held for a year costs a fraction of the nightly set and moves the floor from weeks to months, the range this class of error occupies.

Rehearse a recovery to a deliberately old target. A restore test that always picks yesterday exercises the newest base backup and says nothing about the floor. Choose a target near the oldest recovery point, quarterly.