GCP Cloud SQL failed to delete instance because `deletion_protection` is set to true

Question

I have a tf script for provisioning a Cloud SQL instance, along with a couple of dbs and an admin user. I have renamed the instance, hence a new instance was created but terraform is encountering issues when it comes to deleting the old one.

Error: Error, failed to delete instance because deletion_protection is set to true. Set it to false to proceed with instance deletion

I have tried setting the deletion_protection to false but I keep getting the same error. Is there a way to check which resources need to have the deletion_protection set to false in order to be deleted? I have only added it to the google_sql_database_instance resource.

My tf script:

// Provision the Cloud SQL Instance
resource "google_sql_database_instance" "instance-master" {
  name             = "instance-db-${random_id.random_suffix_id.hex}"
  region           = var.region
  database_version = "POSTGRES_12"

  project = var.project_id

  settings {
    availability_type = "REGIONAL"
    tier              = "db-f1-micro"
    activation_policy = "ALWAYS"
    disk_type         = "PD_SSD"

    ip_configuration {
      ipv4_enabled    = var.is_public ? true : false
      private_network = var.network_self_link
      require_ssl     = true

      dynamic "authorized_networks" {
        for_each = toset(var.is_public ? [1] : [])

        content {
          name  = "Public Internet"
          value = "0.0.0.0/0"
        }
      }
    }

    backup_configuration {
      enabled = true
    }

    maintenance_window {
      day  = 2
      hour = 4

      update_track = "stable"
    }

    dynamic "database_flags" {
      iterator = flag
      for_each = var.database_flags

      content {
        name  = flag.key
        value = flag.value
      }
    }

    user_labels = var.default_labels
  }

  deletion_protection = false
  depends_on          = [google_service_networking_connection.cloudsql-peering-connection, google_project_service.enable-sqladmin-api]
}

// Provision the databases
resource "google_sql_database" "db" {
  name     = "orders-placement"
  instance = google_sql_database_instance.instance-master.name
  project  = var.project_id
}

// Provision a super user
resource "google_sql_user" "admin-user" {
  name     = "admin-user"
  instance = google_sql_database_instance.instance-master.name
  password = random_password.user-password.result
  project  = var.project_id
}

// Get latest CA certificate
locals {
  furthest_expiration_time = reverse(sort([for k, v in google_sql_database_instance.instance-master.server_ca_cert : v.expiration_time]))[0]
  latest_ca_cert           = [for v in google_sql_database_instance.instance-master.server_ca_cert : v.cert if v.expiration_time == local.furthest_expiration_time]
}

// Get SSL certificate
resource "google_sql_ssl_cert" "client_cert" {
  common_name = "instance-master-client"
  instance    = google_sql_database_instance.instance-master.name
}

Answer 1

You will have to set deletion_protection=false, apply it and then proceed to delete.

As per the documentation

On newer versions of the provider, you must explicitly set deletion_protection=false (and run terraform apply to write the field to state) in order to destroy an instance. It is recommended to not set this field (or set it to true) until you're ready to destroy the instance and its databases.

Link

Editing Terraform state files directly / manually is not recommended

Answer 2

If you added deletion_protection to the google_sql_database_instance after the database instance was created, you need to run terraform apply before running terraform destroy so that deletion_protection is set to false on the database instance.