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

问题描述

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

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)

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

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.

我将 Spring Boot 与 JDBC(tomcat 连接池)和 JDBCTemplate 结合使用.

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?

这是我的连接字符串

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

解决方案:

按照所选答案中的建议编辑 RDS 服务器端 TCP_KeepAlive 参数.我使用的参数是:

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.

推荐答案

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

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屋！