Skip to content

Backup Database to Azure Storage #834

Backup Database to Azure Storage

Backup Database to Azure Storage #834

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