What to Record in a Restore Runbook When a Point-in-Time Recovery Stops at a Missing WAL Segment

by ·Comments Off on What to Record in a Restore Runbook When a Point-in-Time Recovery Stops at a Missing WAL Segment

A missing segment is a decision, not an error to clear

Point-in-time recovery stops on a missing WAL segment because the archive cannot supply a file PostgreSQL needs to continue replay. The PostgreSQL documentation is direct about the precondition: recovery using continuous archiving requires “a continuous sequence of archived WAL files that extends back at least as far as the start time of your backup” (PostgreSQL 18, Continuous Archiving and PITR). When that sequence has a hole, the restore cannot reach the target you asked for. It can only reach a point before the hole, or start again from a different base backup.

That makes the missing segment a decision point. The runbook’s job is not to make the decision for the on-call engineer. It is to make the decision explicit, reproducible, and auditable after the fact. A runbook that says “investigate missing WAL” leaves the next reader to reconstruct intent under pressure. A runbook that records the target, the exact segment, the lookup that failed, the branch chosen, and the trade-off accepted gives the next engineer a starting position instead of a blank page.

This is written for teams of two to fifteen engineers who run their own on-call rotation and have no dedicated SRE function. The structure below is a recommendation, not a standard. It is built around what the PostgreSQL and pgBackRest documentation actually exposes, plus the failure modes the Crunchy Data write-up on missing WAL files describes.

Record the recovery intent before the failure

The same missing segment means different things depending on what the restore was trying to reach. A segment missing on the path to a named restore point taken five minutes before a bad migration is a different problem from a segment missing on the path to a timestamp three days ago. Record intent first, so the failure has context.

At minimum, capture:

  • Recovery target type and value. Whether the target is a timestamp, a named restore point, an LSN, or a transaction ID, and the exact value. If a named restore point was used, record the name and the command that created it.
  • Target action. Whether recovery is expected to pause, promote, or shut down when the target is reached. The target action determines what the cluster does after replay, and it should be recorded alongside the target itself.
  • Base backup or backup set. The backup label, the backup set identifier, and the repository it came from. pgBackRest’s user guide notes that “a restore requires the backup files and one or more WAL segments in order to work correctly” (pgBackRest User Guide). The backup set is half of that equation and should be named.
  • Who requested the restore and why. One sentence. This is the part that gets lost first and matters most in the postmortem.

Recording intent first also protects against a subtle failure: a restore that succeeds to the wrong target. If the runbook only records the missing segment, a later reader cannot tell whether the restore that eventually completed actually reached the point the requester needed.

Record the exact failure, verbatim

Paraphrased errors are not reproducible. The runbook entry should contain the literal text PostgreSQL or the backup tool emitted, plus the invocation that produced it.

Useful fields:

  • Missing segment name. The full 24-character segment name, including the timeline prefix. pgBackRest’s documentation describes the naming convention: the first eight hexadecimal digits are the timeline and the next sixteen are the logical sequence number (pgBackRest User Guide). The timeline matters because a failover or switchover increments it, and a segment that exists on one timeline may not exist on another.
  • Verbatim error text. The Crunchy Data article lists several distinct messages that point at different problems, including ERROR: requested WAL segment 00000002000000050000007C has already been removed, INFO: unable to find 0000001600000000000000C2 in the archive, and FATAL: requested timeline 23 does not contain minimum recovery point 0/C2A56FC0 on timeline 22 (Crunchy Data, How to Recover When PostgreSQL is Missing a WAL File). These are not interchangeable. “Already been removed” suggests retention or recycling on the source. “Unable to find in the archive” suggests the archive lookup itself failed. The timeline message suggests a timeline mismatch. Copy the exact line.
  • The restore_command or archive-get invocation. The exact command PostgreSQL ran to fetch the segment. With pgBackRest this is typically pgbackrest --stanza=<name> archive-get %f "%p", but the runbook should record what was actually configured, not what the template says. pgBackRest’s command reference notes that archive-get is “used by PostgreSQL to restore a backup, perform PITR, or as an alternative to streaming for keeping a replica up to date” (pgBackRest Command Reference).
  • Timestamp and host. When and where the failure was observed, in UTC.

