db-backup.yml

name: DB Backup

on:
  schedule:
    - cron: "0 3 * * *"
  workflow_dispatch:

jobs:
  backup:
    name: PostgreSQL Backup (${{ matrix.environment }})
    runs-on: ubuntu-latest
    strategy:
      fail-fast: false
      matrix:
        environment: [staging, production]
    environment: ${{ matrix.environment }}
    steps:
      - name: Dump and upload
        uses: appleboy/ssh-action@v1
        with:
          host: ${{ secrets.SERVER_HOST }}
          username: ${{ secrets.SERVER_USER }}
          key: ${{ secrets.SERVER_SSH_KEY }}
          port: ${{ secrets.SERVER_PORT || 22 }}
          script: |
            set -euo pipefail
            STAMP="$(date -u +%Y%m%dT%H%M%SZ)"
            RUN_KEY="${{ github.run_id }}-${{ github.run_attempt }}"
            DUMP="/tmp/paperscout-${{ matrix.environment }}-${STAMP}-${RUN_KEY}.dump"
            DEST="gs://insights-db-backups/paperscout/${{ matrix.environment }}/paperscout-${STAMP}-${RUN_KEY}.dump"
            trap 'rm -f "$DUMP"' EXIT

            sudo -u postgres pg_dump -Fc paperscout > "$DUMP"
            gsutil cp "$DUMP" "$DEST"
            rm -f "$DUMP"