Backup production database #642
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 | |
| services: | |
| postgres: | |
| image: postgres:11.6-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@v3 | |
| - name: Configure AWS credentials | |
| uses: aws-actions/configure-aws-credentials@v2 | |
| 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 CF_USERNAME From ParameterStore | |
| uses: marvinpinto/action-inject-ssm-secrets@latest | |
| with: | |
| ssm_parameter: /teaching-vacancies/github_action/infra/cf_username_prod | |
| env_variable_name: CF_USERNAME_PROD | |
| - name: Get CF_PASSWORD From ParameterStore | |
| uses: marvinpinto/action-inject-ssm-secrets@latest | |
| with: | |
| ssm_parameter: /teaching-vacancies/github_action/infra/cf_password_prod | |
| env_variable_name: CF_PASSWORD | |
| - name: Get SLACK_WEBHOOK From ParameterStore | |
| uses: marvinpinto/action-inject-ssm-secrets@latest | |
| with: | |
| ssm_parameter: /teaching-vacancies/github_action/infra/slack_webhook | |
| env_variable_name: SLACK_WEBHOOK | |
| - name: Setup cf cli | |
| uses: DFE-Digital/github-actions/setup-cf-cli@master | |
| with: | |
| CF_USERNAME: ${{env.CF_USERNAME_PROD}} | |
| CF_PASSWORD: ${{env.CF_PASSWORD}} | |
| CF_SPACE_NAME: teaching-vacancies-production | |
| INSTALL_CONDUIT: true | |
| - name: Install postgres client | |
| uses: DFE-Digital/github-actions/install-postgres-client@master | |
| - name: Dump production database to file | |
| shell: bash | |
| run: bin/dump-db | |
| - 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}} |