如何自动关闭 PostgreSQL 中的空闲连接? [英] How to close idle connections in PostgreSQL automatically?

问题描述

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

TL;DR

<块引用>

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

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

如果你不想写任何代码
并且您使用的是 Postgresql 版本 >= 14
然后使用 Laurenz Albe 的解决方案

解决方案

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

<块引用>

(...) 使用 cron 作业查看连接上次处于活动状态的时间(请参阅 pg_stat_activity)并使用 pg_terminate_backend 杀死旧连接.(...)

选择的解决方案如下:

• 首先，我们升级到 Postgresql 9.2.
• 然后，我们安排一个线程每秒运行一次.
• 当线程运行时，它会查找任何旧的非活动连接.<块引用>
  • 如果一个连接的状态是idle、idle in transaction、，则该连接被视为非活动事务空闲(中止) 或 disabled.
  • 如果一个连接的状态在超过 5 分钟内保持不变，则该连接被视为旧.

• 还有其他线程可以执行与上述相同的操作.但是，这些线程以不同的用户连接到数据库.
• 对于连接到我们数据库的任何应用程序，我们至少保留一个连接打开.(rank() 函数)

这是线程运行的SQL查询:

WITH inactive_connections AS (选择pid,rank() over (partition by client_addr order by backend_start ASC) 作为排名从pg_stat_activity在哪里-- 排除线程拥有的连接(即没有自动终止)pid <>pg_backend_pid()和-- 排除已知的应用程序连接application_name !~ '(?:psql)|(?:pgAdmin.+)'和-- 包括与线程所连接的同一数据库的连接数据名 = current_database()和-- 包括使用相同线程用户名连接的连接用户名 = current_user和-- 仅包括非活动连接state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')和-- 包括旧连接(通过 state_change 字段找到)current_timestamp - state_change >间隔'5分钟')选择pg_terminate_backend(pid)从非活动连接在哪里等级 >1 -- 为每个连接到数据库的应用程序保留一个连接

Some clients connect to our postgresql database but leave the connections opened. Is it possible to tell Postgresql to close those connection after a certain amount of inactivity ?

TL;DR

IF you're using a Postgresql version >= 9.2
THEN use the solution I came up with

IF you don't want to write any code
THEN use arqnid's solution

IF you don't want to write any code
AND you're using a Postgresql version >= 14
THEN use Laurenz Albe's solution

解决方案

For those who are interested, here is the solution I came up with, inspired from Craig Ringer's comment:

(...) 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.(...)

The chosen solution comes down like this:

• First, we upgrade to Postgresql 9.2.
• Then, we schedule a thread to run every second.
• When the thread runs, it looks for any old inactive connections.

  • 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 state stayed the same during more than 5 minutes.

• There are additional threads that do the same as above. However, those threads connect to the database with different user.
• We leave at least one connection open for any application connected to our database. (rank() function)

This is the SQL query run by the thread:

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 中的空闲连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！