Managing read replicas

MySQL   |  PostgreSQL   |  SQL Server

This page describes how you can disable and enable replication for a read replica, as well as how to promote a replica to a stand-alone instance or delete it. For information about working with read replicas, see Requirements and Tips for Configuring Replication.

Disabling replication

By default, a replica starts with replication enabled. However, you can disable replication, for example, to debug or analyze the state of an instance. When you are ready, you explicitly reenable replication. Disabling or reenabling replication restarts the replica.

Disabling replication does not stop the replica instance; it becomes a read-only instance that is no longer replicating from its master instance. You continue to be charged for the instance. You can reenable replication on the disabled replica, delete the replica, or promote the replica to a stand-alone instance. You cannot stop the replica.

To disable replication:

Console

1. Go to the Cloud SQL Instances page in the Google Cloud Console.

   Go to the Cloud SQL Instances page

2. Open a replica instance by clicking its name.
3. Click Disable replication in the button bar.
4. Click OK.

gcloud

gcloud sql instances patch [REPLICA_NAME] --no-enable-database-replication

REST

To execute this cURL command at a command line prompt, you acquire an access token by using the gcloud auth print-access-token command. You can also use the APIs Explorer on the Instances:patch page to send the REST API request.

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

• project-id: The project ID
• replica-name: The name of the replica instance

HTTP method and URL:

PATCH https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/replica-name

Request JSON body:

{
  "settings":
  {
    "databaseReplicationEnabled": "False"
  }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The command below assumes that you have used either gcloud init or gcloud auth login to authenticate gcloud with your user account. You can check the currently active account by executing gcloud auth list.

Save the request body in a file called request.json, and execute the following command:

curl -X PATCH \
-H "Authorization: Bearer "$(gcloud auth print-access-token) \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/replica-name

PowerShell (Windows)

Note: The command below assumes that you have used either gcloud init or gcloud auth login to authenticate gcloud with your user account. You can check the currently active account by executing gcloud auth list.

Save the request body in a file called request.json, and execute the following command:

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

Invoke-WebRequest `
  -Method PATCH `
  -Headers $headers `
  -ContentType: "application/json; charset=utf-8" `
  -InFile request.json `
  -Uri "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/replica-name" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/replica-name",
  "status": "PENDING",
  "user": "[e-mail]",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "UPDATE",
  "name": "operation-id",
  "targetId": "replica-name",
  "selfLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
  "targetProject": "project-id"
}

Enabling replication

If a replica has not been replicating for a long time, it will take longer for it to catch up to the master. In this case, you should delete the replica and create a new one.

To enable replication:

Console

1. Go to the Cloud SQL Instances page in the Google Cloud Console.

   Go to the Cloud SQL Instances page

2. Select a replica instance by clicking its name.
3. Click Enable replication in the button bar.
4. Click OK.

gcloud

gcloud sql instances patch [REPLICA_NAME] --enable-database-replication

REST

To execute this cURL command at a command line prompt, you acquire an access token by using the gcloud auth print-access-token command. You can also use the APIs Explorer on the Instances:patch page to send the REST API request.

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

• project-id: The project ID
• replica-name: The name of the replica instance

HTTP method and URL:

PATCH https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/replica-name

Request JSON body:

{
  "settings":
  {
    "databaseReplicationEnabled": "True"
  }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The command below assumes that you have used either gcloud init or gcloud auth login to authenticate gcloud with your user account. You can check the currently active account by executing gcloud auth list.

Save the request body in a file called request.json, and execute the following command:

curl -X PATCH \
-H "Authorization: Bearer "$(gcloud auth print-access-token) \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/replica-name

PowerShell (Windows)

Note: The command below assumes that you have used either gcloud init or gcloud auth login to authenticate gcloud with your user account. You can check the currently active account by executing gcloud auth list.

Save the request body in a file called request.json, and execute the following command:

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

Invoke-WebRequest `
  -Method PATCH `
  -Headers $headers `
  -ContentType: "application/json; charset=utf-8" `
  -InFile request.json `
  -Uri "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/replica-name" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/replica-name",
  "status": "PENDING",
  "user": "[e-mail]",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "UPDATE",
  "name": "operation-id",
  "targetId": "replica-name",
  "selfLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
  "targetProject": "project-id"
}

Promoting a replica

Promoting a replica to a stand-alone Cloud SQL primary instance is an irreversible action. Once promoted, an instance cannot be converted back to a read replica. You promote a read replica as part of disaster recovery, when the original primary instance becomes permanently unavailable.

