Chapter 16 · Security, Reliability, and Governance
Backups, Restore Testing, and Recovery Objectives
A backup is only a recovery input. Reliability comes from a tested path that restores the right data, reaches an acceptable point in time, verifies integrity, and returns service within a measured objective.
Learning outcomes
Engineer recovery, not merely backup creation
Define recovery point objective, recovery time objective, and restore scope.
Compare logical dumps, file-level copies, and continuous recovery mechanisms.
Create a consistent live SQLite backup through the backup API.
Build manifests, integrity checks, and application-level verification into a restore drill.
Measure restore performance and close gaps between objectives and observed results.
Translate tolerance into objectives
The recovery point objective limits acceptable data loss:
\[\mathrm{RPO} = t_{failure} - t_{latest\ recoverable\ point}\]The recovery time objective limits service disruption:
\[\mathrm{RTO} = t_{service\ restored} - t_{incident\ declared}\]| Question | Example answer | Design implication |
|---|---|---|
| How much committed data may be lost? | At most 5 minutes | Backups alone may be insufficient; continuous log capture may be needed |
| How quickly must core reads return? | Within 30 minutes | Automated restore, infrastructure, credentials, and runbooks must fit the budget |
| What scope must be recovered? | One database and its encryption keys | The backup set must include dependencies and key recovery |
| What consistency point is required? | Orders and payments from the same point | Coordinate dependent systems or design reconciliation |
Backup families
Logical dump
Portable SQL or archive representation. Useful for selective restore and migration, but restore time may be long.
Physical or file backup
Copies database storage. Fast for whole-system recovery but tied more closely to engine and version.
Continuous recovery
Combines a base backup with change logs so recovery can reach a later time or named target.
Replica
Improves availability and read scaling, but normally propagates accidental deletes and is not a substitute for independent backups.
SQLite live backup
Copying only the main database file while it is active can miss associated journal state. Use SQLite’s Online Backup API, VACUUM INTO, or an engine-aware snapshot procedure.
from __future__ import annotationsimport hashlibimport jsonimport sqlite3from datetime import datetime, timezonefrom pathlib import Pathdef sha256(path: Path) -> str: digest = hashlib.sha256() with path.open("rb") as stream: for block in iter(lambda: stream.read(1024 * 1024), b""): digest.update(block) return digest.hexdigest()def backup_sqlite(source_path: Path, backup_path: Path) -> Path: backup_path.parent.mkdir(parents=True, exist_ok=True) with sqlite3.connect(source_path) as source, sqlite3.connect(backup_path) as target: source.backup(target, pages=256) with sqlite3.connect(backup_path) as restored: integrity = restored.execute("PRAGMA integrity_check").fetchone()[0] if integrity != "ok": raise RuntimeError(f"backup integrity failed: {integrity}") row_count = restored.execute("SELECT COUNT(*) FROM sales_order").fetchone()[0] manifest = { "created_at": datetime.now(timezone.utc).isoformat(), "source": str(source_path), "backup": str(backup_path), "sha256": sha256(backup_path), "sales_order_rows": row_count, "integrity_check": integrity, } manifest_path = backup_path.with_suffix(backup_path.suffix + ".json") manifest_path.write_text(json.dumps(manifest, indent=2), encoding="utf-8") return manifest_pathRestore is a separate operation
A restore drill must avoid overwriting production and must verify business correctness, not merely database startup.
PRAGMA foreign_keys = ON;PRAGMA integrity_check;PRAGMA foreign_key_check;SELECT COUNT(*) AS customer_rows FROM customer;SELECT COUNT(*) AS order_rows FROM sales_order;-- Application invariants.SELECT order_idFROM sales_orderWHERE total_cents < 0;SELECT o.order_idFROM sales_order AS oLEFT JOIN customer AS c ON c.customer_id = o.customer_idWHERE o.customer_id IS NOT NULL AND c.customer_id IS NULL;PostgreSQL recovery choices
# Custom-format logical backup.pg_dump --format=custom --file=academy.dump academy# Inspect before restore.pg_restore --list academy.dump# Restore into an isolated database.createdb academy_restore_testpg_restore --clean --if-exists --no-owner --dbname=academy_restore_test academy.dumpBase backup + complete required WAL sequence -> restore cluster files -> replay WAL -> stop at latest available point, timestamp, transaction, or restore point -> verify database and application invariantsA logical pg_dump is not a physical base backup and cannot replace the WALrequirements of continuous archiving.Recovery drill evidence
| Evidence | Why it matters |
|---|---|
| Backup identifier, timestamp, engine version, checksum | Proves which artifact was used and detects corruption |
| Key and credential recovery result | Encrypted backups are useless without controlled key recovery |
| Restore start and service-ready timestamps | Provides measured RTO rather than an estimate |
| Recovered point in time or latest transaction | Provides measured RPO |
| Integrity and business-query results | Detects structurally valid but operationally incomplete restores |
| Operator notes and failed steps | Improves automation and runbook quality |
Recovery review
- Why is a successful backup job not proof of recoverability?
- Why is replication not automatically a backup?
- Which value is measured by the age of the latest recoverable point?
- What should be restored before a production incident occurs?
Review the answers
The artifact may be corrupt, incomplete, inaccessible, too slow to restore, or missing keys and dependencies. Replicas usually copy destructive changes. The age determines RPO. Restore representative backups into isolated environments on a recurring schedule and measure the result.
Summary and references
- Define RPO, RTO, scope, and consistency requirements before choosing tools.
- Protect backups independently from the production account and failure domain.
- Encrypt backup data and test controlled recovery of keys.
- Restore regularly into an isolated target and verify engine plus business invariants.
- Record measured RPO/RTO and automate the slowest or most failure-prone steps.