Last active
January 31, 2024 09:55
-
-
Save codingjoe/52d84ecb32b791ceca292fa0dfc9bb05 to your computer and use it in GitHub Desktop.
Sanaitize a development database via GitHub actions & Heroku
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: 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 }} |
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
| #!/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 |
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
| #!/usr/bin/env bash | |
| # Add a little Heroku style syntax sugar. | |
| indent() { | |
| sed "s/^/ /" | |
| } | |
| puts-step() { | |
| echo "-----> $*" | |
| } | |
| puts-warn() { | |
| echo " ! $*" | |
| } |
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
| #!/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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment