How to Promote Cloud SQL replica to primary using terraform so promoted instance should be in TF control

Cross-region replica setups become out of sync with the primary right after the promotion is complete. Promoting a replica is done manually and intentionally. It is not the same as high availability, where a standby instance (which is not a replica) automatically becomes the primary in case of a failure or zonal outage. You can promote the read replica using gcloud and Google API manually. By doing both of these will make the instance out of sync with Terraform. So what you are looking for seems to be not available while promoting a replica in Cloud SQL.

As a workaround I would suggest you to promote the replica to primary outside of Terraform, and then try to import the resource back into state which would reset the state file.

Promoting an instance to primary is not supported by Terraform's Google Cloud Provider, but there is an issue (which you should upvote if you care) to add support for this to the provider.

Here's how to work around the lack of support in the meantime. Assume you have the following minimal setup: an instance, a database, a user, and a read replica:

resource "google_sql_database_instance" "instance1" {
  name                 = "old-primary"
  region               = "us-central1"
  database_version     = "POSTGRES_14"
}

resource "google_sql_database" "db" {
  name     = "test-db"
  instance = google_sql_database_instance.instance1.name
}


resource "google_sql_user" "user" {
  name     = "test-user"
  instance = google_sql_database_instance.instance1.name
  password = var.db_password
}


resource "google_sql_database_instance" "instance2" {
  name                 = "new-primary"
  master_instance_name = google_sql_database_instance.instance1.name
  region               = "europe-west4"
  database_version     = "POSTGRES_14"

  replica_configuration {
    failover_target = false
  }
}

Steps to follow:

1. You promote the replica out of band, either using the Console or the gcloud CLI.
2. Next you manually edit the state file:

# remove the old read-replica state; it's now the new primary
terraform state rm google_sql_database_instance.instance2

# import the new-primary as "instance1"
terraform state rm google_sql_database_instance.instance1
terraform import google_sql_database_instance.instance1 your-project-id/new-primary

# import the new-primary db as "db"
terraform state rm google_sql_database.db
terraform import google_sql_database.db your-project-id/new-primary/test-db

# import the new-primary user as "db"
terraform state rm google_sql_user.user
terraform import google_sql_user.user your-project-id/new-primary/test-user

3. Now you edit your terraform config to update the resources to match the state:

resource "google_sql_database_instance" "instance1" {
  name                 = "new-primary"  # this is the former replica's name
  region               = "europe-west4" # this is the former replica's region
  database_version     = "POSTGRES_14"
}

resource "google_sql_database" "db" {
  name     = "test-db"
  instance = google_sql_database_instance.instance1.name
}


resource "google_sql_user" "user" {
  name     = "test-user"
  instance = google_sql_database_instance.instance1.name
  password = var.db_password
}


# this has now been promoted and is now "instance1" so the following 
# block can be deleted.
# resource "google_sql_database_instance" "instance2" {
#   name                 = "new-primary"
#   master_instance_name = google_sql_database_instance.instance1.name
#   region               = "europe-west4"
#   database_version     = "POSTGRES_14"
#
#   replica_configuration {
#     failover_target = false
#   }
# }}
}

4. Then you run terraform apply and see that only the user is updated in-place with the existing password. (This is done because Terraform can't get the password from the API and it was removed as part of the promotion and so has to be re-applied for Terraform's sake.)

5. What you do with your old primary is up to you. It's no longer managed by terraform. So either delete it manually, or re-import it.

Caveats

• Everyone's Terraform setup is different and so you'll probably have to iterate through the steps above until you reach the desired result.
• Remember to use a testing environment first with lots of calls to terraform plan to see what's changing. Whenever a resource is marked for deletion, Terraform will report why.

Nonetheless, you can use the process above to work your way to a terraform setup that reflects a promoted read replica. And in the meantime, upvote the issue because if it gets enough attention, the Terraform team will prioritize it accordingly.