This is the part of the runbook most likely to be skipped during an incident and most valuable afterward. A verbatim error line lets the next reader search logs, reproduce the lookup, and compare against the archive’s own records.

Record the archive lookup path and its result

A missing segment is not the same as a missing archive. The runbook should distinguish between “the segment is not in the archive” and “the archive lookup did not run correctly.” Those lead to different branches.

Record:

  • Which repository or archive location was queried. If pgBackRest is configured with multiple repositories, WAL is fetched “from the repositories in priority order (e.g. repo1, repo2, etc.)” (pgBackRest Command Reference). The runbook should name the repositories in priority order and record which one was reached before the failure.
  • What the lookup returned. Whether the archive-get command returned a file, returned nothing, or errored. If it errored, the error text belongs here, not in the previous section.
  • Whether asynchronous archive-get was in use. pgBackRest’s --archive-missing-retry option controls whether a segment previously reported missing in asynchronous mode is retried. The documentation notes that disabling it “allows PostgreSQL to more reliably recognize when the end of the WAL in the archive has been reached, which permits it to switch over to streaming from the primary” (pgBackRest Command Reference). If the team uses asynchronous archive-get, the runbook should record whether retry was enabled and whether the spool path was cleared before the restore. This is a configuration detail that changes the meaning of a “missing” result.
  • Whether the segment exists on a different timeline. If the error mentions a timeline, record which timelines were checked and what was found. A segment present on timeline 22 but requested on timeline 23 is a different problem from a segment absent everywhere.

The distinction matters because the branches differ. If the segment is genuinely absent from every repository and every timeline, retrying the lookup will not help. If the lookup failed for an operational reason, a retry may resolve it without touching the recovery target.

Record the decision branch and who owns it

This is the core of the runbook. When the segment is genuinely missing, the team has a small number of options, and each carries a trade-off that should be written down at the moment it is accepted.

The branches, as the documentation supports them:

  1. Retry the archive fetch. Appropriate when the lookup failed for an operational reason rather than because the segment is absent. The runbook should record what was retried, how many times, and what changed between attempts. If the retry succeeds, record the segment that was recovered and the time it took.
  2. Move the recovery target earlier, to a point covered by available WAL. This accepts additional data loss in exchange for completing the restore. The runbook should record the new target, the reason the earlier point was chosen, and what is known to be lost between the new target and the original one. The Crunchy Data article notes that PostgreSQL’s own hint in some recovery failures is to “choose an earlier recovery target” (Crunchy Data). That hint is a starting point, not a decision; the decision belongs to whoever owns the data.
  3. Rebuild from a newer base backup. Appropriate when the gap is large enough that moving the target earlier would lose more than the team is willing to accept, or when the base backup itself is suspect. The runbook should record which backup set was chosen, why, and what the expected recovery point is.
  4. Stop and escalate. A legitimate branch. If none of the above can be decided safely by the on-call engineer, the runbook should name who is called and what information they need. This is not a failure of the runbook; it is the runbook working.

For each branch, record:

  • The decision owner. A role, not a name, so the runbook survives turnover.
  • The trade-off accepted. One or two sentences. “Accepted loss of approximately four hours of writes between the original target and the earlier recovery point” is a record. “Chose earlier target” is not.
  • The time the decision was made. In UTC, alongside the failure timestamp, so the postmortem can reconstruct the timeline.

The trade-off is the part most likely to be forgotten and most likely to be re-litigated. Writing it down at the moment of decision is cheaper than reconstructing it afterward.

Record post-recovery verification as a checklist, not a sentence

“Verify data” is not a verification step. The runbook should list what was compared, against what, and what remains uncertain.

