A completed backup job establishes that the backup process produced an artifact. A restore rehearsal establishes whether that artifact, the available tooling, and the required environment can produce a usable system. These are different claims. Recovery evidence should identify the backup used, the state recovered, the dependencies supplied, and the time needed to make the result useful to the application.

When it breaks

A valid database archive can depend on objects outside its scope. PostgreSQL pg_dump exports one database, while roles are cluster-wide objects. Restoring a dump with grants into an independent cluster can fail because the referenced role does not exist. Rehearsing only in the original cluster can hide that dependency because the missing recovery material is already present there.

Restore errors also need deliberate handling. By default, pg_restore can continue after SQL errors and report their count at the end. A half-restored schema should not be accepted merely because some tables can be queried. For this small fixture, a single transaction makes success atomic and makes an expected failure leave no partial restored tables.

The correct recovery point is another requirement. A perfectly restored backup may intentionally lack changes made afterward. A checksum proves agreement with the recorded artifact, not that the artifact includes the newest business event. Likewise, a row count alone can match while amounts, relationships, or privileges are wrong. Choose checks that express the application’s meaning.

Check it quickly

Run this script as a non-root Linux account with PostgreSQL 18 server and client programs and sha256sum available. It was executed on PostgreSQL 18.6. It creates separate temporary source and target clusters with private Unix sockets and no TCP listeners. The fixture role cannot log in; its grants are tested through controlled role switching rather than through real credentials.

#!/bin/sh
set -eu
umask 077
lab=$(mktemp -d /tmp/xbcatm-restore.XXXXXX)
mkdir "$lab/socket"
source=$lab/source
target=$lab/target
cleanup() {
    for cluster in "$target" "$source"; do
        if test -f "$cluster/postmaster.pid"; then
            pg_ctl -D "$cluster" -m fast -w stop >/dev/null
        fi
    done
}
trap cleanup EXIT
for cluster in "$source" "$target"; do
    initdb -D "$cluster" --no-locale --encoding=UTF8 --auth-local=trust --auth-host=reject >/dev/null
    cat >> "$cluster/postgresql.conf" <<CONF
listen_addresses = ''
unix_socket_directories = '$lab/socket'
CONF
done
printf 'port = 56432\n' >> "$source/postgresql.conf"
printf 'port = 56433\n' >> "$target/postgresql.conf"
pg_ctl -D "$source" -l "$lab/source.log" -w -t 15 start >/dev/null
pg_ctl -D "$target" -l "$lab/target.log" -w -t 15 start >/dev/null
export PGHOST=$lab/socket PGPORT=56432 PGDATABASE=postgres PGUSER=$(id -un)
export PGCONNECT_TIMEOUT=2
psql -X -v ON_ERROR_STOP=1 -qAt <<'SQL'
CREATE ROLE backup_reader NOLOGIN;
CREATE TABLE customers (id integer PRIMARY KEY,name text NOT NULL);
CREATE TABLE orders (id integer PRIMARY KEY,customer_id integer REFERENCES customers,amount_cents integer CHECK (amount_cents>0));
INSERT INTO customers VALUES (1,'Ada');
INSERT INTO orders VALUES (10,1,4210),(11,1,1865);
CREATE VIEW order_totals AS SELECT c.name,sum(o.amount_cents) AS total_cents FROM customers c JOIN orders o ON o.customer_id=c.id GROUP BY c.id,c.name;
GRANT SELECT ON customers,orders,order_totals TO backup_reader;
SQL
pg_dump -Fc -w -f "$lab/snapshot.dump" postgres
sha256sum "$lab/snapshot.dump" > "$lab/snapshot.sha256"
psql -X -v ON_ERROR_STOP=1 -qAt -c 'INSERT INTO orders VALUES (12,1,1000)'
printf 'source_after_backup_orders '
psql -X -v ON_ERROR_STOP=1 -qAt -c 'SELECT count(*) FROM orders'
sha256sum -c "$lab/snapshot.sha256"
if PGPORT=56433 pg_restore --single-transaction --exit-on-error -w -d postgres "$lab/snapshot.dump" >"$lab/missing-role.log" 2>&1; then
    echo 'restore unexpectedly ignored a missing role' >&2
    exit 1
