How to test a backup restore: files, PostgreSQL and a full server

Why untested backups fail

Each of these produces a backup file every night and a green status, until the day you need it:

One more trap: by default pg_restore keeps going after errors and ends with pg_restore: warning: errors ignored on restore: 12. It does exit with status 1, but a script that does not check the status reports success. Use --exit-on-error in drills.

Before you start: make backups checkable

Two small additions to your backup job make every later check faster. Say the job archives /srv/app into /backups:

tar -czf /backups/app-2026-10-03.tar.gz -C /srv app

Store a checksum of the archive next to it. Run it from inside /backups so the file name inside the .sha256 file has no directory in front of it; otherwise the check fails on any other machine.

cd /backups

sha256sum app-2026-10-03.tar.gz > app-2026-10-03.tar.gz.sha256

Then record a checksum for every file in the source, from inside the directory so the paths are relative:

cd /srv/app

find . -type f -exec sha256sum {} + > /backups/app-2026-10-03.sha256

To spot empty or near-empty backups, list files under 1,024 bytes, skipping the small checksum files:

find /backups -type f ! -name '*.sha256' -size -1024c

Drill 1: restore files from a tar archive

Copy the archive and both .sha256 files from your backup storage into /backups on the test machine, then check that the archive arrived intact:

cd /backups

sha256sum -c app-2026-10-03.tar.gz.sha256

It prints app-2026-10-03.tar.gz: OK. Extract into a scratch directory, never over the live path. Run tar as root so owners and permissions come back:

mkdir -p /tmp/restore-test

tar -xzpf app-2026-10-03.tar.gz -C /tmp/restore-test

Check every file against the manifest. --quiet prints only failures, so no output means every file matched:

cd /tmp/restore-test/app

sha256sum -c --quiet /backups/app-2026-10-03.sha256

A damaged file shows up like this, and the command exits with status 1:

./public/uploads/img3.bin: FAILED
sha256sum: WARNING: 1 computed checksum did NOT match

If you are testing on the live server, compare the restore with the live directory to find anything the backup never included:

diff -rq /srv/app /tmp/restore-test/app

Files /srv/app/public/index.php and /tmp/restore-test/app/public/index.php differ
Only in /srv/app/public/uploads: new-since-backup.bin

Files changed or added since the backup are expected. Whole directories listed as Only in /srv/app are not: they are paths your backup skips. Last, spot-check ownership and mode on a file that matters:

stat -c '%U:%G %a %n' /tmp/restore-test/app/config/.env

Expect www-data:www-data 600 /tmp/restore-test/app/config/.env, or whatever your app needs. Then clean up:

rm -rf /tmp/restore-test

Drill 2: restore a PostgreSQL dump into a scratch database

This assumes custom-format dumps made with sudo -u postgres pg_dump -Fc appdb > /backups/appdb-2026-10-03.dump. First, read the archive's table of contents. This needs no database and fails at once on a truncated or corrupt file:

pg_restore --list /backups/appdb-2026-10-03.dump | grep 'TABLE DATA'

3419; 0 42621 TABLE DATA public audit_log postgres
3415; 0 42599 TABLE DATA public customers postgres
3417; 0 42609 TABLE DATA public orders postgres

Create an empty scratch database from template0, as the PostgreSQL docs recommend:

sudo -u postgres createdb -T template0 appdb_restore_test

Restore into it and time it. The < redirect is opened by your shell, so the postgres user does not need read access to /backups. The time is your database restore time.

time sudo -u postgres pg_restore --exit-on-error -d appdb_restore_test < /backups/appdb-2026-10-03.dump

Count the tables:

sudo -u postgres psql -X -At -d appdb_restore_test -c "SELECT count(*) FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema')"

Then count rows in every table. Save this query as row-counts.sql. It runs a real count(*) per table; the row estimates in pg_stat_user_tables are not exact enough for this.

SELECT table_schema || '.' || table_name AS table,
       (xpath('/row/n/text()',
              query_to_xml(format('SELECT count(*) AS n FROM %I.%I', table_schema, table_name),
                           false, true, '')))[1]::text::bigint AS row_count
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1;

Run it against the restore and against the live database, then compare:

sudo -u postgres psql -X -At -d appdb_restore_test < row-counts.sql > restored-counts.txt

sudo -u postgres psql -X -At -d appdb < row-counts.sql > live-counts.txt

diff live-counts.txt restored-counts.txt

3c3
< public.orders|4203
---
> public.orders|4200

A few more rows in live tables that take writes is normal. A missing table, or a table at 0 that is not empty in production, is a failed drill. Now check how fresh the data is:

