The Rollback That Never Comes: an automated disaster-recovery drill on Cloud SQL for PostgreSQL

2026-08-26 cloud devops

Databases are very good at undoing mistakes — right up until the moment they aren’t. As long as a transaction is open, ROLLBACK will erase anything: a bad UPDATE, a wrong DELETE, a half-applied schema change. The database gives you an undo button, and it works every time.

But a schema migration that succeeds is a different animal. Every statement ran, every statement committed, the migration tool printed its green checkmark — and the data is wrong anyway. Maybe a type conversion truncated values. Maybe a backfill used the wrong join. The damage is durable, the transaction that caused it is long gone, and ROLLBACK is simply not on the table anymore. Reverting the migration file in git doesn’t help either: running the reverse migration on corrupted data just converts corrupted data back. Once a faulty migration commits, you are no longer in rollback territory. You are in disaster recovery.

Most teams handle this class of failure with a sentence: “we have backups.” Far fewer have ever rehearsed the restore path, and fewer still have checked that a restore actually reproduces the exact pre-incident state rather than something that merely looks plausible. That gap — between having backups and having proven the loop that uses them — is what this lab is about.

So we rehearse the whole thing, for real, with no human in the loop: a Terraform-provisioned Cloud SQL for PostgreSQL instance, a seeded dataset with known-good ground truth, an on-demand backup, a migration with a deliberately silent bug, an invariant check that catches it, an automatic in-place restore triggered by the runner itself, and a final checksum-by-checksum proof that the restored database is byte-for-byte the pre-migration database. One command runs the drill; it exits 0 only if the entire detect–restore–verify chain holds.

Everything in this post comes from the runnable lab linked above. The run described here is a real one: a fresh clone, a real GCP project, a real db-f1-micro instance in europe-west3, about 35 minutes of wall time, well under $1 of spend.

An instance you can afford to break

You cannot rehearse disaster recovery on an instance you’re afraid to lose, so the first requirement is an instance that is entirely disposable: created by code, destroyed by code, no state anywhere except Terraform’s. The lab keeps the Terraform deliberately minimal — one instance, one database, one application user, public IP restricted to your own address.

labs/lab-gcp-postgres-disaster-recovery/terraform/05_cloudsql.tf:

resource "google_sql_database_instance" "postgres" {
  name                = "${local.instance_prefix}-${random_id.instance_suffix.hex}"
  database_version    = var.database_version
  region              = var.region
  deletion_protection = false

  settings {
    tier    = var.tier
    edition = "ENTERPRISE"

    ip_configuration {
      ipv4_enabled = true

      authorized_networks {
        name  = "reader"
        value = var.authorized_cidr
      }
    }
  }

  depends_on = [google_project_service.sqladmin]
}

Three details are doing quiet work here. deletion_protection = false is the honest admission that this instance exists to be destroyed. The random hex suffix in the name matters because Cloud SQL reserves a deleted instance’s name for about a week — with a fixed name, re-running the lab the day after a --destroy would fail on a naming conflict. And authorized_networks pins access to a single CIDR — your public IP, supplied via terraform.tfvars — so the instance is reachable from your laptop and nowhere else.

Defaults keep the blast radius and the bill small: db-f1-micro, PostgreSQL 17, europe-west3, all overridable.

labs/lab-gcp-postgres-disaster-recovery/terraform/01_variables.tf:

variable "tier" {
  description = "Machine tier for the instance (smallest shared-core by default)."
  type        = string
  default     = "db-f1-micro"
}

variable "authorized_cidr" {
  description = "Your public IP in CIDR notation (e.g. 203.0.113.7/32); the only network allowed to reach the instance. No default on purpose."
  type        = string
}

./scripts/deploy_cloud.sh wraps terraform init and terraform apply; in the verified run the apply took 16.5 minutes, almost all of it Cloud SQL provisioning. A helper script then reads the Terraform outputs into a gitignored .env, and from that point on a single Node.js process — npm run drill — drives everything over two channels: port 5432 for SQL, and the Cloud SQL Admin API for backups, restores, and instance state.

Ground truth first: seed, checksum, backup

