Postgres on Google Cloud / Lesson 9 of 9

Backups and point-in-time recovery

Four facts change what you do in an incident. Learn them now, because the incident is a bad time to find out.

≈ 20 minutes · recovery · the lesson you hope to never need

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.

1
A backup is not an export, and you cannot download one
Backups live inside the service and restore into an instance. If you need a file, that is a different operation.
2
Restoring a backup overwrites the target
In place, destructively, with connections dropped.
3
Point-in-time recovery always makes a new instance
Which is a feature, not a limitation — it is what makes it safe to use during an incident.
4
After any restore, your PostgreSQL grants are stale
Layer 3 from lesson 4 does not survive. This one is ours to remember; nothing reports it.

Backups and exports are different things

They get conflated constantly, and the distinction decides which one you should have configured.

BackupExport
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.
"We have backups" may not answer the question you were asked

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.

Turning it on properly

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

Check, do not assume

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.

Restore overwrites. Clone does not.

This is the distinction to have straight before an incident, because the two commands look similar and do very different things.

Restoring a backup

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.

Point-in-time recovery

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.

Read that limitation as a safety feature

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.

Which to reach for

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

The things that bite after a restore

Your grants are stale — every time

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.

The target's own settings get reset

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.

Read replicas must be deleted and recreated

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.

A PITR clone is a new instance, so layers 1 and 2 are empty

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.

What is protected, and what is not

FailureCovered?
Disk or zone failureYes.
Bad UPDATE or DELETEYes — PITR, if the log window reaches back far enough.
A bad migrationYes, same.
Data corrupted slowly over weeksOnly if you notice inside the window. This is the one long retention actually buys.
Someone deletes the instanceOnly via retained backups — and only if that was configured beforehand.
Region-wide outageOnly if backups are also stored elsewhere.
Needing data outside Google CloudNo. That is an export.
The one worth acting on today

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.

A backup you have never restored is a hypothesis

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.

Check yourself

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.