gabriel / musehub public
backup.sh bash
86 lines 3.9 KB
Raw
sha256:bee12c5cbde2334f98421c6c209d768fa6b8004d6705c9ea798ce6c1651bc11f Merge 'infra/database-phase3-4-cleanup' into 'dev' — propos… Human 14 hours ago
1 #!/usr/bin/env bash
2 # Postgres secondary backup script — run via cron on the EC2 instance.
3 #
4 # As of 2026-09-08 both staging and production run on managed AWS RDS, which
5 # already provides daily automated backups and point-in-time recovery
6 # (7-day retention) natively — see docs/database-architecture.md. This
7 # script is now a belt-and-suspenders SECONDARY backup, not the primary
8 # mechanism: it exists to diversify failure modes RDS's own backups don't
9 # cover (e.g. accidental instance/snapshot deletion, an account-level
10 # incident). Runs weekly, not daily, since RDS already covers day-to-day
11 # recovery needs.
12 #
13 # Two-tier backup strategy:
14 # 1. Local dump → /opt/backups/musehub/ (fast restore, 14-day rotation)
15 # 2. R2 upload → r2://BACKUP_R2_BUCKET/musehub-db/ (off-disk, long retention)
16 #
17 # Connects via DATABASE_URL (works against RDS or self-hosted Postgres
18 # identically — it's just a connection string) rather than `docker exec`
19 # into a specific container, since there's no longer a local Postgres
20 # container to exec into on either environment.
21 #
22 # R2 upload requires:
23 # - rclone installed: sudo apt-get install rclone
24 # - rclone configured with an R2 remote named "r2":
25 # rclone config (add remote → S3-compatible → Cloudflare R2)
26 # - BACKUP_R2_BUCKET set in /opt/musehub/.env, e.g.:
27 # BACKUP_R2_BUCKET=musehub-backups
28 # If BACKUP_R2_BUCKET is unset or rclone is not installed, the script
29 # continues with local-only backup and emits a warning.
30 #
31 # Install (on EC2):
32 # sudo mkdir -p /opt/backups/musehub
33 # sudo chown ubuntu:ubuntu /opt/backups/musehub
34 # chmod +x /opt/musehub/deploy/backup.sh
35 # crontab -e
36 # # Add this line (runs weekly, Sunday 3 AM):
37 # 0 3 * * 0 /opt/musehub/deploy/backup.sh >> /var/log/musehub-backup.log 2>&1
38
39 set -euo pipefail
40
41 APP_DIR="/opt/musehub"
42 BACKUP_DIR="/opt/backups/musehub"
43 TIMESTAMP=$(date +%Y%m%d_%H%M%S)
44 BACKUP_FILE="$BACKUP_DIR/musehub_${TIMESTAMP}.sql.gz"
45 RETAIN_DAYS=14
46
47 mkdir -p "$BACKUP_DIR"
48
49 echo "[$(date)] Starting backup..."
50
51 # DATABASE_URL is written by deploy/secrets.sh (SSM-sourced). psql/pg_dump
52 # don't understand SQLAlchemy's "+asyncpg" driver suffix -- strip it.
53 DATABASE_URL=$(grep '^DATABASE_URL=' "$APP_DIR/.env" | cut -d'=' -f2- | sed 's/postgresql+asyncpg/postgresql/')
54 BACKUP_R2_BUCKET=$(grep '^BACKUP_R2_BUCKET=' "$APP_DIR/.env" 2>/dev/null | cut -d'=' -f2- || true)
55
56 sudo docker run --rm postgres:16 pg_dump "$DATABASE_URL" | gzip > "$BACKUP_FILE"
57
58 echo "[$(date)] Backup written: $BACKUP_FILE ($(du -sh "$BACKUP_FILE" | cut -f1))"
59
60 # ── Off-disk: sync to Cloudflare R2 ──────────────────────────────────────────
61 # Keeps backups on a separate storage medium — survives disk failure on the
62 # EC2 instance. rclone copy is idempotent (skips already-uploaded files).
63 if [[ -n "${BACKUP_R2_BUCKET:-}" ]] && command -v rclone &>/dev/null; then
64 echo "[$(date)] Syncing backup to R2 bucket: ${BACKUP_R2_BUCKET}..."
65 rclone copy "$BACKUP_FILE" "r2:${BACKUP_R2_BUCKET}/musehub-db/" \
66 --s3-chunk-size=128M \
67 --s3-upload-concurrency=4 \
68 --stats=30s
69 echo "[$(date)] R2 upload complete."
70
71 # Remove R2 copies older than 90 days (long-term retention).
72 rclone delete "r2:${BACKUP_R2_BUCKET}/musehub-db/" \
73 --min-age=90d \
74 --include "musehub_*.sql.gz" || true
75 echo "[$(date)] R2 old-backup rotation complete."
76 else
77 echo "[$(date)] WARNING: BACKUP_R2_BUCKET not set or rclone not installed — local-only backup."
78 echo "[$(date)] To enable off-disk backups: install rclone, configure an R2 remote,"
79 echo "[$(date)] and set BACKUP_R2_BUCKET=<your-bucket-name> in $APP_DIR/.env"
80 fi
81
82 echo "[$(date)] Removing local backups older than $RETAIN_DAYS days..."
83 find "$BACKUP_DIR" -name "musehub_*.sql.gz" -mtime "+$RETAIN_DAYS" -delete
84
85 echo "[$(date)] Backup complete. Local files kept:"
86 ls -lh "$BACKUP_DIR"
File History 4 commits
sha256:bee12c5cbde2334f98421c6c209d768fa6b8004d6705c9ea798ce6c1651bc11f Merge 'infra/database-phase3-4-cleanup' into 'dev' — propos… Human 14 hours ago
sha256:316e70bc7bfc59633679c76f96aee8b77de3e8b04f70f3bb38ba83df3cb1a5ed Merge 'infra/database-phase2-production-rds' into 'dev' — p… Human 16 hours ago
sha256:3fadb0439bba9451b89229676971c0d4a40900dec7810e9d5f8791b8d950d505 fix: install.sh version from latest published tarball, not … Sonnet 4.6 minor ⚠ 102 days ago
sha256:763eb2cb8675073b84c19345b27586d2ed939a9aee97c5479b69f502f1a70eff fix(tests): update test suite to match current implementation Sonnet 4.6 patch 124 days ago