terraform-provider-aws: Modify aws_db_instance and delete aws_db_parameter_group breaks

You want to use this snippet on the aws_db_parameter_group:

lifecycle {
    create_before_destroy = true
  }

This will create the new DB parameter group, update the DB, and finally delete the old parameter group. You might need to use name_prefix instead of name to avoid collision as well.

I also encountered this while trying to change the name of a db param group

Plan:

-/+ resource "aws_db_parameter_group" "scuba" {
      ~ arn         = "arn:aws:rds:xxxxxxxxxxxxxx" -> (known after apply)
        description = "Managed by Terraform"
        family      = "postgres11"
      ~ id          = "xxxxxx-postgres-11" -> (known after apply)
      ~ name        = "xxxxxx-postgres-11" -> "yyyyyy-postgres-11" # forces replacement
      + name_prefix = (known after apply)
      - tags        = {} -> null

        parameter {
            apply_method = "immediate"
            name         = "temp_file_limit"
            value        = "2147483647"
        }
        parameter {
            apply_method = "immediate"
            name         = "work_mem"
            value        = "65536"
        }
    }

aws_db_parameter_group.xxxxx: Still destroying... [id=xxxxx-postgres-11, 2m40s elapsed]
aws_db_parameter_group.xxxxx: Still destroying... [id=xxxxx-postgres-11, 2m50s elapsed]

Error: Error deleting DB parameter group: InvalidDBParameterGroupState: One or more database instances are still members of this parameter group xxxxx-postgres-11, so the group cannot be deleted
	status code: 400, request id: xxxxx-xxxxx-xxxxx-xxxxx-xxxxx

Action: I saw the rds instance was marked as ready-for-reboot, manually rebooted the instance. Impact: No change - same results as above.

Action: Created a backup of the xxx database group. Tried to delete the xxxxx-postgres-11 database group. Impact: Failed to delete xxxxx-postgres-11: One or more database instances are still members of this parameter group xxxxx-postgres-11, so the group cannot be deleted (Service: AmazonRDS; Status Code: 400; Error Code: InvalidDBParameterGroupState; Request ID: xxxxx-xxxxx-xxxxx-xxxxx-xxxxx).

Action:

1. Logged into the AWS Console
2. modified the actual RDS instance back to the default.postgres11 group provided by AWS
3. re ran terraform apply

Impact: Worked. Once the group was no longer actively assigned to a database, terraform could rename the custom xxxxx-postgres-11 to yyyy-postgres-11 database group. TF then swapped the default.postgres.11 group for the yyyy-postgres-11 group by applying the change immediately.

Suggestion: Looks like terraform needs to assign to a temporary or default group to the RDS instance prior to modifying the aws_db_parameter_group. Upon completion, restore the intended group.

name_prefix and create_before_destroy = true in theory should work (we have upgraded some DBs with no issues) 👍 but today we faced with failed terraform apply 😦

aws_rds_cluster_parameter_group.default[0]: Creation complete after 1s [id=xxx-20211122151251039100000001]
aws_rds_cluster.default[0]: Modifying... [id=xxx]
aws_rds_cluster.default[0]: Still modifying... [id=xxx, 10s elapsed]
aws_rds_cluster.default[0]: Still modifying... [id=xxx, 20s elapsed]
aws_rds_cluster.default[0]: Still modifying... [id=xxx, 30s elapsed]
aws_rds_cluster.default[0]: Modifications complete after 31s [id=xxx]
aws_rds_cluster_instance.default[0]: Modifying... [id=xxx]
aws_rds_cluster_instance.default[0]: Modifications complete after 0s [id=xxx]
aws_rds_cluster_parameter_group.default[0]: Destroying... [id=xxx-20211021125946599700000001]
Error: InvalidDBParameterGroupState: One or more database instances are still members of this parameter group xxx-20211021125946599700000001, so the group cannot be deleted
   status code: 400

maybe there is no additional check that instances are updated too (Available state) aws_rds_cluster_instance.default[0]: Modifications complete after 0s [id=xxx] 0 seconds

