Database cutover: Azure PostgreSQL to Cloud SQL

​

Timings

​

Read-only variant

1. Run the job once in its default MODE=fresh. On a target that already holds a copy it connects to both servers and stops at target already has <n> tables, which proves its egress works without writing anything.
2. Check that no role or database setting overrides the server default: select * from pg_db_role_setting where array_to_string(setconfig, ',') ilike '%read_only%' may list only =on entries.
3. Record the time, then az postgres flexible-server parameter set ... --name default_transaction_read_only --value on. In a new session, show default_transaction_read_only reads on. The setting applies only to transactions that start after it, and a transaction that began earlier can write later even if it has not written yet, so wait for every one of them to end. Repeat this, with the recorded time, until it reads 0:
   select count(*) from pg_stat_activity
    where backend_type = 'client backend' and pid <> pg_backend_pid()
      and xact_start < '<recorded time>'::timestamptz;
   
   The longest api-events transaction is the unlinked-file sweep (15-minute timeout). If a session stays, end it with pg_terminate_backend(<pid>); its transaction rolls back and the job retries on Cloud SQL.
4. Step 3 below (copy). If it fails before step 4, set the parameter back to off and nothing is lost.
5. Add the Cloud SQL DatabaseUrl version only after the copy succeeds. The service is serving, so any instance that starts after the add reads latest and would connect to Cloud SQL before the copy.
6. Step 4.2 and step 5 as written. Old instances keep reading Azure until traffic moves, and cannot write.

​

Never do

• Never run the copy with MODE=replace while api-events is serving. The job refuses a target with other sessions or recent writes, but it cannot see writes still landing on Azure, and those would be lost.
• Never merge to $ENV_BRANCH during the window. Every push to it deploys api-events and applies infra-gcp/environments/$ENV, and either one undoes the drain.
• Never print a password, a token or a URL that holds one. Secrets move through pipes only.
• Never add the Cloud SQL DatabaseUrl version before the drain has finished (--scaling=0 applied). The service reads DatabaseUrl at latest until the follow-up pin, so any instance that starts after the add connects to Cloud SQL.
• Never copy <env>-CloudSqlDatabaseUrl into <env>-DatabaseUrl. Its sslmode=require does not meet R3.

​

Variables

export ENV=qa
export ENV_BRANCH=qa
export GCP_PROJECT=qa-soundcheck-live
export REGION=us-central1
export SERVICE=qa-api-events
export CLOUDSQL_IP=10.74.160.3
export API_HOST=qa-api-events.soundchecklive.io
export AZ_SUB=qa-soundcheck
export AZ_RG=qa01-cus-database-rg
export AZ_SERVER=qa01-cus-core-postgres

azure_connections() {
  az monitor metrics list --subscription $AZ_SUB \
    --resource "$(az postgres flexible-server show --subscription $AZ_SUB -g $AZ_RG -n $AZ_SERVER --query id -o tsv)" \
    --metric active_connections --interval PT1M --offset 15m --query 'value[0].timeseries[0].data[].[timeStamp,maximum]' -o tsv
}
api_instances() {
  gcloud run services describe $SERVICE --region=$REGION --project=$GCP_PROJECT \
    --format='value(status.latestReadyRevisionName,metadata.annotations."run.googleapis.com/scalingMode")'
}

​

1. Before the window

1. Rehearse the copy in the default MODE=fresh on an empty target, or with MODE=replace while nothing uses Cloud SQL, and record its timings above. The job’s own guards are in its README.
2. Check the service: every status.traffic entry is latestRevision: true at 100 percent with no tag, and DATABASE_URL reads $ENV-DatabaseUrl at latest, which is the Azure version. Record that version number as AZURE_URL_VERSION: it is the version rollback returns to.
   gcloud run services describe $SERVICE --region=$REGION --project=$GCP_PROJECT --format=json \
     | jq '{traffic: .status.traffic, db: [.spec.template.spec.containers[0].env[] | select(.name == "DATABASE_URL") | .valueFrom.secretKeyRef]}'
   export AZURE_URL_VERSION="$(gcloud secrets versions list $ENV-DatabaseUrl --project=$GCP_PROJECT --filter=state=ENABLED --sort-by=~createTime --limit=1 --format='value(name)')"
   
