Postgres on Google Cloud / Lesson 9 of 9
Four facts change what you do in an incident. Learn them now, because the incident is a bad time to find out.
Managed PostgreSQL takes backups for you, and that is roughly where most
teams' understanding stops. It is enough right up until the morning someone runs
a DELETE without a WHERE clause, and then four
specifics decide how the next hour goes.
They get conflated constantly, and the distinction decides which one you should have configured.
| Backup | Export | |
|---|---|---|
| What it is | Google's docs: it encompasses the entire disk of the instance |
A pg_dump or CSV file written to Cloud Storage.
Exports can select specific contents. |
| Where it lives | Inside the service. You do not hold the bytes. | A bucket you own and whose lifecycle you manage. |
| Can you download it? | No. | Yes — it is an ordinary object. |
| Restores into | A Cloud SQL instance, and only that. | Anything that speaks PostgreSQL. |
| Granularity | The whole instance. | One database, one schema, one table. |
| Speed to recover | Fast, and operationally simple. | Slower, and you run the restore yourself. |
Backups cover the instance failed and we corrupted the data. They do not cover recover one table into a developer's laptop, hand the data to an auditor, or leave the cloud. Those need exports. Most teams that need both discover it while needing the second one.
Two settings, and the second is the one people miss:
# automated backups — the prerequisite for everything else
gcloud sql instances patch INSTANCE_NAME \
--backup-start-time=03:00
# point-in-time recovery, and how far back it can reach
gcloud sql instances patch INSTANCE_NAME \
--enable-point-in-time-recovery \
--retained-transaction-log-days=7
| Setting | What it governs |
|---|---|
| Backup retention | How many snapshots you keep. Determines which discrete moments you can return to. |
| Transaction log retention | How far back PITR can reach. This is the one that matters in an incident, and it is set separately. |
The log retention range depends on the edition. Google's documentation gives
between 1 and 7 days, with a default of 7 days
for Enterprise, and
between 1 and 35 days, with a default of 14 days
for Enterprise Plus.
Note also that PITR is enabled by default when you create a Cloud SQL
Enterprise Plus edition instance
— and is not on by default
for Enterprise instances created through gcloud or Terraform.
If your instances are declared in Terraform, there is a real chance PITR is off and nobody has looked. Go and read the current value rather than the intent:
gcloud sql instances describe INSTANCE_NAME \
--format="value(settings.backupConfiguration)"
Logs cost disk. From the docs: keeping more days of transaction logs
requires a bigger storage size.
That is the trade — a longer recovery window
against storage — and it is worth deciding deliberately rather than inheriting a
default.
This is the distinction to have straight before an incident, because the two commands look similar and do very different things.
gcloud sql backups list --instance=INSTANCE_NAME
gcloud sql backups restore BACKUP_ID \
--restore-instance=INSTANCE_NAME
Google's wording is worth quoting exactly: the restore operation overwrites
all data on the target instance
, and the target instance is unavailable
for connections during the restore operation; existing connections are
lost.
So restoring onto the live instance is itself a destructive act. If your diagnosis is wrong, you have replaced bad data with differently bad data and destroyed the evidence.
gcloud sql instances clone SOURCE_INSTANCE NEW_INSTANCE \
--point-in-time '2026-01-15T16:19:00.000Z'
A point-in-time recovery always creates a new instance; you cannot perform
a PITR to an existing instance.
The timestamp is UTC, RFC 3339
— and reaching for a local-time value here is a genuinely easy way to recover to
the wrong moment.
Because PITR cannot touch the running instance, you can recover to 10:14:59 — one second before the bad statement — inspect the result, compare it against production, and decide. The live instance is untouched the whole time. This is almost always the right first move in an incident, even when you intend to end up doing an in-place restore.
| Situation | Reach for |
|---|---|
| Someone deleted rows an hour ago | PITR clone to just before it, then copy the rows back. Nothing else is disturbed. |
| A migration corrupted the schema, caught in minutes | PITR clone to verify the good state, then decide between repointing traffic and restoring in place. |
| The instance is gone or unrecoverable | Restore a backup to a new instance. |
| You need one table, not the database | PITR clone, then pg_dump the one table out
of the clone. |
| Refreshing staging from production | Clone, no timestamp. Then re-run your grants, and think hard about the customer data you just copied. |
This is where this lesson meets lesson 7.
A restore brings back objects as they were, and any
GRANT … ON ALL TABLES you ran afterwards described a different set
of tables. The result is the same silent failure: users connect fine and cannot
read.
-- run the bootstrap again, on the restored or cloned instance
-- it is idempotent, so this is free and boring
\i bootstrap.sql
Make this a step in the runbook, not something someone remembers. See the role pattern, whose whole design assumes it will be re-run.
From the docs: after you restore from a backup, the target instance's
backup configurations are set to default values.
So a restore can quietly
turn off the PITR you were relying on. Check it afterwards. Database flags are
not carried across either.
You must delete all replicas and recreate them after the restore operation
completes.
If your read traffic goes to a replica, that is a second outage
inside the first one, and it belongs in the plan.
The clone has the data, not the access. The IAM database users and grants have to be established on it before anyone can connect — and in an incident that step is usually discovered rather than planned. Same three layers as lesson 4, applied to a brand-new instance under time pressure.
| Failure | Covered? |
|---|---|
| Disk or zone failure | Yes. |
Bad UPDATE or DELETE | Yes — PITR, if the log window reaches back far enough. |
| A bad migration | Yes, same. |
| Data corrupted slowly over weeks | Only if you notice inside the window. This is the one long retention actually buys. |
| Someone deletes the instance | Only via retained backups — and only if that was configured beforehand. |
| Region-wide outage | Only if backups are also stored elsewhere. |
| Needing data outside Google Cloud | No. That is an export. |
Backups are tied to the instance. Deleting the instance is, by default, the end of them — the retained-backup behaviour has to be configured before the deletion, not discovered after it. If your recovery plan assumes otherwise, verify it now, on a throwaway instance, while it costs nothing to be wrong.
The only evidence that a backup works is a restore that produced a working database. Everything else is a green tick in a console saying a job completed.
Rows were deleted an hour ago. Production is otherwise fine. First move?
A PITR clone cannot touch the running instance, so it costs you nothing to be wrong. Restoring in place overwrites everything and destroys the evidence, including the hour of good writes since the deletion.
After a successful restore, users report
permission denied for table. Why?
Layer 3 does not survive a restore, and nothing reports it. Re-run the grants script — which is why it is written to be idempotent. Layers 1 and 2 are untouched on an in-place restore.
An auditor wants last quarter's data as a file. What do you need?
Backups cannot leave the service. You would clone or restore to reach the data, then export from there to get a file. Option 3 is the tempting one and describes something that does not exist.
From memory: the difference between restoring a backup and a PITR clone, and the step that follows both.
A restore overwrites the target in place and drops its connections. A PITR clone always creates a new instance and leaves the original untouched. After either, re-run the grants — and after a clone, establish the IAM database users too, because the new instance has none.