Backup Verification, Restore Drills, RPO/RTO Testing, and Immutable Backup Design

Learning outcomes

A backup program becomes trustworthy only when recovery is rehearsed under measurable objectives. ServiceHub therefore needs a recurring restore drill that answers four questions: what point can we recover to, how long does it take, what application state is valid, and what critical assets exist outside PostgreSQL's backup artifact?

1. Build a restore-drill record

Each drill should record source backup identity, start/end time, target recovery point, artifact checksums, PostgreSQL/client versions, restore host, storage class, operator, validation queries, exceptions, and decision. Store the record outside the database being tested so a database incident cannot erase the evidence.

sha256sum servicehub_ch13.dump > servicehub_ch13.dump.sha256sha256sum -c servicehub_ch13.dump.sha256

SELECT count(*) AS event_rows,       min(event_id), max(event_id),       count(DISTINCT recovery_key) AS unique_keysFROM app.ch13_recovery_events;SELECT extname, extversion FROM pg_extension ORDER BY extname;SELECT rolname FROM pg_roles WHERE rolname LIKE 'servicehub_%' ORDER BY rolname;

2. RPO is about the recovered point, not backup age alone

For a logical dump, recovered state usually corresponds to the dump's consistent snapshot. For PITR, the effective recovery point depends on how far archived WAL extends and which target you choose. Measure the gap between the latest business transaction you needed and the latest state you can actually restore.

INSERT INTO app.ch13_recovery_events (recovery_key, work_order_id, state)VALUES ('rpo-marker-20260818-001', 9911, 'committed');SELECT clock_timestamp() AS marker_time,       pg_current_wal_insert_lsn() AS marker_lsn;

After restoring, query for that marker. If it is absent, record the actual last recovered marker/time and compute observed data loss. Do not report an RPO target as though it were an observed result.

3. RTO includes validation and readiness

Start the RTO clock when the organization begins recovery, not when pg_restore starts reading data. Stop it when the restored service has passed agreed validation and is ready for traffic. DNS changes, secret delivery, extension installation, post-restore ANALYZE, application smoke tests, and approvals can dominate database-copy time.

DRILL_START=$(date -Iseconds)# restore, start PostgreSQL, validate invariants, run application smoke testsDRILL_END=$(date -Iseconds)printf 'start=%s\nend=%s\n' "$DRILL_START" "$DRILL_END" > ch13_restore_drill.txt

4. Verification has layers

Artifact integrity asks whether backup bytes match expected checksums or a base-backup manifest. Database integrity asks whether PostgreSQL starts and catalogs/constraints are coherent. Business integrity asks whether application invariants hold. Operational readiness asks whether authentication, secrets, network routing, observability, and dependent services are ready. A successful pg_verifybackup only addresses part of the first layer.

SELECT current_database(), current_user, version();SELECT conrelid::regclass, conname, contype, convalidatedFROM pg_constraintWHERE connamespace = 'app'::regnamespaceORDER BY 1,2;SELECT schemaname, tablename, tableownerFROM pg_tablesWHERE schemaname='app'ORDER BY tablename;SELECT has_schema_privilege('servicehub_app','app','USAGE') AS app_usage;

pg_amcheck --database=servicehub_restore_lab --all --verbose

pg_amcheck can provide additional structural evidence for supported access methods. It is not a substitute for application-level validation or a guarantee against every corruption class.

5. Database backups do not automatically contain everything ServiceHub needs

Logical dumps do not preserve operating-system files such as postgresql.conf, pg_hba.conf, TLS private keys, systemd/container manifests, DNS/load-balancer configuration, application secrets, or files stored in an external object store. Continuous WAL archiving also does not capture manual configuration-file edits. Physical backups may include configuration when configuration files live inside the data directory, but production designs often deliberately keep them elsewhere.

database backup artifactbackup manifest / artifact checksumroles and tablespaces source of truthpostgresql.conf / pg_hba.conf / pg_ident.confTLS certificates and private-key recovery pathsecret-manager references (not plaintext secrets in the backup)extension packages and versionsapplication deployment/configurationDNS/load-balancer/fencing procedureexternal object-storage backup and reconciliation procedure

6. Off-host and immutable design

If ransomware or a privileged operator can delete both production and every backup with the same credentials, the backup architecture has a common-mode failure. Keep at least one recovery copy off the database host and protect a retention tier from routine overwrite/delete privileges. “Immutable” should mean a verified control enforced by the storage system/process, not merely a file named read-only.

Retention must cover base backups, WAL needed to make those backups useful, logical exports where object-level recovery is needed, and evidence required by policy. Periodically delete according to a tested retention algorithm, not ad hoc disk pressure.

7. Final Chapter 13 drill

Run one logical restore and one physical/PITR restore into disposable targets. For each, record artifact identity, target, observed start/end times, business marker recovered, validation result, missing dependencies, and operator notes. The output of this chapter should be a recovery evidence packet that another operator can follow—not merely five command transcripts.

DROP DATABASE IF EXISTS servicehub_restore_lab;DROP TABLE IF EXISTS app.ch13_recovery_events;DROP TABLE IF EXISTS app.ch13_pitr_demo;

Authoritative references

Backup and recovery behavior is version-, topology-, privilege-, and storage-sensitive. These primary PostgreSQL sources define the mechanisms used in this lesson.

• PostgreSQL 18 — Backup and Restore
• PostgreSQL 18 — pg_verifybackup
• PostgreSQL 18 — pg_amcheck
• PostgreSQL 18 — Continuous Archiving and PITR
• PostgreSQL 18 — pg_dump