Merge pull request #1171 from Codex723/feature/stream-detail-redesign… #314
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: | ||
| # Runs daily at 02:00 UTC - low-traffic window | ||
| - cron: '0 2 * * *' | ||
| workflow_dispatch: | ||
| inputs: | ||
| reason: | ||
| description: 'Reason for manual backup' | ||
| required: false | ||
| default: 'Manual trigger' | ||
| jobs: | ||
| backup: | ||
| name: pg_dump - encrypt - upload to S3 | ||
| runs-on: ubuntu-latest | ||
| timeout-minutes: 30 | ||
| steps: | ||
| # ── 0. Install postgresql-client matching server version ─────────────── | ||
| - name: Install postgresql-client | ||
| run: | | ||
| sudo apt-get update -qq | ||
| sudo apt-get install -y --no-install-recommends postgresql-client | ||
| # ── 1. Dump ──────────────────────────────────────────────────────────── | ||
| - name: Run pg_dump | ||
| env: | ||
| DATABASE_URL: ${{ secrets.DATABASE_URL }} | ||
| run: | | ||
| # Parse DATABASE_URL: postgresql://user:password@host:port/dbname | ||
| # Use Python for reliable URL parsing - avoids sed edge cases with | ||
| # special characters in passwords | ||
| eval "$(python3 - <<'EOF' | ||
| import urllib.parse, os, shlex | ||
| url = os.environ["DATABASE_URL"] | ||
| p = urllib.parse.urlparse(url) | ||
| # unquote percent-encoded chars, then shell-quote for safe eval | ||
| print(f'export PGUSER={shlex.quote(urllib.parse.unquote(p.username or ""))}') | ||
| print(f'export PGPASSWORD={shlex.quote(urllib.parse.unquote(p.password or ""))}') | ||
| print(f'export PGHOST={shlex.quote(p.hostname or "")}') | ||
| print(f'export PGPORT={p.port or 5432}') | ||
| print(f'export PGDATABASE={shlex.quote(p.path.lstrip("/"))}') | ||
| EOF | ||
| )" | ||
| TIMESTAMP=$(date -u +"%Y-%m-%dT%H-%M-%SZ") | ||
| DUMP_FILE="stellarstream-backup-${TIMESTAMP}.sql.gz" | ||
| echo "DUMP_FILE=${DUMP_FILE}" >> "$GITHUB_ENV" | ||
| echo "TIMESTAMP=${TIMESTAMP}" >> "$GITHUB_ENV" | ||
| pg_dump \ | ||
| --format=plain \ | ||
| --no-owner \ | ||
| --no-acl \ | ||
| --no-password \ | ||
| | gzip -9 > "/tmp/${DUMP_FILE}" | ||
| DUMP_SIZE=$(du -sh "/tmp/${DUMP_FILE}" | cut -f1) | ||
| echo "Dump completed. Size: ${DUMP_SIZE}" | ||
| # Fail fast if dump is suspiciously small (< 1KB = something went wrong) | ||
| DUMP_BYTES=$(stat -c%s "/tmp/${DUMP_FILE}") | ||
| if [ "$DUMP_BYTES" -lt 1024 ]; then | ||
| echo "ERROR: Dump file is too small (${DUMP_BYTES} bytes). Aborting." | ||
| exit 1 | ||
| fi | ||
| # ── 2. Encrypt ───────────────────────────────────────────────────────── | ||
| - name: Encrypt dump with GPG (AES-256) | ||
| env: | ||
| GPG_PASSPHRASE: ${{ secrets.BACKUP_GPG_PASSPHRASE }} | ||
| run: | | ||
| ENCRYPTED_FILE="${DUMP_FILE}.gpg" | ||
| echo "ENCRYPTED_FILE=${ENCRYPTED_FILE}" >> "$GITHUB_ENV" | ||
| echo "$GPG_PASSPHRASE" | gpg --batch \ | ||
| --yes \ | ||
| --passphrase-fd 0 \ | ||
| --pinentry-mode loopback \ | ||
| --cipher-algo AES256 \ | ||
| --symmetric \ | ||
| --output "/tmp/${ENCRYPTED_FILE}" \ | ||
| "/tmp/${DUMP_FILE}" | ||
| # Verify the encrypted file is non-empty | ||
| if [ ! -s "/tmp/${ENCRYPTED_FILE}" ]; then | ||
| echo "ERROR: Encrypted file is empty or missing." | ||
| exit 1 | ||
| fi | ||
| echo "Encrypted size: $(du -sh /tmp/${ENCRYPTED_FILE} | cut -f1)" | ||
| # Wipe the plaintext dump immediately | ||
| shred -u "/tmp/${DUMP_FILE}" | ||
| # ── 3. Upload to S3 ──────────────────────────────────────────────────── | ||
| - name: Upload to S3 | ||
| env: | ||
| AWS_ACCESS_KEY_ID: ${{ secrets.AWS_ACCESS_KEY_ID }} | ||
| AWS_SECRET_ACCESS_KEY: ${{ secrets.AWS_SECRET_ACCESS_KEY }} | ||
| AWS_DEFAULT_REGION: ${{ secrets.AWS_REGION }} | ||
| S3_BUCKET: ${{ secrets.BACKUP_S3_BUCKET }} | ||
| run: | | ||
| # Path: backups/YYYY/MM/DD/<filename> | ||
| S3_KEY="backups/$(date -u +%Y/%m/%d)/${ENCRYPTED_FILE}" | ||
| aws s3 cp "/tmp/${ENCRYPTED_FILE}" "s3://${S3_BUCKET}/${S3_KEY}" \ | ||
| --sse AES256 \ | ||
| --storage-class STANDARD_IA \ | ||
| --metadata "timestamp=${TIMESTAMP},source=github-actions,project=stellarstream" | ||
| echo "Uploaded: s3://${S3_BUCKET}/${S3_KEY}" | ||
| # Wipe local encrypted file after upload | ||
| shred -u "/tmp/${ENCRYPTED_FILE}" | ||
| # ── 4. Verify upload ─────────────────────────────────────────────────── | ||
| - name: Verify S3 object exists | ||
| env: | ||
| AWS_ACCESS_KEY_ID: ${{ secrets.AWS_ACCESS_KEY_ID }} | ||
| AWS_SECRET_ACCESS_KEY: ${{ secrets.AWS_SECRET_ACCESS_KEY }} | ||
| AWS_DEFAULT_REGION: ${{ secrets.AWS_REGION }} | ||
| S3_BUCKET: ${{ secrets.BACKUP_S3_BUCKET }} | ||
| run: | | ||
| S3_KEY="backups/$(date -u +%Y/%m/%d)/${ENCRYPTED_FILE}" | ||
| CONTENT_LENGTH=$(aws s3api head-object \ | ||
| --bucket "$S3_BUCKET" \ | ||
| --key "$S3_KEY" \ | ||
| --query 'ContentLength' \ | ||
| --output text) | ||
| echo "Verified: s3://${S3_BUCKET}/${S3_KEY} (${CONTENT_LENGTH} bytes)" | ||
| if [ "$CONTENT_LENGTH" -lt 1 ]; then | ||
| echo "ERROR: S3 object has zero size." | ||
| exit 1 | ||
| fi | ||
| # ── 5. Notify on failure ─────────────────────────────────────────────── | ||
| - name: Notify on failure | ||
| if: failure() | ||
| env: | ||
| SLACK_WEBHOOK_URL: ${{ secrets.SLACK_WEBHOOK_URL }} | ||
| run: | | ||
| # SLACK_WEBHOOK_URL is optional - skip silently if not configured | ||
| if [ -z "${SLACK_WEBHOOK_URL}" ]; then | ||
| echo "SLACK_WEBHOOK_URL not set, skipping notification." | ||
| exit 0 | ||
| fi | ||
| curl -s -X POST "$SLACK_WEBHOOK_URL" \ | ||
| -H 'Content-type: application/json' \ | ||
| --data '{ | ||
| "text": ":rotating_light: *StellarStream DB Backup FAILED*", | ||
| "attachments": [{ | ||
| "color": "danger", | ||
| "fields": [ | ||
| {"title": "Workflow", "value": "${{ github.workflow }}", "short": true}, | ||
| {"title": "Run", "value": "<${{ github.server_url }}/${{ github.repository }}/actions/runs/${{ github.run_id }}|View logs>", "short": true} | ||
| ] | ||
| }] | ||
| }' | ||
