如何自动关闭PostgreSQL中的空闲连接？

概述有些客户端连接到我们的postgresql数据库，但保持打开连接。 在一定量的不活动状态之后，是否可以告诉Postgresql关闭这些连接？ TL; DR IF you’re using a Postgresql version >= 9.2 THEN use 07000 IF you don’t want to write any code THEN use 07001 对于有兴趣的人来说，这里 有些客户端连接到我们的postgresql数据库，但保持打开连接。
在一定量的不活动状态之后，是否可以告诉Postgresql关闭这些连接？

TL; DR

IF you’re using a Postgresql version >= 9.2
THEN use 07000

IF you don’t want to write any code
THEN use 07001

对于有兴趣的人来说，这里是我从 Craig Ringer的评论中提出的解决方案：

You Could use a cron job to look at when the connection was last active (see pg_stat_activity) and use pg_terminate_backend to kill old ones. Easily expressed in a simple query. I’m not sure if pg_terminate_backend was available in the pretty-ancIEnt 8.3,though.

所选解决方案如下：

>首先，我们升级到Postgresql 9.2。
>然后，我们安排线程每秒运行一次。
>当线程运行时，它会查找任何旧的非活动连接。

A connection is consIDered inactive if its state is either IDle,IDle in transaction,IDle in transaction (aborted) or Disabled. A connection is consIDered old if its last state has changed for more than 5 minutes.

>还有一些额外的线程和上面的一样。但是，这些线程与不同的用户连接到数据库。
>我们至少为连接到我们的数据库的任何应用程序打开一个连接。 (rank()函数)

这是线程运行的SQL查询：

WITH inactive_connections AS (    SELECT        pID,rank() over (partition by clIEnt_addr order by backend_start ASC) as rank    FROM         pg_stat_activity    WHERE        -- Exclude the thread owned connection (IE no auto-kill)        pID <> pg_backend_pID( )    AND        -- Exclude kNown applications connections        application_name !~ '(?:psql)|(?:pgadmin.+)'    AND        -- Include connections to the same database the thread is connected to        datname = current_database()     AND        -- Include connections using the same thread username connection        usename = current_user     AND        -- Include inactive connections only        state in ('IDle','IDle in transaction','IDle in transaction (aborted)','Disabled')     AND        -- Include old connections (found with the state_change fIEld)        current_timestamp - state_change > interval '5 minutes' )SELECT    pg_terminate_backend(pID)FROM    inactive_connections WHERE    rank > 1 -- Leave one connection for each application connected to the database

总结

以上是内存溢出为你收集整理的如何自动关闭PostgreSQL中的空闲连接？全部内容，希望文章能够帮你解决如何自动关闭PostgreSQL中的空闲连接？所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。