How to test PostgreSQL backup restores automatically

A working script, what each check catches, and what it can't.

Your backup job says "success". That only means a file was written. It doesn't tell you whether the file restores, how long a restore takes, or whether the data inside is complete and recent. The only way to know is to restore it. This guide gives you a script that does that safely, so you can run it on a schedule.

What the script does

1. Starts a throwaway Postgres container with no network and its data directory in memory, so restored data can't leak anywhere or linger on disk.
2. Restores your dump. For custom-format dumps it uses pg_restore --exit-on-error, so any error fails the test instead of being printed and ignored.
3. Reads every row of every table. Postgres has to decode every page and every compressed (TOAST) value, so many kinds of corruption surface here as errors.
4. Runs amcheck's bt_index_check on every B-tree index, including the check that every row is present in its index.
5. Prints exact row counts per table and, for columns you name, the newest value (for example orders.created_at).
6. Deletes the container, whatever happened.

The script

Requirements: Docker and bash. Pass the Postgres major version you run in production. Restoring into a different major version tests something else. Download restore-test.sh.

#!/usr/bin/env bash
# Restore a PostgreSQL dump into a throwaway, network-isolated container and check it.
# Usage: restore-test.sh <dump-file> [postgres-major-version] [freshness-check ...]
#   freshness-check: table.column, e.g. public.orders.created_at (newest value is reported)
# Exit code: 0 = pass, 1 = restore or integrity failure.
set -euo pipefail

