Backup Database for Migration #4
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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_STORAGE_GROUP="s165p01rsmdbbackuppd" >> $GITHUB_ENV | |
| echo "BACKUP_FILE_NAME=rsm-${{ inputs.environment }}-psql-$(date +""%F-%H"")" >> $GITHUB_ENV | |
| echo "RUNNER_IP=$(curl ifconfig.me)" >> $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 |