## Summary DBRE-side execution plan for the maintenance window agreed in [gitlab#629079](https://gitlab.com/gitlab-org/gitlab/-/work_items/629079). Two restore tests run back to back in one window, both against the PITR clone `runway-db-smgke-dr`, which is already live and Terraform-managed in `gitlab-runway-staging`. - **Phase A — full cutover.** GSM repoints `secrets-manager-gke` to the clone. We verify OpenBao unseals from the cloned database and that the workload is genuinely reading the clone. - **Phase B — in-place restore.** With GSM still connected to the clone, restore the clone from a backup we take during the window. Measure full recovery including OpenBao leader election and `ha_locks` reconciliation. Then GSM reverts to the original instance and we tear the clone down. Covers Steps 2 and 3 of the testing plan in [dbo#771](https://gitlab.com/gitlab-com/gl-infra/data-access/dbo/dbo-issue-tracker/-/work_items/771). Step 1 (clone → extract → retire, no cutover) is already done. ## Who and when | | | |--|--| | DBRE | @amritasinha | | Reviewer | `@rhenchen.gitlab` | | Application side (GSM) | `@fcatteau` / `@jmallissery` | | Environment | `gitlab-runway-staging` — staging only, no production impact | | Window | \~4h booked, \~1h30m of actual work expected | | Coordination issue | [gitlab#629079](https://gitlab.com/gitlab-org/gitlab/-/work_items/629079) | **Application-side work is owned by GSM and deliberately not spelled out here.** The Sidekiq pause commands are in [Florent's comment on gitlab#629079](https://gitlab.com/gitlab-org/gitlab/-/work_items/629079#note_3876717713); the scope and his approval of restoring the clone while the app is attached are in [dbo#771 note_3834430619](https://gitlab.com/gitlab-com/gl-infra/data-access/dbo/dbo-issue-tracker/-/work_items/771#note_3834430619). Steps marked **GSM** are a wait-and-confirm for us, not something we execute. ## Instance under test | Item | Value | |------|-------| | Clone | `runway-db-smgke-dr` | | Project | `gitlab-runway-staging` | | Connection name | `gitlab-runway-staging:us-east1:runway-db-smgke-dr` | | DNS / PSC IP | `290042e93774.13qev4qyj3w9r.us-east1.sql.goog.` / `10.0.0.30` | | Cloned from | `runway-db-secrets-manager-gke` at PITR `2026-09-08T13:00:00Z` | | Logical DB | `openbao` | | Live instance (untouched throughout) | `runway-db-secrets-manager-gke` | ## How the clone was built (done, nothing to run in the window) The clone was created by the `cloudsql-dr` pipeline on **ops.gitlab.net**, then imported into Terraform by one MR. The job rule is `$CI_SERVER_HOST == "ops.gitlab.net" && $RUN_CLOUDSQL_DR == "true"`, so the jobs do not exist at all on gitlab.com or without the opt-in variable. **It is a manual pipeline run with five variables** — `RUN_CLOUDSQL_DR` is one of them, not a separate setting: | Variable | Value used | Read by | |----------|------------|---------| | `RUN_CLOUDSQL_DR` | `true` | both jobs — rule gate; without it neither job is created | | `ENVIRONMENT` | `staging` | both jobs — `authenticate` validates it, `clone` derives `gitlab-runway-staging` from it | | `SOURCE_INSTANCE` | `runway-db-secrets-manager-gke` | `clone` | | `CLONE_INSTANCE` | `runway-db-smgke-dr` | `clone` | | `POINT_IN_TIME` | `2026-09-08T13:00:00Z` | `clone` | How the run actually goes: 1. On **ops.gitlab.net**, open the provisioner project, then Build → Pipelines → **New pipeline** on the default branch. 2. Add the five variables above on the form and run it. 3. `cloudsql-dr:authenticate` is `when: manual` — **click play on it.** It logs into Vault with the job's ID token and mints a GCP access token, handed to the next job as a dotenv artifact. 4. `cloudsql-dr:clone` is **not** manual. It has `needs: cloudsql-dr:authenticate`, so it starts by itself once authenticate finishes. There is no second button to press. 5. `scripts/cloudsql-dr-clone.sh` validates the clone name first — must start with `runway-db-`, be at most 33 characters (the name is embedded in PSC endpoint names), and must not end with the reserved `-restore-instance` suffix. Then it clones and waits for the operation. Underlying calls made by the script, if you ever need to run it outside CI: ``` gcloud sql instances clone runway-db-secrets-manager-gke runway-db-smgke-dr \ --project=gitlab-runway-staging \ --point-in-time=2026-09-08T13:00:00Z \ --async --format='value(name)' gcloud sql operations wait <OPERATION> --project=gitlab-runway-staging --timeout=unlimited ``` The source instance is only read, never modified. One wrinkle worth knowing: `cloudsql-dr:authenticate` hardcodes `VAULT_GCP_IMPERSONATED_ACCOUNT="gitlab-runway-production--provisioner"` and does not derive it from `ENVIRONMENT`, so a staging run still mints a token for the production provisioner service account. It works, but it is not obvious from reading the form. - Clone pipeline [6204057](https://ops.gitlab.net/gitlab-com/gl-infra/platform/runway/provisioner/-/pipelines/6204057) — `authenticate` 16s, `clone` 15m55s - Import [MR !1864](https://gitlab.com/gitlab-com/gl-infra/platform/runway/provisioner/-/merge_requests/1864) — plan was `1 to import, 31 to add, 1 to change, 0 to destroy`; `prevent_db_deletion: false` set from the start so teardown is a single MR - Teardown path validated earlier on `runway-db-smgke-dr-test`: [!1857](https://gitlab.com/gitlab-com/gl-infra/platform/runway/provisioner/-/merge_requests/1857), [!1862](https://gitlab.com/gitlab-com/gl-infra/platform/runway/provisioner/-/merge_requests/1862) - Job and script: [`.gitlab/ci/cloudsql-dr.yml`](https://gitlab.com/gitlab-com/gl-infra/platform/runway/provisioner/-/blob/main/.gitlab/ci/cloudsql-dr.yml) · [`scripts/cloudsql-dr-clone.sh`](https://gitlab.com/gitlab-com/gl-infra/platform/runway/provisioner/-/blob/main/scripts/cloudsql-dr-clone.sh) - RTO recorded in [note_3803106481](https://gitlab.com/gitlab-com/gl-infra/data-access/dbo/dbo-issue-tracker/-/work_items/771#note_3803106481) --- # Steps Work top to bottom and tick each box as you finish it. ## Before the window - [x] **Confirm the window.** Date and time agreed in gitlab#629079, GSM team notified, calendar hold in place. _(day before)_ - [x] **Have the credentials ready.** Fetch the `postgres` password from 1Password — the exact vault and item are in the internal note on dbo#771. Confirm Cloud SQL Studio opens for both instances. _(5 min)_ - [x] **Set the gcloud context.** _(1 min)_ ``` gcloud config set project gitlab-runway-staging gcloud config list ``` Expected: `project = gitlab-runway-staging`, account is your `@gitlab.com` identity. - [x] **Verify the clone is healthy.** _(1 min)_ ``` gcloud sql instances describe runway-db-smgke-dr \ --project=gitlab-runway-staging \ --format='yaml(name,state,databaseVersion,connectionName,settings.tier,settings.backupConfiguration)' ``` Expected: `state: RUNNABLE`, `databaseVersion: POSTGRES_18`, `connectionName: gitlab-runway-staging:us-east1:runway-db-smgke-dr`, and `backupConfiguration` showing `enabled: true`, `pointInTimeRecoveryEnabled: true`, `transactionLogRetentionDays: 7`, `retainedBackups: 7`, `startTime: '13:00'`. - [x] **Baseline the clone.** Run the appendix snippet against `runway-db-smgke-dr` and record `openbao_kv_store_rows`, `system_identifier` and `db_size`. _(2 min)_ Expected: `system_identifier 7634519855539863568` — the same as the live instance, since the clone is a physical copy of the same Postgres cluster. Row count around 7379 unless GSM has written to it since. - [x] **Baseline the live instance.** Same snippet against `runway-db-secrets-manager-gke`. _(2 min)_ Expected: identical `system_identifier`, higher row count than the clone. The difference is the original clone's data-loss window. - [x] **Open monitoring.** Both dashboards: [secrets-manager-gke: Overview](https://dashboards.gitlab.net/d/secrets-manager-gke-main/secrets-manager-gke3a-overview) for the workload and [runway-db-secrets-manager-gke: Overview](https://dashboards.gitlab.net/d/runway-db-secrets-manager-gke-main/runway-db-secrets-manager-gke3a-overview) for the Cloud SQL instance. Metrics live in the `mimir-runway` datasource. _(1 min)_ ## Phase A — full cutover to the clone Covers DR cases 1, 5 and 6: bad migration or deploy, `DROP DATABASE` or mass loss, storage or index corruption. - [x] **Log the start time.** Wall-clock in CEST and UTC. Everything from here is measured. - [x] **GSM — wait for the Sidekiq pause.** GSM sets the six `run_sidekiq_jobs_SecretsManagement::*` flags to `false` and verifies with `/chatops gitlab run feature list --match run_sidekiq_jobs`. Commands are in [their comment](https://gitlab.com/gitlab-org/gitlab/-/work_items/629079#note_3876717713). **Do not go further until they confirm.** _(\~5 min, GSM)_ - [x] **Record the live instance row count immediately before cutover.** _(1 min)_ ```sql SELECT count(*) FROM openbao_kv_store; ``` Run against `runway-db-secrets-manager-gke`. This is the number the workload leaves behind — note it, because it makes the delta on revert explainable. - [x] **GSM — wait for the repoint and deploy.** GSM changes the Cloud SQL connection name for `secrets-manager-gke` to `gitlab-runway-staging:us-east1:runway-db-smgke-dr` and deploys. We supply the connection name and wait. _(\~15 min, GSM)_ - [x] **Confirm OpenBao came up on the clone** confirm from the database side that the workload is really connected: ```sql SELECT count(*) AS conns, usename, application_name FROM pg_stat_activity WHERE datname = 'openbao' GROUP BY usename, application_name ORDER BY conns DESC; ``` Expected: live connections from the OpenBao workload on the clone. Note the count — it is what you compare against after the restore. - [x] **GSM — wait for application validation.** GSM confirms secrets are visible in the UI and fetchable from a CI job, using their staging test project. _(\~10 min, GSM)_ - [x] **Record Phase A elapsed time.** Start time through to this point. ## Phase B — in-place restore on the clone Runs immediately after Phase A, with GSM still connected to the clone. This is the last-resort path: the instance name stays the same and everything written since the backup is lost. Approved explicitly by GSM in [note_3834430619](https://gitlab.com/gitlab-com/gl-infra/data-access/dbo/dbo-issue-tracker/-/work_items/771#note_3834430619). - [x] **Snapshot the backup configuration** so it can be diffed afterwards. _(1 min)_ ``` gcloud sql instances describe runway-db-smgke-dr \ --project=gitlab-runway-staging \ --format='yaml(settings.backupConfiguration)' > before-restore-backupconfig.yaml cat before-restore-backupconfig.yaml ``` Expected: `enabled: true`, `pointInTimeRecoveryEnabled: true`, `transactionLogRetentionDays: 7`, `retainedBackups: 7`, `startTime: '13:00'`. - [x] **Take a manual backup. This is the restore point.** _(\~3 min)_ ``` gcloud sql backups create --instance=runway-db-smgke-dr --project=gitlab-runway-staging --description="dbo#771 pre in-place restore, joint session" #waits until the backup is done. ``` Expected: the operation reaches `DONE` with no error. Took \~3 min on the solo run. - [x] **Capture the backup ID.** _(1 min)_ ``` gcloud sql backups list --instance=runway-db-smgke-dr --project=gitlab-runway-staging --format="table(id,type,status,windowStartTime)" ``` Expected: the newest row is `ON_DEMAND` / `SUCCESSFUL` with your description. Record its ID and `windowStartTime`. Solo run: backup `1788953575476`, successful at `11:32:55Z`. - [x] **Insert the marker row, a few minutes after the backup.** This is what demonstrates the data-loss window, and it touches nothing OpenBao owns. _(2 min)_ ```sql CREATE TABLE IF NOT EXISTS dr_test ( id serial PRIMARY KEY, note text, inserted_at timestamptz DEFAULT now() ); INSERT INTO dr_test (note) VALUES ('dbo#771 in-place restore marker'); SELECT * FROM dr_test; SELECT table_name FROM information_schema.tables WHERE table_schema='public' ORDER BY 1; ``` Expected: one row timestamped after the backup, and the table list goes from 2 to 3 (`openbao_ha_locks`, `openbao_kv_store`, `dr_test`). Record the gap between backup and insert — it was 5m13s on the solo run. - [x] **Record the pre-restore state.** _(2 min)_ ```sql SELECT count(*) FROM openbao_kv_store; SELECT * FROM openbao_ha_locks; ``` - [x] **Run the in-place restore.** Start the timer. This restarts the instance and drops every connection. The CLI warns that all current data will be lost and requires confirmation. ``` gcloud sql backups restore <BACKUP_ID> \ --project=gitlab-runway-staging \ --restore-instance=runway-db-smgke-dr ``` Expected: confirmation prompt, then a `RESTORE_VOLUME` operation starts. Record the operation ID and the exact start time. - [x] **Wait for the restore to finish.** _(\~9 min)_ ``` gcloud sql operations wait <OPERATION_ID> --project=gitlab-runway-staging --timeout=unlimited gcloud sql operations list --project=gitlab-runway-staging --instance=runway-db-smgke-dr --limit=3 ``` Expected: `RESTORE_VOLUME` / `DONE`. Solo run: op `003ff39b`, `11:41:30 → 11:49:15 UTC`, inside a 9-minute wall-clock window. - [x] **Record the recovery timings. This is the reason the test is joint.** _(\~10 min)_ Capture each of these separately: - Cloud SQL `RESTORE_VOLUME` start → done - First successful workload reconnection, visible in `pg_stat_activity`, in the `secrets_manager_gke_postgres_*` metrics, and in the `cloud-sql-proxy` sidecar logs - Leader election: `secrets_manager_gke_core_leadership_lost` and `secrets_manager_gke_core_leadership_setup_failed` rising, then settling. Watch for `core: leadership lost, stopping active operation`, then `core: entering standby mode` and `core: acquired lock, enabling active operation` in the `app` container logs - Whether the active pod changed — `secrets_manager_gke_core_active` back to exactly one, and on which pod - `openbao_ha_locks` reconciling in Postgres — stale locks from the dropped connections clearing - `secrets_manager_gke_core_unsealed` back to 1 on every pod (auto-unseal from KMS logs `core: vault is unsealed` then `core: unsealed with stored key`) - GSM confirming secrets are readable again in UI and CI The [secrets-manager-gke runbook](https://runbooks.gitlab.com/secrets-manager-gke/) has the matching playbooks: _Leadership lost or failover_ and _Cloud SQL connection or latency_. ```sql SELECT count(*) AS conns FROM pg_stat_activity WHERE datname = 'openbao'; SELECT * FROM openbao_ha_locks; ``` - [x] **Confirm the data-loss window.** _(2 min)_ ```sql SELECT table_name FROM information_schema.tables WHERE table_schema='public' ORDER BY 1; SELECT count(*) FROM openbao_kv_store; SELECT * FROM openbao_ha_locks; ``` Expected: `dr_test` gone, table list back to 2, `openbao_kv_store` count matching what you recorded pre-restore (nothing of OpenBao's was written after the backup, since provisioning is paused), and `openbao_ha_locks` present. Only the post-backup write is lost, exactly as documented. - [x] **Diff the backup configuration.** _(2 min)_ ``` gcloud sql instances describe runway-db-smgke-dr \ --project=gitlab-runway-staging \ --format='yaml(settings.backupConfiguration)' > after-restore-backupconfig.yaml diff before-restore-backupconfig.yaml after-restore-backupconfig.yaml ``` Expected: **no output** — byte-identical, PITR still enabled. This contradicts the current runbook, which warns that the config resets to Google defaults and can leave PITR off. Confirming it on a Terraform-managed instance is what lets us correct that text. If the diff is non-empty, capture it and treat the runbook warning as correct. - [x] **Check Terraform reconciliation.** The open item the solo run could not cover, because that instance was not in `managed.yml`. Run a plan against the provisioner for the staging workspace. _(\~10 min)_ Expected: **no changes** for `runway-db-smgke-dr`. If it wants to change `backup_configuration`, capture the plan output, align the `managed.yml` entry to the restored reality, and merge. - [x] **Confirm PITR is usable again.** _(2 min)_ ``` gcloud sql instances get-latest-recovery-time runway-db-smgke-dr --project=gitlab-runway-staging gcloud sql instances describe runway-db-smgke-dr \ --project=gitlab-runway-staging \ --format='value(settings.backupConfiguration.pointInTimeRecoveryEnabled)' ``` Read the earliest recovery time from the Cloud SQL console PITR panel — there is no gcloud equivalent for it. Expected: PITR enabled, but the window **reset to the moment of the restore**. You cannot recover to a point before it. Solo run: earliest `2026-09-09T11:48:49Z` against a restore that finished `11:49:15Z`. **An in-place restore is not undoable** — this needs to go into the runbook. - [x] **Clean up and close the phase.** _(2 min)_ ```sql DROP TABLE IF EXISTS dr_test; ``` Record total Phase B elapsed time and the isolated restore time. ## Close-out - [ ] **GSM — wait for the revert.** GSM repoints `secrets-manager-gke` back to `runway-db-secrets-manager-gke` and deploys. Confirm OpenBao unseals and secrets are readable, and cross-check the row count against what you recorded before cutover plus whatever the live instance accrued. _(\~15 min, GSM)_ - [ ] **GSM — wait for Sidekiq to be re-enabled.** GSM deletes the six feature flags and verifies provisioning works again on a staging project. _(\~10 min, GSM)_ - [ ] **Merge the teardown MR.** Remove the `runway-db-smgke-dr` entry from `config/runtimes/gke/cloud-sql/managed.yml` and its `import {}` block from `import.tf`. One MR, since `prevent_db_deletion: false` is already in the entry. Confirm the plan destroys the clone, its PSC endpoints, DNS and the restore-testing project, and touches nothing else. _(\~30 min including review)_ - [ ] **Post the results.** Comment on dbo#771 with the Phase A and Phase B RTO tables, in the same format as note_3803106481 and note_3808637095. _(\~20 min)_ - [ ] **Update the runbook.** `docs/runway/cloudsql_backup_restore.md` — write it against what actually ran, including the two corrections: the backup configuration does not reset, and an in-place restore is not undoable. _(separate MR)_ --- **If something goes wrong**: the clone is a throwaway and the live instance runway-db-secrets-manager-gke is never touched, so there is nothing to recover on the database side — GSM reverts the connection name and the workload is back. ## Monitoring - [secrets-manager-gke: Overview](https://dashboards.gitlab.net/d/secrets-manager-gke-main/secrets-manager-gke3a-overview) — the workload. Key metrics, all in the `mimir-runway` datasource: `secrets_manager_gke_core_unsealed`, `secrets_manager_gke_core_active`, `secrets_manager_gke_core_leadership_lost`, `secrets_manager_gke_core_leadership_setup_failed`, `secrets_manager_gke_postgres_*` - [runway-db-secrets-manager-gke: Overview](https://dashboards.gitlab.net/d/runway-db-secrets-manager-gke-main/runway-db-secrets-manager-gke3a-overview) — the Cloud SQL instance - `gcloud sql operations list --project=gitlab-runway-staging --instance=runway-db-smgke-dr --limit=5` - `pg_stat_activity` on the clone, for connection recovery - GSM service logs (`app` container, namespace `secrets-manager-gke`), driven by their team MR for the clone dashboard is up -\> https://gitlab.com/gitlab-com/runbooks/-/merge_requests/11637 Note the metric prefix: the GKE service uses `secrets_manager_gke_*`. `secrets_manager_openbao_*` belongs to the other secrets-manager service and will not show this workload. ## What this window closes - [ ] Workload reconnects to the clone and comes up healthy - [ ] OpenBao unseals from the restored database, both phases - [ ] Backup configuration after an in-place restore does not reset — corrects the runbook - [ ] Terraform reconciliation on a managed instance after an in-place restore - [ ] Observability follows the new instance: service and metrics catalog entries live in the runbooks repo and are keyed on instance name, so Terraform cannot create them — confirm what has to be pre-staged - [ ] OpenBao recovery time including leader election and `ha_locks` reconciliation ## Appendix — baseline snippet ```sql SELECT 'database' AS metric, current_database()::text AS value UNION ALL SELECT 'system_identifier', (SELECT system_identifier::text FROM pg_control_system()) UNION ALL SELECT 'started', pg_postmaster_start_time()::text UNION ALL SELECT 'now', now()::text UNION ALL SELECT 'db_size', pg_size_pretty(pg_database_size(current_database())) UNION ALL SELECT 'openbao_kv_store_rows', (SELECT count(*)::text FROM openbao_kv_store); ``` ## Related - [dbo#771](https://gitlab.com/gitlab-com/gl-infra/data-access/dbo/dbo-issue-tracker/-/work_items/771) — parent - [gitlab#629079](https://gitlab.com/gitlab-org/gitlab/-/work_items/629079) — window coordination, GSM side - [note_3803106481](https://gitlab.com/gitlab-com/gl-infra/data-access/dbo/dbo-issue-tracker/-/work_items/771#note_3803106481) — clone flow RTO - [note_3808637095](https://gitlab.com/gitlab-com/gl-infra/data-access/dbo/dbo-issue-tracker/-/work_items/771#note_3808637095) — in-place restore RTO - [note_3808140645](https://gitlab.com/gitlab-com/gl-infra/data-access/dbo/dbo-issue-tracker/-/work_items/771#note_3808140645) — the three restore paths explained - [runway/team#936](https://gitlab.com/gitlab-com/gl-infra/platform/runway/team/-/work_items/936) — Terraform-native restore gap - [secrets-manager-gke runbook](https://runbooks.gitlab.com/secrets-manager-gke/) — pod health, leadership and failover playbooks - [runway-db-secrets-manager-gke runbook](https://runbooks.gitlab.com/runway-db-secrets-manager-gke/) — the Cloud SQL instance