Use advanced disaster recovery (DR)

MySQL | PostgreSQL | SQL Server

This page describes how to use advanced disaster recovery (DR). Advanced DR provides two main capabilities:

Replica failover lets you fail over your primary instance to the DR replica immediately in the event of a regional failure. For Cloud SQL for SQL Server, the DR replica is a cascadable replica.
Switchover lets you reverse the roles of the primary instance and a DR replica with zero data loss. You can use switchover to restore a deployment to its original deployment state after replica failover, or you can use switchover to test DR.

Advanced DR is supported only on Cloud SQL Enterprise Plus edition instances.

Before you begin

If you plan to use the Google Cloud SDK, then you must use version 470.0.0 or later and gcloud beta commands. To check the version of the Google Cloud SDK, run gcloud --version. To update the Google Cloud SDK, run gcloud components update.

To install the Google Cloud SDK, see Install the gcloud CLI.

Create a DR replica

Before you use advanced DR, create a cascadable replica of the primary instance in a different region than the primary instance.

Perform a switchover

After you've created a DR replica, you can perform the switchover operation. However, as a best practice, avoid performing the switchover operation under the following circumstances:

The primary instance is being actively used.
Admin operations are in progress, such as automated backup or the enablement or disablement of high availability (HA).

To avoid a timeout, consider performing switchover when the transaction volume is low.

When switchover completes, the operation takes a backup of the new primary instance (the former DR replica) as soon as the new primary instance is promoted. After this backup is complete, then point-in-time-recovery (PITR) is fully enabled on the new primary instance. This backup can take between 5 and 15 minutes to complete depending on the disk size. PITR coverage starts only after this backup has completed. For more information about the considerations of using PITR with advanced DR, see Use PITR with advanced DR.

After the switchover operation is complete, you'll notice that the direction of replication is reversed.

Before you begin

Before you perform the switchover operation, do the following:

If you haven't done so already, create a DR replica.
Verify that the primary instance and the DR replica are online.
Take an on-demand backup of the primary instance. This backup is a precaution in case you need to recover from any unexpected failures.

Perform the switchover operation

Console

To perform the switchover operation, do the following:

In the Google Cloud console, go to the Cloud SQL Instances page.

Go to Cloud SQL Instances
Click the DR replica instance. The Overview page for the DR replica appears.
Click the Switchover button.
On the Perform switchover between the primary and DR replica page, enter the name of the primary instance in the Instance ID field.
Click Switchover.

gcloud

To perform the switchover operation, run the following command:

gcloud beta sql instances switchover REPLICA_NAME

Replace the following variables:

REPLICA_NAME: the name of the designated DR replica that you want the primary instance to switch roles with.

REST v1

Before using any of the request data, make the following replacements:

PROJECT_ID: the ID or project number of the Google Cloud project of the primary instance and the DR replica.
REPLICA_NAME: the name of the DR replica.

HTTP method and URL:

POST https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/REPLICA_NAME/switchover

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login , or by using Cloud Shell, which automatically logs you into the gcloud CLI . You can check the currently active account by running gcloud auth list.

Execute the following command:

curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d "" \
     "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/REPLICA_NAME/switchover"

PowerShell (Windows)

Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login . You can check the currently active account by running gcloud auth list.

Execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method POST `
    -Headers $headers `
    -Uri "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/REPLICA_NAME/switchover" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/REPLICA_NAME",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2024-04-01T22:43:37.981Z",
  "operationType": "SWITCHOVER",
  "name": "OPERATION_ID",
  "targetId": "REPLICA_ID",
  "selfLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/operations/OPERATION_ID",
  "targetProject": "PROJECT_ID"
}

REST v1beta4

Before using any of the request data, make the following replacements:

PROJECT_ID: the ID or project number of the Google Cloud project of the primary instance and the DR replica.
REPLICA_NAME: the name of the DR replica.

HTTP method and URL:

POST https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/REPLICA_NAME/switchover

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Execute the following command:

curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d "" \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/REPLICA_NAME/switchover"

PowerShell (Windows)

Execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method POST `
    -Headers $headers `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/REPLICA_NAME/switchover" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/REPLICA_NAME",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2024-04-01T22:43:37.981Z",
  "operationType": "SWITCHOVER",
  "name": "OPERATION_ID",
  "targetId": "REPLICA_ID",
  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/operations/OPERATION_ID",
  "targetProject": "PROJECT_ID"
}

Perform DR by invoking a replica failover

In the event of a regional failure or a disaster, you can perform DR by invoking a replica failover operation to your designated DR replica. To perform a replica failover, you promote the DR replica. In contrast with switchover, the promotion of the DR replica is immediate.

