google_sql_database_instance - disk_autoresize - disk_size - Problem after resize

[SHackauf]

Community Note

I create my database instance via terraform google_sql_database_instance. Inside my configuration I activated the autorezise option with the following parameters:

disk_autoresize       = "true"
disk_autoresize_limit = "0"
disk_size             = "10"
disk_type             = "PD_SSD"

Unfortunately after the GCP makes an autoresice (for example to size 15) the terraform run does not see this situation. It finds out that inside the terraform file is the disk_size set to parameter 10 and wants to downsize the database again. Downsize in this case means it wants to delete the database instance and wants to create it new.

I am not sure if it is possible, that terraform finds out, that the database was automatically grown to 15 because of parameter disk_autoresize is true. In this case maybe it is easier if terraform does not change the disk_size.

Our current workaround is, that we need to check every time before running an database instance terraform script the database inside GCP and update the disk_size manually.

Terraform Version & Provider Version(s)

Terraform v1.8.4

• provider registry.terraform.io/hashicorp/google v4.43.0

Affected Resource(s)

google_sql_database_instance

Terraform Configuration

Debug Output

No response

Expected Behavior

No response

Actual Behavior

No response

Steps to reproduce

1. terraform apply

Important Factoids

No response

References

No response

b/344889306

[ggtisc]

This issue requires a lot of data to be reproduced, I created the resource to confirm that there aren't conflicts in the configuration and in this point everything is ok, but I don't have any tool to reach the minimum limit of 10 GB of data. Is there an API call to make a test stress for this scenario?

[SHackauf]

We found a work around for this problem. We added the following code in our terraform file from the database resources:

  lifecycle {
    ignore_changes = [settings[0].disk_size]
  }

For already installed databases it works. Link to the documentation: https://developer.hashicorp.com/terraform/language/meta-arguments/lifecycle

[BorysekOndrej]

Hi @SHackauf, this issue has been encountered by several users before, for example #14671 and tangentially related #12618.

I suggest you omit the disk_size argument all together. The SQL instance will be created with the default 10 GB size. Later on, when you run apply after an auto-size-increase has happened, the TF state file will updated completely silently and it will not trigger an re-create.

I'm not sure if this behavior is new, I've looked through the changelog and haven't found any recent changes there.

---

Warning for your workaround using lifecycle: it's possible that the whole settings is getting ignored, not just the disk_size. But maybe I'm missing something.

---

Postgres reproducer

You can test the the auto increase in size by running this sequence of commands: ```sql CREATE TABLE posts ( postid SERIAL PRIMARY KEY, fulldata TEXT ); INSERT INTO posts(fulldata) SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '') FROM generate_series(1, 50*1024*1024); --- run this 15 times to fill more than 10 GB INSERT INTO posts(fulldata) SELECT REPEAT(fulldata, 20) FROM (SELECT fulldata FROM posts WHERE postid=1) as k; SELECT postid, LENGTH(fulldata)/1024/1024 as mb_size FROM "public"."posts" LIMIT 1000; ```