A recovery drill is only as good as its definition of “correct.” The drill’s first move is therefore to manufacture correctness it can later check against: 500 orders with deterministic contents — no randomness, so every run of the lab produces the identical dataset.

labs/lab-gcp-postgres-disaster-recovery/src/drill/seedData.ts:

// Deterministic pseudo-data: same input, same rows, no randomness.
// Prices deliberately land off full euros (199..1098 cents) so a
// truncating integer division cents -> euros visibly corrupts them.
export function generateOrders(count: number): Order[] {
  const orders: Order[] = [];
  for (let i = 1; i <= count; i++) {
    const quantity = (i % 5) + 1;
    const unitPriceCents = 199 + ((i * 37) % 900);
    orders.push({
      id: i,
      item: `item-${(i % 7) + 1}`,
      quantity,
      unitPriceCents,
      totalCents: quantity * unitPriceCents,
    });
  }
  return orders;
}

Note the prices: every unit price deliberately lands off a full euro. That’s the trap being set for the migration we’ll ship in a moment.

Alongside the orders, the seed writes down one more thing — a control_totals row holding the grand total in cents, recorded while the data is still known to be good.

labs/lab-gcp-postgres-disaster-recovery/src/drill/seed.ts:

    // The control total is written down BEFORE anything can go wrong;
    // it is the ground truth the invariant check compares against.
    await client.query(
      "INSERT INTO control_totals (id, grand_total_cents) VALUES (1, $1)",
      [grandTotalCents(orders)],
    );

This is the drill’s ledger: a fact about the data, stored before the risky change, that any value-preserving migration must leave intact.

Then comes the part that makes “prove the restore worked” possible at all: a deterministic checksum per table, computed entirely in SQL.

labs/lab-gcp-postgres-disaster-recovery/src/db/checksums.ts:

// One deterministic checksum per table: hash every row's text
// representation, then hash the sorted concatenation. Sorting by the
// row hash makes the result independent of physical row order, so two
// databases with the same logical content always produce the same
// checksum — which is exactly the property a restore must reproduce.
export function buildTableChecksumQuery(table: string): string {
  if (!IDENTIFIER_PATTERN.test(table)) {
    throw new Error(`Unsafe table name: ${table}`);
  }
  return `
    SELECT md5(coalesce(string_agg(row_hash, ',' ORDER BY row_hash), 'empty')) AS checksum
    FROM (SELECT md5(t::text) AS row_hash FROM ${table} t) rows
  `;
}

The ordering detail is the whole point. A restored instance has no obligation to store rows in the same physical order as the original, so hashing rows in table order would produce false mismatches. Hashing each row individually and sorting by the row hash makes the checksum a function of the table’s logical content only — two databases with the same rows produce the same checksum, however those rows are laid out. In the verified run, the pre-migration baseline came out as:

"checksums":{"orders":"118d48fcde3131b5ad1aeb3efdaae6f0","control_totals":"dda6aa4000a247d2f897d864935ee87f"}

Only now — ground truth recorded, baseline hashed — does the drill take its backup, via the Admin API rather than the console. Backup creation is a long-running operation, so the code polls the operation to completion and then refuses to take “done” for an answer: it looks up the backup run itself and checks its status.

labs/lab-gcp-postgres-disaster-recovery/src/gcp/sqlAdmin.ts:

    const { data: operation } = await sql.backupRuns.insert({
      project,
      instance,
      requestBody: { description: "pre-migration drill backup" },
    });
    if (!operation.name) {
      throw new Error("Backup insert returned an operation without a name");
    }
    await waitForOperation(deps, operation.name, BACKUP_TIMEOUT_MS);
    const backupRunId =
      operation.backupContext?.backupId ?? (await findLatestBackupRunId(deps));
    const { data: run } = await sql.backupRuns.get({
      project,
      instance,
      id: backupRunId,
    });
    if (run.status !== "SUCCESSFUL") {
      throw new Error(
        `Backup run ${backupRunId} finished with status ${run.status}`,
      );
    }

In the real run the backup was quick — 40 seconds of polling — and the backupRunId it returned is the single most important value in the drill: it’s the thread that connects the “before” world to the recovery that comes later.