Since the DR replica assumes the role of the primary instance immediately, it's possible that the replica doesn't have all of the data from the old primary instance due to replication lag. For this reason, a replica failover can incur data loss.

As part of the promotion process, replica failover takes a backup of the new primary instance (the former DR replica) right after the DR replica becomes the new primary instance. After this backup is complete, point-in-time-recovery (PITR) is fully enabled on the new primary instance. This backup can take between 5 and 15 minutes to complete depending on the disk size of the new (and old) primary instance. During this backup period, PITR is not available.

When the old primary instance comes back online, the replica failover process takes a backup. After this backup is taken, the old primary instance is recreated as a read replica of the new primary instance. In this process, the old primary instance loses any old PITR transaction logs that are not yet saved to Cloud Storage. Thus, replica failover does not guarantee that all transaction logs used for PITR on the old primary instance are preserved.

For more information about the considerations of using PITR with advanced DR, see Use PITR with advanced DR.

Before you begin

Before you can perform a replica failover, do the following:

If you haven't done so already, create a DR replica.
Make sure the DR replica is online and healthy.

Perform the replica failover operation

Console

To perform the replica failover operation, do the following:

In the Google Cloud console, go to the Cloud SQL Instances page.

Go to Cloud SQL Instances
Click the DR replica instance. The Overview page for the DR replica appears.
Click the Replica Failover button.
On the Perform replica failover between the primary and DR replica page, enter the name of the primary instance in the Instance ID field to confirm that you want to proceed with the operation.
To start the replica failover, click Replica Failover.

gcloud

To invoke a replica failover to the DR replica, use the following command:

gcloud sql instances promote-replica \
   REPLICA_NAME --failover

Replace the following variable:

REPLICA_NAME: the name of the DR replica

REST v1

Before using any of the request data, make the following replacements:

PROJECT_ID: the ID or project number of the Google Cloud project of the primary instance and DR replica.
REPLICA_NAME: the name of the DR replica.
ENABLE_REPLICA_FAILOVER: set to true to use replica failover. If you set to false, then the API uses the regular promoteReplica method without replica failover.

HTTP method and URL:

POST https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/REPLICA_NAME/promoteReplica?failover=ENABLE_REPLICA_FAILOVER

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Execute the following command:

curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d "" \
     "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/REPLICA_NAME/promoteReplica?failover=ENABLE_REPLICA_FAILOVER"

PowerShell (Windows)

Execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method POST `
    -Headers $headers `
    -Uri "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/REPLICA_NAME/promoteReplica?failover=ENABLE_REPLICA_FAILOVER" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/REPLICA_NAME",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "PROMOTE_REPLICA",
  "name": "OPERATION_ID",
  "targetId": "REPLICA_NAME",
  "selfLink": "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/operations/OPERATION_ID",
  "targetProject": "PROJECT_ID"
}

REST v1beta4

Before using any of the request data, make the following replacements:

PROJECT_ID: the ID or project number of the Google Cloud project of the primary instance and DR replica.
REPLICA_NAME: the name of the DR replica.
ENABLE_REPLICA_FAILOVER: set to true to use replica failover. If you set to false, then the API uses the regular promoteReplica method without replica failover.

HTTP method and URL:

POST https://sqladmin.googleapis.com/v1beta4/projects/PROJECT_ID/instances/REPLICA_NAME/promoteReplica?failover=ENABLE_REPLICA_FAILOVER

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Execute the following command:

curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d "" \
     "https://sqladmin.googleapis.com/v1beta4/projects/PROJECT_ID/instances/REPLICA_NAME/promoteReplica?failover=ENABLE_REPLICA_FAILOVER"

PowerShell (Windows)

Execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method POST `
    -Headers $headers `
    -Uri "https://sqladmin.googleapis.com/v1beta4/projects/PROJECT_ID/instances/REPLICA_NAME/promoteReplica?failover=ENABLE_REPLICA_FAILOVER" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/v1beta4/projects/PROJECT_ID/instances/REPLICA_NAME",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2024-04-01T22:43:37.981Z",
  "operationType": "PROMOTE_REPLICA",
  "name": "OPERATION_ID",
  "targetId": "REPLICA_NAME",
  "selfLink": "https://sqladmin.googleapis.com/v1beta4/projects/PROJECT_ID/operations/OPERATION_ID",
  "targetProject": "PROJECT_ID"
}

Check the status of a replica failover

Replica failover occurs in two phases. The first phase is the promotion of the DR replica. The second phase is the recreation of the old primary instance as a read replica.

To check the status of replica failover, check the status of each phase.

Check the status of the first phase.
Console

To check if the DR replica has been promoted to a standalone instance, do the following:
1. In the Google Cloud console, go to the Cloud SQL Instances page.
  
  Go to Cloud SQL Instances