3. Record the baseline: azure_connections, and the dead-letter depth of every *-dlq subscription (pubsub.googleapis.com/subscription/num_undelivered_messages).
4. Tell everyone who merges that $ENV_BRANCH is frozen for the window. Then wait until no apply or deploy from an earlier merge is still queued or running, because one that starts after the drain restores automatic scaling during the copy. Both lists must print nothing:
   gcloud builds list --region=$REGION --project=$GCP_PROJECT \
     --filter="substitutions.TRIGGER_NAME=tf-apply-$ENV AND status=(QUEUED OR PENDING OR WORKING)" --format='value(id,status)'
   gh run list --workflow=api-events-deploy.yml --branch=$ENV_BRANCH --json status,databaseId \
     --jq '.[] | select(.status != "completed") | .databaseId'
   
   Run both again right before step 2. The Azure root (terraform-apply.yml) applies on the same pushes and does not touch the service.
5. Prod only: the Go API (prod-cus-api-app) holds its own Azure DATABASE_URL. It stops at the drain and does not come back (see Legacy Go API).

​

2. Drain

date -u +%H:%M:%SZ | tee freeze-start.txt
gcloud run services update $SERVICE --region=$REGION --project=$GCP_PROJECT --scaling=0

​

3. Copy

gcloud run jobs execute $ENV-db-copy --region=$REGION --project=$GCP_PROJECT --update-env-vars=MODE=replace --wait

​

4. Switch

1. Add the Cloud SQL DatabaseUrl version. The CA path is the database_server_ca_path output of modules/events.
   gcloud secrets versions access latest --secret=$ENV-CloudSqlAppPassword --project=$GCP_PROJECT \
     | node -e 'let s="";process.stdin.on("data",d=>s+=d).on("end",()=>{const [host,user,ca]=process.argv.slice(1);const u=new URL("postgresql://"+host+"/postgres");u.username=user;u.password=s.trim();u.searchParams.set("sslmode","verify-ca");u.searchParams.set("uselibpqcompat","true");u.searchParams.set("sslrootcert",ca);process.stdout.write(u.toString())})' \
       "$CLOUDSQL_IP:5432" soundcheck_app /etc/soundcheck/cloudsql/server-ca.pem \
     | gcloud secrets versions add $ENV-DatabaseUrl --data-file=- --project=$GCP_PROJECT
   export CLOUDSQL_URL_VERSION="$(gcloud secrets versions list $ENV-DatabaseUrl --project=$GCP_PROJECT --sort-by=~createTime --limit=1 --format='value(name)')"
   
2. Bring api-events back on that version, in one revision:
   gcloud run services update $SERVICE --region=$REGION --project=$GCP_PROJECT \
     --scaling=auto --update-secrets=DATABASE_URL=$ENV-DatabaseUrl:$CLOUDSQL_URL_VERSION
   gcloud run services update-traffic $SERVICE --region=$REGION --project=$GCP_PROJECT --to-latest
   date -u +%H:%M:%SZ | tee freeze-end.txt
   
   This is a hand change. Step 6 records it in Terraform. Until then, an apply sets DATABASE_URL back to latest, which is now the same Cloud SQL version.

​

5. Verify

• The new revision is ready, serves 100 percent, and its boot log shows migrations up to date and no SECURITY WARNING from pg-connection-string:
  gcloud logging read "resource.type=\"cloud_run_revision\" AND resource.labels.service_name=\"$SERVICE\" AND (textPayload:\"migrat\" OR textPayload:\"SECURITY WARNING\" OR severity>=ERROR)" \
    --project=$GCP_PROJECT --freshness=15m --limit=50 --format='value(timestamp,severity,textPayload)'
  
• curl -fsS https://$API_HOST/health, and npm run api -- call v1EventsList --env $ENV with a tester token in SOUNDCHECK_TOKEN.
• Cloud SQL has the app’s sessions (cloudsql.googleapis.com/database/postgresql/num_backends) and Azure stays at its own sessions (azure_connections).
• Pushes to $API_HOST/__encore/pubsub/push/ return 200 again, and the dead-letter depths match the step 1 baseline apart from jobs that were already failing.

