空闲的 PostgreSQL 连接是否有超时? [英] Is there a timeout for idle PostgreSQL connections?

问题描述

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

I see a lot of them. We are trying to fix our connection leak. But meanwhile, we want to set a timeout for these idle connections, maybe max to 5 minute.

推荐答案

听起来您的应用程序中存在连接泄漏，因为它未能关闭池连接.您不仅仅遇到 的问题在事务 会话中，但总体连接过多.

It sounds like you have a connection leak in your application because it fails to close pooled connections. You aren't having issues just with <idle> in transaction sessions, but with too many connections overall.

终止连接不是解决这个问题的正确方法，但它是一个不错的临时解决方法.

Killing connections is not the right answer for that, but it's an OK-ish temporary workaround.

与其重新启动 PostgreSQL 以从 PostgreSQL 数据库启动所有其他连接，请参阅:如何分离所有其他用户来自 postgres 数据库? 和 如果有活动连接，如何删除 PostgreSQL 数据库?.后者显示了更好的查询.

Rather than re-starting PostgreSQL to boot all other connections off a PostgreSQL database, see: How do I detach all other users from a postgres database? and How to drop a PostgreSQL database if there are active connections to it? . The latter shows a better query.

如@Doon 建议的设置超时，请参阅如何自动关闭 PostgreSQL 中的空闲连接?，其中建议您使用 PgBouncer 代理 PostgreSQL 并管理空闲连接.如果您有一个有漏洞的应用程序，无论如何都会泄漏连接，这是一个非常好的主意；我强烈推荐配置 PgBouncer.

For setting timeouts, as @Doon suggested see How to close idle connections in PostgreSQL automatically?, which advises you to use PgBouncer to proxy for PostgreSQL and manage idle connections. This is a very good idea if you have a buggy application that leaks connections anyway; I very strongly recommend configuring PgBouncer.

TCP keepalive 不会执行在这里工作，因为该应用程序仍处于连接状态且处于活动状态，所以不应该如此.

A TCP keepalive won't do the job here, because the app is still connected and alive, it just shouldn't be.

在 PostgreSQL 9.2 及更高版本中，您可以使用新的 state_change 时间戳列和 pg_stat_activity 的 state 字段来实现空闲连接收割者.让 cron 作业运行如下:

In PostgreSQL 9.2 and above, you can use the new state_change timestamp column and the state field of pg_stat_activity to implement an idle connection reaper. Have a cron job run something like this:

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.

In older versions you need to implement complicated schemes that keep track of when the connection went idle. Do not bother; just use pgbouncer.

这篇关于空闲的 PostgreSQL 连接是否有超时?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！