Sanaitize a development database via GitHub actions & Heroku

dev-db.yml

name: Development DB

on:
  schedule:
    - cron: "0 4 * * *"
  workflow_dispatch:

jobs:
  dev-db:
    services:
      postgres:
        image: postgis/postgis:14-3.4
        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
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Install PostgreSQL client
        run: sudo apt install -y postgresql-client-14
      - name: Install Heroku CLI
        run: curl https://cli-assets.heroku.com/install.sh | sh
      - name: Download database backup
        run: heroku pg:backups:download --app ${{ vars.HEROKU_APP_NAME }}
        env:
          HEROKU_API_KEY: ${{ secrets.HEROKU_API_KEY }}
      - name: Restore database
        run: pg_restore --verbose --no-acl --no-owner -d "${DATABASE_URL}" latest.dump
        env:
          DATABASE_URL: postgresql://postgres:postgres@localhost/postgres
      - name: Sanitize Database
        run: psql -d "${DATABASE_URL}" -f ./sql/sanitize-dev-db.sql
        env:
          DATABASE_URL: postgresql://postgres:postgres@localhost/postgres
      - name: Dump Database
        run: pg_dump -Fc --no-acl --no-owner -d "${DATABASE_URL}" > latest.dump
        env:
          DATABASE_URL: postgresql://postgres:postgres@localhost/postgres
      - name: Upload database backup as artifact
        uses: actions/upload-artifact@v4
        with:
          name: latest.dump
          path: latest.dump
      - name: Install Python
        uses: actions/setup-python@v5
        with:
          python-version: 3.x
      - name: Install AWS CLI
        run: pip install awscli
      - name: Upload database backup to S3
        run: aws s3 cp latest.dump "s3://${{ vars.AWS_DB_BACKUP_BUCKET }}/latest.dump"
        env:
          AWS_ACCESS_KEY_ID: ${{ secrets.AWS_ACCESS_KEY_ID }}
          AWS_SECRET_ACCESS_KEY: ${{ secrets.AWS_SECRET_ACCESS_KEY }}
          AWS_DEFAULT_REGION: ${{ vars.AWS_DEFAULT_REGION }}

download_dev_db.sh

#!/usr/bin/env bash
# Download the database dump from the latest GitHub workflow and store as latest.dump

set -eo pipefail

ORG=${1:-MY_ORG_NAME}
REPO=${2:-MY_REPO_NAME}

RUN_ID=$(gh api "repos/${ORG}/${REPO}/actions/runs" | jq -r '.workflow_runs[] | select(.name == "Development DB") | .id'  | sed 's/"//g' | head -n 1)
gh run download --repo "${ORG}/${REPO}" "$RUN_ID" -n latest.dump

heroku_utils.sh

#!/usr/bin/env bash

# Add a little Heroku style syntax sugar.

indent() {
  sed "s/^/       /"
}

puts-step() {
  echo "-----> $*"
}

puts-warn() {
  echo " !     $*"
}

postdeploy.sh

#!/usr/bin/env bash

# Restore development database snapshot for review apps.
# Executed after initial review app build.

set -eo pipefail

BIN_DIR=$(cd "$(dirname "$0")"; pwd)

# shellcheck source=bin/heroku_utils
source "$BIN_DIR/heroku_utils"

puts-step "Installing heroku-cli"
npm install -g heroku | indent

puts-step "Enable maintenance mode"
heroku maintenance:on --app "$HEROKU_APP_NAME" | indent

puts-step "Restore development database"
BACKUP_URL=$(aws s3 presign "s3://${DEV_DB_S3_BUCKET}/latest.dump" --region=eu-west-1)
heroku pg:backups:restore "$BACKUP_URL" DATABASE_URL --app "$HEROKU_APP_NAME" --confirm "$HEROKU_APP_NAME" | indent

puts-step "Disable maintenance mode"
heroku maintenance:off --app "$HEROKU_APP_NAME" | indent