Hi guys, I agree with the previous comment. This causes problems in the automation and use of pipelines. In my case, when using Jenkins. Removing or modifying RDS is impossible in those tasks that pursue terraforms. Unfortunately, I can not offer a solution to the problem in the form of code, I can only assume that this option will work:

1. Rollback to the default RDS group
2. Deleting a parameter group
3. Removing RDS

Here is our code:

resource “aws_db_parameter_group” “pg” { name = “paramg” family = “mysql5.7”

parameter { name = “log_bin_trust_function_creators” value = “1” } } resource “aws_db_instance” “db” { allocated_storage = 30 # gigabytes backup_retention_period = 7 # in days engine = “mysql” engine_version = “5.7” identifier = “db” instance_class = “db.t3.small” multi_az = true name = “mydb” password = “password” port = 5465 storage_type = “gp2” username = “devops” vpc_security_group_ids = [“${aws_security_group.DB-SG.id}”] parameter_group_name = “paramg” skip_final_snapshot = true }

I understand that there are workarounds using AWS console, but agree that this is not a solution to this problem.

Reaction to destroy:

Error deleting DB parameter group: InvalidDBParameterGroupState: One or more database instances are still members of this parameter group paramg, so the group cannot be deleted status code: 400, request id: xxxxxxx-xxxxxx-xxxxxxx-xxxxxxxx-xxxxxxxxxx

Then the pipeline will not move. This problem also reproduces with command-line commands. It doesn’t matter whether it is enabled or not apply_imediately. If I missed something, please correct me. Thank you. Regards.

UPD This problem can be fooled. In the case of Jenkins, we reset the error of the first destruction and immediately launch the second in this way:

    stage('Terraform Destroy') {
      steps {
        input 'Destroy Plan'
        catchError(buildResult: 'SUCCESS', stageResult: 'FAILURE') {
        sh "${env.TERRAFORM_HOME}/terraform destroy -force -input=false"
        }
      }
    }
    stage('Terraform second Destroy') {
      steps {
        input 'Destroy Plan'
        sh "${env.TERRAFORM_HOME}/terraform destroy -force -input=false"
      }
    }
  }
}

I hope it will be useful to someone.

The same issue 2 years after. No RDS cleanup/destroy possible:

Error: InvalidDBParameterGroupState: One or more database instances are still members of this parameter group ambari-hdf-peterz, so the group cannot be deleted status code: 400, request id: 64d52b07-e31d-4355-89a6-76755072a433

Error: error deleting RDS Cluster (ambari-hdf-peterz): DBClusterSnapshotAlreadyExistsFault: Cannot create the cluster snapshot because one with the identifier ambari-hdf-final-snapshot already exists. status code: 400, request id: 58b59224-5a06-4586-b6dc-4d9ab62ead67

Error: Error deleting DB parameter group: InvalidDBParameterGroupState: One or more database instances are still members of this parameter group xxxxx, so the group cannot be deleted status code: 400, request id: xxxxxx

[terragrunt] 2020/06/19 12:37:01 Hit multiple errors: exit status 1

I’m having the same issue when trying to upgrade from Postgres 10.6 to 11.1 on RDS. As with @liamg-form3 the parameter group remains the original one.

My code doesn’t explicitly delete the parameter group, the only change was to upgrade the engine version and use a new postgres11 family for the parameter group. I’m using the terraform-aws-rds module.

This workaround has worked fine on several instances with the same issue:

• Run Terraform, get the error above about parameter group
• Reboot the DB instance - after reboot, will be on the new version, and using the new parameter group from postgres11 family
• Run Terraform again to ensure the old parameter group is deleted

Not very elegant but quite easy to do, and some downtime is required anyway with RDS when upgrading Postgres.

I’m experiencing what may be the same problem. It looks like the modification simply does not happen in my case. After an apply, the parameter group used by my instance is still the original one: default.postgres9.5 (in-sync). There is no pending reboot on the instance either.

If you remove the deletion of your old group, does the modification actually happen at all?