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

How to close idle connections in PostgreSQL automatically?

提问by Stephan

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 ?

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

TL;DR

TL; 博士

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

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

如果您不想编写任何代码，则
使用arqnid 的解决方案

回答by Stephan

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

对于那些感兴趣的人，这是我想出的解决方案，灵感来自Craig Ringer的评论：

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

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

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 inactiveif its stateis either idle, idle in transaction, idle in transaction (aborted)or disabled.
  • A connection is considered oldif its statestayed 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)

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

  • 如果连接的状态为、或，则该连接被视为非活动状态。idleidle in transactionidle in transaction (aborted)disabled
  • 如果连接的状态在 5 分钟内保持不变，则该连接被视为旧连接。

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

This is the SQL query run by the thread:

这是线程运行的 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

回答by fresko

If you are using PostgreSQL >= 9.6 there is an even easier solution. Let's suppose you want to delete all idle connections every 5 minutes, just run the following:

如果您使用 PostgreSQL >= 9.6，则有一个更简单的解决方案。假设您想每 5 分钟删除一次所有空闲连接，只需运行以下命令：

alter system set idle_in_transaction_session_timeout='5min';

In case you don't have access as superuser (example on Azure cloud), try:

如果您没有超级用户访问权限（例如 Azure 云），请尝试：

SET SESSION idle_in_transaction_session_timeout = '5min';

But this latter will work only for the current session, that most likely is not what you want.

但是后者仅适用于当前会话，这很可能不是您想要的。

To disablethe feature,

要禁用该功能，

alter system set idle_in_transaction_session_timeout=0;

or

或者

SET SESSION idle_in_transaction_session_timeout = 0;

(by the way, 0 is the default value).

（顺便说一下，0 是默认值）。

If you use alter system, you must reload configuration to start the change and the change is persistent, you won't have to re-run the query anymore if, for example, you will restart the server.

如果使用alter system，则必须重新加载配置以启动更改并且更改是持久的，例如，如果您将重新启动服务器，则不必再重新运行查询。

To check the feature status:

要检查功能状态：

show idle_in_transaction_session_timeout;

回答by araqnid

Connect through a proxy like PgBouncerwhich will close connections after server_idle_timeoutseconds.

通过像PgBouncer这样的代理连接，它会在server_idle_timeout几秒钟后关闭连接。