Backup and Restore skill for Claude Code

A backup strategy exists only when a restore has been performed from it recently, timed, and verified with queries. Everything else is a hope. Done well means: written RPO and RTO targets, a mechanism that meets them, alerts when backups or WAL archiving fail, copies the production credentials cannot delete, and a dated restore drill report.

Ground rules

• Restore to a new, separate instance or directory by default. Restoring over a live database, dropping a database, or promoting a restored copy to production requires explicit user confirmation, naming the target host.
• Never test restores against production. Never run pg_restore --clean or DROP DATABASE on a target you have not identified with SELECT inet_server_addr(), current_database();.
• In an incident (accidental DELETE), first stop further damage and preserve the current state (snapshot or dump) before restoring anything.

Workflow

- [ ] 1. Get RPO and RTO targets per database (ask; do not assume)
- [ ] 2. Inventory current backups: method, schedule, retention, location, last success
- [ ] 3. Choose the mechanism that meets the targets (table below)
- [ ] 4. Configure, encrypt, and copy off-site/immutable
- [ ] 5. Add monitoring for backup age and WAL archive failures
- [ ] 6. Run a restore drill; record actual RTO and data loss window
- [ ] 7. Write the backup plan and drill report

1. Targets

• RPO (recovery point objective): how much data, measured in time, the business can lose. Determines backup frequency / WAL shipping.
• RTO (recovery time objective): how long until service is restored. Determines restore method, database size limits, and whether you need a standby.

RPO / RTO	Mechanism
RPO 24 h, RTO hours, DB < ~50 GB	Nightly logical dump (pg_dump) to object storage
RPO minutes, RTO < 1-2 h	Physical base backups + continuous WAL archiving (PITR) via pgBackRest/WAL-G, or managed PITR
RPO ~0, RTO minutes	Streaming replica / HA failover (Patroni, managed Multi-AZ) plus PITR backups
Protection from bad deploys / accidental deletes	PITR (replicas replicate the DELETE instantly)

Replicas are not backups: they copy corruption and deletions within milliseconds.

2. Inventory

-- Is WAL archiving on and healthy?
SHOW archive_mode;
SHOW archive_command;        -- or archive_library
SELECT archived_count, last_archived_wal, last_archived_time,
       failed_count, last_failed_wal, last_failed_time
FROM pg_stat_archiver;
SELECT pg_size_pretty(pg_database_size(current_database()));

Managed services: check automated backup retention, PITR window, snapshot schedule, cross-region copies, and deletion protection in the console/CLI (e.g. aws rds describe-db-instances fields BackupRetentionPeriod, LatestRestorableTime).

3-4. Mechanisms

Logical backups (pg_dump) - portable across major versions, per database, slow to restore for large data, no PITR.

# Custom format (compressed, selective restore, parallel restore)
pg_dump -Fc -d "$SRC_URL" -f app_$(date +%F).dump
# Directory format with parallel dump for larger DBs
pg_dump -Fd -j 8 -d "$SRC_URL" -f app_$(date +%F).dir
# Roles and tablespaces (not included in pg_dump)
pg_dumpall --globals-only -d "$SRC_URL" -f globals.sql

# Restore into a NEW empty database
createdb -h restore-host app_restore
pg_restore -j 8 --no-owner --no-privileges -d "postgresql://.../app_restore" app_2026-10-19.dump
pg_restore -l app_2026-10-19.dump | head            # list contents without restoring

• pg_dump takes a consistent snapshot without blocking normal reads/writes, but holds a long transaction (affects vacuum) and conflicts with DDL; run off-peak.
• Use the pg_dump / pg_restore version matching the newer of source and target.
• Managed services often restrict pg_dumpall --globals-only (no superuser); recreate roles from infrastructure code instead.

Physical backups + WAL archiving (PITR) - whole cluster, fast restore, any point in time within retention. Use a dedicated tool rather than hand-written archive_command:

# pgBackRest (after configuring pgbackrest.conf with a repo, e.g. S3, encryption enabled)
pgbackrest --stanza=main stanza-create
pgbackrest --stanza=main check                       # verifies archiving end to end
pgbackrest --stanza=main --type=full backup          # weekly
pgbackrest --stanza=main --type=diff backup          # daily
pgbackrest --stanza=main info                        # backups and WAL range available

Postgres settings for archiving: wal_level = replica (or logical), archive_mode = on, archive_command set to the tool’s push command (pgbackrest --stanza=main archive-push %p), and archive_timeout (e.g. 60s) to bound RPO on quiet databases.

Built-in tools (no extra software):

pg_basebackup -h primary -U replicator -D /backups/base_$(date +%F) -Ft -z -X stream -P -c fast
pg_verifybackup /backups/base_2026-10-19          # plain-format backups; checks manifest
# PostgreSQL 17+: incremental backups (requires summarize_wal = on)
pg_basebackup -D /backups/incr_mon --incremental=/backups/full_sun/backup_manifest
pg_combinebackup /backups/full_sun /backups/incr_mon -o /restore/data

Point-in-time restore (generic Postgres 12+ procedure):

1. Restore the base backup into an empty data directory on the restore host.
2. Configure recovery in postgresql.conf (or postgresql.auto.conf):

restore_command = 'pgbackrest --stanza=main archive-get %f "%p"'
recovery_target_time = '2026-10-19 09:14:00+00'   # just before the bad statement
recovery_target_action = 'pause'                  # inspect first, then promote