sudo -u postgres psql -X -At -d appdb_restore_test -c "SELECT max(created_at) FROM orders"

The gap between that timestamp and now is how much data you would lose if the server died at this moment. Drop the scratch database when you are done:

sudo -u postgres dropdb appdb_restore_test

Drill 3: restore a full server from a snapshot

1. Pick the snapshot you would use in a real outage, usually the newest, and note when it was taken.
2. Create a new server from it in your provider's console or CLI, the same size or larger.
3. When you create it, attach a firewall that allows SSH from your IP only and blocks outbound traffic.
4. Leave DNS alone, and run the checks below on the new server itself.
5. Run the smoke tests, then use the app: log in as a test user, open a page that reads from the database, upload a file.
6. Write down the time from step 2 to a working app. That is your full-server restore time.
7. Destroy the server, its volumes and any reserved IP, so you stop paying for them.

On the new server, list any services that failed to start:

systemctl --failed

Request your site locally with its real hostname, so TLS and virtual hosts are tested too. -f makes curl exit non-zero (22) on an HTTP error; it prints the status code either way:

curl -fsS -o /dev/null -w '%{http_code}\n' --resolve example.com:443:127.0.0.1 https://example.com/

And confirm the database holds recent data:

sudo -u postgres psql -X -At -d appdb -c "SELECT max(created_at) FROM orders"

What to record each time

Two numbers matter. NIST defines the recovery point objective (RPO) as "the point in time to which data must be recovered after an outage": how much recent data you can afford to lose. The recovery time objective (RTO) is how long systems can stay in recovery before the business is hurt. A drill measures what you actually achieve, so you can compare it with those targets.

How often to test

Automate the database drill

This script restores one dump into a scratch database, checks one table against a floor you know it is above, prints one line, and drops the database even if a step fails. Change SCRATCH_DB, MIN_ORDERS and the orders queries to fit your schema.

#!/usr/bin/env bash
# Restore one PostgreSQL dump into a scratch database, check it, drop it.
# Usage: restore-drill.sh /backups/appdb-2026-10-03.dump
set -euo pipefail

DUMP="$1"
SCRATCH_DB="appdb_restore_test"
MIN_ORDERS=1000   # a floor the real table is always above

sudo -u postgres dropdb --if-exists "$SCRATCH_DB"
trap 'sudo -u postgres dropdb --if-exists "$SCRATCH_DB"' EXIT
sudo -u postgres createdb -T template0 "$SCRATCH_DB"

start=$(date +%s)
# --no-owner/--no-acl: a data check that also works on a machine without your roles
sudo -u postgres pg_restore --exit-on-error --no-owner --no-acl -d "$SCRATCH_DB" < "$DUMP"
seconds=$(( $(date +%s) - start ))

orders=$(sudo -u postgres psql -X -At -d "$SCRATCH_DB" -c "SELECT count(*) FROM orders")
newest=$(sudo -u postgres psql -X -At -d "$SCRATCH_DB" -c "SELECT max(created_at) FROM orders")

if [ "$orders" -lt "$MIN_ORDERS" ]; then
  echo "FAIL: orders has $orders rows, expected at least $MIN_ORDERS" >&2
  exit 1
fi

echo "$(date -u +%FT%TZ) OK dump=$DUMP restore_seconds=$seconds orders=$orders newest_order=$newest"

chmod +x /usr/local/bin/restore-drill.sh

/usr/local/bin/restore-drill.sh /backups/appdb-2026-10-03.dump

A truncated dump stops it with pg_restore: error: could not read from input file: end of file and exit status 1. A short table prints the FAIL line. Either way the scratch database is dropped. Run it weekly from cron on the newest dump downloaded from your off-site storage, not on a copy that never left the server.

Frequently asked questions

How do I test a backup without touching production?

Restore into a scratch directory, a scratch database name, or a throwaway server, never over the live path. For full-server drills, block outbound traffic so the clone cannot send email or call payment and partner APIs.

Is checking that the backup file exists enough?

No. A failed pg_dump piped into gzip still writes a valid 20-byte file every night. Check sizes and checksums on every backup, and restore regularly.

Can I run restore drills on the production server?

File and database drills, yes, into scratch paths and database names, if the server has the disk space and spare capacity. Full-server drills need a separate machine. A restore on the same server also hides missing roles and paths, so run a drill on a clean machine at least quarterly.

What is the difference between RPO and RTO?

RPO is how much recent data you can afford to lose, measured in time. RTO is how long recovery can take. A drill tells you the real values: the age of the newest data in the restore, and the time the restore took.