GCP Server crashing with "cloudsql.enable_instance_password_validation"

user_15145374 0
Viewed 148

Over the past day I have been experiencing this error very frequently which results in the cloud instance needing to be reset in order to continue connections:

ERROR:  unrecognized configuration parameter "cloudsql.enable_instance_password_validation"

This is operating on a PostgreSQL 14 GCP Cloud SQL community shared 1 vCPU, 0.614 GB instance but was also tested on the standard 1 vCPU, 3.7 GB instance where the problem persisted.

The only code that has changed since this occurrence is a listen/notify call with a Golang PGX pool interface which has been reverted and the problem persists.

The problem hits regularly with any database calls (within 30 mins of a reset) and I have not set anything involving "enable_instance_password_validation" - I am also unable to find any parameters involving this name.

google-cloud-sql go google-cloud-platform postgresql sql
1 Answers

This was an interesting one, @enocom was correct in pointing out that the error experienced within Postgresql was a red herring. Throughout the process, there were several red herrings due to the problem spanning across both the Go server instance and the Postgresql server.

The real reason for the crashes was that the Golang PGX pool interface for interacting with the Postgres DB had a maximum 'DBMaxPools' cap of 14 connections by default. When we get to this number of connections the server hangs without any error log and all further connections are refused perpetually.

To solve this problem all that is required is adding a 'pool_max_conns' within the dbURI when calling:

dbPool, err := pgxpool.Connect(context.Background(), dbURI)

This can do done with something like:

dbURI = fmt.Sprintf(`postgresql://%s:%s@%s:%s/%s?pool_max_conns=%s`, config.DBUser, config.DBPassword, config.DBAddress, config.DBPort, config.DBName, config.DBMaxPools)

Detailed instructions can be found here: pgxpool package info

When setting PGX max conns, be sure to set this number to the same or similar to your maximum concurrency connections for the server instance to avoid this happening again. For GCP cloud run instances, this can be set within Deployment Revisions, under 'Maximum requests per container'

user_151453740
Related