Backup Database to Azure Storage #198
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@v3 | |
| - uses: Azure/login@v1 | |
| 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=trngen-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 }}-trngen-${{ env.ENV }}-psql" >> $GITHUB_ENV | |
| echo "POSTGRES_SERVER_HOST_NAME=${{ env.RESOURCE_PREFIX }}-trngen-${{ env.ENV }}-psql.postgres.database.azure.com" >> $GITHUB_ENV | |
| echo "POSTGRES_DATABASE_NAME=trn_generation_api_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 s165p01trngenbackuppd | jq -r '.[0].value')" | |
| echo "::add-mask::$BACKUP_STORAGE_ACCESS_KEY" | |
| echo "BACKUP_STORAGE_CONNECTION_STRING=DefaultEndpointsProtocol=https;AccountName=s165p01trngenbackuppd;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@v1 | |
| with: | |
| azcliversion: 2.30.0 | |
| inlineScript: | | |
| az postgres flexible-server firewall-rule create --name s165p01-trngen-production-psql --resource-group s165p01-trngen-pd-rg --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@v1 | |
| with: | |
| azcliversion: 2.30.0 | |
| inlineScript: | | |
| az postgres flexible-server firewall-rule delete --name s165p01-trngen-production-psql --resource-group s165p01-trngen-pd-rg --rule-name Allow-GithubRunner-Postgres --yes | |
| - name: Upload Backup to Azure Storage | |
| run: | | |
| az storage blob upload --container-name trngen \ | |
| --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 |