db-backup.yml

name: Database Backup

on:
  schedule:
    - cron: '0 0 */3 * *' # Runs at 00:00 UTC every 3 days
  workflow_dispatch: # Allows manual trigger

jobs:
  backup:
    runs-on: ubuntu-latest
    environment: production
    env:
      EDGEDB_INSTANCE: ${{ secrets.EDGEDB_INSTANCE }}
      EDGEDB_SECRET_KEY: ${{ secrets.EDGEDB_SECRET_KEY }}

    steps:
      - uses: actions/checkout@v4

      - name: Install EdgeDB CLI
        run: curl --proto '=https' --tlsv1.2 -sSf https://sh.edgedb.com | sh -s -- -y

      - name: Install GPG
        run: sudo apt-get install -y gpg

      - name: Configure AWS Credentials
        uses: aws-actions/configure-aws-credentials@v4
        with:
          aws-access-key-id: ${{ secrets.AWS_ACCESS_KEY_ID }}
          aws-secret-access-key: ${{ secrets.AWS_SECRET_ACCESS_KEY }}
          aws-region: ${{ secrets.AWS_REGION }}

      - name: Create and Process Backup
        run: |
          # Create timestamp for the backup file
          TIMESTAMP=$(date +%Y%m%d_%H%M%S)
          BACKUP_FILE="backup_${TIMESTAMP}.dump"
          ENCRYPTED_FILE="${BACKUP_FILE}.gpg"
          COMPRESSED_FILE="${BACKUP_FILE}.gz"

          # Create the backup
          edgedb dump $BACKUP_FILE

          # Compress the file
          gzip -9 $BACKUP_FILE

          # Encrypt the compressed file
          echo "${{ secrets.GPG_PASSPHRASE }}" | gpg --batch --yes --passphrase-fd 0 \
            --symmetric --cipher-algo AES256 $COMPRESSED_FILE

          # Upload encrypted file to S3
          aws s3 cp ${COMPRESSED_FILE}.gpg s3://${{ secrets.S3_BUCKET }}/backups/$ENCRYPTED_FILE

          # Cleanup local files
          rm -f $BACKUP_FILE $COMPRESSED_FILE ${COMPRESSED_FILE}.gpg

          # Deactivated: Keep only last 40 backups (120 days) 
          # aws s3 ls s3://${{ secrets.S3_BUCKET }}/backups/ | sort -r | tail -n +41 | awk '{print $4}' | xargs -I {} aws s3 rm s3://${{ secrets.S3_BUCKET }}/backups/{}