backup.yml

# Dumps the Postgres database (DATABASE_URL) and uploads it to an S3-compatible bucket.
# Runs monthly on the 1st at 00:00 UTC, or on-demand via the Actions tab.
name: Backup PostgreSQL database to S3

on:
  schedule:
    - cron: '0 0 1 * *'
  workflow_dispatch:

jobs:
  backup:
    runs-on: ubuntu-latest
    steps:
      - name: Install PostgreSQL 16 client
        run: |
          set -euo pipefail
          sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
          curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc \
            | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
          sudo apt-get update
          sudo apt-get install -y postgresql-client-16

      - name: Dump database and upload to S3
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
          S3_BUCKET: ${{ secrets.S3_BUCKET }}
          AWS_ACCESS_KEY_ID: ${{ secrets.AWS_ACCESS_KEY_ID }}
          AWS_SECRET_ACCESS_KEY: ${{ secrets.AWS_SECRET_ACCESS_KEY }}
          AWS_REGION: ${{ secrets.AWS_REGION }}
          S3_ENDPOINT_URL: ${{ secrets.S3_ENDPOINT_URL }}
          # GCS S3-compat rejects aws-cli v2's default x-amz-checksum-* headers
          AWS_REQUEST_CHECKSUM_CALCULATION: when_required
          AWS_RESPONSE_CHECKSUM_VALIDATION: when_required
        run: |
          set -euo pipefail
          FILENAME="backup_$(date -u +%Y%m%d_%H%M%SZ).pgdump"
          pg_dump --format=custom --no-owner --no-privileges "$DATABASE_URL" \
            | aws s3 cp - "s3://${S3_BUCKET}/${FILENAME}" \
                --region "$AWS_REGION" \
                --endpoint-url "$S3_ENDPOINT_URL" \
                --expected-size 5368709120
          echo "Uploaded s3://${S3_BUCKET}/${FILENAME}"