​

6. Close the window

1. Pin the version in Terraform: database_url_secret_version = "<CLOUDSQL_URL_VERSION>" in local.database_cutover of infra-gcp/environments/$ENV/main.tf. It reaches $ENV_BRANCH with the next promotion, and the apply then changes nothing.
2. Refuse writes on Azure: azure_write_refusal = true in infra/environments/$ENV/main.tf (default_transaction_read_only = on, dynamic, no restart). Same promotion.
3. Lift the merge freeze.
4. Disable the copy job, so nobody dumps a stale Azure database over live data: enabled = false in the database_copy module (its MODE=fresh default already refuses a target with tables).

​

Rollback

1. Drain the Cloud SQL revision and wait for in-flight work to finish, so nothing commits on Cloud SQL after the decision:
   gcloud run services update $SERVICE --region=$REGION --project=$GCP_PROJECT --scaling=0
   
   Wait until run.googleapis.com/container/instance_count for the service reads 0, then record the Cloud SQL write counts (cloudsql.googleapis.com/database/postgresql/tuples_processed_count, operation_type insert, update and delete) from step 4 to now. That is the data rollback abandons.
2. If step 6.2 has applied, or the window used the read-only variant, lift the Azure write refusal before anything connects. The Terraform path is a promotion, so make the change by hand and record it in the same task:
   az postgres flexible-server parameter set --subscription $AZ_SUB -g $AZ_RG -s $AZ_SERVER \
     --name default_transaction_read_only --value off
   az postgres flexible-server parameter show --subscription $AZ_SUB -g $AZ_RG -s $AZ_SERVER \
     --name default_transaction_read_only --query value -o tsv
   
   The parameter is dynamic; show must print off. Then set azure_write_refusal = false in infra/environments/$ENV/main.tf, or the next Azure apply turns the refusal back on.
3. Add an Azure version and bring the service back on it:
   gcloud secrets versions access $AZURE_URL_VERSION --secret=$ENV-DatabaseUrl --project=$GCP_PROJECT \
     | gcloud secrets versions add $ENV-DatabaseUrl --data-file=- --project=$GCP_PROJECT
   export ROLLBACK_URL_VERSION="$(gcloud secrets versions list $ENV-DatabaseUrl --project=$GCP_PROJECT --sort-by=~createTime --limit=1 --format='value(name)')"
   gcloud run services update $SERVICE --region=$REGION --project=$GCP_PROJECT \
     --scaling=auto --update-secrets=DATABASE_URL=$ENV-DatabaseUrl:$ROLLBACK_URL_VERSION
   gcloud run services update-traffic $SERVICE --region=$REGION --project=$GCP_PROJECT --to-latest
   
   The new version holds the Azure URL and is the newest, so latest is Azure again and a later apply cannot move the service back to Cloud SQL. Do not disable the Cloud SQL version instead: latest keeps pointing at the newest version even when it is disabled, and new instances then fail to start. If step 6.1 has already pinned the Cloud SQL version in Terraform, change that pin to ROLLBACK_URL_VERSION in the same change as step 2’s refusal flip.
4. Prod: az webapp start --subscription soundcheck -g prod-cus-backend-rg -n prod-cus-api-app. Check that azure_connections rises, that a write succeeds (the step 5 API checks), and that pushes return 200.

​

Legacy Go API (prod)

​

Prod gates

• The scheduled go for the window.
• A qa run of this runbook end to end.
• Decision 34: the Prisma schema engine that runs migrate deploy does not read sslrootcert and treats verify-ca as prefer, so that path encrypts without verifying the certificate.
• Decisions 12 and 42: the shared Azure admin password rotated, so rollback does not depend on an exposed credential.
• R2: the Azure firewall narrowed (database_firewall_rules, starting with AllowAll_2025-2-14_15-15-43).
• teams-backup: a second public Flexible Server in prod-cus-database-rg, not in Terraform, purpose unknown.