如何自动关闭PostgreSQL中的空闲连接? [英] How to close idle connections in PostgreSQL automatically?
本文介绍了如何自动关闭PostgreSQL中的空闲连接?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
一些客户端连接到我们的postgresql数据库,但是打开连接。
是否可以告诉Postgresql在一定时间不活动后关闭这些连接?
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
TL;DR
如果您使用的是Postgresql版本> =
9.2
使用我想出的解决方案
如果您不想写任何代码
然后使用 arqnid的解决方案
IF you don't want to write any code
THEN use arqnid's solution
推荐答案
对于那些感兴趣的人,这里是从 Craig Ringer 的意见:
For those who are interested, here is the solution I came up with from Craig Ringer's comment:
您可以使用cron作业来查看连接上次活动的时间(请参见pg_stat_activity),并使用pg_terminate_backend删除旧的连接。在一个简单的查询中轻松表达。我不知道pg_terminate_backend是否在非常古老的8.3中可用。
像这样:
- 首先,我们升级到Postgresql 9.2。
- 然后,
- 当线程运行时,它会查找任何旧的非活动连接。
- 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.
- 如果某个连接的状态 code>空闲,
空闲在事务
,空闲在事务中(中止)
或disabled
。
- 如果连接的最后状态更改时间超过5分钟,则认为该连接为旧 。
- A connection is considered inactive if its state is either
idle
,idle in transaction
,idle in transaction (aborted)
ordisabled
.- A connection is considered old if its last state has changed for more than 5 minutes.
rank()
function)
rank()
function)线程:
WITH inactive_connections_list 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_list
WHERE
rank > 1 -- Leave one connection for each application connected to the database
这篇关于如何自动关闭PostgreSQL中的空闲连接?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文