PostgreSQL logical backup and restore, validated against an invariant

Objective

Somebody asks whether last night’s database backup is good. Answering takes two things most estates do not have: a property of the data recorded before the backup was taken, and a restore that reproduces it.

You will record a row count and a sum over a money column while the database is alive, take a logical backup, destroy the database, restore it under a different name, and accept the restore only because those two numbers came back unchanged. Then you will run a restore that exits 0 and fails that same check.

Architecture

The two chains below never meet. One ends in a comparison; the other ends in a copy with no instant to compare it against.

flowchart TB
    SEED["rbdr_orders seeded<br/>50000 rows, sum 825025000"] --> INV["invariant.txt written<br/>before any backup exists"]
    INV --> DUMP["pg_dump -Fc<br/>one transaction, one snapshot"]
    DUMP --> AFTER["5000 more orders arrive<br/>this interval is the recovery point"]
    AFTER --> DROP["DROP DATABASE rbdr_shop<br/>psql then exits 2"]
    DROP --> REST["createdb plus pg_restore<br/>into a new name, timed"]
    REST --> CHECK{"do the count and the sum<br/>equal the recorded pair"}
    CHECK -->|match| PASS["restore accepted"]
    CHECK -->|mismatch| FAIL["schema-only restore:<br/>exit 0, every table, zero rows"]
    NAIVE["cp -a of a live PGDATA<br/>started, exit 0, 45000 rows"] --> NOINST["no instant it is a picture of,<br/>so no invariant can test it"]

Requirements

• Docker Engine and the CLI, with permission to remove containers and volumes. Task 6 destroys a database on purpose.
• A PostgreSQL 18 container image. The evidence quoted here was captured on postgres (PostgreSQL) 18.6 (Debian 18.6-1.pgdg13+2). Anything else is your version, and your own output is the evidence.
• A host carrying no rbdr- objects. Every container, volume and database created here uses that prefix, so Cleanup can be scoped and asserted rather than hoped for.

Scenario

An order service keeps its rows in one PostgreSQL database. A nightly pg_dump writes a file to a share, and the monitoring rule is that the job exits 0 and the file is no smaller than yesterday’s. It has been green for two years.

This morning an engineer runs DROP DATABASE in the wrong terminal. You have the file. Nobody has ever opened it.

Tasks

Task 1 — Record pre-lab state

LAB="$HOME/rbdr-lab-21"
OUT=/tmp/rbdr-out-21
mkdir -p "$LAB" "$OUT"

docker --version > "$LAB/versions.txt"

{
  echo "--- containers ---"
  docker ps -a --filter 'name=rbdr-' --format '{{.Names}}' | sort
  echo "--- volumes ---"
  docker volume ls --filter 'name=rbdr-' --format '{{.Name}}' | sort
} | tee "$LAB/pre-state.txt"

Both inventories must be empty. Cleanup diffs against this exact file, so if an rbdr- object already exists here, move to another host rather than deleting something that belongs to someone else.

Task 2 — Start a disposable instance

docker volume create rbdr-pgdata
docker run -d --name rbdr-pg \
  -e POSTGRES_PASSWORD=rbdr-lab-only \
  -v rbdr-pgdata:/var/lib/postgresql \
  postgres:18

for _ in $(seq 1 60); do
  docker exec rbdr-pg pg_isready -U postgres -q && break
  docker inspect rbdr-pg --format '{{.State.Running}}' | grep -qx true \
    || { docker logs rbdr-pg; exit 1; }
  sleep 1
done
docker exec rbdr-pg pg_isready -U postgres -q
docker exec rbdr-pg psql -U postgres -c 'CREATE DATABASE rbdr_shop;'
docker exec rbdr-pg postgres --version | tee -a "$LAB/versions.txt"

The password is written in the clear because this instance exists for seventy minutes and is destroyed by Cleanup. Do not carry the pattern anywhere else.

The server version is appended to versions.txt rather than assumed. The captures quoted below came from postgres (PostgreSQL) 18.6 (Debian 18.6-1.pgdg13+2); if your line differs, that line is what your results should be read against.

Task 3 — Load the dataset and record the invariant

docker exec rbdr-pg psql -U postgres -d rbdr_shop -c "
CREATE TABLE rbdr_orders (
  order_id  bigint PRIMARY KEY,
  placed_at timestamptz NOT NULL DEFAULT now(),
  amount    bigint NOT NULL
);
INSERT INTO rbdr_orders (order_id, amount)
SELECT g, 1016 + 31 * ((g - 1) % 1000)
FROM generate_series(1, 50000) AS g;"

docker exec rbdr-pg psql -U postgres -d rbdr_shop -At \
  -c "SELECT count(*) || ' ' || sum(amount) FROM rbdr_orders;" \
  | tee "$LAB/invariant.txt"

