Backup Database to Azure Storage #1322
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 | |
| concurrency: build_and_deploy_main | |
| 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.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 "app_environment=$(jq -r '.app_environment' ${tf_vars_file})" >> $GITHUB_ENV | |
| - uses: azure/login@v2 | |
| with: | |
| creds: ${{ secrets.AZURE_CREDENTIALS_PRODUCTION }} | |
| - name: Install kubectl | |
| uses: DFE-Digital/github-actions/set-kubectl@master | |
| - uses: DFE-Digital/github-actions/set-kubelogin-environment@master | |
| with: | |
| azure-credentials: ${{ secrets.AZURE_CREDENTIALS_PRODUCTION }} | |
| - name: K8 setup | |
| shell: bash | |
| run: | | |
| make ci production get-cluster-credentials | |
| 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 -t 7200 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: Set env variable | |
| run: echo "SANITISED_FILE_NAME=register_sanitised_$(date +"%F")" >> $GITHUB_ENV | |
| - 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 ${SANITISED_FILE_NAME}.sql.gz | |
| env: | |
| DATABASE_NAME: register_trainee_teachers | |
| PGUSER: postgres | |
| PGPASSWORD: postgres | |
| PGHOST: localhost | |
| PGPORT: 5432 | |
| - name: Upload Backup to Azure Storage | |
| run: | | |
| az storage blob upload --container-name database-backup \ | |
| --file ${SANITISED_FILE_NAME}.sql.gz --name ${SANITISED_FILE_NAME}.sql.gz --overwrite \ | |
| --connection-string '${{ env.STORAGE_CONN_STR }}' | |
| rm ${SANITISED_FILE_NAME}.sql.gz | |
| - name: Check for Failure | |
| uses: ./.github/actions/send-slack-notification/ | |
| if: ${{ failure() }} | |
| with: | |
| slack-title: Backup Failure | |
| slack-message: ':alert: Backup failure :sadparrot:' | |
| slack-webhook: ${{ secrets.SLACK_WEBHOOK }} | |
| restore_staging: | |
| needs: [backup] | |
| name: restore Database (staging) | |
| 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 | |
| - name: Set env variable | |
| run: echo "SANITISED_FILE_NAME=register_sanitised_$(date +"%F")" >> $GITHUB_ENV | |
| - name: Set KV environment variables | |
| run: | | |
| tf_vars_file=terraform/aks/workspace-variables/production.tfvars.json | |
| echo "key_vault_name=$(jq -r '.key_vault_name' ${tf_vars_file})" >> $GITHUB_ENV | |
| - uses: azure/login@v2 | |
| with: | |
| creds: ${{ secrets.AZURE_CREDENTIALS_PRODUCTION }} | |
| - 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: Download Backup | |
| run: | | |
| az storage blob download --container-name database-backup \ | |
| --file ${SANITISED_FILE_NAME}.sql.gz --name ${SANITISED_FILE_NAME}.sql.gz \ | |
| --connection-string '${{ env.STORAGE_CONN_STR }}' | |
| az logout | |
| - uses: azure/login@v2 | |
| with: | |
| creds: ${{ secrets.AZURE_CREDENTIALS_STAGING }} | |
| - name: Install kubectl | |
| uses: DFE-Digital/github-actions/set-kubectl@master | |
| - uses: DFE-Digital/github-actions/set-kubelogin-environment@master | |
| with: | |
| azure-credentials: ${{ secrets.AZURE_CREDENTIALS_STAGING }} | |
| - name: K8 setup | |
| shell: bash | |
| run: | | |
| make ci staging get-cluster-credentials | |
| make install-konduit | |
| - name: Restore backup to aks env database | |
| shell: bash | |
| run: | | |
| bin/konduit.sh -i ${SANITISED_FILE_NAME}.sql.gz -c -t 7200 register-staging -- psql | |
| - name: Check for Failure | |
| uses: ./.github/actions/send-slack-notification/ | |
| if: ${{ failure() }} | |
| with: | |
| slack-title: Staging Restore Failure | |
| slack-message: ':alert: Daily restore failure for staging :sadparrot:' | |
| slack-webhook: ${{ secrets.SLACK_WEBHOOK }} |