What is it? #
Goal: build a place where restores happen routinely rather than only in emergencies, and where each restore is automatically checked.
Most teams restore a backup for the first time during an incident. That is the worst possible moment to discover the dump is incomplete, that roles are missing, or that a full restore takes four hours when everyone assumed one.
A restore environment fixes this by making restores ordinary. It proves the backups work, measures how long recovery actually takes, and produces a realistic dataset for testing — which is genuinely useful beyond backup verification.
It also raises a question you must answer before copying production data anywhere: what happens to the personal data in it? Anonymisation is part of this project, not an afterthought.
Think of it like this #
A practice kitchen that uses the same recipes and equipment as the real one.
You can try things, make mistakes and time yourself, without any of it mattering. And when service is busy and something goes wrong, the actions are already familiar.
The value is not the kitchen. It is having cooked the dish before.
Simple example #
An isolated PostgreSQL instance — a container, a spare VPS, or a second cluster on a different port — that nightly restores the latest production backup, runs a series of checks, records how long it took, and reports.
If anything fails, that is discovered on an ordinary morning rather than during an outage.
Code #
TEXT
---------- ARCHITECTURE ----------
production ──▶ nightly backup ──▶ object storage
│
▼
RESTORE ENVIRONMENT (isolated)
│
┌─────────────────┼─────────────────┐
▼ ▼ ▼
restore + time run checks anonymise -> dev copy
│ │
└────────▶ report / alert on failure
ISOLATION IS MANDATORY: this must never be able to reach production.
Different host or container, different credentials, read-only access
to the backup storage.
BASH
# ---------- PART 1: an isolated instance with Docker ----------
docker run -d \
--name pg_restore_env \
-e POSTGRES_PASSWORD=restore_only_local \
-p 5433:5432 \ # NOT 5432 — cannot clash with prod
-v pg_restore_data:/var/lib/postgresql/data \
postgres:16 # MUST match the production major version
# Match the major version, or pg_restore may refuse the dump.
docker exec pg_restore_env psql -U postgres -c "SELECT version();"
BASH
# Or a second cluster on the same machine (Debian/Ubuntu):
sudo pg_createcluster 16 restore --port 5433
sudo pg_ctlcluster 16 restore start
sudo pg_lsclusters
BASH
#!/usr/bin/env bash
# ---------- PART 2: /usr/local/bin/restore_verify.sh ----------
set -euo pipefail
RESTORE_HOST="${RESTORE_HOST:-localhost}"
RESTORE_PORT="${RESTORE_PORT:-5433}"
BACKUP_DIR="${BACKUP_DIR:-/var/backups/postgresql}"
TARGET_DB="verify_$(date +%Y%m%d_%H%M%S)"
REPORT="/var/log/restore_verify_$(date +%F).log"
PSQL="psql -h <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>R</mi><mi>E</mi><mi>S</mi><mi>T</mi><mi>O</mi><mi>R</mi><msub><mi>E</mi><mi>H</mi></msub><mi>O</mi><mi>S</mi><mi>T</mi><mo>−</mo><mi>p</mi></mrow><annotation encoding="application/x-tex">RESTORE_HOST -p</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8333em;vertical-align:-0.15em;"></span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="mord mathnormal" style="margin-right:0.0278em;">O</span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3283em;"><span style="top:-2.55em;margin-left:-0.0576em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight" style="margin-right:0.0813em;">H</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal" style="margin-right:0.0278em;">O</span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="mspace" style="margin-right:0.2222em;"></span><span class="mbin">−</span><span class="mspace" style="margin-right:0.2222em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:0.625em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">p</span></span></span></span>RESTORE_PORT -U postgres"
log() { echo "[<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mo stretchy="false">(</mo><mi>d</mi><mi>a</mi><mi>t</mi><msup><mi>e</mi><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo>+</mo></mrow><annotation encoding="application/x-tex">(date '+%F %T')]</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:1.0019em;vertical-align:-0.25em;"></span><span class="mopen">(</span><span class="mord mathnormal">d</span><span class="mord mathnormal">a</span><span class="mord mathnormal">t</span><span class="mord"><span class="mord mathnormal">e</span><span class="msupsub"><span class="vlist-t"><span class="vlist-r"><span class="vlist" style="height:0.7519em;"><span style="top:-3.063em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mtight"><span class="mord mtight">′</span></span></span></span></span></span></span></span></span><span class="mord">+</span></span></span></span>*" | tee -a "$REPORT"; }
fail() { log "FAILED: $1"; exit 1; }
cleanup() {
<span class="katex-error" title="ParseError: KaTeX parse error: Expected group as argument to '\"' at end of input: …SE IF EXISTS \"" style="color:#cc0000">PSQL -c "DROP DATABASE IF EXISTS \"</span>TARGET_DB\";" >/dev/null 2>&1 || true
}
trap cleanup EXIT
# --- pick the newest backup ---
DUMP=<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mo stretchy="false">(</mo><mi>l</mi><mi>s</mi><mo>−</mo><mi>t</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">(ls -t "</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mopen">(</span><span class="mord mathnormal" style="margin-right:0.0197em;">l</span><span class="mord mathnormal">s</span><span class="mspace" style="margin-right:0.2222em;"></span><span class="mbin">−</span><span class="mspace" style="margin-right:0.2222em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:0.6944em;"></span><span class="mord mathnormal">t</span><span class="mord">"</span></span></span></span>BACKUP_DIR"/shop_*.dump 2>/dev/null | head -1) \
|| fail "no backup files found"
GLOBALS=<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mo stretchy="false">(</mo><mi>l</mi><mi>s</mi><mo>−</mo><mi>t</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">(ls -t "</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mopen">(</span><span class="mord mathnormal" style="margin-right:0.0197em;">l</span><span class="mord mathnormal">s</span><span class="mspace" style="margin-right:0.2222em;"></span><span class="mbin">−</span><span class="mspace" style="margin-right:0.2222em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:0.6944em;"></span><span class="mord mathnormal">t</span><span class="mord">"</span></span></span></span>BACKUP_DIR"/globals_*.sql 2>/dev/null | head -1) || true
log "=== verifying $DUMP ==="
log "backup file age: <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mo stretchy="false">(</mo><mo stretchy="false">(</mo><mo stretchy="false">(</mo></mrow><annotation encoding="application/x-tex">(( (</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mopen">(((</span></span></span></span>(date +%s) - <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mo stretchy="false">(</mo><mi>s</mi><mi>t</mi><mi>a</mi><mi>t</mi><mo>−</mo><mi>c</mi></mrow><annotation encoding="application/x-tex">(stat -c%Y "</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mopen">(</span><span class="mord mathnormal">s</span><span class="mord mathnormal">t</span><span class="mord mathnormal">a</span><span class="mord mathnormal">t</span><span class="mspace" style="margin-right:0.2222em;"></span><span class="mbin">−</span><span class="mspace" style="margin-right:0.2222em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:0.4306em;"></span><span class="mord mathnormal">c</span></span></span></span>DUMP")) / 3600 )) hours"
# --- structural check before spending time restoring ---
pg_restore --list "$DUMP" >/dev/null || fail "dump is unreadable"
# --- globals first: roles must exist before owned objects ---
if [[ -n "${GLOBALS:-}" ]]; then
log "restoring globals"
<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>P</mi><mi>S</mi><mi>Q</mi><mi>L</mi><mo>−</mo><mi>f</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">PSQL -f "</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8778em;vertical-align:-0.1944em;"></span><span class="mord mathnormal" style="margin-right:0.1389em;">P</span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord mathnormal">Q</span><span class="mord mathnormal">L</span><span class="mspace" style="margin-right:0.2222em;"></span><span class="mbin">−</span><span class="mspace" style="margin-right:0.2222em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal" style="margin-right:0.1076em;">f</span><span class="mord">"</span></span></span></span>GLOBALS" >/dev/null 2>&1 || log "WARN: globals had errors (often just 'role exists')"
fi
# --- the restore, timed ---
log "restoring into $TARGET_DB"
<span class="katex-error" title="ParseError: KaTeX parse error: Expected group as argument to '\"' at end of input: …ATE DATABASE \"" style="color:#cc0000">PSQL -c "CREATE DATABASE \"</span>TARGET_DB\";"
START=$(date +%s)
pg_restore -h "<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>R</mi><mi>E</mi><mi>S</mi><mi>T</mi><mi>O</mi><mi>R</mi><msub><mi>E</mi><mi>H</mi></msub><mi>O</mi><mi>S</mi><mi>T</mi><mi mathvariant="normal">"</mi><mo>−</mo><mi>p</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">RESTORE_HOST" -p "</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8444em;vertical-align:-0.15em;"></span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="mord mathnormal" style="margin-right:0.0278em;">O</span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.0576em;">E</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3283em;"><span style="top:-2.55em;margin-left:-0.0576em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight" style="margin-right:0.0813em;">H</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal" style="margin-right:0.0278em;">O</span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="mord">"</span><span class="mspace" style="margin-right:0.2222em;"></span><span class="mbin">−</span><span class="mspace" style="margin-right:0.2222em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">p</span><span class="mord">"</span></span></span></span>RESTORE_PORT" -U postgres \
-d "<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>T</mi><mi>A</mi><mi>R</mi><mi>G</mi><mi>E</mi><msub><mi>T</mi><mi>D</mi></msub><mi>B</mi><mi mathvariant="normal">"</mi><mo>−</mo><mi>j</mi><mn>4</mn><mo>−</mo><mo>−</mo><mi>e</mi><mi>x</mi><mi>i</mi><mi>t</mi><mo>−</mo><mi>o</mi><mi>n</mi><mo>−</mo><mi>e</mi><mi>r</mi><mi>r</mi><mi>o</mi><mi>r</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">TARGET_DB" -j 4 --exit-on-error "</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8444em;vertical-align:-0.15em;"></span><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="mord mathnormal">A</span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord mathnormal" style="margin-right:0.0576em;">GE</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3283em;"><span style="top:-2.55em;margin-left:-0.1389em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight" style="margin-right:0.0278em;">D</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal" style="margin-right:0.0502em;">B</span><span class="mord">"</span><span class="mspace" style="margin-right:0.2222em;"></span><span class="mbin">−</span><span class="mspace" style="margin-right:0.2222em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:0.854em;vertical-align:-0.1944em;"></span><span class="mord mathnormal" style="margin-right:0.0572em;">j</span><span class="mord">4</span><span class="mspace" style="margin-right:0.2222em;"></span><span class="mbin">−</span><span class="mspace" style="margin-right:0.2222em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:0.7429em;vertical-align:-0.0833em;"></span><span class="mord">−</span><span class="mord mathnormal">e</span><span class="mord mathnormal">x</span><span class="mord mathnormal">i</span><span class="mord mathnormal">t</span><span class="mspace" style="margin-right:0.2222em;"></span><span class="mbin">−</span><span class="mspace" style="margin-right:0.2222em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:0.6667em;vertical-align:-0.0833em;"></span><span class="mord mathnormal">o</span><span class="mord mathnormal">n</span><span class="mspace" style="margin-right:0.2222em;"></span><span class="mbin">−</span><span class="mspace" style="margin-right:0.2222em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:0.6944em;"></span><span class="mord mathnormal" style="margin-right:0.0278em;">er</span><span class="mord mathnormal" style="margin-right:0.0278em;">r</span><span class="mord mathnormal" style="margin-right:0.0278em;">or</span><span class="mord">"</span></span></span></span>DUMP" \
|| fail "pg_restore reported an error"
DURATION=<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mo stretchy="false">(</mo><mo stretchy="false">(</mo></mrow><annotation encoding="application/x-tex">((</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mopen">((</span></span></span></span>(date +%s) - START ))
log "restore completed in ${DURATION}s"
# THIS NUMBER IS YOUR REAL RTO. Track it — it grows with your data.
# --- statistics, or every check below is misleadingly slow ---
<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>P</mi><mi>S</mi><mi>Q</mi><mi>L</mi><mo>−</mo><mi>d</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">PSQL -d "</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8778em;vertical-align:-0.1944em;"></span><span class="mord mathnormal" style="margin-right:0.1389em;">P</span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord mathnormal">Q</span><span class="mord mathnormal">L</span><span class="mspace" style="margin-right:0.2222em;"></span><span class="mbin">−</span><span class="mspace" style="margin-right:0.2222em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:0.6944em;"></span><span class="mord mathnormal">d</span><span class="mord">"</span></span></span></span>TARGET_DB" -c "ANALYZE;" >/dev/null
BASH
# ---------- PART 3: the checks (same script, continued) ----------
check_sql() { # description, query, expected-condition
local desc="<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mn>1</mn><mi mathvariant="normal">"</mi><mi>q</mi><mi>u</mi><mi>e</mi><mi>r</mi><mi>y</mi><mo>=</mo><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">1" query="</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8889em;vertical-align:-0.1944em;"></span><span class="mord">1"</span><span class="mord mathnormal" style="margin-right:0.0359em;">q</span><span class="mord mathnormal">u</span><span class="mord mathnormal" style="margin-right:0.0278em;">er</span><span class="mord mathnormal" style="margin-right:0.0359em;">y</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:0.6944em;"></span><span class="mord">"</span></span></span></span>2"
local result
result=<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mo stretchy="false">(</mo></mrow><annotation encoding="application/x-tex">(</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mopen">(</span></span></span></span>PSQL -d "<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>T</mi><mi>A</mi><mi>R</mi><mi>G</mi><mi>E</mi><msub><mi>T</mi><mi>D</mi></msub><mi>B</mi><mi mathvariant="normal">"</mi><mo>−</mo><mi>t</mi><mi>A</mi><mi>c</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">TARGET_DB" -tAc "</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8444em;vertical-align:-0.15em;"></span><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="mord mathnormal">A</span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord mathnormal" style="margin-right:0.0576em;">GE</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3283em;"><span style="top:-2.55em;margin-left:-0.1389em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="katex-sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight" style="margin-right:0.0278em;">D</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal" style="margin-right:0.0502em;">B</span><span class="mord">"</span><span class="mspace" style="margin-right:0.2222em;"></span><span class="mbin">−</span><span class="mspace" style="margin-right:0.2222em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:0.6944em;"></span><span class="mord mathnormal">t</span><span class="mord mathnormal">A</span><span class="mord mathnormal">c</span><span class="mord">"</span></span></span></span>query")
log " <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>d</mi><mi>e</mi><mi>s</mi><mi>c</mi><mo>:</mo></mrow><annotation encoding="application/x-tex">desc:</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.6944em;"></span><span class="mord mathnormal">d</span><span class="mord mathnormal">esc</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">:</span></span></span></span>result"
echo "$result"
}
log "--- structure ---"
TABLES=$(check_sql "tables" \
"SELECT count(*) FROM information_schema.tables WHERE table_schema='public'")
[[ "<span class="katex-error" title="ParseError: KaTeX parse error: Expected 'EOF', got '&' at position 18: …BLES" -lt 4 ]] &̲& fail "expecte…" style="color:#cc0000">TABLES" -lt 4 ]] && fail "expected at least 4 tables, found</span>TABLES"
check_sql "indexes" "SELECT count(*) FROM pg_indexes WHERE schemaname='public'"
check_sql "constraints" "SELECT count(*) FROM pg_constraint"
log "--- data volume ---"
ORDERS=$(check_sql "orders" "SELECT count(*) FROM orders")
USERS=$(check_sql "customers" "SELECT count(*) FROM customers")
[[ "$ORDERS" -lt 1 ]] && fail "orders table is empty"
[[ "$USERS" -lt 1 ]] && fail "customers table is empty"
log "--- freshness ---"
AGE_H=$(check_sql "newest order (hours old)" \
"SELECT round(EXTRACT(epoch FROM now() - max(placed_at))/3600) FROM orders")
(( AGE_H > 48 )) && fail "newest data is ${AGE_H}h old — backups may have stalled"
log "--- referential integrity ---"
ORPHANS=$(check_sql "orphaned orders" \
"SELECT count(*) FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id)")
[[ "<span class="katex-error" title="ParseError: KaTeX parse error: Expected 'EOF', got '&' at position 19: …HANS" -ne 0 ]] &̲& fail "" style="color:#cc0000">ORPHANS" -ne 0 ]] && fail "</span>ORPHANS orphaned orders"
log "--- sequences (a behind sequence breaks the next INSERT) ---"
<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>P</mi><mi>S</mi><mi>Q</mi><mi>L</mi><mo>−</mo><mi>d</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">PSQL -d "</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8778em;vertical-align:-0.1944em;"></span><span class="mord mathnormal" style="margin-right:0.1389em;">P</span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord mathnormal">Q</span><span class="mord mathnormal">L</span><span class="mspace" style="margin-right:0.2222em;"></span><span class="mbin">−</span><span class="mspace" style="margin-right:0.2222em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:0.6944em;"></span><span class="mord mathnormal">d</span><span class="mord">"</span></span></span></span>TARGET_DB" -tAc "
SELECT 'SEQ ' || c.relname || ': ' ||
CASE WHEN last_value >= COALESCE(maxid,0) THEN 'ok'
ELSE 'BEHIND (' || last_value || ' < ' || maxid || ')' END
FROM pg_sequences s
JOIN pg_class c ON c.relname = s.sequencename
LEFT JOIN LATERAL (SELECT max(id) AS maxid FROM orders) x ON true
WHERE s.schemaname='public';" | tee -a "$REPORT"
log "--- a query that must work ---"
<span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>P</mi><mi>S</mi><mi>Q</mi><mi>L</mi><mo>−</mo><mi>d</mi><mi mathvariant="normal">"</mi></mrow><annotation encoding="application/x-tex">PSQL -d "</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8778em;vertical-align:-0.1944em;"></span><span class="mord mathnormal" style="margin-right:0.1389em;">P</span><span class="mord mathnormal" style="margin-right:0.0576em;">S</span><span class="mord mathnormal">Q</span><span class="mord mathnormal">L</span><span class="mspace" style="margin-right:0.2222em;"></span><span class="mbin">−</span><span class="mspace" style="margin-right:0.2222em;"></span></span><span class="katex-base"><span class="katex-strut" style="height:0.6944em;"></span><span class="mord mathnormal">d</span><span class="mord">"</span></span></span></span>TARGET_DB" -c "
SELECT c.city, count(*) AS orders, sum(o.total) AS revenue
FROM orders o JOIN customers c ON c.id = o.customer_id
GROUP BY c.city ORDER BY revenue DESC LIMIT 5;" | tee -a "$REPORT"
log "=== PASSED: <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mrow><mi>D</mi><mi>U</mi><mi>R</mi><mi>A</mi><mi>T</mi><mi>I</mi><mi>O</mi><mi>N</mi></mrow><mi>s</mi><mi>r</mi><mi>e</mi><mi>s</mi><mi>t</mi><mi>o</mi><mi>r</mi><mi>e</mi><mo separator="true">,</mo></mrow><annotation encoding="application/x-tex">{DURATION}s restore,</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="katex-base"><span class="katex-strut" style="height:0.8778em;vertical-align:-0.1944em;"></span><span class="mord"><span class="mord mathnormal" style="margin-right:0.0278em;">D</span><span class="mord mathnormal" style="margin-right:0.109em;">U</span><span class="mord mathnormal" style="margin-right:0.0077em;">R</span><span class="mord mathnormal">A</span><span class="mord mathnormal" style="margin-right:0.1389em;">T</span><span class="mord mathnormal" style="margin-right:0.0785em;">I</span><span class="mord mathnormal" style="margin-right:0.0278em;">O</span><span class="mord mathnormal" style="margin-right:0.109em;">N</span></span><span class="mord mathnormal" style="margin-right:0.0278em;">sr</span><span class="mord mathnormal">es</span><span class="mord mathnormal">t</span><span class="mord mathnormal" style="margin-right:0.0278em;">or</span><span class="mord mathnormal">e</span><span class="mpunct">,</span></span></span></span>ORDERS orders, newest ${AGE_H}h old ==="
SQL
-- ---------- PART 4: anonymisation, for a shareable dev copy ----------
-- !! Run this ONLY in the restore environment, NEVER against production.
-- !! Verify the connection target before executing.
SELECT current_database(), inet_server_port(); -- CHECK BEFORE RUNNING
BEGIN;
UPDATE customers SET
email = 'user' || id || '@example.invalid', -- .invalid can never resolve
full_name = 'Customer ' || id;
UPDATE addresses SET
line1 = id || ' Example Street',
postcode = '000000';
-- Shift dates rather than randomising them, so intervals between events
-- stay realistic for testing:
UPDATE orders SET placed_at = placed_at - interval '37 days';
-- Remove anything that should never leave production:
TRUNCATE payments;
TRUNCATE order_status_history;
DELETE FROM users WHERE email LIKE '%@internal.example.com';
-- Confirm nothing recognisable survived BEFORE committing:
SELECT count(*) FROM customers WHERE email NOT LIKE '%@example.invalid';
-- Expect 0.
COMMIT;
BASH
# Then dump the anonymised copy for developers:
pg_dump -h localhost -p 5433 -U postgres -d "$TARGET_DB" \
-Fc -f /var/backups/dev_seed_$(date +%F).dump
BASH
# ---------- PART 5: schedule it ----------
sudo crontab -e
# Weekly full verification, Sunday 04:00
0 4 * * 0 /usr/local/bin/restore_verify.sh >> /var/log/restore_verify.log 2>&1
# Treat a FAILURE HERE AS SERIOUSLY AS A FAILED BACKUP — it means every
# backup you hold may be unusable.
BASH
# ---------- PART 6: track restore duration over time ----------
grep "restore completed in" /var/log/restore_verify*.log | tail -12
# restore completed in 182s
# restore completed in 201s
# restore completed in 340s <- growing
# restore completed in 612s <- your RTO is drifting
# When this approaches your agreed RTO, you need parallel restore,
# physical backups, or a standby — not a bigger promise.How it works #
Expected result: a scheduled job that restores the latest backup into an isolated instance, runs structural, volume, freshness and integrity checks, records how long the restore took, and fails loudly if anything is wrong.
The checks are chosen to catch distinct failures:
Structural checks catch a restore that ran but produced almost nothing — a handful of tables where there should be dozens.
Volume checks catch empty tables, which is what a permissions problem during backup looks like.
Freshness is the check most people omit and the one that catches the nastiest failure: a perfectly valid restore of a backup that is two weeks old, because the backup job silently stopped rotating. Success alone does not prove currency.
Referential integrity catches a partial or out-of-order restore, where child rows reference parents that never arrived.
Sequence checks catch the problem that only appears after you go live — a sequence behind its table's maximum id, so the very next insert fails on a duplicate key.
The timing measurement is arguably the most valuable output. It is your real recovery time objective, measured rather than assumed. Tracking it over time shows recovery getting slower as data grows, long before an incident reveals that your agreed RTO became fiction months ago.
Isolation is a hard requirement. This environment restores production data and runs destructive operations such as anonymisation. It must be physically unable to reach production — a different host or container, different credentials, and read-only access to the backup storage. The SELECT current_database(), inet_server_port() check before anonymisation exists because a destructive script pointed at the wrong target is exactly the accident this whole track warns about.
Anonymisation shifts dates rather than randomising them, preserving the intervals between events so the data remains realistic for testing. Using the .invalid top-level domain guarantees no email can accidentally resolve or be sent to.
Real-world use #
The reason to build this is that it converts backup verification from an intention into a routine. "We should test our restores" becomes a job that either passed on Sunday or did not.
Treat a failure here with the same urgency as a production incident. It means your recovery capability is gone, even though everything appears fine — and you have found out on a Sunday rather than during an outage.
The restore duration is the number to watch and to share. Recovery time objectives are usually agreed early and rarely revisited, while data grows continuously. A graph of restore duration against your agreed RTO is the clearest possible argument for investing in physical backups or a standby, at the moment it becomes necessary rather than after.
The anonymised dev copy is a genuine side benefit. Realistic data volumes and distributions make development and testing dramatically more useful than seeded fixtures, and the query-optimisation project shows exactly why plans on small data are misleading. Get explicit agreement on what must be anonymised, and have someone verify it — the failure mode here is a data protection incident, not a technical one.
Be careful about what else the restore environment can reach. It holds a full copy of production data, so it deserves comparable access controls. An under-secured "test" environment containing real customer data is a common and serious oversight.
Common mistakes #
- Building the restore environment somewhere that can reach production, risking a destructive script running against the wrong target.
- Checking that the restore succeeded without checking whether the data is current.
- Never recording restore duration, so the agreed RTO silently becomes fiction as data grows.
- Securing the restore environment less carefully than production, despite it holding the same data.
- Forgetting ANALYZE after restoring, making every verification query misleadingly slow.
Practice #
Build the environment and run a full verification. Then break things deliberately and confirm each is caught: restore a deliberately old backup and check the freshness test fails; delete a table from a copy of the dump and check the structural test fails; restore only the data without the schema and check the integrity test fails. Next, add three checks of your own — one that verifies a specific business invariant, one that confirms every expected index exists, and one that fails if any table has zero rows. Finally, record the restore duration weekly for a month and graph it.