Backup Database to Azure Storage #917
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: | |
| overwriteThisMorningsBackup: | |
| required: true | |
| type: boolean | |
| default: false | |
| restoreToProductionDataEnv: | |
| required: true | |
| type: boolean | |
| default: false | |
| restoreToProductionAnalysisEnv: | |
| required: true | |
| type: boolean | |
| default: false | |
| restoreToStagingEnv: | |
| required: true | |
| type: boolean | |
| default: false | |
| schedule: # 03:00 UTC | |
| - cron: '0 3 * * *' | |
| jobs: | |
| backup: | |
| name: Backup AKS Database (production) | |
| if: ${{ github.event_name == 'schedule' || (github.event_name == 'workflow_dispatch' && github.event.inputs.overwriteThisMorningsBackup == 'true') }} | |
| runs-on: ubuntu-latest | |
| services: | |
| postgres: | |
| image: postgres:11.10 | |
| 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 | |
| steps: | |
| - uses: actions/checkout@v4 | |
| name: Checkout | |
| - name: Set KV environment variables | |
| run: | | |
| tf_vars_file=terraform/aks/workspace-variables/production_aks.tfvars.json | |
| echo "key_vault_name=$(jq -r '.key_vault_name' ${tf_vars_file})" >> $GITHUB_ENV | |
| echo "key_vault_infra_secret_name=$(jq -r '.key_vault_infra_secret_name' ${tf_vars_file})" >> $GITHUB_ENV | |
| echo "namespace=$(jq -r '.namespace' ${tf_vars_file})" >> $GITHUB_ENV | |
| echo "cluster=$(jq -r '.cluster' ${tf_vars_file})" >> $GITHUB_ENV | |
| echo "aks_app_environment=$(jq -r '.paas_app_environment' ${tf_vars_file})" >> $GITHUB_ENV | |
| - uses: azure/login@v1 | |
| with: | |
| creds: ${{ secrets.AZURE_CREDENTIALS_PRODUCTION_AKS }} | |
| - name: Install kubectl | |
| uses: azure/setup-kubectl@v3 | |
| with: | |
| version: "v1.26.1" # default is latest stable | |
| - name: K8 setup | |
| shell: bash | |
| run: | | |
| az aks get-credentials -g s189p01-tsc-pd-rg -n s189p01-tsc-production-aks | |
| make install-konduit | |
| - name: Setup postgres client | |
| uses: DFE-Digital/github-actions/install-postgres-client@master | |
| - name: Set environment variable | |
| run: echo "BACKUP_FILE_NAME=register_prod_$(date +"%F")" >> $GITHUB_ENV | |
| - name: Backup Prod DB | |
| run: | | |
| bin/konduit.sh register-production -- pg_dump -E utf8 --clean --if-exists --no-owner --verbose --no-password -f ${BACKUP_FILE_NAME}.sql | |
| tar -cvzf ${BACKUP_FILE_NAME}.tar.gz ${BACKUP_FILE_NAME}.sql | |
| - name: Set Connection String | |
| run: | | |
| STORAGE_CONN_STR="$(az keyvault secret show --name REGISTER-BACKUP-STORAGE-CONNECTION-STRING-AKS --vault-name ${{ env.key_vault_name }} | jq -r .value)" | |
| echo "::add-mask::$STORAGE_CONN_STR" | |
| echo "STORAGE_CONN_STR=$STORAGE_CONN_STR" >> $GITHUB_ENV | |
| - name: Upload Backup to Azure Storage | |
| run: | | |
| az storage blob upload --container-name database-backup \ | |
| --file ${BACKUP_FILE_NAME}.tar.gz --name ${BACKUP_FILE_NAME}.tar.gz --overwrite \ | |
| --connection-string '${{ env.STORAGE_CONN_STR }}' | |
| rm ${BACKUP_FILE_NAME}.tar.gz | |
| - name: Restore backup to aks productiondata database | |
| if: ${{ github.event_name == 'schedule' || (github.event_name == 'workflow_dispatch' && github.event.inputs.restoreToProductionDataEnv == 'true') }} | |
| shell: bash | |
| run: | | |
| bin/konduit.sh -i ${{ env.BACKUP_FILE_NAME }}.sql -t 7200 register-productiondata -- psql | |
| - name: Restore backup to aks analysis database | |
| if: ${{ github.event_name == 'schedule' || (github.event_name == 'workflow_dispatch' && github.event.inputs.restoreToProductionAnalysisEnv == 'true') }} | |
| shell: bash | |
| run: | | |
| bin/konduit.sh -i ${{ env.BACKUP_FILE_NAME }}.sql -p ANALYSIS_DATABASE_URL -t 7200 register-production -- psql | |
| bin/konduit.sh -p ANALYSIS_DATABASE_URL -t 300 register-production -- psql -c 'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "${{ env.SQLPAD_USER }}"' | |
| bin/konduit.sh -p ANALYSIS_DATABASE_URL -t 300 register-production -- psql -c 'GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO "${{ env.SQLPAD_USER }}"' | |
| env: | |
| SQLPAD_USER: ${{ secrets.SQLPAD_USER }} | |
| - name: Disk cleanup | |
| shell: bash | |
| run: | | |
| sudo rm -rf /usr/local/lib/android || true | |
| sudo rm -rf /usr/share/dotnet || true | |
| sudo rm -rf /opt/ghc || true | |
| - name: Create local Sanitised Database | |
| run: | | |
| createdb ${DATABASE_NAME} && psql -f ${{ env.BACKUP_FILE_NAME }}.sql -d ${DATABASE_NAME} | |
| env: | |
| DATABASE_NAME: register_trainee_teachers | |
| PGUSER: postgres | |
| PGPASSWORD: postgres | |
| PGHOST: localhost | |
| PGPORT: 5432 | |
| - name: Remove backup file | |
| shell: bash | |
| run: | | |
| rm ${{ env.BACKUP_FILE_NAME }}.sql | |
| - name: Sanitise the local Database | |
| run: | | |
| psql -d ${DATABASE_NAME} -f db/scripts/sanitise.sql | |
| env: | |
| DATABASE_NAME: register_trainee_teachers | |
| PGUSER: postgres | |
| PGPASSWORD: postgres | |
| PGHOST: localhost | |
| PGPORT: 5432 | |
| - name: Dump the Sanitised Database | |
| run: | | |
| pg_dump --encoding utf8 --compress=1 --clean --no-owner --if-exists -d ${DATABASE_NAME} -f backup_sanitised.sql.gz | |
| env: | |
| DATABASE_NAME: register_trainee_teachers | |
| PGUSER: postgres | |
| PGPASSWORD: postgres | |
| PGHOST: localhost | |
| PGPORT: 5432 | |
| - name: Upload Sanitised Backup | |
| uses: actions/upload-artifact@v3 | |
| with: | |
| name: backup_sanitised | |
| path: backup_sanitised.sql.gz | |
| retention-days: 3 | |
| - name: Check for Failure | |
| if: ${{ failure() }} | |
| uses: rtCamp/action-slack-notify@master | |
| env: | |
| SLACK_CHANNEL: twd_publish_register_tech | |
| SLACK_COLOR: '#ef5343' | |
| SLACK_ICON_EMOJI: ':github-logo:' | |
| SLACK_USERNAME: Register Trainee Teachers | |
| SLACK_TITLE: Backup Failure | |
| SLACK_MESSAGE: ':alert: Backup failure :sadparrot:' | |
| SLACK_WEBHOOK: ${{ secrets.SLACK_WEBHOOK }} | |
| restore_staging: | |
| needs: [backup] | |
| name: restore Database (staging_aks) | |
| if: ${{ github.event_name == 'schedule' || (github.event_name == 'workflow_dispatch' && github.event.inputs.restoreToStagingEnv == 'true') }} | |
| runs-on: ubuntu-latest | |
| steps: | |
| - uses: actions/checkout@v4 | |
| name: Checkout | |
| - uses: azure/login@v1 | |
| with: | |
| creds: ${{ secrets.AZURE_CREDENTIALS_STAGING_AKS }} | |
| - name: Download Sanitised Backup | |
| uses: actions/download-artifact@v3 | |
| with: | |
| name: backup_sanitised | |
| - name: Install kubectl | |
| uses: azure/setup-kubectl@v3 | |
| with: | |
| version: "v1.26.1" # default is latest stable | |
| - name: K8 setup | |
| shell: bash | |
| run: | | |
| az aks get-credentials -g s189t01-tsc-ts-rg -n s189t01-tsc-test-aks | |
| make install-konduit | |
| - name: Restore backup to aks env database | |
| shell: bash | |
| run: | | |
| bin/konduit.sh -i backup_sanitised.sql.gz -c -t 7200 register-staging -- psql | |
| - name: Check for Failure | |
| if: ${{ failure() }} | |
| uses: rtCamp/action-slack-notify@master | |
| env: | |
| SLACK_CHANNEL: twd_publish_register_tech | |
| SLACK_COLOR: '#ef5343' | |
| SLACK_ICON_EMOJI: ':github-logo:' | |
| SLACK_USERNAME: Register Trainee Teachers | |
| SLACK_TITLE: Staging Restore Failure | |
| SLACK_MESSAGE: ':alert: Daily restore failure for staging :sadparrot:' | |
| SLACK_WEBHOOK: ${{ secrets.SLACK_WEBHOOK }} |