backup-db.yml

name: Backup database to Azure storage

on:
  workflow_dispatch:
    inputs:
      environment:
        description: Environment to backup
        required: true
        default: qa_aks
        type: choice
        options:
        - qa_aks
        - staging_aks
        - sandbox_aks
        - production_aks
      backup-file:
        description: |
          Backup file name (without extension). Default is att_[env]_adhoc_YYYY-MM-DD. Set it explicitly when backing up a point-in-time (PTR) server. (Optional)
        required: false
        type: string
        default: default
      db-server:
        description: |
          Name of the database server. Default is the live server. When backing up a point-in-time (PTR) server, use the full name of the PTR server. (Optional)

  schedule:
    - cron: "0 2 * * *" # 02:00 UTC

env:
  SERVICE_NAME: apply
  SERVICE_SHORT: att
  TF_VARS_PATH: terraform/aks/workspace_variables

jobs:
  backup:
    name: Backup database
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:14
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: postgres
        ports:
          - 5432:5432
        options: --health-cmd pg_isready --health-interval 10s --health-timeout 5s --health-retries 5
    environment:
      name: ${{ inputs.environment || 'production_aks' }}
    env:
      DEPLOY_ENV: ${{ inputs.environment || 'production_aks'  }}
      BACKUP_FILE: ${{ inputs.backup-file || 'schedule'  }}

    steps:
    - uses: actions/checkout@v4

    - uses: azure/login@v2
      with:
        creds: ${{ secrets.AZURE_CREDENTIALS }}

    - name: Set environment variables
      run: |
        source global_config/${DEPLOY_ENV}.sh
        tf_vars_file=${TF_VARS_PATH}/${DEPLOY_ENV}.tfvars.json
        echo "CLUSTER=$(jq -r '.cluster' ${tf_vars_file})" >> $GITHUB_ENV
        echo "AKS_ENV=$(jq -r '.app_environment' ${tf_vars_file})" >> $GITHUB_ENV
        echo "RESOURCE_GROUP_NAME=${RESOURCE_NAME_PREFIX}-${SERVICE_SHORT}-${CONFIG_SHORT}-rg" >> $GITHUB_ENV
        echo "STORAGE_ACCOUNT_NAME=${RESOURCE_NAME_PREFIX}${SERVICE_SHORT}dbbkp${CONFIG_SHORT}sa" >> $GITHUB_ENV
        TODAY=$(date +"%F")
        echo "DB_SERVER=${RESOURCE_NAME_PREFIX}-${SERVICE_SHORT}-${CONFIG_SHORT}-psql" >> $GITHUB_ENV
        if [ "${BACKUP_FILE}" == "schedule" ]; then
          BACKUP_FILE=${SERVICE_SHORT}_${CONFIG_SHORT}_${TODAY}
        elif [ "${BACKUP_FILE}" == "default" ]; then
          BACKUP_FILE=${SERVICE_SHORT}_${CONFIG_SHORT}_adhoc_${TODAY}
        else
          BACKUP_FILE=${BACKUP_FILE}
        fi
        echo "BACKUP_FILE=${BACKUP_FILE}" >> $GITHUB_ENV

    - name: Backup ${{ env.DEPLOY_ENV }} postgres
      uses: DFE-Digital/github-actions/backup-postgres@master
      with:
        storage-account: ${{ env.STORAGE_ACCOUNT_NAME }}
        resource-group: ${{ env.RESOURCE_GROUP_NAME }}
        app-name: ${{ env.SERVICE_NAME }}-${{ env.AKS_ENV }}
        cluster: ${{ env.CLUSTER }}
        azure-credentials: ${{ secrets.AZURE_CREDENTIALS }}
        backup-file: ${{ env.BACKUP_FILE }}.sql
        db-server-name: ${{ inputs.db-server }}
        slack-webhook: ${{ secrets.SLACK_WEBHOOK }}

    - name: Disk cleanup
      if: github.event_name == 'schedule'
      shell: bash
      run: |
        sudo rm -rf /usr/local/lib/android || true
        sudo rm -rf /usr/share/dotnet || true
        sudo rm -rf /opt/ghc || true
        sudo rm -rf /usr/local/.ghcup || true
        sudo rm -rf /opt/hostedtoolcache/CodeQL || true
        sudo rm -rf /usr/local/share/boost || true
        sudo docker image prune --all --force || true
        sudo apt-get remove -y '^aspnetcore-.*' || true
        sudo apt-get remove -y '^dotnet-.*' --fix-missing || true
        sudo apt-get remove -y '^llvm-.*' --fix-missing || true
        sudo apt-get remove -y 'php.*' --fix-missing || true
        sudo apt-get remove -y '^mongodb-.*' --fix-missing || true
        sudo apt-get remove -y '^mysql-.*' --fix-missing || true
        sudo apt-get remove -y google-chrome-stable firefox powershell mono-devel libgl1-mesa-dri --fix-missing || true
        sudo apt-get remove -y google-cloud-sdk --fix-missing || true
        sudo apt-get remove -y google-cloud-cli --fix-missing || true
        sudo rm -rf "$AGENT_TOOLSDIRECTORY"/PyPy || true
        sudo rm -rf "$AGENT_TOOLSDIRECTORY"/Python || true
        sudo rm -rf "$AGENT_TOOLSDIRECTORY"/go || true
        sudo rm -rf "$AGENT_TOOLSDIRECTORY"/node || true
        sudo apt-get autoremove -y || true
        sudo apt-get clean

    - name: Sanitise dump
      if: github.event_name == 'schedule'
      run: |
        createdb ${DATABASE_NAME} && gzip -d --to-stdout ${{ env.BACKUP_FILE }}.sql.gz | psql -d ${DATABASE_NAME}
        rm ${{ env.BACKUP_FILE }}.sql.gz
        psql -d ${DATABASE_NAME} -f db/scripts/sanitise.sql
        pg_dump --encoding utf8 --compress=1 --clean --no-owner --if-exists -d ${DATABASE_NAME} -f att_backup_sanitised.sql.gz
      env:
        DATABASE_NAME: apply_manage_itt
        PGUSER: postgres
        PGPASSWORD: postgres
        PGHOST: localhost
        PGPORT: 5432

    - name: Upload sanitized backup to Azure storage
      if: github.event_name == 'schedule'
      run: |
        STORAGE_CONN_STR=$(az storage account show-connection-string -g ${{ env.RESOURCE_GROUP_NAME }} -n ${{ env.STORAGE_ACCOUNT_NAME }} --query 'connectionString')
        echo "::add-mask::$STORAGE_CONN_STR"
        echo "STORAGE_CONN_STR=$STORAGE_CONN_STR" >> $GITHUB_ENV
        az storage blob upload --container-name database-backup \
        --file att_backup_sanitised.sql.gz --name att_backup_sanitised.sql.gz --overwrite \
        --connection-string '${{ env.STORAGE_CONN_STR }}'
        rm att_backup_sanitised.sql.gz