← All skills

Most teams have backups and no restores. The backup job has been green for 400 days, the bucket has 2 TB in it, and the first time anyone opens a restore they find the dump is truncated, the encryption key is in the server that died, or the restore takes 11 hours against an assumed 2.

This skill treats the restore as the product: define RPO and RTO, back up, then restore on a schedule into a clean environment and verify with checksums and row counts. You end with a drill that runs monthly, a record per run, and a number you can quote to a customer or an auditor.

When to use this skill

• You have a backup job and have never restored from it.
• The backup script lives on the same server as the database.
• Nobody knows whether the restore takes 20 minutes or 8 hours.
• A compliance form asked for your RPO and RTO and you guessed.

Do NOT use for: designing the production database, or choosing a storage provider on price. It also does not cover application-level logical backups for a single table - this is the whole-system restore path.

Stage 1 - Write the two numbers before touching a script

1.1 RPO - how much data you can afford to lose, in minutes. A nightly dump is an RPO of up to 24 hours, which is unacceptable for a payment ledger.

1.2 RTO - how long a restore may take, in hours. This is the number that decides the architecture, not the backup tool.

1.3 Get both agreed in writing with whoever owns the business risk, not decided by the person writing the script.

1.4 Derive the schedule from the RPO: 15 minutes needs continuous archiving (WAL or binlog), 24 hours allows a nightly dump.

Gate: RPO and RTO written down per data class, signed off by the owner of the risk.

Stage 2 - Move the backup off the machine it protects

2.1 Back up to a different provider or at minimum a different account and region. A script on the same host dies with the host.

2.2 Object storage with versioning and object lock, so ransomware or a bad script cannot delete the history.

2.3 Encrypt at rest with a key stored outside the backup account. A key in the same account is not a second copy.

2.4 Restrict delete permission on the bucket to a break-glass role used twice a year.

2.5 Keep 7 daily, 4 weekly and 12 monthly copies, and say where each tier lives.

Gate: backups exist in a second account, versioned, encrypted with an externally stored key, with a documented retention tier per class.

Stage 3 - Take the backup with a method that can be verified

3.1 For Postgres, use pg_dump -Fc for logical or pg_basebackup plus WAL for physical. Do not tar a live data directory.

3.2 Record a checksum at creation: sha256sum for a dump, the provider's ETag for an object.

3.3 Record the row count of the 5 most important tables at backup time. A restore without a count is a guess.

3.4 Log the backup's start, end, size, duration and checksum to a table or a file that is not on the backed-up host.

set -euo pipefail
TS=$(date -u +%Y%m%dT%H%M%SZ)
OUT="/tmp/db-$TS.dump"
pg_dump -Fc --no-owner --no-privileges "$DATABASE_URL" -f "$OUT"
SHA=$(sha256sum "$OUT" | cut -d' ' -f1)
SIZE=$(stat -c%s "$OUT")
aws s3 cp "$OUT" "s3://backups-prod/postgres/$TS.dump" \
  --expected-size "$SIZE" --sse aws:kms
echo "$TS $SIZE $SHA" >> /var/log/backup-manifest.txt

Gate: every backup has a checksum, a size and a duration recorded off-host.

Stage 4 - Restore into a clean environment, not over production

4.1 A restore drill creates a fresh instance, restores into it, verifies, then destroys it. Never test by restoring over live data.

4.2 Script the whole drill: provision, restore, verify, report, teardown. A manual drill happens once and never again.

4.3 Time every phase and record it: provision, download, restore, verify. The RTO is the sum, not the download time.

4.4 Run the drill from a machine that is not the database host, with credentials a different person holds.

# drill.sh - full restore into a throwaway database
export PGPASSWORD="$DRILL_PW"
createdb -h "$DRILL_HOST" drill_restore
time pg_restore -h "$DRILL_HOST" -d drill_restore --no-owner --jobs 4 "$DUMP"
psql -h "$DRILL_HOST" -d drill_restore -Atc \
  "SELECT 'orders', count(*) FROM orders UNION ALL SELECT 'users', count(*) FROM users;"

Gate: the drill completes end to end from a script, on a machine that is not the source.

Stage 5 - Verify with numbers, not with a successful exit code

5.1 Compare row counts for the 5 critical tables against the counts recorded at backup time.

