database-backup-migrate.yml

name: Backup Database for Migration

on:
  workflow_dispatch:
    inputs:
      environment:
        description: Environment to backup
        required: true
        default: test
        type: choice
        options:
          - test
          - preprod
          - production

jobs:
  backup:
    name: Backup Azure Database
    runs-on: ubuntu-latest
    environment:
      name: ${{ inputs.environment }}
    steps:
      - name: Checkout code
        uses: actions/checkout@v4

      - uses: Azure/login@v2
        with:
          creds: ${{ secrets.azure_credentials }}

      - name: Set environment variables
        shell: bash
        run: |
          tf_vars_file="terraform/workspace_variables/${{ inputs.environment }}.tfvars.json"
          echo "KEY_VAULT_NAME=$(jq -r '.key_vault_name' ${tf_vars_file})" >> $GITHUB_ENV
          echo "RESOURCE_PREFIX=$(jq -r '.resource_prefix' ${tf_vars_file})" >> $GITHUB_ENV
          echo "ENV=$(jq -r '.environment_name' ${tf_vars_file})" >> $GITHUB_ENV
          echo "RESOURCE_GROUP_NAME=$(jq -r '.resource_group_name' ${tf_vars_file})" >> $GITHUB_ENV
          echo "BACKUP_FILE_NAME=rsm-${{ inputs.environment }}-psql-$(date +'%F-%H')" >> $GITHUB_ENV
          echo "RUNNER_IP=$(curl ifconfig.me)" >> $GITHUB_ENV
          source global_config/${{ inputs.environment }}.sh
          echo "BACKUP_STORAGE_GROUP="${AZURE_RESOURCE_PREFIX}rsmdbbackup${CONFIG_SHORT}"  >> $GITHUB_ENV

      - name: Set postgres environment variables
        shell: bash
        run: |
          echo "POSTGRES_SERVER_NAME=${{ env.RESOURCE_PREFIX }}-${{ env.ENV }}-psql" >> $GITHUB_ENV
          echo "POSTGRES_SERVER_HOST_NAME=${{ env.RESOURCE_PREFIX }}-${{ env.ENV }}-psql.postgres.database.azure.com" >> $GITHUB_ENV
          echo "POSTGRES_DATABASE_NAME=refer_serious_misconduct_${{ inputs.environment }}" >> $GITHUB_ENV

      - name: Get BACKUP_STORAGE_CONNECTION_STRING
        run: |
          BACKUP_STORAGE_ACCESS_KEY="$(az storage account keys list -g ${{ env.RESOURCE_GROUP_NAME }} -n ${{ env.BACKUP_STORAGE_GROUP }} | jq -r '.[0].value')"
          echo "::add-mask::$BACKUP_STORAGE_ACCESS_KEY"
          echo "BACKUP_STORAGE_CONNECTION_STRING=DefaultEndpointsProtocol=https;AccountName=${{ env.BACKUP_STORAGE_GROUP }};AccountKey=${BACKUP_STORAGE_ACCESS_KEY};EndpointSuffix=core.windows.net" >> $GITHUB_ENV
        shell: bash

      - uses: DfE-Digital/keyvault-yaml-secret@v1
        id: get_monitoring_secret
        with:
          keyvault: ${{ env.KEY_VAULT_NAME }}
          secret: MONITORING
          key: SLACK_WEBHOOK
        env:
          GITHUB_TOKEN: ${{secrets.GITHUB_TOKEN}}

      - uses: DfE-Digital/keyvault-yaml-secret@v1
        id: get_infrastructure_secrets
        with:
          keyvault: ${{ env.KEY_VAULT_NAME }}
          secret: INFRASTRUCTURE
          key: POSTGRES_ADMIN_USERNAME,POSTGRES_ADMIN_PASSWORD
        env:
          GITHUB_TOKEN: ${{secrets.GITHUB_TOKEN}}

      - name: Add firewall rule
        uses: azure/CLI@v2
        with:
          azcliversion: 2.30.0
          inlineScript: |
            az postgres flexible-server firewall-rule create --name ${{ env.POSTGRES_SERVER_NAME }} --resource-group ${{ env.RESOURCE_GROUP_NAME }} --rule-name Allow-GithubRunner-Postgres --start-ip-address ${{ env.RUNNER_IP }} --end-ip-address ${{ env.RUNNER_IP }}

      - name: Add PG PASS
        shell: bash
        run: |
          echo '${{ env.POSTGRES_SERVER_HOST_NAME }}:5432:${{ env.POSTGRES_DATABASE_NAME }}:${{ steps.get_infrastructure_secrets.outputs.POSTGRES_ADMIN_USERNAME }}:${{ steps.get_infrastructure_secrets.outputs.POSTGRES_ADMIN_PASSWORD }}' >> ~/.pgpass
          chmod 600 ~/.pgpass

      - name: Run PG Dump
        shell: bash
        run: |
          export PGSSLMODE=require
          pg_dump -Fc -v --host=${{ env.POSTGRES_SERVER_HOST_NAME }} --port=5432 --username=${{ steps.get_infrastructure_secrets.outputs.POSTGRES_ADMIN_USERNAME }} --dbname=${{ env.POSTGRES_DATABASE_NAME }} > ${{ env.BACKUP_FILE_NAME }}.sql
          zip -r ${{ env.BACKUP_FILE_NAME }}.sql.zip ${{ env.BACKUP_FILE_NAME }}.sql

      - name: Delete firewall rule
        if: always()
        uses: azure/CLI@v2
        with:
          azcliversion: 2.30.0
          inlineScript: |
            az postgres flexible-server firewall-rule delete --name ${{ env.POSTGRES_SERVER_NAME }} --resource-group ${{ env.RESOURCE_GROUP_NAME }} --rule-name Allow-GithubRunner-Postgres --yes

      - name: Upload Backup to Azure Storage
        run: |
          az storage blob upload --container-name rsm \
          --file ${BACKUP_FILE_NAME}.sql.zip --name ${BACKUP_FILE_NAME}.sql.zip \
          --connection-string '${{ env.BACKUP_STORAGE_CONNECTION_STRING }}' \
          --overwrite true