英文:
GCP Server crashing with "cloudsql.enable_instance_password_validation"
问题
在过去的一天里，我经常遇到这个错误，导致云实例需要重置才能继续连接：
错误：无法识别的配置参数“cloudsql.enable_instance_password_validation”
这是在一个 PostgreSQL 14 GCP Cloud SQL 社区共享的 1 vCPU、0.614 GB 实例上运行的，但在标准的 1 vCPU、3.7 GB 实例上也进行了测试，问题仍然存在。
自从出现这个问题以来，唯一改变的代码是使用 Golang PGX 池接口进行的 listen/notify 调用，已经回滚但问题仍然存在。
这个问题经常在任何数据库调用中出现（在重置后的 30 分钟内），而且我没有设置任何与“enable_instance_password_validation”有关的内容 - 我也找不到任何涉及这个名称的参数。
英文:
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.
答案1
得分: 0
这是一个有趣的问题，@enocom正确地指出，在Postgresql中遇到的错误是一个误导。在整个过程中，由于问题涉及到Go服务器实例和Postgresql服务器，出现了几个误导。
崩溃的真正原因是Golang PGX池接口与Postgres数据库交互时，默认情况下具有最大连接数限制为14个。当连接数达到这个数量时，服务器会无错误日志地挂起，并且所有后续连接将被永久拒绝。
要解决这个问题，只需在调用时在dbURI中添加'pool_max_conns'：
dbPool, err := pgxpool.Connect(context.Background(), dbURI)
可以使用以下方式实现：
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)
详细的说明可以在这里找到：pgxpool包信息
在设置PGX最大连接数时，请确保将此数字设置为与服务器实例的最大并发连接数相同或相似，以避免再次发生此问题。对于GCP云运行实例，可以在部署修订版本中设置，在“每个容器的最大请求数”下设置。
英文:
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'
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论