Skip to content

Database

Database #278

Workflow file for this run

name: Database
on:
workflow_dispatch:
schedule:
- cron: "0 4 * * *" # 04:00 UTC
jobs:
backup-production:
name: Backup production
runs-on: ubuntu-latest
environment: production
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
steps:
- name: Checkout code
uses: actions/checkout@v4
- name: Get resource group name
id: resource-group-name
run: echo "value=$(make -s production print-resource-group-name)" >> $GITHUB_OUTPUT
- uses: ./.github/actions/set-kubernetes-credentials
with:
environment: production
azure-credentials: ${{ secrets.AZURE_CREDENTIALS }}
- name: Refresh Terraform
run: make ci production terraform-refresh
env:
TF_VAR_azure_sp_credentials_json: ${{ secrets.AZURE_CREDENTIALS }}
DOCKER_IMAGE: "ghcr.io/dfe-digital/apply-for-qualified-teacher-status:no-tag"
- name: Get Azure backup storage details
id: azure-backup-storage
working-directory: terraform/application
run: |
echo "account-name=$(terraform output -raw postgres_azure_backup_storage_account_name)" >> $GITHUB_OUTPUT
echo "container-name=$(terraform output -raw postgres_azure_backup_storage_container_name)" >> $GITHUB_OUTPUT
- name: Install kubectl
uses: azure/setup-kubectl@v3
- name: Install konduit
run: make install-konduit
- name: Dump database
run: bin/konduit.sh apply-for-qts-production-web -- pg_dump -E utf8 --compress=1 --clean --if-exists --no-owner --verbose -f backup.sql.gz
- name: Set connection string
run: |
STORAGE_CONN_STR=$(az storage account show-connection-string -g ${{ steps.resource-group-name.outputs.value }} -n ${{ steps.azure-backup-storage.outputs.account-name }} --query 'connectionString')
echo "::add-mask::$STORAGE_CONN_STR"
echo "AZURE_STORAGE_CONNECTION_STRING=$STORAGE_CONN_STR" >> $GITHUB_ENV
- name: Upload backup
run: |
az config set extension.use_dynamic_install=yes_without_prompt
az config set core.only_show_errors=true
az storage azcopy blob upload \
--container ${{ steps.azure-backup-storage.outputs.container-name }} \
--source backup.sql.gz \
--destination $(date +"%F-%H").sql.gz
- name: Install postgres client
uses: DFE-Digital/github-actions/install-postgres-client@master
with:
version: 14
- name: Sanitise dump
run: |
gzip -d --to-stdout backup.sql.gz | psql -d postgres
psql -d postgres -f db/scripts/sanitise.sql
pg_dump -E utf8 --compress=1 --clean --if-exists --no-owner --verbose --no-password -f backup-sanitised.sql.gz
env:
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
- id: key-vault-name
if: failure()
shell: bash
run: echo "value=$(make -s production print-infrastructure-key-vault-name)" >> $GITHUB_OUTPUT
- uses: Azure/get-keyvault-secrets@v1
if: failure()
id: key-vault-secrets
with:
keyvault: ${{ steps.key-vault-name.outputs.value }}
secrets: "SLACK-WEBHOOK"
- 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
SLACK_WEBHOOK: ${{ steps.key-vault-secrets.outputs.SLACK-WEBHOOK }}
SLACK_COLOR: failure
SLACK_FOOTER: Sent from backup-production job in database-backups workflow
restore-preproduction:
name: Restore preproduction
needs: [backup-production]
runs-on: ubuntu-latest
environment: preproduction
steps:
- name: Checkout code
uses: actions/checkout@v4
- name: Download sanitised backup
uses: actions/download-artifact@v4
with:
name: backup-sanitised
- uses: ./.github/actions/set-kubernetes-credentials
with:
environment: preproduction
azure-credentials: ${{ secrets.AZURE_CREDENTIALS }}
- name: Install kubectl
uses: azure/setup-kubectl@v3
- name: Install konduit
run: make install-konduit
- name: Restore sanitised backup
shell: bash
run: bin/konduit.sh -i backup-sanitised.sql.gz -c apply-for-qts-preproduction-web -- psql