The seed walks a thousand-value ramp fifty times. One ramp sums to 1000 * 1016 + 31 * (0 + 1 + ... + 999) — 1,016,000 plus 15,484,500, or 16,500,500 — and fifty of them sum to 825,025,000. That is arithmetic you can check without running anything, and it deliberately reproduces the pair the point-in-time capture recorded for its own dataset.

$ the capture harness reporting the state of the business data before the incident

  rows now                      : 50000
checksum of the business data : sum(amount)=825025000
recovery target time          : 2026-08-28 13:34:40.077562+00

Two numbers, recorded before any backup exists. Everything after this point is judged against them.

Task 4 — Watch one transaction hold one instant

docker exec -i rbdr-pg tee /tmp/rbdr-snapshot.sql > /dev/null <<'SQL'
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT 'first read  ' || count(*) FROM rbdr_orders;
SELECT pg_sleep(5);
SELECT 'second read ' || count(*) FROM rbdr_orders;
COMMIT;
SQL

docker exec rbdr-pg psql -U postgres -d rbdr_shop -At -f /tmp/rbdr-snapshot.sql \
  | tee "$LAB/snapshot-proof.txt" &

sleep 2
docker exec rbdr-pg psql -U postgres -d rbdr_shop -At \
  -c "INSERT INTO rbdr_orders (order_id, amount) VALUES (900001, 1016);"
wait

docker exec rbdr-pg psql -U postgres -d rbdr_shop -At \
  -c "DELETE FROM rbdr_orders WHERE order_id = 900001;"
docker exec rbdr-pg psql -U postgres -d rbdr_shop -At \
  -c "SELECT count(*) || ' ' || sum(amount) FROM rbdr_orders;"

The statements go through a file rather than psql -c. The psql reference states that a multi-statement -c string is sent as a single request and processed in one transaction unless the string itself divides it, so a BEGIN placed inside arrives after a transaction has already started and the isolation level is not the one you asked for. Read from a file, psql sends each statement in turn and the BEGIN takes effect. The two-second gap before the insert exists so the background session has reached its first read; if your host is slower, widen it — pg_sleep(5) leaves a five-second window to land in.

$ docker exec rbdr-pg psql -U postgres -d rbdr_shop -At -f /tmp/rbdr-snapshot.sql

first read  50000

second read 50000

The second read is taken after another session committed a row, and it still reports 50000. That is the mechanism: a transaction at REPEATABLE READ sees the database as it stood when the transaction began, and no later commit changes what it reports. A logical dump runs the entire extraction inside one such transaction, which is why the documentation can say pg_dump makes consistent backups even while the database is in use. The file is a picture of one instant, not a smear across the minutes the dump took to write. The extra row is then deleted so the Task 3 invariant still holds, and the last line re-proves it.

Now the contrast. The captured run also copied a live data directory with cp -a, with no database involvement of any kind, while a workload wrote to it, then started a server on the copy.

$ the capture harness starting a server on a copy of a live data directory

$ pg_ctl -D /work/naive-copy start
waiting for server to start.... done
server started
>>> exit code: 0

$ the capture harness counting rows in the copy and in the live database

  rows readable from the naive copy : 45000
rows in the live database         : 45000

That copy started, went through crash recovery exactly as it would after a power cut, returned all of its rows — and established nothing. cp walked the tree over several seconds while pages were written underneath it, so there is no instant the result is a picture of, and therefore no recorded pair of numbers it can be compared against. The dump is testable because it has one; the copy is untestable because it has none.

Task 5 — Take the logical backup, then let business continue

BACKUP_AT=$(date +%s)
docker exec rbdr-pg pg_dump -U postgres -Fc -d rbdr_shop -f /tmp/rbdr-shop.dump
echo ">>> exit code: $?"
docker cp rbdr-pg:/tmp/rbdr-shop.dump "$OUT/rbdr-shop.dump"
ls -l "$OUT/rbdr-shop.dump"

docker exec rbdr-pg psql -U postgres -d rbdr_shop -c "
INSERT INTO rbdr_orders (order_id, amount)
SELECT g, 1016 + 31 * ((g - 1) % 1000)
FROM generate_series(50001, 55000) AS g;"

docker exec rbdr-pg psql -U postgres -d rbdr_shop -At \
  -c "SELECT count(*) || ' ' || sum(amount) FROM rbdr_orders;"

Five thousand more orders arrive after the dump. They are not in the file, and the dump is not wrong for lacking them: the interval between BACKUP_AT and the loss is the recovery point you achieved, and it is a property of your schedule alone.

Task 6 — Destroy the database and confirm it by exit code

