从Azure连接到Amazon RDS上的Postgres数据库的超时 [英] Timeouts connecting to a Postgres database on Amazon RDS from Azure

问题描述

在数据库连接保持空闲一段时间后，我的应用程序中出现以下异常:

  ...发送到后端时发生I/O错误.嵌套的异常是org.postgresql.util.PSQLException:发送到后端时发生I/O错误.java.net.SocketException:操作超时在java.net.SocketInputStream.socketRead0(本地方法) 

psql中也发生了同样的问题，而且我没有连接到本地数据库的问题，所以我很确定问题出在RDS上.

  psql =>选择确定"；SSL SYSCALL错误:操作超时psql =>选择确定"；SSL SYSCALL错误:检测到EOF与服务器的连接已丢失.尝试重置:成功. 

我发现了这个其他问题，这表明围绕该问题的一项工作可以改善情况(超时现在需要更长的时间)，但并没有解决.

我正在将Spring Boot与JDBC(tomcat连接池)和JDBCTemplate一起使用.

是否有解决方法或解决方法?也许强迫连接池进行测试并重新连接?在这种环境下我该怎么做?

这是我的连接字符串

  jdbc:postgresql://myhost.c2estvxozjm3.eu-west-1.rds.amazonaws.com/dashboard?tcpKeepAlive = true 

解决方案:

根据所选答案中的建议，编辑了RDS服务器端TCP_KeepAlive参数.我正在使用的参数是:

  tcp_keepalives_count 5tcp_keepalives_idle 200tcp_keepalives_interval 200 

解决方案

连接跟踪，或者在一段时间后忘记连接./p>

我建议启用TCP Keepalive .您可能可以在AWS RDS配置中在服务器端启用它们.如果没有，则可以在JDBC驱动程序中请求它们在客户端.

TCP保持连接比验证/测试查询要好得多，因为它们的开销要低得多，并且不会在服务器查询日志中导致不必要的日志垃圾邮件.

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=> 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 question which 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.

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:

tcp_keepalives_count      5
tcp_keepalives_idle     200
tcp_keepalives_interval 200

解决方案

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.

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 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.

这篇关于从Azure连接到Amazon RDS上的Postgres数据库的超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！