fi
grep -F 'role "backup_reader" does not exist' "$lab/missing-role.log"
test "$(PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt -c "SELECT to_regclass('public.orders') IS NULL")" = t
echo failed_restore_rolled_back
PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt -c 'CREATE ROLE backup_reader NOLOGIN'
PGPORT=56433 pg_restore --single-transaction --exit-on-error -w -d postgres "$lab/snapshot.dump"
PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt <<'SQL'
SELECT count(*) FROM orders;
SELECT name,total_cents FROM order_totals;
SELECT has_table_privilege('backup_reader','orders','SELECT'),has_table_privilege('backup_reader','orders','INSERT');
DO $$ BEGIN
  IF (SELECT count(*) FROM orders)<>2 OR
     (SELECT string_agg(id||':'||amount_cents,',' ORDER BY id) FROM orders) IS DISTINCT FROM '10:4210,11:1865' OR
     (SELECT total_cents FROM order_totals WHERE name='Ada') IS DISTINCT FROM 6075 OR
     (SELECT count(*) FROM orders o LEFT JOIN customers c ON c.id=o.customer_id WHERE c.id IS NULL)<>0 THEN
    RAISE EXCEPTION 'restored business data mismatch';
  END IF;
END $$;
DO $$ BEGIN
  BEGIN
    INSERT INTO orders VALUES (98,999,100);
    RAISE EXCEPTION 'restored foreign key unexpectedly accepted an orphan';
  EXCEPTION WHEN foreign_key_violation THEN
    RAISE NOTICE 'restored foreign key rejected orphan';
  END;
END $$;
SET ROLE backup_reader;
SELECT id,amount_cents FROM orders ORDER BY id;
DO $$ BEGIN
  BEGIN
    INSERT INTO orders VALUES (99,1,100);
    RAISE EXCEPTION 'read-only role unexpectedly wrote data';
  EXCEPTION WHEN insufficient_privilege THEN
    RAISE NOTICE 'read-only role rejected write';
  END;
END $$;
SQL
printf 'lab_directory %s\n' "$lab"

The source contains one customer, two orders, a totals view, a foreign key, and a read-only role grant. A custom-format dump captures that state. The source then receives a third order. Its printed count of three confirms that later activity exists outside the chosen backup point, rather than allowing a test to confuse the current source with the saved snapshot.

The checksum check succeeds, but the first restore must still fail because backup_reader is absent on the target. The script checks that specific error and verifies the orders table was rolled back. This is the dependency failure that an artifact-only check would miss. It then creates the documented fixture role and repeats the restore with the same archive.

The recovered target must contain two orders with the original identifiers and amounts, and the view must report Ada|6075. The third order is correctly absent. An attempted orphan order must fail through the restored foreign key. Permission checks report allowed selection and denied insertion; switching to the reader role confirms that reads work and a real write attempt is rejected.

These assertions cover data values, an application query, a relationship constraint, and effective privileges. All experimental servers stop on exit, and the printed directory retains the dump and logs. The drill does not test external authentication, encryption-key recovery, or application startup, which must be added when those are part of the actual recovery dependency set.

Record a recovery result people can use

A production rehearsal should retrieve the artifact from its intended recovery source, rebuild the required environment, restore data, and exercise representative application behavior. Provisioning compatible software is a normal part of recovery; include its elapsed time and prerequisites instead of assuming a prepared host will always exist. Keep recovery access available when production systems are unavailable.

Measure recovery duration against the recovery-time objective, and compare the recovered endpoint with the recovery-point objective. Include retrieval, validation, and the application becoming usable, not just the database command’s runtime. For larger restores, choose transaction and parallelism options appropriate to the workload, with an explicit partial-failure policy.

Repeat the rehearsal after changes to backup tooling, schema, roles, retention, or infrastructure. Preserve the successful checks and any corrected failure as evidence. A repeatable restore procedure with known inputs and limits supports an operational decision; a green backup scheduler alone does not answer whether recovery is ready.

Sources