Error: could not start transaction: dial tcp <ip>:5432: connect: connection timed out

[vesubramanian]

Hi,

Please help. I am facing a timeout issue. I have tried my best to provide whatever info required.

Terraform Version

1.1.4

Affected Resource(s)

Please list the resources as a list, for example:

• postgresql_role (on aurora rds serverless v2 postgresql)

Terraform Configuration Files

  required_providers {
    aws = {
      version = ">= 4.12.0"
    }
    postgresql = { 
      source = "cyrilgdn/postgresql"
      version = "1.15.0"
    }
  }
}

provider "postgresql" {
  scheme   = "awspostgres"
  host     = module.rds-aurora.cluster_endpoint
  port     = module.rds-aurora.cluster_port
  database = "postgres"
  username = var.superuser_name
  password = var.superuser_password
  connect_timeout = 720
  superuser       = false
  expected_version = "13.7"
}

resource "postgresql_role" "pg_roles" {
  for_each           = var.rolelist
  name               = each.key
  login              = true
  password           = each.value
  encrypted_password = true
  #provider = postgresql
  depends_on = [module.rds-aurora]
}.

Expected Behavior

Roles being passed as key value pairs (username & password) should have been created in the cluster/writer instance

Actual Behavior

Error: could not start transaction: dial tcp :5432: connect: connection timed out

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

1. terraform apply

Important Factoids

Running on Aurora Serverless v2 Postgresql (Not publicly accessible. Tried with Publicly accessible also, but still same error. However, public access cannot be turned on).

[egeland]

I get the same, when Aurora has been idle for a while. If I retry, it works.

Is there any way to add a retry option?

[egeland]

I increased my connect_timeout to 900 and it seems to have helped. Will keep an eye on it, though...

[cyrilgdn]

Hi @vesubramanian ,

Do you still have this issue?

If yes, it's a bit hard to help just with a timeout. Are you able to connect manually (with psql) when the provider encounters the timeout?

[fernandonogueira]

I have very frequent issues with timeouts, too. I didn't have time to investigate better, though.
They are completely random, and I don't have issues connecting with any clients, only with the provider.
Btw, I'm using the gcppostgres scheme.

[sun4ezzz]

I have the same intermittent issue and also use gcppostgres scheme. I can only add that the issue started when the amount of db resources in TF state became quite huge.

[AndresPinerosZen]

Same issue here. Also gcppostgres.

Even with terraform apply -paralellism=1.

[AndresPinerosZen]

The actual cause for this issue is shown here:
#572

It's not related with the CloudSQL Go Connector, it's the way the connections are managed.

[AndresPinerosZen]

Something important to understand with GCP is that the problem happens because they have a ratelimint on the amount of connections that a single user can establish per minute. So, this is something that I haven't tested yet, but a workaround could be to have 5 different users (5 different provider configs) and then map different resources to each provider.

Which really really sucks, but it could help if anyone is stuck and has to get this working.