Backup production database #1100
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 production database | |
| on: | |
| schedule: | |
| # The schedule is in UTC and uses cron syntax | |
| # * is a special character in YAML so you have to quote this string | |
| - cron: '0 2 * * *' | |
| jobs: | |
| sync: | |
| name: Backup production database | |
| runs-on: ubuntu-20.04 | |
| environment: production | |
| services: | |
| postgres: | |
| image: postgres:14.9-alpine | |
| 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: Configure AWS credentials | |
| uses: aws-actions/configure-aws-credentials@v4 | |
| with: | |
| aws-access-key-id: ${{ secrets.AWS_ACCESS_KEY_ID }} | |
| aws-secret-access-key: ${{ secrets.AWS_SECRET_ACCESS_KEY }} | |
| aws-region: eu-west-2 | |
| role-to-assume: Deployments | |
| role-duration-seconds: 3600 | |
| role-skip-session-tagging: true | |
| - name: Get secrets from AWS ParameterStore | |
| uses: dkershner6/aws-ssm-getparameters-action@v2 | |
| with: | |
| parameterPairs: "/teaching-vacancies/github_action/infra/slack_webhook = SLACK_WEBHOOK" | |
| - name: Install postgres client | |
| uses: DFE-Digital/github-actions/install-postgres-client@master | |
| with: | |
| version: 14 | |
| - name: Install kubectl | |
| uses: DFE-Digital/github-actions/set-kubectl@master | |
| with: | |
| version: "v1.26.1" | |
| - uses: Azure/login@v2 | |
| with: | |
| creds: ${{ secrets.AZURE_CREDENTIALS }} | |
| - uses: DFE-Digital/github-actions/set-kubelogin-environment@master | |
| with: | |
| azure-credentials: ${{ secrets.AZURE_CREDENTIALS }} | |
| - name: K8 setup for staging | |
| run: | | |
| make production get-cluster-credentials CONFIRM_PRODUCTION=YES | |
| make bin/konduit.sh | |
| - name: Backup production DB | |
| run: | | |
| bin/konduit.sh teaching-vacancies-production -- pg_dump -E utf8 --clean --if-exists --no-owner --verbose --no-password -f full.sql | |
| tar -cvzf full.tar.gz full.sql | |
| - name: Upload backup to S3 | |
| run: bin/upload-db-backup | |
| env: | |
| BACKUP_TYPE: full | |
| - name: Sanitise the Database backup | |
| run: | | |
| echo "::group::Restore backup to intermediate database" | |
| createdb ${DATABASE_NAME} && psql -f full.sql -d ${DATABASE_NAME} | |
| echo "::endgroup::" | |
| echo "::group::Sanitise data" | |
| psql -d ${DATABASE_NAME} -f db/scripts/sanitise.sql | |
| echo "::endgroup::" | |
| echo "::group::Backup Sanitised Database" | |
| pg_dump --encoding utf8 --clean --no-owner --if-exists -d ${DATABASE_NAME} -f sanitised.sql | |
| echo "::endgroup::" | |
| env: | |
| DATABASE_NAME: teaching-vacancies | |
| PGUSER: postgres | |
| PGPASSWORD: postgres | |
| PGHOST: localhost | |
| PGPORT: 5432 | |
| - name: Upload sanitised backup to S3 | |
| run: bin/upload-db-backup | |
| env: | |
| BACKUP_TYPE: sanitised | |
| - name: Send job status message to twd_tv_dev channel | |
| if: always() && github.ref == 'refs/heads/main' | |
| uses: rtCamp/[email protected] | |
| env: | |
| SLACK_CHANNEL: twd_tv_dev | |
| SLACK_USERNAME: CI Deployment | |
| SLACK_TITLE: Deployment ${{ job.status }} | |
| SLACK_MESSAGE: 'Backup production database - ${{ job.status }}' | |
| SLACK_WEBHOOK: ${{env.SLACK_WEBHOOK}} |