About restoring an instance

This page provides information to review before restoring an instance from a backup or performing a point-in-time recovery (PITR).

What happens during a restore?

For Cloud SQL Enterprise edition and Cloud SQL Enterprise Plus edition, you can restore an instance from a backup. You can also restore backups across instances of different editions.

When you restore an instance, the following data from the primary instance are restored to the new instance:

  • Databases
  • Users

The restore operation causes the instance to restart.

Point-in-time recovery

Point-in-time recovery helps you recover an instance to a specific point in time. For example, if an error causes a loss of data, you can recover a database to its state before the error occurred.

A point-in-time recovery always creates a new instance; you can't perform a point-in-time recovery to an existing instance. The new instance inherits the settings of the source instance, similar to how clone creation works.

Point-in-time recovery is enabled by default when you create a Cloud SQL instance.

Point-in-time recovery uses write-ahead logging (WAL) archiving. By default, point-in-time recovery is enabled for Cloud SQL Enterprise Plus edition instances. All of these instances store their WAL logs in Cloud Storage for the number of log retention days that you specify.

On January 9, 2023, we launched storing WAL logs for point-in-time recovery in Cloud Storage. For Cloud SQL Enterprise edition instances, the following conditions apply for point-in-time recovery:

  • New instances that you create with point-in-time recovery enabled after this launch store their logs in Cloud Storage for the number of log retention days that you specify.
  • Existing instances for which you enable point-in-time recovery after this launch also store their logs in Cloud Storage.

  • Existing instances that had point-in-time recovery enabled before this launch continue to have their logs stored on disk.

After you use a PostgreSQL client such as psql or pgAdmin to connect to a database of the instance, run the following command: show archive_command. If any WALs are archived in Cloud Storage, then you see -async_archive -remote_storage.

If the logs are stored in Cloud Storage, then Cloud SQL uploads logs every five minutes or less. As a result, if a Cloud SQL instance is accessible, then the instance can be recovered to the latest time. However, if the instance isn't available, then the recovery point objective is typically five minutes or less. Use the API to check for the latest time to which you can restore the instance and perform the recovery to that time.

The write-ahead logs used with point-in-time recovery are deleted automatically with their associated automatic backup, which generally happens after the value set for transactionLogRetentionDays is met. This is the number of days of transaction logs that Cloud SQL retains for point-in-time recovery, from 1 to 35 for Cloud SQL Enterprise Plus edition and 1 to 7 for Cloud SQL Enterprise edition.

When you restore a backup on a Cloud SQL instance before enabling point-in-time recovery, you lose the WAL logs that allow the operability of point-in-time recovery.

For instances that have write-ahead logs stored in Cloud Storage, the logs are stored in the same region as the primary instance. This log storage (up to seven days, the maximum length for point-in-time recovery) generates no additional cost per instance.

If your instance has point-in-time recovery enabled, and if the size of your write-ahead logs on disk is causing an issue for your instance, then disable point-in-time recovery and re-enable it to ensure that new logs are stored in Cloud Storage in the same region as the instance. This deletes existing write-ahead logs, so you can't perform a point-in-time-restore earlier than the time that you re-enabled point-in-time recovery. However, the disk size remains the same.

For step-by-step instructions for performing a point-in-time recovery, see Perform a point-in-time recovery.

Restore an unavailable instance

You can use point-in-time recovery to restore a Cloud SQL instance that isn't available. Point-in-time recovery typically offers a recovery point objective (RPO) of five minutes or less.

If the instance is unavailable, then you can use the API to check for the latest time to which you can restore the instance and perform the recovery to that time. If the zone in which the instance is configured isn't accessible, then you can clone the instance to a different zone.

Suppose a Cloud SQL instance becomes unavailable at 4 PM EST. If the latest recovery time is at 3:55 PM EST, then you can recover the instance up to this time.

General tips about performing a restore

When you restore an instance from a backup, whether to the same instance or to a different instance, keep in mind the following items:

  • The restore operation overwrites all data on the target instance.
  • The target instance is unavailable for connections during the restore operation; existing connections are lost.
  • If you are restoring to an instance with read replicas, you must delete all replicas and recreate them after the restore operation completes.
  • The restore operation restarts the instance.

For step-by-step instructions for performing a restore, see:

Tips and requirements for restoring to a different instance

When you are restoring a backup to a different instance, keep in mind the following restrictions and best practices:

  • The target instance must have the same database version and edition as the instance from which the backup was taken.

  • Cloud SQL always sets the storage capacity of the target instance to the maximum value of the size of both the configured disk and the backup disk. The backup disk is the size of the disk when the backup is taken.

  • The storage capacity of the target instance must be at least as large as the capacity of the instance being backed up. The amount of storage used does not matter. You can see the storage capacity of the instance in the console Cloud SQL instances page.

  • The target instance must be in the RUNNABLE state.

  • The target instance can have a different number of cores or amount of memory than the instance from which the backup was taken.

  • The target instance can be in a different region from the source instance.

  • During an outage, you can still retrieve a list of backups in a particular project. See Viewing backups during an outage.

Restore rate limitations

You are allowed a maximum of three restore operations every 30 minutes per instance per region per project. If a restore operation fails, it does not count towards this quota. If you reach the limit, the operation fails with an error message that tells you when you can run the operation again.

Let's take a look at how Cloud SQL performs rate limiting for restores.

Cloud SQL uses tokens from a bucket to determine how many restore operations are available at any one time. For each backup, there's a bucket for each target project and target region. The target instances from the same project share one bucket if they are in the same region. There's a maximum of three tokens in each bucket that you can use for restore operations. Every 10 minutes, a new token is added to the bucket. If the bucket is full, the token overflows.

Each time you issue a restore operation, a token is granted from the bucket. If the operation succeeds, the token is removed from the bucket. If it fails, the token is returned to the bucket. The following diagram shows how this works:

How tokens work

For example, in the following figure, Backup1, Backup2, and Backup3 are the backups from the same source instance.

Tokens for restore operation rate limiting

  • Each backup (Backup1, Backup2, and Backup3) has its own bucket of tokens for restore operations that target different instances in Project 1 in Region A (Bucket11A, Bucket21A, and Bucket31A). Because each backup has its own bucket, you can restore each backup to the same instance three times every thirty minutes.
  • Each backup has a bucket for a separate project and for a separate region. For example, if there are five projects in a region, there are five buckets for that backup in that region, one in each project. In the previous figure, we have two projects in region A: Project 1 and Project n.
    • Backup1 has two buckets of tokens for restore operations in Region A. One bucket for Project 1 (Bucket11A), and one bucket for Project n (Bucket1nA).
    • Similarly, Backup3 has two buckets for restore operations in Region A. One for Project 1 (Bucket31A) and one for Project n (Bucket3nA).
  • Backup3 has one bucket in Region B, for Project1, because all instances in the same target project and the same target region share one bucket.

What's next