Learn how to set up Cloud SQL Read Replicas with Terraform. Our Google Cloud Support team is here to help you with your questions and concerns.

How to Set Up Cloud SQL Read Replicas with Terraform

Did you know that Google Cloud SQL offers a strong and scalable solution for managing relational databases in the cloud?

One of the key features it provides is the ability to create “read replicas”. They can improve performance by offloading read operations and providing redundancy.

Today, we will walk through the step-by-step process of creating and managing read replicas, along with advanced configurations such as enabling Private Service Connect, setting up IAM database authentication, and cascading replicas.

An Overview:

1. Create a Read Replica in the Google Cloud Console
2. Create a Read Replica with Private Service Connect
3. Configure Read Replicas for IAM Database Authentication
4. Create Cascading Replicas
5. Promote a Read Replica and Managing Terraform State
6. Key Differences Between Primary and Replica Configurations

1. Create a Read Replica in Google Cloud Console

To create a read replica, follow these steps:

1. First, go to the Cloud SQL Instances page in the Google Cloud console.
2. Then, locate the instance for which we want to create a replica. Click the “More actions” menu.
3. Next, select “Create read replica”. If this option is not visible, the instance may already be a replica, and Google Cloud doesn’t allow replicas of replicas.
4. In the “Customize your instance” section, click “Show configuration options” to adjust settings for the replica. Here, we can modify the configuration groups to suit our needs. A summary of our selected options will appear on the right.
5. Finally, click “Create replica”. A backup will be created, and then the replica will be set up. Afterward, we will be returned to the instance page of the primary database.

2. Create a Read Replica with Private Service Connect

If we want to create a read replica with “Private Service Connect” enabled, we must use the “gcloud CLI” or API instead of the console. Here’s how to set it up:

gcloud sql instances create REPLICA_INSTANCE_NAME \
--master-instance-name=PRIMARY_INSTANCE_NAME \
--project=PROJECT_ID \
--region=REGION_NAME \
--enable-private-service-connect \
--allowed-psc-projects=ALLOWED_PROJECTS \
--availability-type=AVAILABILITY_TYPE \
--no-assign-ip


We can create replicas either in the same region as the primary instance or cross-region for better disaster recovery.

3. Configure Read Replicas for IAM Database Authentication

If we have enabled “IAM database authentication” on the primary instance, this will be automatically enabled for the read replicas in PostgreSQL. However, if this is not enabled on the primary, we cannot use IAM authentication on the replicas.

To manually configure IAM database authentication on a read replica, follow these steps:

1. First, go to the “Cloud SQL Instances page” in the Google Cloud console.
2. Then, click the name of the instance to open the Overview page.
3. Under “Configuration”, check if the `cloudsql.iam_authentication` flag is listed. If it is, enable it on the replica by:
   • Select “Replicas” from the navigation menu.
   • Click the name of the replica and select “Edit”.
   • In the Flags section, add the flag `cloudsql.iam_authentication` and ensure it is set to On.
   • Then, click Save to apply the changes.

4. Create Cascading Replicas

Cascading replicas let us create a replica of an existing replica. Here’s how to set one up:

1. In the Google Cloud console, go to the Cloud SQL Instances page.
2. Then, click the Replicas tab for the parent replica we want to use as the source for the new replica.
3. Select “Create replica”, and update the necessary configuration details such as Instance ID, Region, and Zone.
4. Finally, click Create, and a new cascading replica will be created.

Repeat these steps to create additional cascading replicas as needed.

5. Promote a Read Replica and Managing Terraform State

Promoting a replica to a primary is a manual process and is different from high availability, where a standby instance automatically takes over during failures.

Once we promote a replica, the instance will be out of sync with Terraform. Here’s how to promote and adjust the Terraform state:

1. Promote the replica via the gcloud CLI or Google API.
2. Manually update the Terraform state file:

   terraform state rm google_sql_database_instance.instance2
terraform import google_sql_database_instance.instance1 your-project-id/new-primary
terraform import google_sql_database.db your-project-id/new-primary/test-db
terraform import google_sql_user.user your-project-id/new-primary/test-user

3. Update the Terraform configuration to reflect the new primary instance.
4. Finally, run `terraform apply` to ensure everything is synchronized.

6. Key Differences Between Primary and Replica Configurations

When setting up a replica, we need to understand how its configuration differs from the primary instance:

• On replicas, the `failover_target` must be set to `false` since it cannot be elected as the new primary in the event of a failure.
• The `availability_type` for replicas must be set to `ZONAL`. Only primary instances should be configured as highly available (`REGIONAL`).
• Backups should be disabled on replicas (`backup_configuration.enabled = false`) since they are intended for read-only operations.

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

Creating and managing read replicas in Google Cloud SQL provides performance benefits by offloading read-heavy workloads. Whether we are setting up basic replicas, cross-region replicas, or enabling advanced features like Private Service Connect and IAM database authentication, this blog covers the essential steps to configure and maintain our replica environment.

In brief, our Support Experts demonstrated how to set up Cloud SQL Read Replicas with Terraform.