Design Database Backup & Recovery with Claude Code: Automated Backups, PITR, Monthly Recovery Tests

#claudecode #postgres #aws #devops

Introduction

Having backups but never testing recovery is dangerous — those backups might be unusable. Design automated backup + PITR + monthly recovery testing automation with Claude Code.

CLAUDE.md Rules

## DB Backup Rules
- Full backup: nightly at 2 AM
- WAL archive: continuous (PITR)
- Retention: 30 days prod, 7 days staging
- Encryption: AES-256 (S3 SSE-KMS)
- RTO: < 4 hours, RPO: < 1 hour
- Monthly automated recovery test

Generated Implementation

// src/backup/backupService.ts
export async function performFullBackup(): Promise<BackupResult> {
  const timestamp = new Date().toISOString().replace(/[:.]/g, '-');
  const backupFile = `/tmp/backup-${timestamp}.dump`;

  // pg_dump custom format (parallel restore support)
  await execAsync(`pg_dump --format=custom --compress=0 --dbname="${DATABASE_URL}" --file="${backupFile}"`);

  // Gzip compress (60-70% size reduction)
  await pipeline(createReadStream(backupFile), createGzip({ level: 6 }), createWriteStream(backupFile + '.gz'));

  // Upload to S3 with KMS encryption
  await s3.send(new PutObjectCommand({
    Bucket: BACKUP_BUCKET,
    Key: `backups/full/${date}/${timestamp}.dump.gz`,
    Body: createReadStream(backupFile + '.gz'),
    ServerSideEncryption: 'aws:kms',
    SSEKMSKeyId: process.env.BACKUP_KMS_KEY_ID,
  }));
}

// Monthly recovery test
export async function performRecoveryTest() {
  const testDb = `recovery_test_${Date.now()}`;
  try {
    // Download latest backup and restore
    await execAsync(`aws s3 cp s3://${BUCKET}/${latestBackup.s3Key} /tmp/${testDb}.dump.gz`);
    await execAsync(`createdb ${testDb}`);
    await execAsync(`gunzip -c /tmp/${testDb}.dump.gz | pg_restore --dbname=${testDb}`);

    // Integrity checks: row counts, foreign keys
    const checks = await runIntegrityChecks(testDb);
    const passed = checks.every(c => c.passed);

    if (!passed) await sendAlert('backup_test_failed', { checks });
    return { passed, checks };
  } finally {
    await execAsync(`dropdb --if-exists ${testDb}`);
  }
}