High Availability Configuration and Connection Patterns

[Shubhanan Bakre]

I need to better understand the high availability setup for Cloud SQL with PostGreSQL and MySQL and had the following questions:

1. Given that a failover replica can be treated as a read replica when the Master is up and running, how reliable is the failover instance? I am referring to this: https://cloud.google.com/sql/docs/postgres/replication/tips#read-replica

2. As I understand from https://groups.google.com/forum/#!msg/google-cloud-sql-discuss/WwfY_CwFbVU/IKfo7Rn_BwAJ, when Cloud SQL DB fails over, the master is brought up in the failover zone and the failover instance is moved to another zone.

But what happens if the failover replica goes down (before the master)?

3. Lets say we have a bunch of read replicas in this HA setup. Are read replicas still in sync after the master fails?

4.What are the differences between MySQL and PostGreSQL for the above?

5. This is question is unrelated to the HA setup. Can we connect from a private cluster to Cloud SQL using 

a) Cloud proxy b) Authorized IP

   - Followup question: How about from a centralized VM hosted cloud proxy client?

Ref: 

https://cloudplatform.googleblog.com/2017/11/Cloud-SQL-for-PostgreSQL-adds-high-availability-and-replication.html

https://cloud.google.com/sql/docs/mysql/high-availability

https://cloud.google.com/sql/docs/postgres/replication/tips#read-replica

[Brett Hesterberg]

Hi Shubhana,

There are differences between Cloud SQL for Postgres and Cloud SQL for MySQL with respect to high availability.  I'll try to addressing your questions while pointing out those differences along the way.

1) Cloud SQL for MySQL has a failover replica that can be used for read queries (similar to a read replica).  Uptime of the primary (read/write) instance is prioritized and it is the primary instance, in a high availability configuration, that is backed by Cloud SQL's SLA.  Cloud SQL uses failover to isolate the primary instance from failures and it uses automation to repair failover replicas and read replicas. However, uptime of failover replicas and read replicas is not backed by the SLA.

Unlike Cloud SQL for MySQL, Cloud SQL for Postgres does not have a failover replica that can also be used for read queries.  Its high availability configuration is described in a recent blog post.  As with Cloud SQL for MySQL, the primary Cloud SQL for Postgres instance, in a high availability configuration, is backed by Cloud SQL's SLA.  Cloud SQL for Postgres read replicas are not backed by the SLA.

2) Both primary and standby instances of Cloud SQL send a heartbeat signal, which is evaluated to define an availability state for the master (primary) instance.  If there is a problem with the failover replica, then failover of a primary instance will not occur.

3) When a failover is triggered, the master instance will go offline while the read replica remains online.  After the master instance is back online, the read replica will go offline for a short period of time while it is reconnected to the master instance.

4) I've noted differences between Cloud SQL for MySQL and Cloud SQL for Postgres as part of each answer.

5) Each Cloud instance is assigned its own public IP address, even when the Cloud SQL proxy is used.  It is recommended that the Cloud SQL proxy be installed on each client machine.

Thanks,
Brett

[Shubhanan Bakre]

Hi Brett,

Thanks for the response. I was able to figure out much of the same information after digging through the docs. However, I would like to know - are the heartbeat parameters configurable?

Thanks!

Shubhanan

[Digil (Google Cloud Platform Support)]

You can check this help center article to get an overview of the high availability configuration for Second Generation instances. And if you want any specific parameter to be configurable, you could always request for this feature on the SQL instance by using the issue tracker. While opening the request, make sure that you have included all the necessary details along with a use-case scenario as well.

[Brett Hesterberg]

Hi Shubhanan-

No, heartbeat parameters aren't configurable.

Thanks,
Brett