The ordering here is the first transferable lesson. Seed, then checksum, then backup, then migrate: the backup is taken immediately before the risky change, so the recovery point is the last known-good state, not last night’s schedule. Snapshot-before-risky-migration is a habit you can adopt tomorrow on any managed database.

The break: four statements, zero errors

Now the drill ships the kind of migration that ends up in a postmortem. The business asked to store euros instead of cents; someone wrote the type conversion in a hurry.

labs/lab-gcp-postgres-disaster-recovery/src/drill/faultyMigration.ts:

// The "cents to euros" migration a team might ship in a hurry. The bug:
// `unit_price_cents / 100` is INTEGER division in Postgres, so 199
// cents becomes 1.00 euros instead of 1.99 — and because every
// statement commits, the damage is durable the moment it runs. A
// correct migration would divide by 100.0. Nothing here errors; the
// corruption is completely silent.
const FAULTY_MIGRATION_STATEMENTS = [
  "ALTER TABLE orders ALTER COLUMN unit_price_cents TYPE numeric(10,2) USING unit_price_cents / 100",
  "ALTER TABLE orders RENAME COLUMN unit_price_cents TO unit_price_eur",
  "ALTER TABLE orders ALTER COLUMN total_cents TYPE numeric(10,2) USING total_cents / 100",
  "ALTER TABLE orders RENAME COLUMN total_cents TO total_eur",
];

The bug is one missing .0. In PostgreSQL, integer / integer is integer division, so the USING clause truncates: 199 cents becomes 1.00 euros, and the missing 99 cents don’t go anywhere — they just stop existing. Every statement succeeds. The migration tool would report success. A smoke test that checks “can I still SELECT from orders?” passes. This is the silent flavor of corruption, chosen deliberately, because it teaches the uncomfortable truth: if your only validation is “did the migration error?”, this failure mode is invisible.

And because the statements committed, the standard reflexes are dead ends. There is no transaction to roll back. Restoring the column types with a reverse migration would multiply the already-truncated values back up — wrong data, now with the original schema. The moment those four statements committed, this stopped being a migration problem.

Detection is a decision

The drill’s fourth stage re-derives the checksums (the orders hash now differs from the baseline; control_totals, which the migration never touches, is unchanged) and then runs two explicit SQL invariants — one at row level, one against the ledger written down at seed time.

labs/lab-gcp-postgres-disaster-recovery/src/drill/invariantCheck.ts:

// Row-level: a value-preserving conversion must keep total = qty * unit.
const ROW_INVARIANT_QUERY =
  "SELECT count(*)::int AS corrupted FROM orders WHERE total_eur <> quantity * unit_price_eur";

// Ledger-level: converting cents to euros must not change the grand
// total recorded in control_totals before the migration ran.
const GRAND_TOTAL_DRIFT_QUERY = `
  SELECT (SELECT grand_total_cents FROM control_totals WHERE id = 1)
       - (SELECT round(sum(total_eur) * 100)::bigint FROM orders) AS drift_cents
`;

In the verified run, the verdict came back unambiguous:

{"corruptedRows":270,"grandTotalDriftCents":23750,"holds":false,"msg":"Checking post-migration invariants succeeded."}

270 of the 500 orders violate total = quantity × unit price, and the books are off by 23,750 cents — €237.50 that silently evaporated in the truncation. (Not all 500 rows trip the row-level check, incidentally: when a unit price truncates and the quantity happens to multiply the truncated price back to the truncated total, that row stays internally consistent while still being wrong — which is exactly why the ledger-level check against an external ground truth exists.)

What happens next is the heart of the lab. Nobody is watching this run. The runner reads the invariant result and makes the call itself:

labs/lab-gcp-postgres-disaster-recovery/src/drill/runDrill.ts:

  // Stage 4: detect. The runner, not a human, decides what happens next.
  const invariant = await stages.invariants(client);
  if (invariant.holds) {
    throw new Error(
      "Invariant unexpectedly holds after the faulty migration; nothing to recover from, aborting the drill",
    );
  }
  if (diffChecksums(baseline, postCorruption).length === 0) {
    throw new Error(
      "Checksums did not change after the faulty migration; corruption evidence is missing",
    );
  }
  logger.warn(
    { corruptedRows: invariant.corruptedRows, backupRunId },
    "Invariant violated; triggering automatic restore...",
  );

