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
}

Jacalynjacamar

Did you updated the old one with the deletion_protection to false? – Bestead

which version of terraform are you using? I found this Github issue where it is mentioned that this is solved on newer versions of terraform – Remorseless

Answer 1

Seems like your code going to recreate this sql-instance. But your current tfstate file contains an instance-code with true value for deletion_protection parameter. In this case, you need first of all change value of this parameter to false manually in tfstate file or by adding deletion_protection = true in the code with running terraform apply command after that (beware: your code shouldn't do a recreation of the instance). And after this manipulations, you can do anything with your SQL instance

Telephonic

deletion_protection = true is the default set by recent terraform versions if it is not defined in the apply step (at least for Google SQL). As such it needs to be set to disabled first before destroy works like Aron writes. An alternative is to go into the console and remove the instance there and then run terraform destroy again to proceed. – Hilten

In vi terraform.tfstate locate deletion_protection and change it – Road

@rsantiago, it's also can be used as a workaround way. But almost all manual changes in the *.tfstate file is dangerous – Telephonic

Answer 2

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

Sustenance

Answer 3

I faced the similar issue and solved it in different way. I wanted to change bigquery table name but deletion_protection was not set to false. So i made two changes in terraform file:

1. Set delete_protection to false.
2. Change the bigquery table name.

After making the two changes in terraform file, apply command did not work as expected. The first change was not reflected while the apply command is executed. Actually delete_protection value was coming from state file that was the default value true. So i did the two things:

1. Set delete_protection to false and run apply command. Now delete_protection value is false in state file.
2. Change the table name and run apply command. Now updated value delete_protection attributed is applied.

Incubation

Answer 4

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.

Beset

this doesn't work as the deletion protection cannot be set to cloud SQL instances. – Remorseless