Backup database to Azure storage #57
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: | |
| inputs: | |
| environment: | |
| description: Environment to backup | |
| required: true | |
| default: qa_aks | |
| type: choice | |
| options: | |
| - qa_aks | |
| - staging_aks | |
| - sandbox_aks | |
| - production_aks | |
| backup-file: | |
| description: | | |
| Backup file name (without extension). Default is att_[env]_adhoc_YYYY-MM-DD. Set it explicitly when backing up a point-in-time (PTR) server. (Optional) | |
| required: false | |
| type: string | |
| default: default | |
| db-server: | |
| description: | | |
| Name of the database server. Default is the live server. When backing up a point-in-time (PTR) server, use the full name of the PTR server. (Optional) | |
| schedule: | |
| - cron: "0 2 * * *" # 02:00 UTC | |
| env: | |
| SERVICE_NAME: apply | |
| SERVICE_SHORT: att | |
| TF_VARS_PATH: terraform/aks/workspace_variables | |
| jobs: | |
| backup: | |
| name: Backup database | |
| runs-on: ubuntu-latest | |
| services: | |
| postgres: | |
| image: postgres:14 | |
| env: | |
| POSTGRES_USER: postgres | |
| POSTGRES_PASSWORD: postgres | |
| POSTGRES_DB: postgres | |
| ports: | |
| - 5432:5432 | |
| options: --health-cmd pg_isready --health-interval 10s --health-timeout 5s --health-retries 5 | |
| environment: | |
| name: ${{ inputs.environment || 'production_aks' }} | |
| env: | |
| DEPLOY_ENV: ${{ inputs.environment || 'production_aks' }} | |
| BACKUP_FILE: ${{ inputs.backup-file || 'schedule' }} | |
| steps: | |
| - uses: actions/checkout@v4 | |
| - uses: azure/login@v2 | |
| with: | |
| creds: ${{ secrets.AZURE_CREDENTIALS }} | |
| - name: Set environment variables | |
| run: | | |
| source global_config/${DEPLOY_ENV}.sh | |
| tf_vars_file=${TF_VARS_PATH}/${DEPLOY_ENV}.tfvars.json | |
| echo "CLUSTER=$(jq -r '.cluster' ${tf_vars_file})" >> $GITHUB_ENV | |
| echo "AKS_ENV=$(jq -r '.app_environment' ${tf_vars_file})" >> $GITHUB_ENV | |
| echo "RESOURCE_GROUP_NAME=${RESOURCE_NAME_PREFIX}-${SERVICE_SHORT}-${CONFIG_SHORT}-rg" >> $GITHUB_ENV | |
| echo "STORAGE_ACCOUNT_NAME=${RESOURCE_NAME_PREFIX}${SERVICE_SHORT}dbbkp${CONFIG_SHORT}sa" >> $GITHUB_ENV | |
| TODAY=$(date +"%F") | |
| echo "DB_SERVER=${RESOURCE_NAME_PREFIX}-${SERVICE_SHORT}-${CONFIG_SHORT}-psql" >> $GITHUB_ENV | |
| if [ "${BACKUP_FILE}" == "schedule" ]; then | |
| BACKUP_FILE=${SERVICE_SHORT}_${CONFIG_SHORT}_${TODAY} | |
| elif [ "${BACKUP_FILE}" == "default" ]; then | |
| BACKUP_FILE=${SERVICE_SHORT}_${CONFIG_SHORT}_adhoc_${TODAY} | |
| else | |
| BACKUP_FILE=${BACKUP_FILE} | |
| fi | |
| echo "BACKUP_FILE=${BACKUP_FILE}" >> $GITHUB_ENV | |
| - name: Backup ${{ env.DEPLOY_ENV }} postgres | |
| uses: DFE-Digital/github-actions/backup-postgres@master | |
| with: | |
| storage-account: ${{ env.STORAGE_ACCOUNT_NAME }} | |
| resource-group: ${{ env.RESOURCE_GROUP_NAME }} | |
| app-name: ${{ env.SERVICE_NAME }}-${{ env.AKS_ENV }} | |
| cluster: ${{ env.CLUSTER }} | |
| azure-credentials: ${{ secrets.AZURE_CREDENTIALS }} | |
| backup-file: ${{ env.BACKUP_FILE }}.sql | |
| db-server-name: ${{ inputs.db-server }} | |
| slack-webhook: ${{ secrets.SLACK_WEBHOOK }} | |
| - name: Disk cleanup | |
| if: github.event_name == 'schedule' | |
| shell: bash | |
| run: | | |
| sudo rm -rf /usr/local/lib/android || true | |
| sudo rm -rf /usr/share/dotnet || true | |
| sudo rm -rf /opt/ghc || true | |
| sudo rm -rf /usr/local/.ghcup || true | |
| sudo rm -rf /opt/hostedtoolcache/CodeQL || true | |
| sudo rm -rf /usr/local/share/boost || true | |
| sudo docker image prune --all --force || true | |
| sudo apt-get remove -y '^aspnetcore-.*' || true | |
| sudo apt-get remove -y '^dotnet-.*' --fix-missing || true | |
| sudo apt-get remove -y '^llvm-.*' --fix-missing || true | |
| sudo apt-get remove -y 'php.*' --fix-missing || true | |
| sudo apt-get remove -y '^mongodb-.*' --fix-missing || true | |
| sudo apt-get remove -y '^mysql-.*' --fix-missing || true | |
| sudo apt-get remove -y google-chrome-stable firefox powershell mono-devel libgl1-mesa-dri --fix-missing || true | |
| sudo apt-get remove -y google-cloud-sdk --fix-missing || true | |
| sudo apt-get remove -y google-cloud-cli --fix-missing || true | |
| sudo rm -rf "$AGENT_TOOLSDIRECTORY"/PyPy || true | |
| sudo rm -rf "$AGENT_TOOLSDIRECTORY"/Python || true | |
| sudo rm -rf "$AGENT_TOOLSDIRECTORY"/go || true | |
| sudo rm -rf "$AGENT_TOOLSDIRECTORY"/node || true | |
| sudo apt-get autoremove -y || true | |
| sudo apt-get clean | |
| - name: Sanitise dump | |
| if: github.event_name == 'schedule' | |
| run: | | |
| createdb ${DATABASE_NAME} && gzip -d --to-stdout ${{ env.BACKUP_FILE }}.sql.gz | psql -d ${DATABASE_NAME} | |
| rm ${{ env.BACKUP_FILE }}.sql.gz | |
| psql -d ${DATABASE_NAME} -f db/scripts/sanitise.sql | |
| pg_dump --encoding utf8 --compress=1 --clean --no-owner --if-exists -d ${DATABASE_NAME} -f att_backup_sanitised.sql.gz | |
| env: | |
| DATABASE_NAME: apply_manage_itt | |
| PGUSER: postgres | |
| PGPASSWORD: postgres | |
| PGHOST: localhost | |
| PGPORT: 5432 | |
| - name: Upload sanitized backup to Azure storage | |
| if: github.event_name == 'schedule' | |
| run: | | |
| STORAGE_CONN_STR=$(az storage account show-connection-string -g ${{ env.RESOURCE_GROUP_NAME }} -n ${{ env.STORAGE_ACCOUNT_NAME }} --query 'connectionString') | |
| echo "::add-mask::$STORAGE_CONN_STR" | |
| echo "STORAGE_CONN_STR=$STORAGE_CONN_STR" >> $GITHUB_ENV | |
| az storage blob upload --container-name database-backup \ | |
| --file att_backup_sanitised.sql.gz --name att_backup_sanitised.sql.gz --overwrite \ | |
| --connection-string '${{ env.STORAGE_CONN_STR }}' | |
| rm att_backup_sanitised.sql.gz |