LOSS_AT=$(date +%s)
docker exec rbdr-pg dropdb -U postgres rbdr_shop

docker exec rbdr-pg psql -U postgres -d rbdr_shop -At -c 'SELECT 1;'
echo ">>> exit code: $?"

$ docker exec rbdr-pg psql -U postgres -d rbdr_shop -At -c 'SELECT 1;'

psql: error: connection to server failed: FATAL:  database "rbdr_shop" does not exist
>>> exit code: 2

Read the exit code, not the sentence. The psql reference gives 2 as the status when the connection to the server went bad and the session was not interactive — which is what -c under docker exec is, and naming a database that no longer exists is exactly such a failed connection. A check that greps stdout for a row count sees nothing here and never notices; the message is on stderr and the row it wanted was never returned.

Task 7 — Restore into a name that did not exist at dump time

docker cp "$OUT/rbdr-shop.dump" rbdr-pg:/tmp/rbdr-restore.dump

RESTORE_START=$(date +%s)
docker exec rbdr-pg createdb -U postgres rbdr_shop_restored
docker exec rbdr-pg pg_restore -U postgres -d rbdr_shop_restored /tmp/rbdr-restore.dump
echo ">>> exit code: $?"
RESTORE_END=$(date +%s)

{
  docker exec rbdr-pg psql -U postgres -d rbdr_shop_restored -At \
    -c "SELECT count(*) || ' ' || sum(amount) FROM rbdr_orders;"
  echo "restore seconds: $(( RESTORE_END - RESTORE_START ))"
  echo "rpo seconds: $(( LOSS_AT - BACKUP_AT ))"
} | tee "$LAB/restored-invariant.txt"

The dump is copied back into the container before the clock starts, so the measured seconds cover createdb and pg_restore and nothing else. The restore goes into rbdr_shop_restored, which did not exist when the dump was written: a logical dump carries objects and rows rather than files and block offsets, which is why the SQL Dump documentation presents such a file as something you can load elsewhere rather than as an image of one data directory.

$ the capture harness comparing the recovered database against the invariant recorded before the incident

  rows recovered  : 50000   (expected 50000)
sum(amount)     : 825025000   (expected 825025000)
RECOVERED - row count and business checksum both match the pre-DELETE state

That capture is quoted for one reason, and it is the reason this lab exists: the line declaring success names the two recorded numbers rather than an exit status. Your own Task 7 output is one line, 50000 825025000, and it carries exactly the same weight — it is the pair from invariant.txt, recomputed on a database that did not exist when the dump was written.

Task 8 — Prove the check has teeth

docker exec rbdr-pg createdb -U postgres rbdr_shop_schemaonly
docker exec rbdr-pg pg_restore -U postgres -d rbdr_shop_schemaonly \
  --schema-only /tmp/rbdr-restore.dump
echo ">>> exit code: $?"

{
  docker exec rbdr-pg psql -U postgres -d rbdr_shop_schemaonly -At \
    -c "SELECT count(*) FROM rbdr_orders;"
} | tee "$LAB/teeth.txt"

$ the two lines Task 8 prints: pg_restore --schema-only, then SELECT count(*) FROM rbdr_orders

>>> exit code: 0
0

Validation

Run every line and compare the printed string and the exit code. Anything else is a finding, not a variation.

LAB="$HOME/rbdr-lab-21"
OUT=/tmp/rbdr-out-21

docker exec rbdr-pg psql -U postgres -d rbdr_shop_restored -At \
  -c "SELECT count(*) || ' ' || sum(amount) FROM rbdr_orders;"
echo "restored exit: $?"
docker exec rbdr-pg psql -U postgres -d rbdr_shop -At -c 'SELECT 1;'
echo "dropped db exit: $?"
docker exec rbdr-pg psql -U postgres -d rbdr_shop_schemaonly -At \
  -c "SELECT count(*) FROM rbdr_orders;"
echo "schema-only exit: $?"
test -s "$OUT/rbdr-shop.dump"
echo "dump file exit: $?"
grep -c ' 50000$' "$LAB/snapshot-proof.txt"
echo "snapshot proof exit: $?"
grep -c 'rbdr-' "$LAB/pre-state.txt"
head -1 "$LAB/restored-invariant.txt" | diff - "$LAB/invariant.txt" \
  && echo "INVARIANT MATCH"

The first row is load-bearing. 50000 825025000 is the count and sum of exactly fifty ramps of a thousand rows, so any other value means the seed, the dump or the restore differed. The fifth row is the one that proves Task 4: two lines ending in 50000 means the same transaction reported the same count on both sides of another session’s commit, and a 1 there means the insert landed outside the snapshot.

Expected Outcome

