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.

Note: This page contains features related to Cloud SQL editions. For more information about Cloud SQL editions, see Introduction to Cloud SQL editions.

Before you begin

If you plan to use the Google Cloud SDK, then you must use version 502.0.0 or later. 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.

After your old primary instance is reconfigured as a read replica, the DNS write endpoint, which previously resolved to the old primary instance, resolves to the new primary instance.

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.
• If you're using a DNS write endpoint, then verify that the SSL configuration for the primary instance and the DR replica are the same. For example, if the DR replica is configured to enforce SSL encryption, but the primary instance allows unencrypted connections, then clients won't be able to connect to the new primary instance after the switchover operation completes.
• Take an on-demand backup of the primary instance. This backup is a precaution in case you need to recover from any unexpected failures.

Note: For Cloud SQL to update a write endpoint and point it to the correct instance, make sure that your replica is on the new network architecture before the switchover operation occurs. If the instance isn't created with this architecture, then you can upgrade the instance to the new network architecture.

Perform the switchover operation

gcloud

To perform the switchover operation, run the following command:

gcloud sql instances switchover REPLICA_NAME

Replace the following variables:

• REPLICA_NAME: the name of the 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": "[e-mail]",
  "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)

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/sql/v1beta4/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/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": "[e-mail]",
  "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 isn't 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.

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

After you invoke the replica failover operation, the DNS write endpoint, which previously resolved to the old primary instance, resolves to the new primary instance.

Before you begin

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

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

Note: For Cloud SQL to update a write endpoint and point it to the correct instance, make sure that your replica is on the new network architecture before the replica failover operation occurs. If the instance isn't created with this architecture, then you can upgrade the instance to the new network architecture.

Perform the replica failover operation

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)

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/promoteReplica?failover=ENABLE_REPLICA_FAILOVER"

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/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": "[e-mail]",
  "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/sql/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)

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/sql/v1beta4/projects/PROJECT_ID/instances/REPLICA_NAME/promoteReplica?failover=ENABLE_REPLICA_FAILOVER"

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/sql/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/sql/v1beta4/projects/PROJECT_ID/instances/REPLICA_NAME",
  "status": "PENDING",
  "user": "[e-mail]",
  "insertTime": "2024-04-01T22:43:37.981Z",
  "operationType": "PROMOTE_REPLICA",
  "name": "OPERATION_ID",
  "targetId": "REPLICA_NAME",
  "selfLink": "https://sqladmin.googleapis.com/sql/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.

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

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

• 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 and as a primary instance.

You can perform PITR to a time before the switchover when the instance was a primary instance. For both switchover and replica failover operations, Cloud SQL initiates a best-effort backup for the new primary instance as soon as the new primary instance is promoted. PITR is enabled on the promoted instance only after this backup is completed. This backup can take 5 to 15 minutes to complete depending on the disk size.

Split-brain during replica failover

It's 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.

Note: Split-brain can't happen for switchover operations.

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.

Note: You can't modify backup configuration settings while the instance is a replica.

Limitations

• Advanced DR isn't supported for Cloud SQL instances that use Private Service Connect. Advanced DR does support private services access.

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

• Terraform isn't supported for switchover or replica failover operations.

• You can't use the Google Cloud console to perform replica failover or switchover operations.

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