Database Backup & Restore

# Database Backup & Restore

## PostgreSQL dump
```bash
# Full dump (custom format, compressed)
pg_dump -U postgres -d mydb -Fc -f mydb_$(date +%Y%m%d_%H%M%S).dump

# Plain SQL (human-readable)
pg_dump -U postgres -d mydb > mydb.sql

# Specific tables only
pg_dump -U postgres -d mydb -t users -t orders -Fc -f partial.dump

# Restore
pg_restore -U postgres -d mydb_new -Fc mydb.dump
psql -U postgres -d mydb_new < mydb.sql

# Restore to new DB
createdb -U postgres mydb_new
pg_restore -U postgres -d mydb_new mydb.dump
```

## Automated backup script to S3
```bash
#!/bin/bash
# /opt/scripts/backup-db.sh
set -euo pipefail

DB_NAME="mydb"
DB_USER="postgres"
BUCKET="s3://my-backups/postgres"
FILE="${DB_NAME}_$(date +%Y%m%d_%H%M%S).dump"
RETAIN_DAYS=30

# Dump
pg_dump -U $DB_USER -d $DB_NAME -Fc -f /tmp/$FILE

# Upload to S3
aws s3 cp /tmp/$FILE $BUCKET/$FILE
rm /tmp/$FILE

# Remove old backups
aws s3 ls $BUCKET/ | awk '{print $4}' | sort | head -n -$RETAIN_DAYS | \
  xargs -I{} aws s3 rm $BUCKET/{}

echo "Backup complete: $FILE"
```
```bash
# Add to crontab (daily at 2am)
crontab -e
0 2 * * * /opt/scripts/backup-db.sh >> /var/log/db-backup.log 2>&1
```

## Docker Compose backup
```bash
docker compose exec db pg_dump -U app -d appdb -Fc > backup.dump
docker compose exec -T db pg_restore -U app -d appdb < backup.dump
```

## Point-in-time recovery (PostgreSQL WAL)
```bash
# postgresql.conf
archive_mode = on
archive_command = 'aws s3 cp %p s3://my-wal-archive/%f'
wal_level = replica
# Restore: use pg_basebackup + WAL files to recover to specific timestamp
```

## Managed DB backups (RDS / Supabase / Neon)
- Enable automated backups: 7-35 day retention
- RDS: `aws rds create-db-snapshot --db-instance-identifier mydb --db-snapshot-identifier snap-1`
- Test restores regularly to a separate instance

## Redis persistence
```bash
# redis.conf options:
# RDB (point-in-time snapshots)
save 900 1      # save if 1 key changed in 900s
save 300 10     # save if 10 keys changed in 300s
dbfilename dump.rdb
dir /var/lib/redis

# AOF (append-only log, better durability)
appendonly yes
appendfsync everysec   # fsync every second (balance)
# appendfsync always   # fsync every write (safest, slower)
```
```bash
# Manual snapshot
redis-cli BGSAVE
# Copy dump.rdb to backup location
cp /var/lib/redis/dump.rdb /backup/redis_$(date +%Y%m%d).rdb
```