To promote a replica to a stand-alone instance:

Console

1. Go to the Cloud SQL Instances page in the Google Cloud Console.

   Go to the Cloud SQL Instances page

2. Select a replica instance by clicking its name.
3. Click Promote replica in the button bar.
4. Click OK.

gcloud

gcloud sql instances promote-replica [REPLICA_NAME]
  

REST

To execute this cURL command at a command line prompt, you acquire an access token by using the gcloud auth print-access-token command. You can also use the APIs Explorer on the Instances:promoteReplica page to send the REST API request.

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

• project-id: The project ID
• replica-name: The name of the replica instance

HTTP method and URL:

POST https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/replica-name/promoteReplica

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The command below assumes that you have used either gcloud init or gcloud auth login to authenticate gcloud with your user account. You can check the currently active account by executing 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://www.googleapis.com/sql/v1beta4/projects/project-id/instances/replica-name/promoteReplica

PowerShell (Windows)

Note: The command below assumes that you have used either gcloud init or gcloud auth login to authenticate gcloud with your user account. You can check the currently active account by executing 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://www.googleapis.com/sql/v1beta4/projects/project-id/instances/replica-name/promoteReplica" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://www.googleapis.com/sql/v1beta4/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://www.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
  "targetProject": "project-id"
}

Confirm that the promoted instance is configured correctly. In particular, you should enable automated backups and consider configuring the instance for high availability if needed.

Checking replication status

When you log into an individual replica instance using an administration client, you get details about replication, including status and metrics. When you use the Google Cloud Console or the gcloud command-line tool, you get a brief summary of replication.

To check replication status:

Console

1. Go to the Cloud SQL Instances page in the Google Cloud Console.

   Go to the Cloud SQL Instances page

2. Select a replica instance by clicking its name.
3. The replication status is provided in a banner at the top of the page.

gcloud

For a replica instance, check the replication status with:

gcloud sql instances describe [REPLICA_NAME]

In the output, look for the properties databaseReplicationEnabled and masterInstanceName.

For a master instance, check if there are replicas with:

gcloud sql instances describe [MASTER_INSTANCE_NAME]

In the output, look for the property replicaNames.

psql Client

1. Connect to the replica with a PostgreSQL client.

   For information, see Connection options for external applications.

2. Check the replica's status, on the primary:

   select * from pg_stat_replication;

3. Look for the following metrics in the output of the command:
   • client_addr: The IP address of the replica instance.
   • state: Indicates whether the SQL thread for executing events in the relay log is running. It should be streaming when replication is started.
   • replay_lag: The number of bytes that the replica SQL thread is behind the primary instance. It should be O or a small number of bytes.
4. Check the replica's status, on the replica:

   select * from pg_stat_wal_receiver;

5. Look for the following metrics in the output of the command:
   • sender_host: The IP address of the primary instance.
   • status: Indicates whether the SQL thread for executing events in the relay log is running. It should be streaming when replication is started.
   • last_msg_send_time and last_msg_receipt_time: The difference between these two timestamps is the lag time.

Troubleshooting

For this error...	The issue might be...	Try this...
Replication has stopped.	The replica might have reached the maximum storage limit.	Enable the automatic storage increase option on the instance configuration page under the Machine type and storage option.
Replication lag is consistently high.	The SQL thread on the replica is unable to keep up with the IO thread.	Some kinds of queries and workloads cause temporary or permanent high replication lag for a given schema. Some of the typical causes of replication lag are: All tables should have a unique/primary key. Every update on a table without a unique/primary key causes full table scans on the replica. CPU intensive queries such as DELETE ... WHERE field < 50000000 cause replication lag with row-based replication since a huge number of updates are piled up on the replica. You can throttle the incoming load on the primary or choose to increase the instance size to increase the memory (RAM) of the system. If your replica is chronically falling behind the primary instance, a possible option is to shard your database so that the write operations are shared between multiple primary instances.
Unable to create read replica: unknown error.	Service Networking API is not enabled.	Check the logs. If the actual error is: set Service Networking service account as servicenetworking.serviceAgent role on consumer project, disable and re-enable the Service Networking API. This action creates the service account necessary to continue with the process.
The replica thread throws an UPDATE_DISK_SIZE event.	The primary instance's disk becomes full during replica creation and replica creation fails.	Increase the primary instance disk size, and then attempt to create the read replica again. This lets the replica generate an instance with an appropriate disk size.

What's next

• Learn how to create a read replica.
• Learn more about requirements and best practices for replication.