postgresql 从 Azure 连接到 Amazon RDS 上的 Postgres 数据库超时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22696745/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Timeouts connecting to a Postgres database on Amazon RDS from Azure
提问by Cleber Goncalves
I get the following exception in my application after leaving a database connection idle for some amount of time:
在使数据库连接空闲一段时间后，我的应用程序中出现以下异常：
... An I/O error occured while sending to the backend.; nested exception is org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.] with root cause
java.net.SocketException: Operation timed out
at java.net.SocketInputStream.socketRead0(Native Method)
The same issue happens in psql AND I don't have issues connecting to a local database, so I'm pretty sure the problem is on RDS.
同样的问题发生在 psql 中，而且我在连接到本地数据库时没有问题，所以我很确定问题出在 RDS 上。
psql=> select 'ok';
SSL SYSCALL error: Operation timed out
psql=> select 'ok';
SSL SYSCALL error: EOF detected
The connection to the server was lost. Attempting reset: Succeeded.
I found this other questionwhich suggests a work around that improved the situation (timeouts now take a lot longer) but didn't fix it.
我发现了另一个问题，它表明有一种解决方法可以改善情况（现在超时需要更长的时间）但没有解决。
I'm using Spring Boot with JDBC (tomcat connection pooling) and JDBCTemplate.
我将 Spring Boot 与 JDBC（tomcat 连接池）和 JDBCTemplate 一起使用。
Is there a work around or a fix? Perhaps forcing the connection pool to test and reconnect? How do I do that in this environment?
有解决方法或修复方法吗？也许强制连接池测试并重新连接？在这种环境下我该怎么做？
EDIT: This is my connection string
编辑：这是我的连接字符串
jdbc:postgresql://myhost.c2estvxozjm3.eu-west-1.rds.amazonaws.com/dashboard?tcpKeepAlive=true
SOLUTION:
解决方案：
Edited the RDS server side TCP_KeepAlive parameters as suggested in the selected answer. The parameters I'm using are:
按照所选答案中的建议编辑 RDS 服务器端 TCP_KeepAlive 参数。我使用的参数是：
tcp_keepalives_count 5
tcp_keepalives_idle 200
tcp_keepalives_interval 200
回答by Craig Ringer
It looks like something - maybe a NAT router on your end, maybe something on AWS's end - is connection tracking, and is forgetting about connections after a while.
看起来有些东西 - 可能是你端的 NAT 路由器，也可能是 AWS 端的东西 - 是连接跟踪，一段时间后会忘记连接。
I suggest enabling TCP keepalives. You might be able to enable them server side in the AWS RDS configuration; if not, you can request them client-side in the JDBC driver.
我建议启用 TCP keepalives。您可以在 AWS RDS 配置中启用它们的服务器端；如果没有，您可以在 JDBC 驱动程序中在客户端请求它们。
TCP keepalives are a lot better than a validation/test query, because they're much lower overhead, and they don't result in unnecessary log spam in the server query logs.
TCP keepalive 比验证/测试查询要好得多，因为它们的开销要低得多，而且它们不会在服务器查询日志中导致不必要的日志垃圾邮件。
回答by Dave Syer
Maybe try
也许试试
spring.datasource.validation-query=SELECT 1
spring.datasource.test-on-borrow=true
(See AbstractDataSourceConfigurationfor other options.)
（有关AbstractDataSourceConfiguration其他选项，请参阅。）
回答by user2040074
In your connection string are you also including the port or just the endpoint? Try using the entire endpoint in your connection string. Also check to make sure the security group assigned to the RDS instance has the proper ports and Inbound CIDR defined.
在您的连接字符串中，您还包括端口还是仅包括端点？尝试在连接字符串中使用整个端点。还要检查以确保分配给 RDS 实例的安全组具有正确的端口和定义的入站 CIDR。
