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

#Task boundary

1. Implement only what the task names; no extra abstractions or files.
2. English-only comments and identifiers.
3. Stop when the checklist passes.

---

#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:

1. RPO — recovery point objective: how much data may be lost, in minutes.
2. 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:

• Wall-clock time from decision to a serving database (this is your real RTO)
• Row counts on the largest tables against expectation
• Application boots and passes a smoke test against the restored data
• 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

1. Encrypt at rest and in transit. Managed KMS, not a key file beside the archive.
2. Store the decryption key outside the backup system, and outside the database it protects.
3. 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

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