Cloud SQL point-in-time recovery: enable it, restore to a timestamp, verify the clone

You need the database back to exactly 14:32 UTC — right before a bad migration ran — without touching anything written after that moment. Cloud SQL point-in-time recovery (PITR) does this by cloning your instance to a chosen timestamp, but only if you enabled the right combination of settings before the incident. This guide covers setup, a restore drill, and the checks that tell you the feature actually works.

What PITR requires

PITR depends on two things being enabled together on the instance:

• Automated backups — the base snapshot the restore starts from.
• Transaction logs — binary logging on MySQL, or WAL archiving (the point_in_time_recovery flag) on PostgreSQL. These capture every change between backups.

Neither alone is sufficient. Without transaction logs you can only restore to a backup boundary, which may be hours before the incident.

Two constraints shape the recoverable window:

1. Log retention. You configure how many days of transaction logs are kept. The earliest restorable time is bounded by the oldest retained log and the oldest available backup.
2. Storage pressure. Retained logs consume disk. Under storage pressure Cloud SQL may prune logs earlier than your configured retention, silently shrinking the window. Monitor disk usage rather than assuming the configured number of days is guaranteed.

Version note: exact flag names, defaults, and whether enabling binary logging on an existing MySQL instance requires a restart vary by engine version. Historically, enabling binary logging on a running MySQL instance triggered a restart, so plan a maintenance window. Confirm current behavior in the Cloud SQL documentation for your engine and major version before scheduling the change.

Enable PITR

Run these from any machine with the Cloud SDK installed, authenticated as a principal with cloudsql.instances.update on the instance (for example, the Cloud SQL Admin role). Replace INSTANCE_ID, PROJECT_ID, and retention values with your own.

gcloud sql instances patch INSTANCE_ID \\\n  --project=PROJECT_ID \\\n  --enable-bin-log \\\n  --retained-backups-count=7 \\\n  --retained-transaction-log-days=7

For PostgreSQL, the equivalent is enabling automated backups plus the point-in-time recovery flag:

gcloud sql instances patch INSTANCE_ID \\\n  --project=PROJECT_ID \\\n  --backup-start-time=02:00 \\\n  --enable-point-in-time-recovery \\\n  --retained-transaction-log-days=7

Expected check afterwards:

gcloud sql instances describe INSTANCE_ID \\\n  --format=\"yaml(settings.backupConfiguration)\"

Confirm the output shows backups enabled, binary logging (or PITR) enabled, and your retention values. If the instance restarted during the change, verify application connectivity before leaving the maintenance window.

Restore to a timestamp

PITR never modifies the original instance. It creates a clone — a new instance restored to your chosen time. Timestamps are UTC; timezone mistakes are the most common cause of restoring to the wrong point, so convert your target time explicitly.

gcloud sql instances clone SOURCE_INSTANCE CLONE_INSTANCE \\\n  --project=PROJECT_ID \\\n  --point-in-time=\"2026-09-21T14:32:00.000Z\"

Cloning takes roughly as long as restoring a backup plus replaying logs — plan for tens of minutes on a large instance. When the clone is reachable, verify it before any cutover:

1. Query a table you know changed at the incident time. The clone should show the pre-incident state.
2. Check instance settings: flags, authorized networks, and read replicas are not guaranteed to carry over exactly. Re-verify each against the source.
3. Confirm the clone's data ends at your timestamp — nothing written after 14:32 UTC exists on it.

Cutover means pointing the application at the clone (new connection name or IP) or promoting it, depending on your topology. Test this path in a non-production project first; connection-string changes often touch secrets managers, VPC peering, and private service access.

Limitations and the recovery gap

• Writes after the timestamp are lost from the clone. If users kept writing to the original instance after the incident, export or capture those rows separately before cutover and re-apply them manually.
• The window is not a promise. Verify the actual earliest restorable time in the console or via gcloud rather than trusting the configured retention number, especially after disk-resize events or write-heavy periods.
• Clones cost money. A forgotten clone bills like a normal instance. Delete drill clones after verification.

Prove it works with a drill

Configuration checks tell you PITR is on; only a drill tells you it works:

1. In a test instance, insert a recognizable row and note the exact UTC time.
2. Delete the row, then clone the instance to a timestamp between the insert and the delete.
3. Query the clone: the row must be present. If it is missing, your timestamp, retention, or log configuration is wrong — better to learn that now.
4. Watch disk usage and retained-log metrics for a week to confirm the retention window holds under your real write volume.

Run this drill after any major version upgrade or storage change, since both can affect log retention behavior.