Loading Posts...

Google Cloud Platform – GCP MySQL And PostgreSQL High Availability

Highly available database platform is one of the necessary aspect of cloud application and I thought to look in to the Google Cloud Platform aka GCP MySQL and PostgreSQL High Availability features in this article. If you are new to Google Cloud Platform just see how you can get started with GCP in my previous article.

Working with Google Cloud Platform (GCP) is quite simple and let’s see how Google Cloud SQL can configure to tolerate the failures and how to enable high availability (HA) features. HA feature sometimes refer as clustering and data redundancy techniques, deploying read replicas in different zones or regions will allow it to tolerate the failures. Google Cloud SQL made up of a primary SQL instance which is referred as Master in a primary zone and failover replica in a secondary zone and data is being replicated across the zones.

Basically, MySQL use semisynchronous replication and PostgreSQL use synchronous replication among the instances. Let’s see separately how we can configure the High Availability in these two Cloud SQL versions.

Limitations of GCP Failover Replica Configuration

  • Both Primary and secondary replica instances are in the same region, but different zone
  • Only a single replica can be created for the primary instance
  • Single maintenance window for both replicas
  • Backups only support for primary instance
  • Failover replica can be created only for the primary instance and creating failover replica for read replica does not supported

Google Cloud Platform MySQL HA Configuration

Both these replica instances created as two separate instances and
and have the same database flags users and passwords. It’s obvious that it will be charged as two separate instances. If you are willing to learn more information about pricing please check the MySQL Pricing Page.

This is how the basic setup of these replica instances

GCP MySQL and PostgreSQL High Availability : MySQL Setup
Image courtesy of Google Cloud Platform

How To Configure Cloud MySQL HA In Google Cloud Console

Just click on the “Hamburger” Menu and select “SQL

GCP MySQL and PostgreSQL High Availability : Open SQL

If you don’t have any existing instances click on “Create Instance

GCP MySQL and PostgreSQL High Availability : Create MySQL

Select MySQL and “Next

GCP MySQL and PostgreSQL High Availability : Select MySQL

Choose the Version Recommended version is “MySQL Second Generation

GCP MySQL and PostgreSQL High Availability : Choose Version

Provide the details such as Instance ID, Root password (can be generated), Region and Zone. To Enable the High Availability and backup features select “Show configuration options

Expand the “Enable auto backups and high availability” option

Backup configuration binary logging and failover replica options available and select “Create failover replica” option and provide a failover replica instance name. Create the failover replica clicking on “Create

It will take sometime to create the instances and you can see the Primary and Secondary replicas in different Zones

Click on the Primary instance and you can see the MySQL configuration and information, Failover can be done using the “FAILOVER” option at the top.

Provide the instaance name and click on the “TRIGGER FAILOVER” option to start the manual failover

Operation will take sometime to failover the master instance

Master instance will be changed and new replica will be created in a different zone. Just check the “Operations and log” for the tasks carried out during the operation

Google Cloud Platform Cloud PostgreSQL HA Configuration

The basic setup of this is bit different comparatively the previous MySQL setup

How To Configure Cloud PostgreSQL HA In Google Cloud Console

When setting up the PostgreSQL database, under “Enable auto backups and high availability” configuration you can find the High Availability feature. It can be configured as Single Zone or Regional configuration, Regional configuration is recommended for the production

Once the database is created you can perform the manual failover similar to the previous step

Instance name should be manually provided to trigger the Failover

It will failover to the secondary database which is in a different zone, Failback option might be grayed out as soon as you did the Failover. Background processes should be completed first in order to failback

Article Short Link: https://tcrum.net/GCPCloudSQLHA

If You Found This Post As Useful Please Rate The Post And Share It!

Click to rate this post!
[Total: 8 Average: 5]

Aruna Fernando

"Sharing knowledge doesn't put your job at risk - iron sharpen iron" I heard this and it's true.

Get Updates Directly To Your Inbox!


Leave a Comment

Loading Posts...