Backup Database to Azure Storage #801
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=get-an-identity-production-pg-svc-$(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 }}getanid-${{ env.ENV }}-psql" >> $GITHUB_ENV | |
| echo "POSTGRES_SERVER_HOST_NAME=${{ env.RESOURCE_PREFIX }}getanid-${{ env.ENV }}-psql.postgres.database.azure.com" >> $GITHUB_ENV | |
| echo "POSTGRES_DATABASE_NAME=${{ env.RESOURCE_PREFIX }}getanid-${{ env.ENV }}-psql-db" >> $GITHUB_ENV | |
| - uses: Azure/get-keyvault-secrets@v1 | |
| id: get_storage_secret | |
| with: | |
| keyvault: ${{ env.KEY_VAULT_NAME }} | |
| secrets: "BACKUP-STORAGE-CONNECTION-STRING" | |
| - 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 ${{ 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@v1 | |
| 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 get-an-identity \ | |
| --file ${BACKUP_FILE_NAME}.sql.zip --name ${BACKUP_FILE_NAME}.sql.zip \ | |
| --connection-string '${{ steps.get_storage_secret.outputs.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 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 |