Skip to content

7.4.2 Database Recovery

Use this runbook when data in a prod database was deleted or damaged, for example by a faulty migration or a bug. Cloud SQL keeps a daily backup and the log of every change, so the instance can be recreated as it was at any moment of the recovery window, next to prod and without touching it.

  • When to use: rows are missing or wrong in a prod database, and the moment the damage started is known or can be found.
  • Impact: depends on the data. Remember that the prod instance holds the databases of every system: a restore over the whole instance would take all of them back in time.
  • Access needed: the prod-db-access grant, approved by a second person (see Identity & Access), and the Cloud SQL admin role on moonitoring-prod-db for the clone.

A zone failure is not a recovery

The prod instance is highly available: if its zone fails, Cloud SQL switches to the standby in a second zone on its own, usually within a minute. This runbook is for damaged data, which the standby copies too.

Recovery window

Point-in-time recovery covers the last 7 days; daily backups are kept for 30 days. Both are set in Terraform. How much data we may lose (recovery point objective) and how long a recovery may take (recovery time objective) are not decided yet.

7.4.2.1 Check

  1. Find when the damage started: the deployment time in the Moonitoring Console, the time of the faulty job, or the WriteCommit that wrote the bad data.

  2. Clone the prod instance to a moment just before the damage. The clone is a new instance; prod is not touched.

    gcloud sql instances clone prod-sql prod-sql-recovery \
  --point-in-time="2026-10-29T08:00:00Z" \
  --project=moonitoring-prod-db

  3. Compare the affected rows on the clone with prod, read-only, through the Dev ORM of the system that owns them.

7.4.2.2 Fix

Choose the smallest repair:

  • Some rows are wrong: copy the correct rows from the clone into prod through the Dev ORM of the system that owns them, as a new WriteCommit. History stays complete, nothing written since is lost, and the other systems are not touched. This is the normal case.
  • One database is unusable: stop the services of the systems that use it (see Where the Systems Run Today), export it from the clone and import it into prod. Everything those systems wrote after the chosen moment is lost, so a second person must agree first.
  • The whole instance is lost: point the services to the clone through a Terraform change, reviewed like any other. This is the only case that moves every system back in time.

7.4.2.3 Verify

  1. The affected rows match the clone.
  2. The services that use the database answer normally.

7.4.2.4 If It Does Not Work

Call the other platform engineer. If the damage is older than the recovery window, the data must be rebuilt from the raw files in Cloud Storage by a re-parse; plan it as an incident.

7.4.2.5 Afterwards

  • Delete the clone: it is a full instance and is billed while it exists.
  • Write a postmortem, and add a test or a migration check that would have caught the cause.