如何编写PostgreSQL存储过程自动查找并终止空闲查询

完整存储过程代码 #

以下存储过程直接整合了查询符合条件的空闲连接、终止连接的逻辑，同时排除当前执行会话避免误杀：

CREATE OR REPLACE PROCEDURE terminate_idle_connections()
LANGUAGE plpgsql
AS $$
BEGIN
    PERFORM pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE datname = 'dbdataanalytics'
      AND state = 'idle'
      -- 筛选空闲时间超过1天的连接
      AND state_change <= CURRENT_DATE - INTERVAL '1 day'
      -- 排除当前执行存储过程的会话，避免自误杀
      AND pid <> pg_backend_pid();
END;
$$;

功能验证方法 #

• 手动执行存储过程测试效果：CALL terminate_idle_connections();
• 执行以下查询验证剩余空闲连接：

SELECT pid, usename, state, state_change 
FROM pg_stat_activity 
WHERE datname = 'dbdataanalytics' AND state = 'idle';

配置每日自动执行方案 #

PostgreSQL 无内置定时任务能力，两种常用实现方式：

方案1：Linux 系统 Crontab 定时任务 #

1. 编写执行脚本，保存为 /opt/scripts/clean_idle_conn.sh：

#!/bin/bash
# 替换为实际的数据库用户名，建议配置免密登录避免明文密码
psql -U 你的数据库用户名 -d dbdataanalytics -c "CALL terminate_idle_connections();"

2. 给脚本赋予执行权限：chmod +x /opt/scripts/clean_idle_conn.sh
3. 编辑crontab配置，添加每日凌晨2点执行规则：

0 2 * * * /opt/scripts/clean_idle_conn.sh >> /var/log/clean_idle_conn.log 2>&1

方案2：使用pg_cron扩展（数据库层定时） #

如果已经安装pg_cron扩展，直接在数据库内配置定时即可：

-- 首次使用需要加载扩展
CREATE EXTENSION IF NOT EXISTS pg_cron;
-- 配置每日凌晨2点执行存储过程，任务名为daily-clean-idle-conn
SELECT cron.schedule('daily-clean-idle-conn', '0 2 * * *', 'CALL terminate_idle_connections();');
-- 验证定时任务是否添加成功
SELECT * FROM cron.job;

注意：执行存储过程的用户需要持有pg_signal_backend权限或超级用户权限，否则无法终止其他用户创建的连接。

内容的提问来源于stack exchange，提问作者Ratkal-Guru