Useful checks, in the order they are usually run:

  • Recovery completed to the intended target. The cluster’s reported recovery target and the actual last replayed LSN or timestamp should be recorded. If the target was moved earlier, record both the original and the new target.
  • The cluster is accepting connections and is not in recovery. If the target action was to promote, confirm the promotion happened. If the target action was to pause, confirm the pause is intentional and who will resume.
  • Row counts or checksums for the tables the requester named. The requester should have named specific tables or queries before the restore started. If they did not, that is a gap to record, not to paper over.
  • Sequence values and recent inserts. A restored cluster can be internally consistent and still be missing the most recent writes. Compare the highest sequence value or the most recent timestamp in a known table against the expected recovery point.
  • What remains uncertain. If the restore reached an earlier target than requested, record what is known to be missing and what is unknown. “Writes between 14:00 and 18:00 UTC on the affected tables are not present; whether any of those writes were committed is not known from the archive alone” is a record. “Some data may be missing” is not.

The verification checklist should be written before the restore starts, not after. The requester is the best source of what “correct” means, and they are most available before the incident is resolved.

Record follow-up as a question, not a verdict

The follow-up section of the runbook should route to the blameless postmortem, not to a conclusion. The question is why the gap existed, and the answer is usually one of a small number of causes that the documentation describes.

Candidate questions to record:

  • Did archiving fail repeatedly before the gap? PostgreSQL’s WAL configuration documentation notes that “if WAL archiving cannot keep up with the pace that WAL is generated, or if archive_command or archive_library fails repeatedly, old WAL files will accumulate in pg_wal until the situation is resolved” (PostgreSQL 18, WAL Configuration). Accumulation is visible; a gap that appears later may have started as an archiving failure that was not monitored.
  • Did retention expire the segment before the restore needed it? The Crunchy Data article describes a replica that has been disconnected long enough for archived WAL to pass its retention period attempting to restore files that no longer exist (Crunchy Data). The same mechanism can affect a restore from a base backup if retention is shorter than the team assumed.
  • Did a timeline change move the segment? Failovers and switchovers increment the timeline. A segment that exists on the old timeline may not be requested on the new one, and vice versa. The Crunchy Data article notes that time synchronization across cluster members is “critically important for correct cluster operation” (Crunchy Data), which is relevant because timeline confusion often follows clock or coordination problems.
  • Was the archive command returning success when it should have failed? The PostgreSQL documentation is explicit: “It is important that the archive command return zero exit status if and only if it succeeds” (PostgreSQL 18, Continuous Archiving and PITR). An archive command that returns zero on partial writes can produce a gap that looks like a retention problem.

These are questions for the postmortem, not diagnoses. The runbook should record which questions were asked and what evidence was collected, not which answer was assumed.

What not to put in the runbook

Two things are worth leaving out.

First, do not record a specific recovery time objective, recovery point objective, or acceptable data-loss window as if it were a property of the system. Those are decisions the team makes, and they belong in a separate decision record that the runbook references. A runbook that hard-codes “acceptable loss is one hour” will be wrong the first time a different service uses it.

Second, do not record pg_resetwal as a recovery step. The Crunchy Data article advises against running it casually and notes that it may lose data and cause further damage (Crunchy Data). If the team has a documented, reviewed procedure for using it as a last resort, that procedure belongs in its own document with its own approvals, not in the PITR runbook where it might be reached for under pressure.

Similarly, the runbook should record that an OS-level copy of the data directory is made before any repair attempt. The Crunchy Data article recommends this explicitly: “make a copy of the DB’s data directory using your favorite OS-level snapshot utility” before attempting repairs (Crunchy Data). That step belongs in the runbook as a precondition, not as an optional note.

A labeled template

The following is an illustrative template, not a record of a real incident. The values are hypothetical and are marked as such. Teams should replace the bracketed fields with their own conventions.

## Restore record: [hypothetical example]

### Intent
- Target type: timestamp
- Target value: 2026-03-14 18:00:00 UTC
- Target action: promote
- Base backup: [backup label], repository [repo1]
- Requester: [role], reason: [one sentence]

