> ## Documentation Index
> Fetch the complete documentation index at: https://docs.soundchecklive.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Database cutover: Azure PostgreSQL to Cloud SQL

> Runbook for moving an environment's database from Azure Database for PostgreSQL to Cloud SQL with the db-copy job inside a short write freeze

This runbook moves one environment's database from its Azure Flexible Server into the environment's Cloud SQL instance, then switches api-events to Cloud SQL. api-events is drained to zero, the `<env>-db-copy` Cloud Run job dumps Azure and restores into Cloud SQL in one transaction, and api-events comes back on a new `DatabaseUrl` version that points at Cloud SQL.

qa and prod use this runbook (decision 2026-11-03: dump and restore, not DMS). Dev moved with DMS on 2026-11-02; that path is in the git history of this page and in `infra-gcp/modules/database-migration`. Dev and qa are approved to run. Prod needs its own scheduled go.

| Piece | Where |
| - | - |
| Copy job (`<env>-db-copy`), its checks and modes | `infra-gcp/modules/database-copy` (README) |
| `DatabaseUrl` pin, Cloud SQL CA mount | `infra-gcp/modules/events` (`database_url_secret_version`, `database_server_ca_secret_id`) |
| Cloud SQL instance, app login, CA secret | `infra-gcp/modules/database` |
| Azure write refusal | `infra/modules/database`, `azure_write_refusal` in `infra/environments/<env>/main.tf` |

## Timings

| Run | Dump | Restore | Row counts | Job total |
| - | - | - | - | - |
| qa rehearsal `qa-db-copy-t7kxr` (2026-11-05) | 16s, 3.7 MB | 15s | about 2.5 min, 382 tables, 0 differ | 5 min |
| prod rehearsal `prod-db-copy-6c2kt` (2026-11-05) | 38s, 31 MB | 23s | about 2.7 min, 382 tables, 0 differ | 6 min 37 s |
| qa cutover `qa-db-copy-dqqtw` (2026-11-05, `MODE=replace`) | 19s | 17s | about 2.7 min, 382 tables, 0 differ | 6 min |
| prod cutover `prod-db-copy-4qsnw` (2026-11-10, `MODE=replace`) | 27s, 33 MB | 30s | about 2.9 min, 411 tables, 0 differ | 5 min 20 s |

About 2.5 minutes of each job is startup and the `ca-certificates` install. The qa cutover froze writes from 17:32:15 to 17:49:48 UTC (17.5 minutes): 9 minutes waiting for the Azure connection metric to settle after the drain, 6 minutes of copy, 1.5 minutes for the new revision. Adding the Cloud SQL `DatabaseUrl` version during the drain wait, as qa did, keeps it off the critical path.

Prod used the read-only variant below instead of the drain, and froze writes from 00:37:57 to 00:48:55 UTC (11 minutes). A first attempt at 00:15 was aborted after 10 minutes because the job's `ca-certificates` install could not reach the Debian mirrors twice in a row; a `MODE=fresh` dry run 5 minutes later reached them. Run that dry run right before every window.

## Read-only variant

