有些客户端连接到我们的postgresql数据库，但保持打开连接。
在一定量的不活动状态之后，是否可以告诉Postgresql关闭这些连接？
在一定量的不活动状态之后，是否可以告诉Postgresql关闭这些连接？
TL; DR
IF you’re using a Postgresql version >=
9.2
THEN use 07000IF 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。
>然后，我们安排线程每秒运行一次。
>当线程运行时，它会查找任何旧的非活动连接。
>还有一些额外的线程和上面的一样。但是，这些线程与不同的用户连接到数据库。
>我们至少为连接到我们的数据库的任何应用程序打开一个连接。 (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
