Database backup automation

port { promisify } from 'util'; import { createHash } from 'crypto'; import { S3Client, PutObjectCommand } from '@aws-sdk/client-s3'; import { Readable } from 'stream';

const execAsync = promisify(exec);

interface BackupConfig { dbType: 'postgres' | 'mysql'; host: string; port: number; user: string; password: string; database: string; bucket: string; prefix: string; kmsKeyId: string; }

async function createBackupStream(config: BackupConfig): Promise<Readable> { const dumpCmd = config.dbType === 'postgres' ? PGPASSWORD=${config.password} pg_dump -h ${config.host} -p ${config.port} -U ${config.user} -Fc -d ${config.database} : MYSQL_PWD=${config.password} mysqldump -h ${config.host} -P ${config.port} -u ${config.user} --single-transaction --routines --triggers ${config.database};

const { stdout } = await execAsync(dumpCmd, { maxBuffer: 1024 * 1024 * 512 }); return Readable.from(stdout); }

async function computeChecksum(stream: Readable): Promise<string> { const hash = createHash('sha256'); for await (const chunk of stream) { hash.update(chunk); } return hash.digest('hex'); }

async function uploadToS3(stream: Readable, config: BackupConfig, checksum: string): Promise<void> { const s3 = new S3Client({ region: process.env.AWS_REGION }); const key = ${config.prefix}/${new Date().toISOString()}-backup-${checksum.slice(0, 8)}.dump;

await s3.send(new PutObjectCommand({ Bucket: config.bucket, Key: key, Body: stream, ServerSideEncryption: 'aws:kms', SSEKMSKeyId: config.kmsKeyId, Metadata: { 'x-amz-meta-checksum': checksum, 'x-amz-meta-database': config.database, 'x-amz-meta-backup-type': 'full' } })); }

export async function runBackup(config: BackupConfig): Promise<void> { const stream = await createBackupStream(config); const checksum = await computeChecksum(stream); const resetStream = Readable.from(stream.read() || '');

await uploadToS3(resetStream, config, checksum); console.log(Backup completed. Checksum: ${checksum}); }

### Architecture Decisions & Rationale
- **Database-native tools over file copies**: Ensures transactional consistency, handles schema/permissions, and supports incremental/WAL streaming.
- **Checksum + Metadata tagging**: Enables automated validation, prevents silent corruption, and supports audit trails.
- **KMS envelope encryption**: Decouples data encryption from key management, enables rotation without re-encrypting backups, and satisfies compliance requirements.
- **Stream-based processing**: Avoids disk I/O bottlenecks, reduces temporary storage requirements, and enables parallel upload chunks.
- **Idempotent upload keys**: Prevents duplicate artifacts during retries, ensuring deterministic storage lifecycle management.

## Pitfall Guide

1. **Skipping restore drills**: Backups are only valuable if they restore correctly. Schedule automated restore tests to isolated environments weekly. Validate row counts, index integrity, and application connectivity.
2. **Backing up without consistency guarantees**: File-level copies or unquiesced dumps produce corrupted or partially committed states. Use `--single-transaction` (MySQL), `pg_dump -Fc`, or WAL/binlog archiving for crash-consistent backups.
3. **Storing backups on the same host or availability zone**: Shared failure domains negate backup value. Use cross-region object storage, immutable policies, and separate IAM roles.
4. **Missing encryption or KMS rotation**: Unencrypted backups violate compliance and expose sensitive data. Implement envelope encryption, rotate keys annually, and audit access logs.
5. **No retention or lifecycle policy**: Unbounded storage growth increases cost and compliance risk. Define tiered retention (hot/warm/cold), automate expiration, and align with legal hold requirements.
6. **Ignoring transaction logs for PITR**: Full backups alone cannot recover to a specific point. Archive WAL/binlog continuously, verify log continuity, and test point-in-time recovery procedures.
7. **Alerting on backup start instead of completion**: Operators waste time investigating false positives. Alert on failure, retry exhaustion, checksum mismatch, or validation drift. Include runbook links in alert payloads.

