Replication is the ability to create copies of a Cloud SQL instance or anon-premises database, and offload work to the copies.

Introduction

The primary reason for using replication is to scale the use of data in adatabase without degrading performance.

Other reasons include:

• Migrating data between regions
• Migrating data between platforms
• Migrating data from an on-premises database to Cloud SQL

Additionally, a replica could be promoted if the original instancebecomes corrupted.

When referring to a Cloud SQL instance, the instance that is replicated iscalled theprimary instance and the copies are calledread replicas. The primary instance and read replicas reside inCloud SQL.

When referring to an on-premises database, the replication scenario is calledreplicating from an external server. In this scenario, the database thatis replicated is thesource database server. The copies that reside inCloud SQL are calledCloud SQL replicas. There is also aninstance that represents the source database server in Cloud SQL calledthesource representation instance.

In a disaster recovery scenario, you canpromote a replica to convert it to aprimary instance. This way, you can use it in place of an instance that's in aregion that's having an outage. You can also promote a replica to replace aninstance that's corrupted.

Cloud SQL supports the following types of replicas:

• Read replicas
• Cross-region read replicas
• Cascading read replicas

By usingconnector enforcement, you can enforce using only the Cloud SQL Auth Proxy or Cloud SQL Language Connectors to connect to Cloud SQL instances. With connector enforcement, Cloud SQL rejects direct connections to the database. You can't create read replicas for an instance that has connector enforcement enabled. Similarly, if an instance has read replicas, then you can't enable connector enforcement for the instance.

This limitation doesn't apply to the instances that haveprivate service access configured.You can also useDatabase Migration Servicefor continuous replication from a source database server to Cloud SQL.Note: Cloud SQL allows users to manage their own replication using PostgreSQL'slogical replicationfeatures.

Cloud SQL doesn't support replication between two external servers.

Read replicas

You use a read replica to offload work from a Cloud SQL instance. The readreplica is an exact copy of the primary instance. Data and other changes on theprimary instance are updated in almost real time on the read replica.

Read replicas are read-only; you cannot write to them. The read replicaprocesses queries, read requests, and analytics traffic, thus reducing the loadon the primary instance.

Note: We recommend that you limit the number ofdirect read replicas ofyour primary instance to 10 or fewer. If you need additional replicas, thenyou can create cascading read replicas.

Youconnect to a replicadirectly using its connection name and IP address. If you're connecting to areplica using a private IP address, you don't need to create an additional VPCprivate connection for the replica because the connection is inherited from theprimary instance.

For information about how to create a read replica, see Creating read replicas.For information about managing a read replica, see Managing read replicas.

Note: Read replicas don't provide failover capability. To provide failovercapability for an instance, seeConfiguring an instance for highavailability.

As a best practice, put read replicas in a different zone than the primaryinstance when you use HA on your primary instance. This practice ensures thatread replicas continue to operate when the zone that contains the primaryinstance has an outage. See theOverview of high availability formore information.

Selecting an appropriate machine type

Read replicas can have a different number of vCPUs and memory from that of theprimary. You shouldmonitor metrics on yourinstance such as CPU and memory usageto ensure that the replica instance is sized correctly for its workload,especially if it is smaller than the primary instance. A replica instance thatis undersized is more prone to poor performance, such as frequent out-of-memory(OOM) events.

Storage capacity on read replicas

When a primary instance is resized, all of its read replicas are resized, ifneeded, so that they have at least as much storage capacity as the updatedprimary instance.

Impact on themax_connections flag when the read replica has a machine type with less memory than the primary

On a PostgreSQL instance, if you don't set themax_connections flagto a value of your choice, Cloud SQL automatically sets it based on theamount of memory on the instance. For more information, seesupported flags. PostgreSQL requiresthat thevalue ofmax_connections is always at least as large on a readreplica as it is on its primary. Therefore, if a read replica has less memorythan its primary, and you haven't set themax_connections flag, itmight inherit a larger value ofmax_connections based on the sizeof the primary instance. In this situation, if you rely on themax_connections setting to limit the number of connections to thereplica instance, it could become overloaded because the value is too highrelative to the machine type of the instance. To avoid this, you can do any ofthe following:

