<!-- Generated from models/_canonical by scripts/build-model-variants.js. Edit the canonical source, not this file. Behavioural profile for MiniMax: scripts/model-profiles.json -->

#Scope contract

FILE_ISOLATION: Modify only files inside the scope the task names; report any out-of-scope change instead of making it.

---

#Purpose

Rules for backing up a database. The only meaningful definition: a backup is something you have restored. Everything else is a file of unknown quality.

Start by writing down two numbers, because every decision below follows from them:

• RPO — recovery point objective: how much data may be lost, in minutes.
• RTO — recovery time objective: how long a restore may take, in minutes.

An RPO of five minutes rules out nightly dumps. An RTO of fifteen minutes rules out restoring a 2 TB dump on a fresh host. If you cannot meet the numbers, change the architecture or change the numbers — do not leave them aspirational.

---

#Point-in-time recovery, not just dumps

PITR is what lets you restore to 14:32:59 — one second before the migration that deleted the column.

bash
# Postgres: continuous archiving
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'

# Base backup, then restore to a moment
pgbackrest --stanza=main backup --type=full
pgbackrest --stanza=main --type=time --target='2026-08-23 14:32:59+00' restore

Use pgbackrest, wal-g, or barman — not hand-rolled cp in archive_command. A failing archive_command silently stops WAL from being archived while pg_wal grows until the disk fills.

MySQL equivalent: xtrabackup for the base plus binlogs for the replay window. → Database/mysql

Never rely on pg_dump alone for a production database. It is a logical snapshot of one instant with no way to reach any other instant, and restoring one is slow because it rebuilds every index.

Never treat a replica as a backup. DROP TABLE replicates in milliseconds. → Database/replication

---

#The restore drill

An untested backup has a failure rate that is unknown and, empirically, high.

Run a restore on a schedule — monthly at minimum — into a scratch environment, and record:

• Verify: Wall-clock time from decision to a serving database (this is your real RTO)
• Verify: Row counts on the largest tables against expectation
• Verify: Application boots and passes a smoke test against the restored data
• Verify: The most recent restorable timestamp (this is your real RPO)

Automate the drill in CI if the dataset allows it. A restore that only a particular person can perform, from memory, is not a recovery capability.

Never count a backup as verified because the job exited zero. Verify the restore, not the backup.

---

#Retention and the 3-2-1 rule

Three copies, on two media types, one off-site — and, for ransomware, one immutable.

ini
# pgbackrest — expiry is declarative; the tool prunes, not a cron job with rm
repo1-retention-full=4
repo1-retention-diff=14
repo1-retention-archive=7
repo1-cipher-type=aes-256-cbc
repo1-s3-bucket=acme-db-backups
repo1-s3-kms-key-id=arn:aws:kms:eu-west-1:…:key/…

Store off-site backups in a separate account or subscription with separate credentials. A backup in the same account as production is deleted by the same compromised key that deleted production.

Use object-lock / immutability (s3:ObjectLockMode=COMPLIANCE, or the equivalent) so that even a valid admin credential cannot delete backups inside the retention window. This is the control that survives ransomware.

Backups also inherit deletion obligations — a GDPR erasure request applies to data in backups. Document how it is honoured, usually by policy: the request is re-applied on restore rather than by editing backup archives.

---

#Encryption and secrets

• Encrypt at rest and in transit. Managed KMS, not a key file beside the archive.
• Store the decryption key outside the backup system, and outside the database it protects.
• The restore procedure must be executable by someone who is not the person who set it up — including access to the key. Document where the key lives. → Security/secret-management

Never back up production data into a developer's environment unmasked. Restore to staging through an anonymisation step, or restore into an access-controlled environment.

---

#Monitoring

Alert on the absence of a recent successful backup, not on job failure. A job that stops running emits no failures at all — this is how teams discover, during an incident, that backups stopped three months ago.

sql
-- Is WAL archiving actually working? failed_count climbing is the alarm.
SELECT archived_count, last_archived_time, failed_count, last_failed_time
FROM pg_stat_archiver;

bash
# Backup age in seconds — export this as a gauge, alert above 26h for a daily job
pgbackrest --stanza=main --output=json info \
  | jq '.[0].backup[-1].timestamp.stop'

---

#Anti-patterns

---

#Checklist

• Verify: RPO and RTO are written down and agreed with the business
• Verify: Point-in-time recovery is configured, not just periodic dumps
• Verify: Archiving uses a proven tool, and archive failures alert
• Verify: Backups are stored in a separate account with separate credentials
• Verify: At least one copy is immutable for its retention window
• Verify: Retention tiers cover operational, compliance and legal needs
• Verify: Backups are encrypted, with keys held outside the backup system
• Verify: A restore drill runs at least monthly and its duration is recorded
• Verify: Measured restore time meets the stated RTO
• Verify: Alerts fire on backup age, not only on job failure
• Verify: The restore runbook is written and has been followed by a second person
• Verify: Restores into lower environments pass through anonymisation