### Failure
- Missing segment: 00000001000000A900000065
- Timeline: 1
- Verbatim error: [exact line from PostgreSQL log]
- restore_command: pgbackrest --stanza=[name] archive-get %f "%p"
- Observed at: 2026-03-14 18:42 UTC on [host]

### Archive lookup
- Repositories queried, in priority order: repo1, repo2
- Result from repo1: [found / not found / error text]
- Result from repo2: [found / not found / error text]
- Asynchronous archive-get: [enabled / disabled]
- archive-missing-retry: [enabled / disabled]
- Spool path cleared before restore: [yes / no]
- Timelines checked: [list]

### Decision
- Branch chosen: move target earlier
- New target: 2026-03-14 13:55:00 UTC
- Decision owner: [role]
- Trade-off accepted: writes between 13:55 and 18:00 UTC on affected tables are not present; whether any were committed is not known from the archive alone
- Decided at: 2026-03-14 19:10 UTC

### Verification
- Recovery reached: 2026-03-14 13:54:58 UTC (reported)
- Cluster accepting connections: [yes / no]
- Row counts compared: [table, expected, actual]
- Highest sequence value: [value]
- Remains uncertain: [list]

### Follow-up questions for postmortem
- Did archiving fail before the gap? Evidence: [link to pg_stat_archiver output]
- Did retention expire the segment? Evidence: [retention config, backup set age]
- Did a timeline change move the segment? Evidence: [timeline history]
- Did the archive command return success incorrectly? Evidence: [archive command, logs]

The template is deliberately longer than most teams will fill in during an incident. The point is not to complete every field under pressure; it is to have a place to put the fields that matter, so that the next reader can tell what was known, what was decided, and what was left uncertain.

Why this belongs in the runbook, not in the postmortem

The postmortem is written after the incident, by people who may not have been on call. The runbook is written before, by the team that will be on call. The runbook’s job is to preserve the decision context at the moment of decision, when the trade-off is still visible. The postmortem’s job is to ask why the gap existed and what would prevent it next time.

For a lean team, the runbook is also the onboarding document. A new engineer reading the restore record learns what the team considers a complete record: target, failure, lookup, decision, verification, follow-up. That is more useful than a paragraph of prose about the importance of backups.

If the team does not yet have a recovery checklist, the companion piece on writing the recovery checklist before you need it covers the broader structure. This article is the narrow case: what to record when the restore stops on a missing segment, and why the record is the deliverable.

FAQ

Is a missing WAL segment always a data-loss event?

No. It is a failure to reach the requested recovery target. The team may be able to retry the archive fetch, move the target earlier, or rebuild from a newer base backup. Whether data was lost depends on which branch is chosen and what the requester needed. The runbook should record the branch and the trade-off, not assume the outcome.

Should the runbook include the exact pgBackRest restore command?

Yes, if the team uses pgBackRest, because the command is part of the reproducible record. The pgBackRest command reference documents the restore options, including target and target-action, and the user guide describes the restore process. Recording the exact invocation lets the next reader reproduce the attempt or see what was different.

What if the team uses restic instead of pgBackRest for base backups?

restic can store base backup files, but it does not provide a WAL archive-get interface for PostgreSQL. The WAL archive still needs a mechanism PostgreSQL can call through restore_command or archive_library. The runbook should record which tool handles which part: restic for base backup storage, and the archive mechanism for WAL. The restic documentation index describes repository and restore operations but does not describe a PITR archive interface, so the runbook should not assume one.

How long should the restore record be kept?

At least as long as the postmortem and any follow-up actions are open, and ideally as long as the backup set it refers to is retained. A restore record that outlives its backup set is still useful for the postmortem, but it cannot be used to reproduce the restore.

Does this apply to replicas as well as primary restores?

Yes. The Crunchy Data article describes the replica case in detail: a replica that has fallen behind may request a segment the primary has already recycled, and the same decision branches apply. The runbook should record whether the restore was for a primary, a replica, or a test environment, because the acceptable trade-off differs.