1. Resize the replica instance to a larger machine type.
2. Configure your client application to limit it to some number of connectionsthat is less than the value ofmax_connections.
3. Set themax_connections flag on the primary and the replica toan appropriate value.

Hash index operations using read replicas

Hash index operations don't use write-ahead-logging for PostgreSQL 9.6.Cloud SQL has only one available version underPostgreSQL 10. This is documented in theyellow caution box on thePostgreSQL release page.This also applies to Cloud SQL read replicas.

Because hash index updates don't propagate to the read replica underPostgreSQL 9.6, they can't be used by the replica. As a workaround, you caneither refrain from having read replicas orupgrade to a major PostgreSQL version (10 or above).

Cross-region read replicas

Cross-region replication lets you create a read replica in a different regionfrom the primary instance. You create a cross-region read replica the same wayas youcreate anin-region replica.

Cross-region replicas:

• Improve read performance by making replicas available closer to yourapplication's region.
• Provide additional disaster recovery capability to guard against a regionalfailure.
• Let you migrate data from one region to another.

SeePromotingreplicas for regional migration or disaster recovery for more informationabout cross-region replicas.

Cascading read replicas

Cascading replication lets you create a read replica under another read replicain the same or a different region. The following scenarios are use cases for usingcascading replicas:

• Disaster recovery: You can use a cascading hierarchy of read replicas tosimulate the topology of your primary instance and its read replicas. During anoutage, your selected read replica is promoted to primary and the read replicasunder the new primary continue to replicate and are ready for use.
• Performance improvements: Reduce the burden on the primary instance byoffloading replication work to multiple read replicas.
• Scale Reads: You can have more replicas to share the read load.
• Cost reduction: You can reduce networking costs by using a singlecascading replica with cross-region replication in other regions.

Note: You can't directly promote a cascading read replicathat's a replica of an external server replica. First, promote the external server replica.Then, you can promote the replica of the promoted external server replica.

Terminology

• Cascading replica: A read replica that can have its own replica.
• Levels: You can create levels of replicas in a cascading replica hierarchy. For example, if you add four replicas to an instance, those four replicas are at the same level.
• Sibling instances: Multiple replicas that replicate from the same primary instance. Siblings are at the same level in the replica hierarchy. A replica can officially have up to eight siblings.
• Leaf replica: A read replica that does not have any replicas of its own. In a multi-level replication hierarchy, the leaf replica is the last level.
• Promote An action that converts a replica, at any level in the hierarchy, into a primary instance. When promoted, the replica's cascading replica hierarchy is retained.

Configure cascading replicas

Cascading replicas let you add read replicas to any existing replicas. You can add up to four levels of replicas, including the primary instance. Whenyou promote the replica at the top of a cascading replica hierarchy, it becomes a primary instance and its cascading replicas continue to replicate.

To plan your configuration, you need to have a goal for what the readreplicas intend to do. The next two sections describe the configurationsfor disaster recovery and multi-region replication.

Disaster recovery

To understand how cascading replicas help you recover quickly during an outage,consider the following replication scenario:

Configuration

Outage

Promotion

If you want to use an instance in Region B in a disaster recovery configurationand have:

• Replicas in the same region attached to the primary instance (Replica A)
• Replicas in other regions (Cascading Replica) attached to the primary.

You can create read replicas under the cascading replica in Region B.

On theOutage tab, if there's an outage in Region A, the cascading replica ispromoted to a primary instance. It already has read replicas underneath it,reducing the recovery time objective (RTO).

On thePromote tab, you see that when a cascading replica is promoted, itsreplicas are also promoted and continue to replicate under it.

Multi-region replication

Another use case for cascading replicas is to distribute read capacity to asecond region in a cost-efficient manner. Cascading replicas C and D can becreated that replicate from Replica B. Clients can distribute read queriesacross replicas B, C, and D to reduce the load on each replica. The cost ofcross-region network traffic is incurred only once, from the primary instance toReplica B. Replication from B to C and D uses in-region network transfer, whichis free.

You can create a hierarchy of up to four instances using cascading replicas for multi-region replication:

Primary A → Replica B → Replica C and Replica D

Restrictions