2. Find the name of the DR replica that you promoted.
3. Verify that SQL Server VERSION appears in the Type column for the new primary instance.
gcloud
You can check the status by running the following command:
```
gcloud sql instances describe DR_REPLICA_NAME
```
Replace the following variable:
- DR_REPLICA_NAME: the name of the promoted DR replica
In the output, check that the following field appears and the replica has become a standalone Cloud SQL primary instance:
```
instanceType: CLOUD_SQL_INSTANCE
```
To verify the completion of the second phase, check the operations log on the instance for the message RECONFIGURE_OLD_PRIMARY.

The appearance of this message depends on when the old primary instance returns online, which can take minutes or days in the event of a disaster.

For more information on how to check the operations logs on an instance, see View instance logs.

Use PITR with advanced DR

With both switchover and replica failover, as soon as the DR replica is promoted to a primary instance, the following changes occur to support backup and PITR:

Backup configuration, including any automated backup scheduling, is copied from the old primary instance to the new primary instance.
If disabled, the binlog configuration flag is turned on to enable PITR.
A new backup is taken to support PITR on the new primary instance.
The transaction log retention policy is copied from the old primary instance to the new primary instance.

For both the backup configuration and transaction log retention policies, we recommend that you verify that the settings inherited from the old primary instance are correct for the new primary instance.

Start of PITR coverage

At the end of the switchover operation, Cloud SQL schedules automated backups and takes the first backup of the new primary instance. If you want PITR coverage to begin sooner than later, then we recommend that you verify that the first backup is successful. The newly promoted primary instance has PITR coverage only after the first automated backup has completed successfully.

For more information about how to view the backups that are available for an instance, see View a list of backups.

PITR coverage for instances during switchover and replica failover

When an instance participates in a switchover or a replica failover operation, the instance spends time as a read replica. PITR and restoring a backup are supported during the time period that the instance spends as a read replica. If you want to perform PITR to a point in time before the switchover event occurred (when the instance was a primary), you can issue the clone command to target the time that the instance was a primary instance. You cannot request PITR to a time when the instance was a read replica.

If you are unable to perform PITR because the primary instance was a read replica at the time of interest, then you must attempt the PITR request on the instance that was as the acting primary instance at the time of interest.

Similarly, you can restore a backup that was taken at a time when the replica was a primary instance. While the instance is a replica, the restore command must target a different standalone instance and cannot be restored onto the replica itself.

To determine which instance to use for the PITR request, use the operations list. The operations list for an instance can help determine when an instance underwent switchover or replica failover operations.

Split-brain during replica failover

It is possible that split-brain occurs when the primary instance continues to accept writes while a replica is promoted using replica failover. After the replica is promoted, when the old primary instance is available again, it is rebuilt as a replica of the promoted instance and a final backup is made. This backup can be used to recover any split-brain data that was not written to the promoted replica.

Deletion of backups and transaction logs on replicas

If a primary instance that was enabled with PITR and backups becomes a read replica, then the last backup and PITR retention policy from its time as a primary instance is preserved and applied during its time as a replica. Even though the new primary instance is not taking backups, the old backups and transaction logs used for PITR are deleted on the read replica according to the last configured policy.

For example, if the instance is configured to have daily automated backups and keep 7 backups with 7 days of PITR logs, then when this instance becomes a read replica, anything older than 7 days is deleted once a day.

If you need to delete backups sooner, then you can remove backups manually. For more information, see Delete a backup.

Limitations

Advanced DR is not supported for Cloud SQL instances that use Private Service Connect.
You can't designate a Cloud SQL Enterprise Plus edition read replica instance as DR replica if the primary instance stores its transaction logs for point-in-time recovery (PITR) on disk. To check where an instance stores its logs for PITR, see Check the storage location of transaction logs used for PITR.
You can't designate an external replica as a DR replica.

Troubleshoot

Issue	Troubleshooting
Switchover operation has failed.	Make sure the instance meets all the stated DR replica (cascadable replica) requirements. Check the volume of transactions on the database. If the transaction volume is high, then the operation might timeout. Consider retrying the operation when the transaction load is lower.
Switchover operation has failed and the primary instance is stuck in read-only mode.	Perform a database restart to bring the primary instance back to write mode.
Switchover operation has completed, but the Google Cloud console doesn't show the new reversed roles for the instances.	Refresh your browser to show the updated topology.
Replica failover operation has failed.	Ensure that you've created a DR replica for the primary instance and that the DR replica is online. If failover to the DR replica has failed, then promote to a regular (non-DR) read replica instead.

What's next

Read about database observability
Monitor Cloud SQL instances