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

一些客户端连接到我们的 postgresql 数据库，但保持连接打开。 是否可以告诉 Postgresql 在一定时间不活动后关闭这些连接？

TL;DR

如果您使用的是 Postgresql 版本 >=9.2
然后使用我想出的解决方案

如果你不想写任何代码
然后使用arqnid 的解决方案

如果你不想写任何代码
并且您使用的是 Postgresql 版本 >=14
然后使用劳伦兹·阿尔伯的解决方案

对于那些感兴趣的人，这是我提出的解决方案，灵感来自克雷格·林格的评论：

(...) 使用 cron 作业查看连接上次活动的时间（请参阅 pg_stat_activity）并使用 pg_terminate_backend 终止旧连接。(...)

所选择的解决方案如下：

• 首先，我们升级到 Postgresql 9.2。
• 然后，我们安排一个线程每秒运行一次。
• When the thread runs, it looks for any old inactive connections.

  • 考虑连接inactive if its state或者是idle, idle in transaction, idle in transaction (aborted) or disabled.
  • 考虑连接old if its state5分钟多时间里保持不变。

• 还有其他线程执行与上面相同的操作。但是，这些线程使用不同的用户连接到数据库。
• 我们为连接到我们数据库的任何应用程序保留至少一个打开的连接。 (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