Terraform resource to promote cloud sql replica to primary

[kp250278]

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment. If the issue is assigned to the "modular-magician" user, it is either in the process of being autogenerated, or is planned to be autogenerated soon. If the issue is assigned to a user, that user is claiming responsibility for the issue. If the issue is assigned to "hashibot", a community member has claimed the issue already.

Description

Current Terraform Version
[vagrant@centos7 ~]$ terraform version
Terraform v0.12.31

Checked with the Existing provider/versions this feature is not available with latest as well
We are using cloud sql postgres with cross region read replica option and there is option to enable automatic failover using terraform but there is no option to failover that using terraform resource/settings/option.

Currently we are suing Gcloud API commands to promote the replica for DR testing. As we are using gcloud command to promote the replica our cloud sql instance created using terraform no longer sync with statefile(terraform)

Autofailover option not feasible as if there is any other issue with primary it will promote the replica in that case. also we need to update the connections strings into applications as application is still pointing to primary instance connection string.

New or Affected Resource(s)

• google_sql_database
• google_sql_database_instance

Potential Terraform Configuration

Attempted Solutions
Created ansible role using gcloud command to promote replica. due to this instance going out of sync with statefile and we unable to update using TF.
referance - https://cloud.google.com/sql/docs/mysql/replication/manage-replicas#:~:text=gcloud%20sql%20instances%20promote

Proposal
if there is a TF resource/settings/option available that we can use for promote read replica to primary that will be good.
so state file will not out of sync with the promoted instance and we are good to use this clous sql instance under terraform

# Propose what you think the configuration to take advantage of this feature should look like.
# We may not use it verbatim, but it's helpful in understanding your intent.

References

referance - https://cloud.google.com/sql/docs/mysql/replication/manage-replicas#:~:text=gcloud%20sql%20instances%20promote

• b/257028890

[kp250278]

Can you please help on above we are stuck due to this issue

[c2thorn]

@kp250278 Given that this is represented in the API via another endpoint (https://cloud.google.com/sql/docs/mysql/admin-api/rest/v1beta4/instances/promoteReplica), this seems like an imperative action that won't fit into Terraform's declarative schema. How would you expect this to look like as Terraform configuration?

Alternatively, have you attempted to promote to primary outside of Terraform, and then import the resource back into state? That would reset the state file.

[kp250278]

@kp250278 Given that this is represented in the API via another endpoint (https://cloud.google.com/sql/docs/mysql/admin-api/rest/v1beta4/instances/promoteReplica), this seems like an imperative action that won't fit into Terraform's declarative schema. How would you expect this to look like as Terraform configuration?

Alternatively, have you attempted to promote to primary outside of Terraform, and then import the resource back into state? That would reset the state file.

We are thinking there would be some settings under google_sql_database_instance resource that we can use to promote the replica or if you would suggest best way that would be great

I will try the import option

[c2thorn]

@kp250278 please try out the import method, as I do not think that this action can be represented in Terraform in a declarative way with the way the API represents it.

[cb250373]

Hey just wanted to follow up on this ticket.

"Given that this is represented in the API via another endpoint (https://cloud.google.com/sql/docs/mysql/admin-api/rest/v1beta4/instances/promoteReplica), this seems like an imperative action that won't fit into Terraform's declarative schema. How would you expect this to look like as Terraform configuration?"

In the spirit of Declarative schema.. wouldn't Terraform always want to support the end state of your system, regardless of the path it took to get there - regardless if you are creating a primary instance from scratch or promoting an existing read replica?

Use Case 1 - Promoting to Primary Instance (Not Supported)
If I configured the resource to be a primary instance and it was currently a read-replica, I would expect the plan to show a dropping of the replication map value and a reference to the master node, and for Terraform to promote up the read replica to being a primary instance.

Use Case 2 - Creating Primary Instance(Currently Supported)
If no instance existed there, then I would expect it to create the instance from scratch instead.

In both cases I've simply declared that I want a primary instance.
This feels declarative to me, am I wrong?

Both are fully supported use cases through the UI / API from GCP.

Open to feedback / your thoughts. @c2thorn

[c2thorn]

Hey @cb250373, thanks for the feedback. You're right, and I should correct my statement above to "I do not think this can be represented in the provider easily based on how it is represented in the API".

You are correct that the use cases themselves are considered declarative, however the provider is usually limited to what is available from the API. The resource schema in Terraform essentially mirrors the API schema, and deviating from it often leads to problems. My concern was that there wasn't a field that gave us a direct indication of whether something was intended to be a primary instance vs a replica (replication_config didn't seem great because we've already set that to default to the API's current value).

BUT after spending more time browsing the API I found a field that makes it pretty explicit and declarative-friendly: https://cloud.google.com/sql/docs/mysql/admin-api/rest/v1beta4/instances#sqlinstancetype

This could allow us to check the type set in the backend and demote/promote based on how that compares to the value set by user configuration.

I'll open this back up for triage to be discussed with the team.

[kp250278]

Hi Tea, @rileykarson @c2thorn , Any update on this

[kp250278]

@rileykarson @c2thorn Hi Team, Can we have update on this

[c2thorn]

@kp250278 while acknowledging this is possible to do, our team does not have plans to implement this anytime soon due to prioritization. From what I understand, reimporting the resource is still a viable workaround. Has this option failed for you?

[enocom]

I've written up a short explanation of how to do this by reimporting the resource here: https://stackoverflow.com/a/72625750/927514.

Nonetheless, having support for promoting a read replica in the provider would be a huge win in my (biased 😉 ) opinion.

[n-oden]

Possibly worth mentioning in terms of prioritization: google seems to have recently (and silently) made a change that has resulted CloudSQL backups becoming instantly unavailable when an instance is deleted. While this admittedly aligns with what the documentation has always said, the actual observed reality until this month was that as long as you had a record of the deleted database instance ID, you could restore from any of its backups for 6-48 hours after it was deleted. This no longer being the case, any DR/failover workflows that depended on this behavior (ahem, ask me now I know) are broken and I suspect that a lot of people who had previously not had any need to manage primary/replica failover in terraform will be in some need of this.

[c2thorn]

closed with GoogleCloudPlatform/magic-modules#7190

[github-actions]

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.
If you have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.