Recommended cloud sql postgresql client settings

[Robert Xu]

We have a postgresql v13 instance where we get occasionally "connection reset by peer" errors.

We are not using the Cloud SQL Auth proxy.

Code is in golang and using pgx driver with database/sql Interface.

For each client, we have a max connection of 20, a max idle connection of 5, and max connection life time of 5 minutes.

We are deploying multiple clients with these settings. Looking in in the graph of active connections, we have a max of 500 and in the last week have a max active connection of 14.

Are there some recommend settings that we should be using to prevent us from getting these  occasional "connection reset by peer" errors?

[fiescocasasola]

Did you follow Google’s documentation to setup the postgresql instance? If you use Google’s documentation, can you share it please?  The recommended settings are always part of Google’s documentation. 

Here is a google document to create a  PostgreSQL instance [1]

[1]:https://cloud.google.com/sql/docs/postgres/create-instance

[Robert Xu]

Yes.

We defined the instance creation in terraform as:

resource "google_sql_database_instance" "postgres" {

  name = "prefix-postgres"

  database_version = "POSTGRES_13"

  deletion_protection = true

  settings {

    tier = "db-custom-4-15360"

    disk_autoresize = true

    disk_size = 200

    disk_type = "PD_SSD"

    activation_policy = "ALWAYS"

    availability_type = "REGIONAL"

    backup_configuration {

      enabled = true

      start_time = "07:00"

      location = "us"

      transaction_log_retention_days = 7

      point_in_time_recovery_enabled = true

      backup_retention_settings {

        retained_backups = 365

        retention_unit = "COUNT"

      }

    }

    database_flags {

      name = "cloudsql.enable_pgaudit"

      value = "on"

    }

    database_flags {

      name = "pgaudit.log_parameter"

      value = "on"

    }

    database_flags {

      name = "cloudsql.logical_decoding"

      value = "on"

    }

    ip_configuration {

      ipv4_enabled = false

      dynamic "authorized_networks" {

      }

      private_network = resource.google_compute_network.default.id

        require_ssl = true

    }

    maintenance_window {

      day = 7

      hour = 8

    }

  }

  lifecycle {

    ignore_changes = [

      settings[0].disk_size

    ]

  }

}

[contreraspinon]

Do you see an improvement if you use the default connection amount limits, according to what is recommended from the documentation? The documentation also describes this error being caused by connection timeouts, have you found better results by increasing the connection life?