Test a PostgreSQL backup with a restore drill

01

1. Define the result before taking the backup

Use a small orders table with two known rows. A useful verification checks the row count, a monetary total and primary-key behavior. File size and a successful pg_dump exit are necessary checks, but they do not replace restoring the archive.

The example requires Docker, Bash and the postgres:16 image. It creates two new containers without published ports or a connection to an existing database. The password in the example is only for this disposable lab.

02

2. Run the complete disposable drill

Save this script as backup-lab.sh in a new working folder and run bash backup-lab.sh. It starts a source and a separate empty target, waits for them, seeds the source and creates a custom-format archive.

The trap removes only the container IDs this invocation created. Its temporary archive is removed too. This is a repeatable verification exercise, not a production backup-storage script. Change that retention design deliberately before using the approach for real backups.

#!/usr/bin/env bash
set -euo pipefail
lab_dir=$(mktemp -d)
source_id=''
restore_id=''
cleanup() {
  for id in "$source_id" "$restore_id"; do
    if [ -n "$id" ]; then docker rm -f "$id" >/dev/null; fi
  done
  rm -rf "$lab_dir"
}
trap cleanup EXIT
for role in source restore; do
  id=$(docker run --rm -d --network none \
    --label ctk.editorial=backup-lab \
    --tmpfs /var/lib/postgresql/data \
    -e POSTGRES_USER=lab -e POSTGRES_DB=app \
    -e POSTGRES_PASSWORD=disposable-lab-only postgres:16)
  if [ "$role" = source ]; then source_id=$id; else restore_id=$id; fi
  ready=false
  for attempt in $(seq 1 60); do
    if docker exec "$id" pg_isready -h 127.0.0.1 -U lab -d app >/dev/null 2>&1; then
      ready=true
      break
    fi
    sleep 0.5
  done
  [ "$ready" = true ] || { echo 'Database did not become ready' >&2; exit 1; }
done

docker exec -i "$source_id" psql -U lab -d app -v ON_ERROR_STOP=1 <<'SQL'
CREATE TABLE orders (id integer PRIMARY KEY, total numeric(12,2) NOT NULL);
INSERT INTO orders VALUES (1, 10.50), (2, 20.00);
SQL

docker exec "$source_id" pg_dump -U lab -d app -Fc > "$lab_dir/app.dump"
[ -s "$lab_dir/app.dump" ]
# No -t: this is a binary archive, not an interactive terminal session.
docker exec -i "$restore_id" pg_restore -U lab -d app \
  --exit-on-error --no-owner --no-privileges < "$lab_dir/app.dump"
query='SELECT count(*), sum(total) FROM orders;'
before=$(docker exec "$source_id" psql -U lab -d app -Atc "$query")
after=$(docker exec "$restore_id" psql -U lab -d app -Atc "$query")
[ "$before" = '2|30.50' ]
[ "$after" = "$before" ]
if docker exec "$restore_id" psql -U lab -d app -v ON_ERROR_STOP=1 \
  -c 'INSERT INTO orders VALUES (1, 99.00);' > "$lab_dir/duplicate.log" 2>&1; then
  echo 'Expected duplicate primary key rejection' >&2
  exit 1
fi
printf 'PASS rows-and-total=%s primary-key-rejection=verified\n' "$after"

03

3. Understand the stream boundary

pg_dump runs inside the source container, while shell redirection writes the archive on the host. pg_restore runs inside the target and receives that archive through stdin. docker exec -i keeps that input stream open.

Do not allocate a terminal with -t for this binary archive. The restore uses --exit-on-error so SQL failures fail the drill instead of being hidden among output. The no-owner and no-privileges options simplify this single-user lab; they intentionally do not reproduce production ownership and grants.

04

4. Check data and behavior

The expected success line includes rows-and-total=2|30.50 and primary-key-rejection=verified. The source and target aggregates must match, and inserting a duplicate ID into the restored table must fail.

These assertions check the fixture’s data and one schema property. In an application, add checks for sequences, required functions, migrations and important relationships. A row count alone can match even when the wrong records were restored.

05

5. Keep recovery requirements separate

A logical database archive is not a full backup of an entire cluster, every role or every external dependency. Plan credentials, extensions, privileges, restore permissions and version compatibility separately. Only restore archives from a source you trust.

For a production drill, use an approved isolated target and non-sensitive or appropriately protected backup material. Record the restore duration and your application checks. Do not overwrite the source database to test whether its backup works.

06

Verification boundary

The editorial check ran this script against fresh PostgreSQL 16 containers, restored the archive, matched the two-row total and confirmed duplicate-key rejection. It then removed the lab containers. That validates this small logical restore workflow, not a disaster-recovery objective for a larger system.