database.yaml

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@v4

      - 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@v4

      - 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