PostgreSQL连接空闲时是否存在超时时间？

Question

1 S postgres  5038   876  0  80   0 - 11962 sk_wai 09:57 ?        00:00:00 postgres: postgres my_app ::1(45035) idle                                                                                 
1 S postgres  9796   876  0  80   0 - 11964 sk_wai 11:01 ?        00:00:00 postgres: postgres my_app ::1(43084) idle             

我看到很多这样的连接。我们正在努力修复连接泄漏。但同时，我们希望为这些空闲连接设置一个超时时间，最长可能为5分钟。

- user1012451

---

你是如何连接到数据库的？socketTimeout 可能是你要找的。 - Doon

我们有这个传统的Pylons Web应用程序，我们使用了SQLAlchemy，但显然我们没有正确使用它。我不记得了。我们正在尝试修复泄漏问题。从文档中看来，socketTimeout会完全关闭与数据库的连接。我正在尝试关闭每个空闲连接，并且计数器在连接建立后立即开始。 - user1012451

可以配置PostgreSQL以自动关闭空闲连接。具体做法是通过修改postgresql.conf文件中的以下参数：tcp_keepalives_idle、tcp_keepalives_interval和tcp_keepalives_count。将它们设置为适当的值，就可以让PostgreSQL在一段时间内没有活动时自动关闭连接。 - Doon

@user1012451 当你说“关闭每个空闲”时，你是指终止<IDLE> in transaction会话，使会话保持运行但处于<IDLE>状态吗？换句话说，终止事务但不终止会话？（被投票降低：问题不清楚） - Craig Ringer

@CraigRinger 一段时间后，我们达到了最大客户端连接数。为了解决这个问题，我们必须重新启动Web应用程序，这也会强制重新启动PostgreSQL。这将清除所有连接。当我们看到这些“空闲”状态时，我们想知道是否可以在每个连接/会话上设置超时（我真的不知道正确的术语，抱歉）。如果一个事务对于普通的Web应用程序需要5分钟，那么肯定有什么问题.... - user1012451

自动重新连接怎么样？ - Satish Patro

Answer 1

看起来你的应用程序存在连接泄漏，因为它未能关闭池化的连接。你不仅与<idle>in transaction会话有问题，而且总体连接太多。

杀死连接并不是解决这个问题的正确答案，但这是一个可以接受的临时解决方法。

与其重新启动 PostgreSQL 以断开所有其他连接到 PostgreSQL 数据库的用户，不如参考：如何从 postgres 数据库中分离所有其他用户？ 和 如果有活动连接，如何放弃 PostgreSQL 数据库？。后者显示了更好的查询。

对于设置超时时间，建议像 @Doon 建议的那样请参阅 如何自动关闭 PostgreSQL 中的空闲连接？，该文章建议使用 PgBouncer 作为 PostgreSQL 的代理，管理空闲连接。如果您的应用程序本来就存在连接泄漏问题，这是一个非常好的想法，我强烈推荐配置 PgBouncer。

TCP keepalive 在这里行不通，因为应用程序仍然连接并保持活动状态，但实际上它不应该是这样的。

在 PostgreSQL 9.2 及以上版本中，您可以使用新的 state_change 时间戳列和 pg_stat_activity 的 state 字段来实现空闲连接清理器。请运行类似以下的 cron 作业：

SELECT pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE datname = 'regress'
      AND pid <> pg_backend_pid()
      AND state = 'idle'
      AND state_change < current_timestamp - INTERVAL '5' MINUTE;

在旧版本中，您需要实现复杂的方案来跟踪连接何时处于空闲状态。不要费心；只需使用pgbouncer即可。

在旧版本中，你需要实现复杂的机制来追踪连接空闲的时间。不必麻烦；直接使用pgbouncer即可。

- Craig Ringer

---

好的，但这会导致其他PgAdmin后端进程终止。请使用附加条件application_name=''。 - Andrew Selivanov

如果我正在使用pgbouncer，我能运行pg_terminate_backend吗？ - Henley

@HenleyChiu 我认为没有问题，尽管我还没有具体检查过。 - Craig Ringer

运行这个似乎导致了我的WAL发送进程崩溃。 - Joseph Persico

@CraigRinger 即使是 psql 连接也被视为空闲连接。为什么首先要关闭空闲连接呢？我有一个长时间运行的代码，它与 pg 建立连接执行一些 DML 操作，然后等待队列中的消息，然后执行更多的 DML 操作。现在，在此期间，即使像上面提到的等待队列（等待消息）时，与 postges 的连接仍然处于“空闲”状态。为什么我要关闭它呢？ - Viren

Answer 2

在 PostgreSQL 9.6 中，有一个新选项idle_in_transaction_session_timeout，它应该能够实现你所描述的内容。您可以使用SET命令进行设置，例如：

SET SESSION idle_in_transaction_session_timeout = '5min';

- shosti

---

很遗憾，我必须问这么简单的问题，但我对数据库完全是新手 - 你能否请给出一个非常基本的例子来说明如何使用这个函数？ - s g

以前的 PostgreSQL 版本中有类似的东西吗？ - sdsc81

不，之前的版本需要类似其他答案的解决方案。 - shosti

SET SESSION 只适用于当前会话（一旦您打开新连接，它将返回默认设置）。您还可以使用例如 ALTER DATABASE SET idle_in_transaction_session_timeout = '5min' 在数据库级别上设置配置参数，或者使用配置文件（参见 https://www.postgresql.org/docs/current/static/config-setting.html）。 - shosti

你可以在 postgresql.conf 文件中设置，或通过 alter system 命令进行设置。非常好。 - JL Peyret

Answer 3

在PostgreSQL 9.1中，使用以下查询可以查看处于空闲状态的连接。这对我避免了需要重新启动数据库的情况。这种情况通常发生在未正确关闭的JDBC连接上。

SELECT
   pg_terminate_backend(procpid)
FROM
   pg_stat_activity
WHERE
   current_query = '<IDLE>'
AND
   now() - query_start > '00:10:00';

- sramay

---

pg_terminate_backend自8.4版本开始存在。 - Andrew Banks

Answer 4

如果您使用的是PostgreSQL 9.6+，那么您可以在postgresql.conf中设置

idle_in_transaction_session_timeout = 30000 (毫秒)

- Bertrand David

Answer 5

网络错误会导致连接中断，存在一个超时机制来处理这种情况，使用的是操作系统的TCP keepalive功能。在Linux上，默认情况下，破裂的TCP连接将在约2小时后关闭（参见sysctl net.ipv4.tcp_keepalive_time）。

还有一个关于遗留事务的超时设置idle_in_transaction_session_timeout 和锁超时设置 lock_timeout。建议在postgresql.conf文件中修改这些设置。

但是对于已经建立的客户端连接，没有超时限制。如果客户端想要保持连接打开状态，那么应该能够无限期地执行。如果客户端存在连接泄漏的情况（例如不断打开连接并且从未关闭），请修复客户端问题。千万不要试图在服务器端终止已经正确建立的空闲连接。

- rustyx

Answer 6

一种可能的解决方法，可以启用数据库会话超时而无需外部定期任务，是使用我开发的扩展pg_timeout。

- pifor

Answer 7

另一种选择是设置此值“tcp_keepalives_idle”。在文档https://www.postgresql.org/docs/10/runtime-config-connection.html中了解更多信息。

- Delirante