空闲 PostgreSQL 连接是否超时？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13236160/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a timeout for idle PostgreSQL connections?
提问by user1012451
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
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.
我看到很多。我们正在尝试修复我们的连接泄漏。但与此同时，我们想为这些空闲连接设置超时，可能最多 5 分钟。
回答by Craig Ringer
It sounds like you have a connection leakin your application because it fails to close pooled connections. You aren't having issues just with <idle> in transactionsessions, but with too many connections overall.
听起来您的应用程序中存在连接泄漏，因为它无法关闭池连接。您不仅会遇到<idle> in transaction会话问题，还会遇到整体连接数过多的问题。
Killing connections is not the right answer for that, but it's an OK-ish temporary workaround.
终止连接不是正确的答案，但它是一个不错的临时解决方法。
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.
与其重新启动 PostgreSQL 以从 PostgreSQL 数据库启动所有其他连接，请参阅：如何从 postgres 数据库中分离所有其他用户？如果有活动连接，如何删除 PostgreSQL 数据库？. 后者显示了更好的查询。
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 stronglyrecommend configuring PgBouncer.
对于设置超时，正如@Doon 建议的那样，请参阅如何自动关闭 PostgreSQL 中的空闲连接？，建议您使用 PgBouncer 来代理 PostgreSQL 并管理空闲连接。如果您有一个有漏洞的应用程序，无论如何都会泄漏连接，这是一个非常好的主意；我强烈建议配置 PgBouncer。
A TCP keepalivewon't do the job here, because the app is still connected and alive, it just shouldn't be.
一个TCP存活不会在这里做的工作，因为该应用程序仍处于连接状态，充满活力，它只是不应该。
In PostgreSQL 9.2 and above, you can use the new state_changetimestamp column and the statefield of pg_stat_activityto implement an idle connection reaper. Have a cron job run something like this:
在 PostgreSQL 9.2 及以上版本中，您可以使用新的state_change时间戳列和state字段pg_stat_activity来实现空闲连接收割者。让一个 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;
In older versions you need to implement complicated schemes that keep track of when the connection went idle. Do not bother; just use pgbouncer.
在旧版本中，您需要实施复杂的方案来跟踪连接何时空闲。不打扰; 只需使用 pgbouncer。
回答by shosti
In PostgreSQL 9.6, there's a new option idle_in_transaction_session_timeoutwhich should accomplish what you describe. You can set it using the SETcommand, e.g.:
在 PostgreSQL 9.6 中，有一个新选项idle_in_transaction_session_timeout可以完成您所描述的内容。您可以使用SET命令设置它，例如：
SET SESSION idle_in_transaction_session_timeout = '5min';
回答by sramay
In PostgreSQL 9.1, the idle connections with following query. It helped me to ward off the situation which warranted in restarting the database. This happens mostly with JDBC connections opened and not closed properly.
在 PostgreSQL 9.1 中，空闲连接具有以下查询。它帮助我避免了重新启动数据库所需的情况。这主要发生在 JDBC 连接打开但未正确关闭的情况下。
SELECT
pg_terminate_backend(procpid)
FROM
pg_stat_activity
WHERE
current_query = '<IDLE>'
AND
now() - query_start > '00:10:00';
回答by Bertrand David
if you are using postgresql 9.6+, then in your postgresql.conf you can set
如果您使用的是 postgresql 9.6+，那么在您的 postgresql.conf 中您可以设置
idle_in_transaction_session_timeout = 30000(msec)
idle_in_transaction_session_timeout = 30000(毫秒)
回答by pifor
A possible workaround that allows to enable database session timeout without an external scheduled task is to use the extension pg_timeoutthat I have developped.
允许在没有外部计划任务的情况下启用数据库会话超时的可能解决方法是使用我开发的扩展pg_timeout。