Notice that the drill also fails if the invariant unexpectedly holds — a disaster-recovery drill where the disaster doesn’t happen proves nothing, so “nothing to recover from” is treated as a failed run, not a lucky one. In the real run, the log line Invariant violated; triggering automatic restore... was followed by the restore call 18 milliseconds later. Detection to recovery decision: faster than a human can read the alert.

What an in-place restore actually does

The recovery itself is one Admin API call, plus a lot of patience.

labs/lab-gcp-postgres-disaster-recovery/src/gcp/sqlAdmin.ts:

    const { data: operation } = await sql.instances.restoreBackup({
      project,
      instance,
      requestBody: { restoreBackupContext: { backupRunId } },
    });

An in-place restore is not an incremental operation — Cloud SQL replaces the instance’s data wholesale with the backup’s contents. That has two practical consequences the drill has to engineer around.

First, the restore drops every open connection, so the runner closes its own connection before triggering the restore and reconnects from scratch afterwards. Second, the operation is slow relative to everything else in the drill: in the verified run, polling the restore operation took 14 minutes and 10 seconds — for a 500-row database on the smallest tier Cloud SQL sells. The restore’s cost is dominated by instance mechanics, not data volume, which is worth knowing before you first exercise this path with production sizes and a stopwatch running.

Even “restore finished” isn’t the end. The drill then polls the instance back to RUNNABLE, and treats even that with suspicion:

labs/lab-gcp-postgres-disaster-recovery/src/db/client.ts:

// After an in-place restore the instance drops every connection and is
// briefly unreachable even once it reports RUNNABLE, so the reconnect
// path retries with a fixed delay instead of failing fast.
export async function connectDbWithRetry(

(In the verified run the 14-minute restore wait left the instance fully ready and the first reconnect went straight through — but the retry loop is there because RUNNABLE describes the control plane’s opinion, not a guarantee that the socket will open.)

The in-place approach was a deliberate choice over the alternative — restoring to a new instance. In place keeps the endpoint stable: nothing downstream has to be repointed, which is also what makes the drill loop simple enough to automate. The trade-off is availability (the instance is out of service for the duration) and the loss of the corrupted state, which in a real incident you might want to keep around for forensics. A clone-based restore inverts all three properties.

Proof, not hope

Here is the step most restore stories skip. The instance is back, the app can connect — declare victory? The drill doesn’t. RUNNABLE means the restore operation finished; it says nothing about whether the recovered data is actually the data you meant to recover. So the final stage recomputes every table checksum on the restored instance and compares them against the baseline recorded back in stage 2.

labs/lab-gcp-postgres-disaster-recovery/src/drill/runDrill.ts:

  // Stage 5: recover. The connection is already closed (above); the
  // restore replaces the whole instance state, so reconnect from scratch
  // afterwards.
  await stages.restore(backupRunId);
  const restoredClient = await stages.connectWithRetry();

  // Stage 6: prove. A restore you have not verified is a hope, not a
  // recovery.
  try {
    const restored = await stages.checksums(restoredClient);
    const mismatches = diffChecksums(baseline, restored);
    if (mismatches.length > 0) {
      throw new Error(
        `Restored checksums differ from the pre-migration baseline for: ${mismatches.join(", ")}`,
      );
    }

The three checksum snapshots from the verified run tell the entire story in two hashes:

"checksums":{"orders":"118d48fcde3131b5ad1aeb3efdaae6f0","control_totals":"dda6aa4000a247d2f897d864935ee87f"}
"checksums":{"orders":"49b7077bf7196ae9cd590a495ed0cff6","control_totals":"dda6aa4000a247d2f897d864935ee87f"}
"checksums":{"orders":"118d48fcde3131b5ad1aeb3efdaae6f0","control_totals":"dda6aa4000a247d2f897d864935ee87f"}

Baseline, corruption, restore. The orders checksum diverges after the migration and returns to exactly its baseline value after the restore — not “close”, not “row counts match”, but the same hash over the same logical content. And the divergence in the middle is load-bearing: the drill separately asserts that the post-corruption checksums differed from the baseline, because a proof that can’t distinguish the corrupted state from the correct one isn’t a proof of anything. Only when both conditions hold does the process exit 0:

{"backupRunId":"1787725903366","corruptedRows":270,"grandTotalDriftCents":23750,"tablesVerified":["control_totals","orders"],"msg":"Running the disaster-recovery drill succeeded."}

That exit code is the lab’s real deliverable. The entire detect–restore–verify chain is a single boolean you could wire into CI, a migration pipeline, or a scheduled game-day job.

RPO honesty: what this backup does and doesn’t promise

It’s worth being precise about what the drill’s recovery point actually is, because “we have backups” hides a number: the recovery point objective, or how much committed data you accept losing.

An on-demand backup protects exactly one moment — the moment it was taken. The drill exploits this perfectly: it takes the backup immediately before the risky migration, so the recovery point is the last known-good state and the drill loses nothing. That’s the honest scope of the pattern: snapshot-before-risky-change gives you a zero-loss recovery point for that one hazard, and nothing else. If corruption is instead discovered hours after a migration ran — the common case in production, where damage surfaces in a report, not a post-deploy check — restoring the pre-migration backup also discards every legitimate write since. For a scheduled nightly backup the same arithmetic is worse: the recovery point can be a full day old.

That’s the gap point-in-time recovery exists to close. PITR supplements periodic backups with a continuous record of changes (PostgreSQL’s write-ahead log), letting you restore to an arbitrary timestamp — say, one minute before the bad migration ran — instead of to the last snapshot. In Cloud SQL, a point-in-time restore lands on a new instance rather than replacing the current one, which changes the recovery choreography: applications must be repointed, but the corrupted original stays available for investigation. The trade is operational complexity for a recovery point measured in seconds.

The drill uses the simpler tool because its hazard is known in advance — a scheduled risky change is exactly when snapshot-just-before is optimal. For the unknown hazards, the ones that announce themselves days later, you want PITR enabled as well. The two compose; neither replaces the other. What transfers unchanged in either case is the rest of the loop: an explicit invariant to detect damage, an automated path from detection to restore, and a proof — not a hope — that what came back is what you lost.

Run it, then break it yourself

The whole drill is a handful of commands from a fresh clone, straight from labs/lab-gcp-postgres-disaster-recovery/README.md:

cd terraform && cp terraform.tfvars.example terraform.tfvars   # fill in project_id + your IP
cd .. && ./scripts/deploy_cloud.sh                             # ~10-20 min
./scripts/generate_env.sh
npm install
npm run drill                                                  # ~15-30 min
./scripts/deploy_cloud.sh --destroy

Budget 30–45 minutes end to end; nearly all of it is waiting on Cloud SQL operations (in the verified run: 16.5 minutes of provisioning, 40 seconds of backup, just over 14 minutes of restore), and at db-f1-micro the bill stays well under $1 if you run the destroy at the end.

Even the teardown taught a disaster-recovery-shaped lesson. The first --destroy attempt failed: Terraform deletes the database and the user in parallel, and Cloud SQL refuses to drop a PostgreSQL role that still owns objects — the drill’s tables. The fix is to let the instance deletion do the work.

labs/lab-gcp-postgres-disaster-recovery/terraform/05_cloudsql.tf:

# The database and user are abandoned on destroy instead of deleted:
# the drill's tables are owned by the app user, and Cloud SQL refuses
# to drop a Postgres role that still owns objects, which made the
# first destroy attempt fail. The instance deletion right after wipes
# both anyway, so skipping their individual deletes makes teardown
# succeed in a single pass.
resource "google_sql_database" "app" {
  name     = var.db_name
  instance = google_sql_database_instance.postgres.name

  deletion_policy = "ABANDON"
}

Which is the lab’s thesis in miniature: every path you haven’t actually executed — restore or teardown — is a path you should assume is broken. The only recovery plan worth having is one that has already run, decided on its own, and proven its result. Now you have one you can rehearse for under a dollar — and a blueprint to port to whatever database you actually can’t afford to lose.