有空闲的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.

推荐答案

您的应用程序中的连接泄漏，因为无法关闭共享连接。您没有遇到问题，只需使用< idle>

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并管理空闲连接。这是一个很好的主意，如果你有一个buggy应用程序泄漏连接; 非常强烈建议您配置PgBouncer。

For setting timeouts, as @Doon suggested see Is it possible to configure PostgreSQL to automatically close idle connections?, 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.

A 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 timestamp列和 status 字段 pg_stat_activity 实现空闲连接回收。有一个cron作业运行这样：

In PostgreSQL 9.2 and above, you can use the new state_change timestamp column and the status 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. Don't bother; just use pgbouncer.

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