5.2 Run 3 real queries the application runs, and check they return plausible results - not just that they execute.

5.3 Check the newest row's timestamp is within the RPO window. A restore that ends 26 hours ago fails a 24-hour RPO.

5.4 Check that indexes, sequences, foreign keys and permissions came back. pg_restore --no-owner restores data without owners, which can break an app that relies on role permissions.

5.5 Spot-check 5 random rows against the source by primary key.

Gate: all five checks pass, and the newest row is inside the RPO window.

Stage 6 - Set the drill schedule and make it boring

6.1 Monthly full restore drill. Weekly automated verification of the checksum and the row counts without a full restore.

6.2 The drill owner is named, and the person who wrote the script must be able to be on leave during a drill.

6.3 Rotate who runs the drill every quarter. A drill only one person can run is a single point of failure.

6.4 Any failed drill blocks the release train until it is fixed. A red drill that nobody fixes is a false sense of safety.

Gate: 3 consecutive monthly drills recorded, one of them run by someone other than the author.

Stage 7 - Write the runbook for the real event

7.1 One page: how to find the latest good backup, how to restore, how to verify, how to point the app at the restored database.

7.2 Include the exact commands with placeholders clearly marked, and the names of the people who hold the credentials.

7.3 Include the decision: restore to a point in time, or restore the last nightly. Point-in-time needs the WAL archive, and the runbook must say where it is.

7.4 Practise reading the runbook aloud with someone who has never done it. Any step that needs explanation is a step that fails at 3am.

Gate: a person who has not done it before completes the drill using only the runbook.

Stage 8 - Cover the parts people forget

8.1 Secrets and configuration: the app's environment variables, the TLS certificates and the encryption keys. A restored database with no keys is a pile of ciphertext.

8.2 File uploads and generated documents, backed up separately from the database with the same RPO discipline.

8.3 The backup system itself: its configuration and credentials in a sealed copy, because the person who knows the login may be unavailable.

8.4 DNS and provider access: the ability to repoint a domain, with credentials not stored only in one person's account.

Gate: every row in the table has a tested restore path, not just a backup.

Stage 9 - Report it in numbers

9.1 Per drill: duration, rows restored, checks passed, RPO achieved, RTO achieved.

9.2 Trend the RTO month over month. A restore that creeps from 40 minutes to 4 hours is a capacity problem before it is an outage.

9.3 Cost per month of storage and drill time, so the trade-off against the risk is visible.

9.4 Keep the last 12 drill records in one file. That file is what answers a compliance question and what stops an argument after an incident.

Gate: 12 months of drill records exist, with both numbers per drill.

Worked example

A 12-person SaaS on a single Postgres instance, 40 GB, nightly dumps to the same provider.

The first drill failed: the dump restored but the sequences were not reset, so the app's next insert collided with an existing id. That failure had been sitting in production for 14 months and would have appeared on the worst possible day.

Quality checklist

• RPO and RTO written per data class and signed off
• Backups stored in a second account or provider
• Object versioning and lock enabled
• Encryption key stored outside the backup account
• Delete permission restricted to a break-glass role
• Retention tiers defined: 7 daily, 4 weekly, 12 monthly
• Backup method is dump or base backup, never a live directory tar
• Checksum and size recorded off-host per backup
• Row counts of 5 critical tables recorded at backup time
• Drill restores into a clean instance, never over production
• Drill scripted end to end, including teardown
• Row counts compared against backup-time counts
• Newest row inside the RPO window
• Sequences, indexes, foreign keys and permissions verified
• Runbook usable by someone who has never done it
• Secrets, uploads, certificates and DNS in the tested set

Failure modes

Copy-ready agent prompt

Read the skill at the URL above and set up a restore drill. Ask me: what I back up, where the backups live, whether they are in a second account, whether I have ever restored, and what my business can tolerate losing. Then give me the RPO and RTO table per data class, the off-host backup design with versioning, object lock and an external key, the backup command with checksum and row-count recording, the scripted drill that restores into a clean instance and tears it down, the five verification checks including sequences and permissions, the drill schedule with a rotated owner, the one-page runbook, and the table of assets beyond the database that need a tested restore path. Tell me the first thing that would fail if I restored today.