Highly available, read-replicated PostgreSQL with Google CloudSQL and Terraform
In this tutorial I will share the process of creating a highly available instance of PostgreSQL on GCP, the “git ops” way!
To keep this succinct I will not cover the basic of Terraform and GCP, but assume that you already have some projects up and running.
Creating a random ID generator resource
We use this resource so that we can create randomized element within the name of our DB instances.
We need a randomized element in the name because sometimes in the process of changing settings, Terraform must destroy and recreate the instance. This was fine if not for a CloudSQL restriction which states:
You cannot reuse an instance name for up to a week after you have deleted the instance.
So unless the name of the instance is randomized, we cannot re-create it on the spot.
Creating the master instance
The most important settings in the master configuration are:
1) settings.availability_type
2) settings.location_preferences.zone
Setting availability.type to “REGIONAL” is what makes the master instance highly available. This means a standby instance will be set up with the same region.
Setting the zone lets you select a zone in which to operate. You should select a zone that’s nearest to where your application is deployed.
Creating a database and database user
Now we set a non-default database and database user to be used by our application. In this case named “main”.
The user’s credentials should be set via Terraform variables.
Creating the read-replica
One crucial detail here is the master_instance_name. It’s not enough to specify the name in itself, it must prefixed with the google project ID and a colon (:). So a master instance name would be “my-gcp-project:my-master-instance-name”. As always, it’s best to use Terraform variables to compose this.
There another are number of small but important differences between the master and the replica:
1) replica_configuration.failover_target
2) settings.availability_type
3) settings.backup_configuration.enabled
The failover_target must be set to false. The master instance is configured as “REGIONAL” so a standby is already in place. This is a read only replica so it cannot be elected as the new master in case of a failover.
The availability_type must be set to “ZONAL”. The read replica cannot be a highly available (“REGIONAL”) instance.
The backup_configuration must be disabled.
Apply the changes and voilà!