• You can't delete a replica that has replicas under it. To delete the replica, youmust start with the leaf replicas and work your way upward through thehierarchy.
• Circular region dependency isn't supported. To have the replica of acascading replica in the same region as the primary instance,the cascading replica must also be in the same region.

Logical replication

Cloud SQL lets you configure your own replication solutions by usingPostgreSQL's logical replication features. Logical replication is a flexiblesolution allowing:

• Standard replication from a primary instance to a replica
• Selective replication of only certain tables or rows
• Replication across PostgreSQL major versions
• Replication to non-PostgreSQL databases
• Change data capture (CDC) workflows where all database changes are streamedto a consumer

For more information, seeSetting up logical replication.That page includes information about:

• Built-in logical replication
• The pglogical extension

Replication use cases

The following use cases apply for each type of replication.

Name	Primary	Replica	Benefits and use cases	More information
Read replica	Cloud SQL instance	Cloud SQL instance	Additional read capacity Analytics target	Creating read replicas Managing read replicas Replication metrics Replication lag
Cross-region read replica	Cloud SQL instance	Cloud SQL instance	Additional read capacity Analytics target Additional disaster recovery capability Improve read performance Migrate data between regions	Creating read replicas Managing read replicas Replication metrics
Logical replication	Any PostgreSQL instance	Any PostgreSQL instance, or an external consumer	Replication from the outside of Cloud SQL Replication across PostgreSQL versions Replicating only a subset of data in some tables, using row and column filtering. Also see pglogical 2.1 and Logical Replication in PostgreSQL 10. Replication to non-PostgreSQL destinations (change data capture)	Setting up logical replication

Billing

• A read replica is charged at the same rate as a standard Cloud SQLinstance. There is no charge for the data replication.
• Pricing for a cross-region read replica is the same as for creating a newCloud SQL instance in the region. Refer toCloud SQL instance pricing and select theappropriate region. In addition to the regular cost associated with theinstance, a cross-region replica incurs cross-region data transfer chargesfor replication logs sent from the primary instance to the replica instance,as described inNetwork Egress Pricing.

Quick reference for Cloud SQL read replicas

Topic	Discussion
Backups	You cannot configure backups on the replica.
Cores and memory	Read replicas can use a different number of cores and amount of memory from those of the primary instance.
Deleting the primary instance	Before you can delete a primary instance, you must promote all of its read replicas to standalone instances or delete the read replicas.
Deleting the replica	When you delete a replica, there is no impact on the status of the primary instance.
Disabling write-ahead logging	Before you can disable write-ahead logs on a primary instance, you must promote or delete all of its read replicas.
Failover	A primary instance can failover to a replica only if the replica is a DR replica. Read replicas are unable to failover in any way during an outage.
High availability	Read replicas allow you to enablehigh availability on the replicas.
Load balancing	Cloud SQL doesn't provide load balancing between replicas. You can choose to implement load balancing for your Cloud SQL instance. You can also useconnection pooling to distribute queries across replicas with your load balancing setup for better performance.
Maintenance windows	Read replicas share maintenance windows with the primary instance. The replicas follow the maintenance settings for the primary instance, including the maintenance window, rescheduling, and the deny maintenance period. During maintenance, Cloud SQL updates all read replicas first before updating the primary instance.
Multiple read replicas	Cloud SQL supportscascading replicas. As a result, you can create up to 10 replicas for a single primary instance and create replicas of those replicas, up to four levels including the primary instance.
Private IP	If you're connecting to a replica using a private IP address, you don't need to create an additional VPC private connection for the replica, as it is inherited from the primary instance.
Restoring the primary instance	You cannot restore the primary instance of a replica while the replica exists. Before restoring an instance from a backup, or performing a point-in-time recovery on it, you must promote or delete all of its replicas.
Settings	The settings of the primary instance are propagated to the replica, including the password for the postgres user and changes to the user table.
Stopping a replica	You cannotstop a replica. You canrestart,delete, ordisable replication on it, but you cannot stop it as you can a primary instance.
Upgrading a replica	Read replicas can experience a disruptive upgrade at any time.
User tables	You cannot make changes on the replica. All user changes must be done on the primary instance.

What's next

• Learn how tocreate a read replica.
• Learn how toconfigure an instance for high availability.