Verifying Backup Integrity Across Shards

Eight shards means eight backup configurations, eight archive destinations and eight opportunities for one of them to have quietly stopped working. This guide builds fleet-wide verification: freshness and continuity checks that run continuously, restore drills on a rotation, and one view that answers “which shards can we actually recover?”. It operationalises the verification requirement from Backup & Restore for Partitioned Databases, part of Shard Migration & Rebalancing Operations.

Prerequisites

Step 1 — Check freshness and continuity on every shard, continuously

Two questions per shard, answered from the tool’s own metadata:

#!/usr/bin/env bash
# backup_status.sh — one line of metrics per shard, scraped every 5 minutes
for stanza in shard0 shard1 shard2 shard3 shard4 shard5 shard6 shard7; do
  json=$(pgbackrest --stanza="$stanza" info --output=json)

  last_stop=$(jq -r '.[0].backup[-1].timestamp.stop' <<<"$json")
  wal_min=$(jq  -r '.[0].archive[-1].min'            <<<"$json")
  wal_max=$(jq  -r '.[0].archive[-1].max'            <<<"$json")
  age=$(( $(date +%s) - last_stop ))

  echo "backup_age_seconds{shard=\"$stanza\"} $age"
  echo "backup_wal_present{shard=\"$stanza\",min=\"$wal_min\",max=\"$wal_max\"} 1"
done
# a backup older than 36 hours means the schedule is broken somewhere
backup_age_seconds > 129600

# archiving stopped while the database kept running — the backup is already invalid
pg_stat_archiver_last_failed_time > pg_stat_archiver_last_archived_time

Operational note: Freshness on its own is misleading. A base backup taken an hour ago is worthless if WAL archiving broke two days ago, because recovery to any point after the break is impossible.

DBA tip: Record the WAL range covered by the archive alongside the backup age. The pair — recent base backup plus continuous WAL up to now — is what “recoverable” actually means.

One view: which shards are actually recoverable right now A matrix of eight shards against three checks. Seven shards have a fresh base backup, continuous write-ahead log and a recent successful drill. Shard five has a fresh backup and a broken WAL chain, which makes point-in-time recovery impossible despite the backup looking healthy in the tool's own listing. shard base backup < 36 h WAL continuous to now drill passed < 90 days shard0shard1shard2shard3 shard4shard5shard6shard7 ← archiver failed 41 h ago Shard 5's backup listing looks perfectly healthy. Only the WAL column reveals that nothing after the break can be recovered.

Step 2 — Scan the WAL archive for gaps directly

Trusting the tool’s summary is not the same as checking the objects:

#!/usr/bin/env bash
# wal_gap_scan.sh — list archived segments and find discontinuities
STANZA=shard3
aws s3 ls "s3://backups/${STANZA}/archive/" --recursive \
  | awk '{print $4}' | grep -oE '[0-9A-F]{24}' | sort -u > /tmp/wal_present.txt

python3 - <<'PY'
segs = [l.strip() for l in open('/tmp/wal_present.txt') if l.strip()]
def n(s):  # timeline is the first 8 hex chars; the rest is the segment number
    return int(s[8:], 16)
gaps = [(a, b) for a, b in zip(segs, segs[1:]) if n(b) != n(a) + 1]
print(f"{len(segs)} segments, {len(gaps)} gap(s)")
for a, b in gaps[:10]:
    print(f"  gap after {a} → next present {b}")
PY
1284391 segments, 1 gap(s)
  gap after 0000000100000A2B000000C4 → next present 0000000100000A2B000000C9

Operational note: Run this weekly, not continuously — it lists every object in the archive, which is slow and costs money on some object stores. The continuous check is the archiver metric; this is the audit.

SRE tip: A gap found here is not repairable. The correct response is to take a fresh base backup immediately, which re-establishes a recoverable window from that moment, and to treat the period before it as lost.

Step 3 — Drill one shard on a rotation

The drill is the only check that proves recoverability end to end:

# .github/workflows/restore-drill.yml — one shard per week, rotating
name: restore-drill
on:
  schedule:
    - cron: '0 3 * * 1'
jobs:
  drill:
    runs-on: [self-hosted, drill-host]
    steps:
      - name: pick the shard whose drill is oldest
        id: pick
        run: echo "stanza=$(python3 scripts/oldest_drill.py)" >> "$GITHUB_OUTPUT"
      - name: restore to 24 hours ago
        run: |
          pgbackrest --stanza=${{ steps.pick.outputs.stanza }} \
            --type=time --target="$(date -u -d '24 hours ago' '+%Y-%m-%d %H:%M:%S+00')" \
            --target-action=promote --pg1-path=/drill/pgdata restore
          pg_ctl -D /drill/pgdata start
      - name: assert the instance is usable
        run: python3 scripts/drill_assertions.py --stanza ${{ steps.pick.outputs.stanza }}
      - name: record the result
        run: python3 scripts/record_drill.py --stanza ${{ steps.pick.outputs.stanza }} --status "$?"