**Best Practices from Production**:
- Treat backups as versioned artifacts with immutable storage policies.
- Separate backup execution from database load using dedicated compute or read replicas.
- Implement structured logging with correlation IDs for end-to-end traceability.
- Enforce least-privilege IAM: backup runner, storage writer, and restore operator roles must be distinct.
- Measure backup health with SLOs: success rate >99.5%, validation pass rate 100%, RPO/RTO within policy.

## Production Bundle

### Action Checklist
- [ ] Define RPO/RTO targets and consistency model (logical vs physical + WAL/binlog)
- [ ] Provision cross-region object storage with versioning, WORM retention, and KMS encryption
- [ ] Implement backup pipeline with database-native tools, checksum verification, and stream upload
- [ ] Schedule automated restore drills to isolated environments with validation thresholds
- [ ] Configure lifecycle policies for retention tiering and automatic expiration
- [ ] Instrument metrics, structured logs, and failure-only alerts with runbook links
- [ ] Audit IAM roles, key rotation schedules, and compliance evidence collection quarterly

### Decision Matrix

| Scenario | Recommended Approach | Why | Cost Impact |
|----------|---------------------|-----|-------------|
| Small startup (<10GB DB) | Logical backups + cron + S3 with lifecycle | Low operational overhead, sufficient for RPO 1–4h | Minimal ($5–15/mo storage + compute) |
| Mid-scale SaaS (10GB–1TB) | Physical backups + WAL/binlog archiving + automated validation | Guarantees sub-15min RPO, handles high write throughput | Moderate ($50–200/mo storage + compute + validation env) |
| Regulated enterprise (>1TB, HIPAA/SOC2) | Multi-region physical backups + immutable WORM + automated restore drills + KMS rotation | Meets compliance, ensures disaster recovery, eliminates manual drift | High ($200–800/mo storage + cross-region replication + compliance tooling) |

### Configuration Template
```json
{
  "backup": {
    "database": {
      "type": "postgres",
      "host": "${DB_HOST}",
      "port": 5432,
      "user": "${DB_USER}",
      "password": "${DB_PASSWORD}",
      "name": "${DB_NAME}"
    },
    "storage": {
      "provider": "s3",
      "bucket": "${BACKUP_BUCKET}",
      "prefix": "db-backups/prod",
      "region": "${AWS_REGION}",
      "kmsKeyId": "${KMS_KEY_ID}",
      "retention": {
        "hot": "30d",
        "warm": "90d",
        "cold": "365d",
        "deleteExpired": true
      }
    },
    "validation": {
      "enabled": true,
      "restoreTarget": "isolated-env",
      "checks": ["checksum", "row-count", "schema-integrity"],
      "schedule": "0 3 * * 0"
    },
    "observability": {
      "metrics": ["duration_ms", "size_bytes", "success_rate"],
      "alerts": {
        "onFailure": true,
        "onValidationMismatch": true,
        "webhook": "${SLACK_WEBHOOK}"
      }
    }
  }
}

Quick Start Guide

1. Install dependencies: npm install @aws-sdk/client-s3 child_process util crypto stream
2. Set environment variables: DB_HOST, DB_USER, DB_PASSWORD, DB_NAME, BACKUP_BUCKET, AWS_REGION, KMS_KEY_ID, SLACK_WEBHOOK
3. Run dry execution: node backup-runner.js --dry-run to verify connectivity, dump generation, and checksum computation without uploading
4. Schedule pipeline: Add to cron or CI/CD scheduler with 0 2 * * * for nightly full backups, and configure WAL/binlog streaming for continuous archiving
5. Validate: Trigger a manual restore to an isolated environment, verify row counts and schema integrity, and confirm alert routing works on simulated failure