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: | |
| push: | |
| branches: [1749-ittms-prod-db-backup-workflow] | |
| # 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 | |
| environment: | |
| name: production | |
| 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/application/config/production.tfvars.json | |
| echo "key_vault_name=$(jq -r '.infra_key_vault_name' ${tf_vars_file})" >> $GITHUB_ENV | |
| echo "key_vault_infra_secret_name=$(jq -r '.infra_key_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 '.cluster' ${tf_vars_file})" >> $GITHUB_ENV | |
| - uses: azure/login@v2 | |
| with: | |
| creds: ${{ secrets.AZURE_CREDENTIALS }} | |
| - name: Install kubectl | |
| uses: azure/setup-kubectl@v4 | |
| with: | |
| version: "v1.26.1" # default is latest stable | |
| - uses: DFE-Digital/github-actions/set-kubelogin-environment@master | |
| with: | |
| azure-credentials: ${{ secrets.AZURE_CREDENTIALS }} | |
| - 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=ittm_prod_$(date +"%F")" >> $GITHUB_ENV | |
| - name: Backup Prod DB | |
| run: | | |
| bin/konduit.sh -t 7200 ittm-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@v4 | |
| 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 }} |