Database Backup #56
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: Database Backup | |
| on: | |
| schedule: | |
| # Daily full snapshot at 02:00 UTC | |
| - cron: "0 2 * * *" | |
| workflow_dispatch: | |
| inputs: | |
| action: | |
| description: "Action to perform" | |
| required: true | |
| type: choice | |
| options: | |
| - snapshot | |
| - verify-latest | |
| - cleanup | |
| env: | |
| # Retention windows | |
| SNAPSHOT_RETENTION_DAYS: "30" | |
| WAL_RETENTION_DAYS: "7" | |
| # Alert threshold: if no successful snapshot within this many hours, alert | |
| SNAPSHOT_ALERT_THRESHOLD_HOURS: "25" | |
| jobs: | |
| # ── Daily full snapshot ───────────────────────────────────────────────────── | |
| snapshot: | |
| name: Full Database Snapshot | |
| runs-on: ubuntu-latest | |
| if: github.event_name == 'schedule' || (github.event_name == 'workflow_dispatch' && inputs.action == 'snapshot') | |
| environment: production | |
| steps: | |
| - uses: actions/checkout@v4 | |
| - name: Install dependencies | |
| run: | | |
| sudo apt-get update -qq | |
| sudo apt-get install -y postgresql-client awscli | |
| - name: Create compressed encrypted snapshot | |
| env: | |
| DATABASE_URL: ${{ secrets.PROD_DATABASE_URL }} | |
| BACKUP_ENCRYPTION_KEY: ${{ secrets.BACKUP_ENCRYPTION_KEY }} | |
| BACKUP_BUCKET: ${{ secrets.BACKUP_S3_BUCKET }} | |
| AWS_ACCESS_KEY_ID: ${{ secrets.BACKUP_AWS_ACCESS_KEY_ID }} | |
| AWS_SECRET_ACCESS_KEY: ${{ secrets.BACKUP_AWS_SECRET_ACCESS_KEY }} | |
| AWS_DEFAULT_REGION: ${{ secrets.BACKUP_AWS_REGION }} | |
| run: | | |
| set -euo pipefail | |
| TIMESTAMP=$(date -u +%Y%m%dT%H%M%SZ) | |
| FILENAME="snapshot_${TIMESTAMP}.sql.gz.enc" | |
| TMPFILE=$(mktemp) | |
| echo "📸 Creating snapshot: ${FILENAME}" | |
| # Dump → compress → encrypt | |
| pg_dump "$DATABASE_URL" \ | |
| | gzip \ | |
| | openssl enc -aes-256-cbc -pbkdf2 -pass env:BACKUP_ENCRYPTION_KEY \ | |
| > "$TMPFILE" | |
| FILE_SIZE=$(stat -c%s "$TMPFILE") | |
| echo "SNAPSHOT_FILENAME=${FILENAME}" >> "$GITHUB_ENV" | |
| echo "SNAPSHOT_SIZE=${FILE_SIZE}" >> "$GITHUB_ENV" | |
| echo "SNAPSHOT_TIMESTAMP=${TIMESTAMP}" >> "$GITHUB_ENV" | |
| # Upload to cross-region bucket | |
| aws s3 cp "$TMPFILE" "s3://${BACKUP_BUCKET}/snapshots/${FILENAME}" \ | |
| --sse aws:kms \ | |
| --storage-class STANDARD_IA | |
| rm -f "$TMPFILE" | |
| echo "✅ Snapshot uploaded: s3://${BACKUP_BUCKET}/snapshots/${FILENAME} (${FILE_SIZE} bytes)" | |
| - name: Persist snapshot metadata | |
| env: | |
| DATABASE_URL: ${{ secrets.PROD_DATABASE_URL }} | |
| BACKUP_BUCKET: ${{ secrets.BACKUP_S3_BUCKET }} | |
| run: | | |
| psql "$DATABASE_URL" <<SQL | |
| INSERT INTO backup_metadata | |
| (snapshot_filename, file_size_bytes, storage_location, snapshot_at, verification_status) | |
| VALUES | |
| ('${{ env.SNAPSHOT_FILENAME }}', | |
| ${{ env.SNAPSHOT_SIZE }}, | |
| 's3://${{ env.BACKUP_BUCKET }}/snapshots/${{ env.SNAPSHOT_FILENAME }}', | |
| NOW(), | |
| 'pending'); | |
| SQL | |
| - name: Clean up old snapshots | |
| env: | |
| BACKUP_BUCKET: ${{ secrets.BACKUP_S3_BUCKET }} | |
| AWS_ACCESS_KEY_ID: ${{ secrets.BACKUP_AWS_ACCESS_KEY_ID }} | |
| AWS_SECRET_ACCESS_KEY: ${{ secrets.BACKUP_AWS_SECRET_ACCESS_KEY }} | |
| AWS_DEFAULT_REGION: ${{ secrets.BACKUP_AWS_REGION }} | |
| run: | | |
| CUTOFF=$(date -u -d "${SNAPSHOT_RETENTION_DAYS} days ago" +%Y-%m-%dT%H:%M:%SZ) | |
| echo "Removing snapshots older than ${CUTOFF}" | |
| aws s3 ls "s3://${BACKUP_BUCKET}/snapshots/" \ | |
| | awk '{print $4}' \ | |
| | while read -r key; do | |
| KEY_DATE=$(echo "$key" | grep -oP '\d{8}T\d{6}Z' | head -1 || true) | |
| if [[ -n "$KEY_DATE" && "$KEY_DATE" < "${CUTOFF//[-:]/}" ]]; then | |
| aws s3 rm "s3://${BACKUP_BUCKET}/snapshots/${key}" | |
| echo "Deleted: ${key}" | |
| fi | |
| done | |
| - name: Notify on failure | |
| if: failure() | |
| env: | |
| SLACK_WEBHOOK: ${{ secrets.SLACK_WEBHOOK_URL }} | |
| run: | | |
| curl -s -X POST "$SLACK_WEBHOOK" \ | |
| -H 'Content-type: application/json' \ | |
| -d '{"text":"🚨 *Database snapshot FAILED* — <${{ github.server_url }}/${{ github.repository }}/actions/runs/${{ github.run_id }}|View run>"}' \ | |
| || true | |
| # ── Automated backup verification ────────────────────────────────────────── | |
| verify: | |
| name: Verify Latest Snapshot | |
| runs-on: ubuntu-latest | |
| needs: snapshot | |
| if: always() && (github.event_name == 'schedule' || (github.event_name == 'workflow_dispatch' && inputs.action == 'verify-latest')) | |
| services: | |
| postgres-verify: | |
| image: postgres:16 | |
| env: | |
| POSTGRES_PASSWORD: verify | |
| POSTGRES_DB: aframp_verify | |
| options: >- | |
| --health-cmd pg_isready | |
| --health-interval 10s | |
| --health-timeout 5s | |
| --health-retries 5 | |
| ports: | |
| - 5433:5432 | |
| steps: | |
| - uses: actions/checkout@v4 | |
| - name: Install dependencies | |
| run: | | |
| sudo apt-get update -qq | |
| sudo apt-get install -y postgresql-client awscli | |
| - name: Download and restore latest snapshot | |
| env: | |
| BACKUP_BUCKET: ${{ secrets.BACKUP_S3_BUCKET }} | |
| BACKUP_ENCRYPTION_KEY: ${{ secrets.BACKUP_ENCRYPTION_KEY }} | |
| AWS_ACCESS_KEY_ID: ${{ secrets.BACKUP_AWS_ACCESS_KEY_ID }} | |
| AWS_SECRET_ACCESS_KEY: ${{ secrets.BACKUP_AWS_SECRET_ACCESS_KEY }} | |
| AWS_DEFAULT_REGION: ${{ secrets.BACKUP_AWS_REGION }} | |
| run: | | |
| set -euo pipefail | |
| # Find the most recent snapshot | |
| LATEST=$(aws s3 ls "s3://${BACKUP_BUCKET}/snapshots/" \ | |
| | sort | tail -1 | awk '{print $4}') | |
| echo "Restoring: ${LATEST}" | |
| echo "VERIFIED_SNAPSHOT=${LATEST}" >> "$GITHUB_ENV" | |
| TMPFILE=$(mktemp) | |
| aws s3 cp "s3://${BACKUP_BUCKET}/snapshots/${LATEST}" "$TMPFILE" | |
| # Decrypt → decompress → restore | |
| openssl enc -d -aes-256-cbc -pbkdf2 -pass env:BACKUP_ENCRYPTION_KEY -in "$TMPFILE" \ | |
| | gunzip \ | |
| | psql "postgresql://postgres:verify@localhost:5433/aframp_verify" | |
| rm -f "$TMPFILE" | |
| echo "✅ Restore complete" | |
| - name: Run integrity checks | |
| run: | | |
| set -euo pipefail | |
| VERIFY_URL="postgresql://postgres:verify@localhost:5433/aframp_verify" | |
| # Verify key tables exist and have rows | |
| for TABLE in transactions wallets payment_provider_configs exchange_rate_history; do | |
| COUNT=$(psql "$VERIFY_URL" -tAc "SELECT COUNT(*) FROM ${TABLE}" 2>/dev/null || echo "-1") | |
| if [ "$COUNT" = "-1" ]; then | |
| echo "❌ Table ${TABLE} missing or inaccessible" | |
| exit 1 | |
| fi | |
| echo "✅ ${TABLE}: ${COUNT} rows" | |
| done | |
| # Verify schema version matches expected | |
| MIGRATION_COUNT=$(psql "$VERIFY_URL" -tAc "SELECT COUNT(*) FROM _sqlx_migrations" 2>/dev/null || echo "0") | |
| echo "✅ Migrations applied: ${MIGRATION_COUNT}" | |
| echo "INTEGRITY_PASSED=true" >> "$GITHUB_ENV" | |
| - name: Update backup metadata with verification result | |
| env: | |
| DATABASE_URL: ${{ secrets.PROD_DATABASE_URL }} | |
| run: | | |
| STATUS="${INTEGRITY_PASSED:-false}" | |
| psql "$DATABASE_URL" <<SQL | |
| UPDATE backup_metadata | |
| SET verification_status = CASE WHEN '${STATUS}' = 'true' THEN 'verified' ELSE 'failed' END, | |
| verified_at = NOW() | |
| WHERE snapshot_filename = '${{ env.VERIFIED_SNAPSHOT }}' | |
| AND verification_status = 'pending'; | |
| SQL | |
| - name: Alert on verification failure | |
| if: failure() | |
| env: | |
| SLACK_WEBHOOK: ${{ secrets.SLACK_WEBHOOK_URL }} | |
| run: | | |
| curl -s -X POST "$SLACK_WEBHOOK" \ | |
| -H 'Content-type: application/json' \ | |
| -d '{"text":"🚨 *Backup verification FAILED* for `${{ env.VERIFIED_SNAPSHOT }}` — <${{ github.server_url }}/${{ github.repository }}/actions/runs/${{ github.run_id }}|View run>"}' \ | |
| || true | |
| # ── WAL archiving lag check ───────────────────────────────────────────────── | |
| wal-lag-check: | |
| name: WAL Archiving Lag Check | |
| runs-on: ubuntu-latest | |
| if: github.event_name == 'schedule' | |
| steps: | |
| - name: Check WAL archiving lag | |
| env: | |
| DATABASE_URL: ${{ secrets.PROD_DATABASE_URL }} | |
| WAL_MAX_LAG_SECONDS: "300" | |
| run: | | |
| sudo apt-get install -y postgresql-client -qq | |
| LAG=$(psql "$DATABASE_URL" -tAc \ | |
| "SELECT EXTRACT(EPOCH FROM (NOW() - last_archived_time))::int | |
| FROM pg_stat_archiver" 2>/dev/null || echo "99999") | |
| echo "WAL archiving lag: ${LAG}s (threshold: ${WAL_MAX_LAG_SECONDS}s)" | |
| if [ "$LAG" -gt "$WAL_MAX_LAG_SECONDS" ]; then | |
| echo "❌ WAL lag ${LAG}s exceeds threshold ${WAL_MAX_LAG_SECONDS}s" | |
| curl -s -X POST "${{ secrets.SLACK_WEBHOOK_URL }}" \ | |
| -H 'Content-type: application/json' \ | |
| -d "{\"text\":\"🚨 *WAL archiving lag* is ${LAG}s (threshold ${WAL_MAX_LAG_SECONDS}s)\"}" \ | |
| || true | |
| exit 1 | |
| fi | |
| echo "✅ WAL archiving lag within threshold" | |
| # ── Old WAL segment cleanup ───────────────────────────────────────────────── | |
| cleanup: | |
| name: Clean Up Old WAL Segments | |
| runs-on: ubuntu-latest | |
| if: github.event_name == 'schedule' || (github.event_name == 'workflow_dispatch' && inputs.action == 'cleanup') | |
| steps: | |
| - name: Remove WAL segments older than retention window | |
| env: | |
| BACKUP_BUCKET: ${{ secrets.BACKUP_S3_BUCKET }} | |
| AWS_ACCESS_KEY_ID: ${{ secrets.BACKUP_AWS_ACCESS_KEY_ID }} | |
| AWS_SECRET_ACCESS_KEY: ${{ secrets.BACKUP_AWS_SECRET_ACCESS_KEY }} | |
| AWS_DEFAULT_REGION: ${{ secrets.BACKUP_AWS_REGION }} | |
| run: | | |
| sudo apt-get install -y awscli -qq | |
| CUTOFF=$(date -u -d "${WAL_RETENTION_DAYS} days ago" +%Y-%m-%dT%H:%M:%SZ) | |
| echo "Removing WAL segments older than ${CUTOFF}" | |
| aws s3 ls "s3://${BACKUP_BUCKET}/wal/" \ | |
| | awk '{print $4}' \ | |
| | while read -r key; do | |
| MOD_DATE=$(aws s3api head-object \ | |
| --bucket "${BACKUP_BUCKET}" \ | |
| --key "wal/${key}" \ | |
| --query 'LastModified' --output text 2>/dev/null || echo "") | |
| if [[ -n "$MOD_DATE" && "$MOD_DATE" < "$CUTOFF" ]]; then | |
| aws s3 rm "s3://${BACKUP_BUCKET}/wal/${key}" | |
| echo "Deleted WAL segment: ${key}" | |
| fi | |
| done | |
| echo "✅ WAL cleanup complete" |