DUMP=$(realpath "$1"); PGVER=${2:-16}; shift $(( $# >= 2 ? 2 : 1 ))
NAME="restore-test-$$"
PSQL=(docker exec -i "$NAME" psql -U postgres -d restored -v ON_ERROR_STOP=1 -At)

cleanup() { docker rm -f "$NAME" >/dev/null 2>&1 || true; }
trap cleanup EXIT

echo "== file"
echo "size:  $(stat -c %s "$DUMP") bytes"
echo "age:   $(( ( $(date +%s) - $(stat -c %Y "$DUMP") ) / 3600 )) hours"

# --network none: the restored data can't talk to anything. tmpfs: nothing touches disk.
docker run -d --name "$NAME" --network none --tmpfs /var/lib/postgresql/data \
  -e POSTGRES_PASSWORD=unused -v "$DUMP":/backup.dump:ro "postgres:$PGVER" >/dev/null
# The image runs a socket-only temporary server during init; TCP readiness means the real one is up.
until docker exec "$NAME" pg_isready -h 127.0.0.1 -U postgres -q 2>/dev/null; do sleep 1; done
docker exec "$NAME" createdb -U postgres restored

echo "== restore"
start=$(date +%s)
if head -c 5 "$DUMP" | grep -q PGDMP; then
  # Custom-format dump: pg_restore reports every error; --exit-on-error makes any of them fatal.
  docker exec "$NAME" pg_restore -U postgres -d restored --no-owner --exit-on-error /backup.dump
else
  docker exec "$NAME" psql -U postgres -d restored -v ON_ERROR_STOP=1 -q -f /backup.dump >/dev/null
fi
echo "ok in $(( $(date +%s) - start ))s"

echo "== integrity"
# Reading every row forces Postgres to decode every page and TOAST value; corruption errors out here.
"${PSQL[@]}" <<'SQL'
CREATE EXTENSION IF NOT EXISTS amcheck;
DO $$
DECLARE r record; n bigint;
BEGIN
  FOR r IN SELECT c.oid::regclass AS t FROM pg_class c JOIN pg_namespace s ON s.oid = c.relnamespace
           WHERE c.relkind = 'r' AND s.nspname NOT IN ('pg_catalog', 'information_schema') LOOP
    EXECUTE format('SELECT count(*) FROM (SELECT * FROM %s) x', r.t) INTO n;
  END LOOP;
  -- B-tree index structure, and that every heap tuple is present in its index.
  FOR r IN SELECT c.oid::regclass AS i FROM pg_index x JOIN pg_class c ON c.oid = x.indexrelid
           JOIN pg_am a ON a.oid = c.relam JOIN pg_namespace s ON s.oid = c.relnamespace
           WHERE a.amname = 'btree' AND s.nspname NOT IN ('pg_catalog', 'information_schema') LOOP
    PERFORM bt_index_check(r.i, true);
  END LOOP;
END $$;
SQL
echo "all tables read, btree indexes ok"

echo "== row counts"
"${PSQL[@]}" <<'SQL'
SELECT format('%s.%s %s', schemaname, relname,
  (xpath('/row/n/text()', query_to_xml(format('SELECT count(*) AS n FROM %I.%I', schemaname, relname), false, true, '')))[1]::text)
FROM pg_stat_user_tables ORDER BY 1;
SQL

if (( $# )); then
  echo "== freshness"
  for spec in "$@"; do
    col=${spec##*.}; tbl=${spec%.*}
    newest=$("${PSQL[@]}" -c "SELECT max($col) FROM $tbl")
    echo "$tbl.$col newest: ${newest:-<empty>}"
  done
fi
echo "== PASS"

Usage

pg_dump -Fc mydb > mydb.dump          # or fetch last night's backup from S3
./restore-test.sh mydb.dump 16 public.orders.created_at public.users.created_at

Output for a healthy backup (a test database with 200,000 orders):

== file
size:  1557455 bytes
age:   0 hours
== restore
ok in 1s
== integrity
all tables read, btree indexes ok
== row counts
public.customers 5000
public.orders 200000
== freshness
public.orders.created_at newest: 2026-10-25 14:09:46.343989+00
== PASS

And for a dump that was cut off mid-upload, which a "backup succeeded" check would happily accept:

== restore
pg_restore: error: could not read from input file: end of file

The script exits non-zero, so cron, CI or your scheduler can alert on it.

What it catches, and what it doesn't

The hard failures fail loudly. The dangerous ones are quiet: a backup that restores perfectly but contains last week's data, or half the rows. Catching those needs history. Store each run's counts and freshness values, then alert when a table shrinks sharply or the newest record is much older than the backup.

Running it on a schedule

A minimal setup:

# /etc/cron.d/restore-test, every night at 04:00
0 4 * * * backup aws s3 cp s3://my-backups/latest.dump /tmp/latest.dump \
  && /opt/restore-test.sh /tmp/latest.dump 16 public.orders.created_at >> /var/log/restore-test.log 2>&1 \
  || curl -fsS -d "Restore test failed on $(hostname)" https://hooks.example.com/alert; \
  rm -f /tmp/latest.dump

Things to get right:

• Use read-only credentials for fetching backups, separate from whatever writes them. A restore test should never be able to delete a backup.
• Monitor the monitor. If the cron job itself stops running, you get silence, which looks exactly like success. Use a dead man's switch (a check-in URL that alerts when it doesn't hear from you).
• Watch memory and disk. The script keeps the restored database in RAM (--tmpfs). For databases larger than a few GB, use a temporary volume instead and delete it afterwards.
• Record the restore time. It's your measured recovery time, and the number your recovery time objective (RTO) should be based on.

What auditors want to see

Most security frameworks require restore testing, not just backups:

• ISO/IEC 27001:2022, control 8.13 (Information backup): backup copies must be maintained and regularly tested according to your backup policy.
• SOC 2, criterion A1.3: recovery plan procedures that support system recovery are tested.
• NIS2, Article 21(2)(c): business continuity measures, including backup management and disaster recovery.

In practice, auditors ask for evidence: when each test ran, what was restored, whether it passed, how long it took, and what you did when one failed. Keep the log output from every run, not only failures. A year of nightly logs is much stronger evidence than one manual test the week before the audit. More detail, including a record template: restore testing evidence for ISO 27001, SOC 2 and NIS2.