rbdr_shop no longer exists and answering a query against it exits 2. rbdr_shop_restored exists, holds 50000 rows summing to 825025000, and was created after the original was destroyed. rbdr_shop_schemaonly exists, holds every table from the same dump, and returns 0 rows after a pg_restore that exited 0. The five thousand orders written after the dump did not return; they were never in the file, and their absence is the recovery point, not a defect in the dump.

Record both measurements from your own run:

• Actual restore time: _______ seconds, the restore seconds line from Task 7. It counts createdb and pg_restore only — not noticing the loss, finding the file, or getting approval, which in a real incident are the larger numbers.
• Actual RPO observed: _______ seconds, the rpo seconds line — the interval between the dump in Task 5 and the drop in Task 6, holding exactly 5000 orders. It belongs to your schedule; no tool supplies it.

Only your own run can fill those two blanks, and this page ships them empty on purpose. No transcript in the course evidence directory covers The older captures quoted above cover a file-level copy and a WAL-based recovery, not this sequence. The complete eight-task logical restore run is captured in docs/courses/backup-dr/execution-evidence/backup-dr-lab-21-postgresql-logical-backup-and-restore-2026-08-29.txt and supports the last_executed date.

Troubleshooting

could not connect to server during Task 2. The server is still initialising its data directory; that is what the pg_isready loop is for. If it never returns, docker logs rbdr-pg names the reason.

database "rbdr_shop" is being accessed by other users, exit 1, in Task 6. A session is still attached. Close it, or terminate that database’s backends, then repeat the dropdb.

pg_restore: error: could not open input file. The dump reached the host in Task 5 but was never copied back in. Task 7 does that with docker cp; run it before pg_restore.

createdb: error: database creation failed: ERROR: database "rbdr_shop_restored" already exists, exit 1. You are on a second pass through Task 7. Drop that database, or restore into a third name; do not restore on top of a database whose contents you have not accounted for.

pg_restore exits 0 and the table is empty. You passed --schema-only, which is Task 8, not Task 7 — the failing case working as designed.

The restored count is 55000, not 50000. The dump was taken after the Task 5 insert. The dump comes first and the orders second; the difference between them is the exercise.

The count is 50000 but the sum is not 825025000. The seed expression was altered. Only 1016 + 31 * ((g - 1) % 1000) over generate_series(1, 50000) produces that total.

Both reads in Task 4 report 50001. The insert landed before the transaction opened, so there was nothing to be blind to. Re-run it, background session first.

Cleanup

LAB="$HOME/rbdr-lab-21"
OUT=/tmp/rbdr-out-21

docker rm -f rbdr-pg 2>/dev/null
docker volume rm rbdr-pgdata 2>/dev/null
rm -f "$OUT/rbdr-shop.dump"
rmdir "$OUT" 2>/dev/null

{
  echo "--- containers ---"
  docker ps -a --filter 'name=rbdr-' --format '{{.Names}}' | sort
  echo "--- volumes ---"
  docker volume ls --filter 'name=rbdr-' --format '{{.Name}}' | sort
} > "$LAB/post-state.txt"

diff "$LAB/pre-state.txt" "$LAB/post-state.txt" \
  && echo "CLEAN: post-state matches the inventory from Task 1"

diff exiting 0 with CLEAN printed is the assertion that the host is back where Task 1 found it; any other output names what is still there. Removing the container does not remove the databases — rbdr_shop_restored and rbdr_shop_schemaonly live in the rbdr-pgdata volume, which survives docker rm and has to be removed by name. That is why the volume is listed separately, and why the post-state inventory checks volumes as well as containers. The files under $LAB are the lab’s deliverables and are deliberately kept.

Production notes

• Record the invariant on the source, before the backup runs, and store it beside the backup. A number computed from the restored copy validates nothing.
• Choose invariants a partial restore breaks: a row count catches truncation, a sum over a money column catches wrong values. Neither is a checksum of the file — they are properties of the business data.
• A dump job that exits 0 has not shown the data can be recovered. Only a restore compared against something recorded earlier shows that.
• Restore into a new database name, never over the live one, so the original stays available while you judge the copy.
• Time the restore and write the seconds beside the file. An untimed restore is an estimate, and estimates here are reliably optimistic.

What You Learned

• The invariant was recorded before the backup existed, the only ordering that makes a later comparison mean anything.
• A transaction at REPEATABLE READ reported the same count across another session’s commit, which is what makes a logical dump a picture of one instant rather than of an interval.
• The restore was accepted because 50000 and 825025000 came back, into a database created after the original was destroyed.
• A schema-only restore exited 0 with every table present and zero rows, so the exit-code check and the invariant check disagreed about one restore — and only one of them was right.
• The captured file-level copy started, completed crash recovery and returned all 45000 of its rows, and still established nothing: no instant, so no number to compare it against.