Backup Database to Azure Storage #654
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 to Azure Storage | |
| on: | |
| workflow_dispatch: | |
| schedule: # 01:00 UTC | |
| - cron: "0 1 * * *" | |
| jobs: | |
| backup: | |
| name: Backup Azure Database ( Production ) | |
| runs-on: ubuntu-latest | |
| environment: | |
| name: production | |
| 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/production.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-production-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_production" >> $GITHUB_ENV | |
| - name: Get BACKUP_STORAGE_CONNECTION_STRING | |
| run: | | |
| BACKUP_STORAGE_ACCESS_KEY="$(az storage account keys list -g ${{ env.RESOURCE_GROUP_NAME }} -n s165p01rsmdbbackuppd | jq -r '.[0].value')" | |
| echo "::add-mask::$BACKUP_STORAGE_ACCESS_KEY" | |
| echo "BACKUP_STORAGE_CONNECTION_STRING=DefaultEndpointsProtocol=https;AccountName=s165p01rsmdbbackuppd;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 | |
| - name: Notify Slack channel on job failure | |
| if: failure() | |
| uses: rtCamp/action-slack-notify@v2 | |
| env: | |
| SLACK_USERNAME: CI Deployment | |
| SLACK_TITLE: Database backup failure | |
| SLACK_MESSAGE: Production database backup job for ${{ env.POSTGRES_SERVER_NAME }} failed. Also check the azure postgres server firewall rules. | |
| SLACK_WEBHOOK: ${{ steps.get_monitoring_secret.outputs.SLACK_WEBHOOK }} | |
| SLACK_COLOR: failure | |
| SLACK_FOOTER: Sent from backup job in database-backup workflow |