# scripts/drill_assertions.py — what a drill must actually prove
def assertions(conn, expected):
    assert conn.query_one("SELECT pg_is_in_recovery()") is False,      "still in recovery"
    assert conn.query_one("SELECT count(*) FROM pg_tables") > 40,      "schema looks empty"
    parts = conn.query_one("""SELECT count(*) FROM pg_inherits
                               WHERE inhparent = 'events'::regclass""")
    assert parts >= expected["min_partitions"],                        f"only {parts} partitions"
    newest = conn.query_one("SELECT max(occurred_at) FROM events")
    assert newest > expected["target"] - timedelta(hours=1),           "data is older than the target"
    run_readonly_smoke_tests(conn)                                     # the application's own checks
Rotating drills keep every shard's age under the threshold Over thirteen weeks, one shard is drilled each week in rotation, so no shard goes more than eight weeks without a full restore. A schedule that always drills the same shard leaves seven shards untested indefinitely, and those are the ones where a configuration change went unnoticed. w1w2w3w4 w5w6w7w8 w9w10w11w12w13 shard0shard1shard2shard3 shard4shard5shard6shard7 Each shard's worst-case drill age is eight weeks, comfortably inside a ninety-day threshold — and every shard's configuration is exercised, which is where drift hides.

Operational note: Pick the shard with the oldest successful drill rather than a fixed order. It self-corrects when a drill is skipped or fails.

SRE tip: Run the drill against a target 24 hours in the past rather than “latest”. It exercises WAL replay, which the latest-only path barely touches.

Step 4 — Publish one recoverability metric per shard

Reduce all of it to a number an on-call engineer can read at a glance:

Three checks, three cadences, three different failures A metadata check runs every five minutes at negligible cost and catches a backup that stopped being taken. A weekly archive-listing scan costs a full bucket listing and catches a gap in the WAL chain. A quarterly full restore drill costs hours of machine time and is the only check that proves the backup can actually be used. CheckCadenceCostCatches backup age + archiver statusevery 5 minnegligiblethe schedule stopped WAL gap scan of the archiveweeklyone bucket listingan unrecoverable window full restore drill, rotatingweekly, one shardhours of machine timeeverything else partition archive restoreweekly, randomminutesan unreadable archive Only the third row proves recoverability; the first two exist so that the third is rarely the first to discover a problem. Rotate the drill by oldest successful run rather than by a fixed order, so a skipped week self-corrects. 
# a shard is recoverable when all three hold
backup_recoverable{shard="shard3"} =
    (backup_age_seconds{shard="shard3"} < 129600)
  * (wal_gap_count{shard="shard3"} == 0)
  * (drill_age_days{shard="shard3"} < 90)
# alert when any shard falls out of the recoverable set
min by (shard) (backup_recoverable) == 0

Operational note: Compose it from the three inputs rather than replacing them. During an incident the composite tells you there is a problem; the components tell you which one.

DBA tip: Put this metric on the same dashboard as replication lag and disk. Recoverability is a property people only look for after they need it unless it is somewhere they already look.

Verification

Test the verification itself by breaking something deliberately on a non-production shard:

# staging: point the archive command at an unwritable location
psql -c "ALTER SYSTEM SET archive_command = 'false'"
psql -c "SELECT pg_reload_conf()"
psql -c "SELECT pg_switch_wal()"
SELECT failed_count, last_failed_wal, last_failed_time FROM pg_stat_archiver;
 failed_count |     last_failed_wal      |       last_failed_time
--------------+--------------------------+-------------------------------
           14 | 0000000100000A2C00000101 | 2026-08-03 10:22:41.118+00

The alert must fire within its evaluation window, the recoverability metric for that shard must drop to zero, and restoring the correct archive_command must clear both. A verification pipeline that does not fail when the thing it watches is broken is not verifying anything.

Failure mode table

Failure mode Root cause SRE mitigation
Every check green, recovery still impossible checks watched backup freshness only, not WAL continuity compose recoverability from freshness, gap count and drill age; alert on the composite
Drills always pass, real recovery fails the drill restored to “latest” and never exercised WAL replay, or always used the same shard target a point 24 hours in the past; rotate shards by oldest drill age
Drill host unavailable when needed the drill infrastructure was only provisioned for the scheduled job keep the drill host in the standard fleet with capacity for the largest shard; the drill and the real recovery use the same path

FAQ

Is a backup tool's own check enough?

It verifies that the stored files match their recorded checksums, which catches corruption in the object store and nothing else. It does not prove that the database can start from them, that the WAL chain is unbroken to now, or that the schema restores usably. Those are only demonstrated by an actual restore, which is why a rotation of real restore drills is the check that matters.

How often should each shard be drilled?

Rotate so that every shard is fully restored at least once a quarter, which for an eight-shard fleet means roughly one drill a week. Rotation matters more than frequency: drilling the same shard weekly proves one shard works and tells you nothing about the other seven, which are usually the ones with the drifted configuration.

What should the drill actually assert?

That the instance starts, that recovery reaches the requested target, that the schema matches production’s, that row counts for a sample of partitions are within an expected range, and that the application’s read-only smoke tests pass against it. A drill that only asserts the process exited zero will pass against an empty database.

Back to Backup Restore For Partitioned Databases