Prod kept api-events serving reads during the window instead of draining it:

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:

   ```sql theme={null}
   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.

Instances that start during the window still boot: `prisma migrate deploy` with no pending migration takes only an advisory lock, which a read-only server allows. Do not deploy a new migration during the window. Saves fail with a 500 and scheduled jobs, pushes and webhooks retry; a handler that sends an email or text before writing its record can send it twice.

## 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

```bash theme={null}
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
```

| Env | `ENV_BRANCH` | `GCP_PROJECT` | `SERVICE` | `CLOUDSQL_IP` | `API_HOST` | `AZ_SUB` / `AZ_RG` / `AZ_SERVER` |
| - | - | - | - | - | - | - |
| qa | `qa` | `qa-soundcheck-live` | `qa-api-events` | `10.74.160.3` | `qa-api-events.soundchecklive.io` | `qa-soundcheck` / `qa01-cus-database-rg` / `qa01-cus-core-postgres` |
| prod | `main` | `soundcheck-live` | `prod-api-events` | `10.95.224.3` | `api-events.soundchecklive.io` | `soundcheck` / `prod-cus-database-rg` / `prod-cus-core-postgres` |

Helpers:

```bash theme={null}
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.

   ```bash theme={null}
   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:

   ```bash theme={null}
   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](#legacy-go-api-prod)).

## 2. Drain

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

Prod only: `az webapp stop --subscription soundcheck -g prod-cus-backend-rg -n prod-cus-api-app`.

`--scaling=0` disables the service: requests get `Service unavailable` and in-flight requests finish. Repeat `azure_connections` until the count falls to the server's own sessions (the `azuresu`, Azure agent and pg\_cron sessions) and stays there. On qa it fell from 17 to a floor of 7 to 8 over about 6 minutes. The metric lags by one to two minutes. Also check that no other service reads `$ENV-DatabaseUrl` and that the service's `run.googleapis.com/container/instance_count` is 0.

Scheduled `run-*` subscriptions that push to `$API_HOST` keep failing while the service is at zero. With a 10-second to 600-second backoff and 10 delivery attempts, a message reaches its dead-letter topic only after about 40 minutes, so a window under 30 minutes needs no push hold. If the window will run longer, save and clear the push configs as in the dev runbook (step 8.2 in this page's history) and restore them after step 4.

## 3. Copy

```bash theme={null}
gcloud run jobs execute $ENV-db-copy --region=$REGION --project=$GCP_PROJECT --update-env-vars=MODE=replace --wait
```

The override applies to this execution only. Read the job log and require `restore done`, `tables: <n>, differing: 0` and `copy finished`. Row counts are exact here, because nothing writes to Azure during the freeze. A failed restore rolls back on Cloud SQL and leaves the previous copy; Azure is untouched, so the service returns on the Azure version ([Rollback](#rollback), first paragraph; step 3 in full if the Cloud SQL version was already added during the drain).

## 4. Switch

1. Add the Cloud SQL `DatabaseUrl` version. The CA path is the `database_server_ca_path` output of `modules/events`.

   ```bash theme={null}
   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:

   ```bash theme={null}
   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:

  ```bash theme={null}
  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.

Once the Cloud SQL revision serves, scheduled jobs and users write to it within seconds, and a rollback to Azure loses those writes ([Rollback](#rollback)). Do the first two checks within minutes of step 4.

## 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).

Later, after the observation period: delete the Azure server only per brief step 9 (final backup first).

## Rollback

A copy that fails before step 4.1 needs only the `services update` and `update-traffic` commands of step 3 below, with `ROLLBACK_URL_VERSION=$AZURE_URL_VERSION`: Cloud SQL took no writes and `latest` is still the Azure version. After the read-only variant, also run step 2 first, because Azure refuses writes from the start of that window. Once step 4.1 has added the Cloud SQL version, `latest` points at Cloud SQL and the service reads `latest` until step 6.1, so the next apply would move it back to Cloud SQL without the writes Azure took in between. From then on, always run step 3 in full, which makes an Azure copy the newest version, even when the service never served on Cloud SQL.

Once the Cloud SQL revision has served, scheduled jobs write within seconds, so any rollback loses those writes. Roll back only when the approver accepts that loss, and record it. Then:

1. Drain the Cloud SQL revision and wait for in-flight work to finish, so nothing commits on Cloud SQL after the decision:

   ```bash theme={null}
   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:

   ```bash theme={null}
   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:

   ```bash theme={null}
   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.

After the first Cloud SQL write, rolling back means copying Cloud SQL back into Azure, which this tooling does not build. Forward-fix on Cloud SQL is the default.

## Legacy Go API (prod)

The Go API (`prod-cus-api-app`, App Service in `prod-cus-core-vnet`) holds its own `DATABASE_URL` and had 3 open sessions on the prod database on 2026-10-20. Cloud SQL has a private IP only, so the Go API cannot reach it. It stops at the drain and starts again only for a rollback to Azure before the first Cloud SQL write (decision 22). qa has no Go API.

## Prod gates

Prod does not start until each of these is closed or carries an approved exception:

* 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.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.