3. touch $PGDATA/recovery.signal and start Postgres. With pgBackRest the whole thing is pgbackrest --stanza=main --type=time "--target=2026-10-19 09:14:00+00" --target-action=pause restore (add --delta only when deliberately restoring over an existing data directory).
4. While paused, verify the data (SELECT max(created_at) ..., check the deleted rows exist). Then SELECT pg_wal_replay_resume(); to promote, or adjust the target and repeat.
5. For accidental deletes, usually copy the missing rows from the restored instance back to production (COPY ... TO / \copy or pg_dump -t table --data-only) instead of replacing production wholesale. Confirm the approach with the user.

Find the bad statement’s time from application logs, log_statement output, or audit tables; pick a target a few seconds earlier.

Managed databases

Service	Backups	Restore
AWS RDS / Aurora	Automated backups with PITR (retention up to 35 days), manual snapshots	Always restores to a new instance/cluster; switch connection strings after verification
Google Cloud SQL	Automated backups + PITR via binary logs / WAL	Restores to new or existing instance (existing = overwrite; confirm)
Azure Database for PostgreSQL	Automated backups with PITR	New server
Supabase / Neon	PITR add-on / branching	Restore or branch at a timestamp

Still take periodic logical dumps to storage in a different account: provider-level backups disappear with the account or a deleted instance (unless retained explicitly).

MySQL

mysqldump --single-transaction --routines --triggers --events --set-gtid-purged=OFF \
  -h src -u backup -p app | zstd > app_$(date +%F).sql.zst
# Larger: MySQL Shell dump utilities (util.dumpInstance / util.loadDump) or Percona XtraBackup
# PITR: restore full backup, then replay binlogs up to the bad event
mysqlbinlog --stop-datetime="2026-10-19 09:14:00" binlog.000123 binlog.000124 | mysql -h restore-host -u root -p

--single-transaction gives a consistent snapshot only for InnoDB tables.

Storage, security, retention

• 3-2-1: at least three copies, two media/services, one off-site. Put one copy in a separate cloud account with object lock / immutability so compromised production credentials cannot delete backups.
• Encrypt backups at rest (tool-level encryption such as pgBackRest repo1-cipher-type, or bucket KMS) and in transit. Store keys separately; a backup you cannot decrypt is not a backup.
• Retention example: 7 daily, 4 weekly, 12 monthly; PITR window 7-14 days. Align with legal retention and deletion obligations (GDPR erasure requests may require expiring old backups on a schedule).
• Dumps contain all personal data. Restrict access, and never copy production backups to laptops or dev environments without anonymization and the user’s approval.

5. Monitoring

Alert on:

• Age of the last successful backup > 1.5x the schedule interval.
• pg_stat_archiver.failed_count increasing, or last_archived_time older than archive_timeout + a margin.
• pg_wal directory growth (failed archiving retains WAL until the disk fills and the database stops).
• Backup size changing abruptly (+/- 30%): truncated dumps or runaway growth.

6. Restore drill

Run at least quarterly and after any backup change:

1. Pick a random backup (not always the latest) and a random PITR target.
2. Restore to an isolated instance; time each phase (download, restore, WAL replay).
3. Verify:

SELECT count(*) FROM orders;                               -- compare with production snapshot counts
SELECT max(created_at) FROM orders;                        -- matches the target time
SELECT pg_size_pretty(pg_database_size(current_database()));
-- Optional: checksums on a stable subset
SELECT md5(string_agg(id::text || total_cents::text, ',' ORDER BY id)) FROM orders WHERE id < 100000;

4. Point a staging build of the application at it and run smoke tests.
5. Tear the instance down (it contains production data).

Gotchas

• pg_dump of one database misses roles, grants to roles that do not exist on the target, and other databases in the cluster.
• WAL archives without a base backup, or a base backup without the WAL from during its run, cannot be restored. Tools like pgBackRest enforce this; hand-rolled scripts often do not.
• A restored database has stale statistics: run ANALYZE (or vacuumdb --analyze-in-stages) before judging performance.
• Sequences and identity values are restored with the dump; copying rows back for partial recovery must not collide with IDs issued since.
• Large restores are bounded by network and disk throughput. Measure: 1 TB at 200 MB/s is ~1.5 hours before WAL replay even starts. If that breaks the RTO, you need a standby or smaller databases.
• Time zones in recovery_target_time: always include the offset.

Output: backup plan and drill report

## Backup plan: orders-db (Postgres 17, 420 GB)
**Targets:** RPO 5 min, RTO 2 h (agreed with product 2026-10-01)
**Mechanism:** pgBackRest to S3 (account backup-prod, object lock 30 d, AES-256)
- full weekly Sun 02:00, diff daily, WAL archive continuous, archive_timeout 60s
- retention: 4 full, PITR window 14 days; monthly logical dump kept 12 months
**Monitoring:** backup age > 36 h, archiver failed_count > 0, pg_wal > 20 GB -> page on-call

## Restore drill 2026-10-19
**Backup:** full 2026-10-12 + WAL to 2026-10-17 14:03:00Z (random target)
**Timings:** download 22 min, restore 31 min, WAL replay 18 min -> RTO 71 min (target 120)
**Verification:** row counts within 0.01% of prod snapshot; max(created_at) 14:02:58Z; app smoke tests pass
**Issues:** restore host disk was too small on first try (fixed: runbook now specifies 1 TB)