Incrementally testing GH action. #49
Workflow file for this run
This file contains hidden or 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: Copy PROD DB to QA | |
| on: | |
| workflow_dispatch: # Supports manual deployment | |
| push: | |
| branches: | |
| - 1117-allow-qa-environment-to-use-prod-database-contents | |
| jobs: | |
| run-script: | |
| runs-on: ubuntu-latest | |
| env: | |
| SOURCE_PROJECT_ID: ${{ vars.PROD_MOBILITY_FEEDS_PROJECT_ID }} | |
| DEST_PROJECT_ID: ${{ vars.QA_MOBILITY_FEEDS_PROJECT_ID }} | |
| DUMP_BUCKET_NAME: "mobilitydata-database-dump-qa" | |
| BUCKET_PROJECT_ID: ${{ vars.QA_MOBILITY_FEEDS_PROJECT_ID }} | |
| GCP_REGION: ${{ vars.MOBILITY_FEEDS_REGION }} | |
| DB_INSTANCE_NAME: ${{ secrets.DB_INSTANCE_NAME }} | |
| DEST_DATABASE_PASSWORD: ${{ secrets.QA_POSTGRE_USER_PASSWORD }} | |
| DUMP_FILE_NAME: "prod-db-dump.sql" | |
| SOURCE_DATABASE_NAME: ${{ vars.PROD_POSTGRE_SQL_DB_NAME }} | |
| DEST_DATABASE_NAME: "MobilityDatabaseProdDuplicate" | |
| DEST_DATABASE_USER: ${{ secrets.QA_POSTGRE_USER_NAME }} | |
| DEST_DATABASE_IMPORT_USER: ${{ secrets.PROD_POSTGRE_USER_NAME }} | |
| GCP_FEED_BASTION_SSH_KEY: ${{ secrets.GCP_FEED_BASTION_SSH_KEY }} | |
| steps: | |
| - name: Checkout code | |
| uses: actions/checkout@v2 | |
| - name: Authenticate to Google Cloud PROD project | |
| id: gcloud_auth_prod | |
| uses: google-github-actions/auth@v2 | |
| with: | |
| credentials_json: ${{ secrets.PROD_GCP_MOBILITY_FEEDS_SA_KEY }} | |
| - name: GCloud Setup PROD | |
| uses: google-github-actions/setup-gcloud@v2 | |
| - name: Get PROD SQL service account | |
| run: | | |
| SERVICE_ACCOUNT=$(gcloud sql instances describe "mobilitydata-database-instance" --project=$SOURCE_PROJECT_ID --format="value(serviceAccountEmailAddress)") | |
| echo "SOURCE_SQL_SERVICE_ACCOUNT=$SERVICE_ACCOUNT" >> $GITHUB_ENV | |
| echo "Destination SQL Service Account: $SERVICE_ACCOUNT" | |
| - name: Authenticate to Google Cloud QA project | |
| id: gcloud_auth_qa | |
| uses: google-github-actions/auth@v2 | |
| with: | |
| credentials_json: ${{ secrets.QA_GCP_MOBILITY_FEEDS_SA_KEY }} | |
| - name: GCloud Setup QA | |
| uses: google-github-actions/setup-gcloud@v2 | |
| - name: Create DB dump bucket and give permissions | |
| run: | | |
| BUCKET_PROJECT_ID=$DEST_PROJECT_ID | |
| # Check if the bucket already exists | |
| if ! gsutil ls -b "gs://${DUMP_BUCKET_NAME}" &> /dev/null; then | |
| echo "Bucket doesn't exist. Creating..." | |
| gsutil mb -l $GCP_REGION -p $BUCKET_PROJECT_ID "gs://${DUMP_BUCKET_NAME}" | |
| else | |
| echo "Bucket already exists." | |
| fi | |
| # Give write permission for the source sql instance to write to the bucket | |
| gsutil iam ch serviceAccount:$SOURCE_SQL_SERVICE_ACCOUNT:objectAdmin gs://$DUMP_BUCKET_NAME | |
| # Get the service account for the QA DB and give read permission to the bucket | |
| DEST_SQL_SERVICE_ACCOUNT=$(gcloud sql instances describe $DB_INSTANCE_NAME --format="value(serviceAccountEmailAddress)") | |
| echo "Destination SQL Service Account: $DEST_SQL_SERVICE_ACCOUNT" | |
| # Give read-write permission on the bucket to the destination sql instance | |
| gsutil iam ch serviceAccount:$DEST_SQL_SERVICE_ACCOUNT:objectAdmin gs://$DUMP_BUCKET_NAME | |
| - name: Authenticate to Google Cloud PROD project Again | |
| uses: google-github-actions/auth@v2 | |
| with: | |
| credentials_json: ${{ secrets.PROD_GCP_MOBILITY_FEEDS_SA_KEY }} | |
| - name: GCloud Setup PROD again | |
| uses: google-github-actions/setup-gcloud@v2 | |
| - name: Dump the PROD DB | |
| run: | | |
| gcloud sql export sql $DB_INSTANCE_NAME gs://$DUMP_BUCKET_NAME/$DUMP_FILE_NAME --database=$SOURCE_DATABASE_NAME --quiet | |
| - name: Authenticate to Google Cloud QA project Again | |
| uses: google-github-actions/auth@v2 | |
| with: | |
| credentials_json: ${{ secrets.QA_GCP_MOBILITY_FEEDS_SA_KEY }} | |
| - name: GCloud Setup QA Again | |
| uses: google-github-actions/setup-gcloud@v2 | |
| - name: QA backup and import dump into the QA DB | |
| run: | | |
| # Dump the QA database as a backup | |
| # According to chatgpt, | |
| # This is Google's recommended, safe method and doesn’t require direct access to the DB. It runs the export | |
| # in a way that avoids locking the database and works from GCP itself (so no traffic leaves GCP). | |
| gcloud sql export sql $DB_INSTANCE_NAME gs://$DUMP_BUCKET_NAME/qa-db-dump-backup.sql --database=$SOURCE_DATABASE_NAME --quiet | |
| # Delete the existing database | |
| gcloud sql databases delete $DEST_DATABASE_NAME --instance=$DB_INSTANCE_NAME --quiet | |
| # Create a the new database | |
| gcloud sql databases create $DEST_DATABASE_NAME --instance=$DB_INSTANCE_NAME | |
| # Import the dump into the QA database | |
| # The exported sql contains statements that require authentication as user postgres. | |
| # In theory we could dump the DB without these statements, with: | |
| # pg_dump --no-owner --no-privileges -d your_database > clean_dump.sql. | |
| export PGPASSWORD=$DEST_DATABASE_PASSWORD | |
| gcloud sql import sql $DB_INSTANCE_NAME gs://$DUMP_BUCKET_NAME/$DUMP_FILE_NAME --database=$DEST_DATABASE_NAME --user=$DEST_DATABASE_IMPORT_USER --quiet | |
| - name: Delete dump file from bucket | |
| run: | | |
| gsutil rm gs://$DUMP_BUCKET_NAME/$DUMP_FILE_NAME | |
| - name: Load secrets from 1Password | |
| uses: 1password/[email protected] | |
| with: | |
| export-env: true # Export loaded secrets as environment variables | |
| env: | |
| OP_SERVICE_ACCOUNT_TOKEN: ${{ secrets.OP_SERVICE_ACCOUNT_TOKEN }} | |
| GCP_FEED_SSH_USER: "op://rbiv7rvkkrsdlpcrz3bmv7nmcu/GCP_FEED_SSH_USER/username" | |
| GCP_FEED_BASTION_NAME: "op://rbiv7rvkkrsdlpcrz3bmv7nmcu/GCP_FEED_BASTION_NAME/username" | |
| GCP_FEED_BASTION_SSH_KEY: "op://rbiv7rvkkrsdlpcrz3bmv7nmcu/GCP_FEED_BASTION_SSH_KEY/private key" | |
| - name: Tunnel | |
| run: | | |
| mkdir -p ~/.ssh | |
| echo "${{ env.GCP_FEED_BASTION_SSH_KEY }}" > ~/.ssh/id_rsa | |
| chmod 600 ~/.ssh/id_rsa | |
| ./scripts/tunnel-create.sh -project_id $DEST_PROJECT_ID -zone ${GCP_REGION}-a -instance ${GCP_FEED_BASTION_NAME}-qa -target_account ${GCP_FEED_SSH_USER} -db_instance ${DB_INSTANCE_NAME} -port 5454 | |
| sleep 10 # Wait for the tunnel to establish | |
| export PGPASSWORD=$DEST_DATABASE_PASSWORD | |
| cat <<'EOF' | psql -h localhost -p 5454 -U data_feeds_user -d $DEST_DATABASE_NAME | |
| DO $$ | |
| DECLARE | |
| r RECORD; | |
| BEGIN | |
| FOR r IN | |
| SELECT table_name | |
| FROM information_schema.tables | |
| WHERE table_schema = 'public' | |
| LOOP | |
| EXECUTE format('ALTER TABLE public.%I OWNER TO postgres;', r.table_name); | |
| END LOOP; | |
| END | |
| $$; | |
| EOF | |
| cat <<'EOF' | psql -h localhost -p 5454 -U postgres -d $DEST_DATABASE_NAME | |
| UPDATE feed | |
| SET feed_contact_email = REPLACE(feed_contact_email, '@', '_') || '@mobilitydata.org' | |
| WHERE feed_contact_email IS NOT NULL | |
| AND TRIM(feed_contact_email) <> ''; | |
| EOF |