如何通过pg_stat_activity定位PostgreSQL未正常关闭连接的查询？

一、从pg_stat_activity抓取关键连接信息 #

直接执行以下SQL，过滤出非闲置/闲置但残留查询的连接，这类是未正常释放的重点排查对象：

SELECT pid, client_addr, application_name, state, query, backend_start, query_start
FROM pg_stat_activity
WHERE state NOT IN ('idle', 'idle in transaction') 
   OR (state = 'idle' AND query IS NOT NULL);

• pid：PostgreSQL进程ID，可用于终止异常连接
• client_addr：发起连接的客户端IP，定位来源机器/应用
• application_name：连接的应用名称，区分pgAdmin或业务应用
• state：连接状态，idle in transaction是典型的“占着连接不释放”状态（事务未提交/回滚）
• query：该连接最后执行的SQL语句，直接锁定未释放的具体查询

如果要专门排查闲置超10分钟的事务连接（这类是连接泄漏的高发区）：

SELECT pid, client_addr, application_name, query, query_start
FROM pg_stat_activity
WHERE state = 'idle in transaction' 
  AND now() - query_start > interval '10 minutes';

二、用pg_stat_statements定位高频/长耗时查询 #

利用已安装的扩展，执行以下SQL找出最耗资源、调用最频繁的语句，这类语句大概率是连接未释放的源头：

SELECT queryid, query, calls, total_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 20;

• total_time：语句累计执行总时间，排序靠前的是系统性能重灾区
• calls：语句调用次数，次数过多且未释放连接会快速耗尽连接数

三、快速定位连接占比最高的来源 #

执行以下SQL按客户端IP和应用分组统计连接数，一眼锁定连接过载的源头：

SELECT client_addr, application_name, COUNT(*) AS connection_count
FROM pg_stat_activity
GROUP BY client_addr, application_name
ORDER BY connection_count DESC;

四、应急缓解卡顿 #

如果系统已经严重卡顿，先临时调大连接数（需重启PostgreSQL生效）：

ALTER SYSTEM SET max_connections = 200; -- 将200替换为合适数值，比如原设置是100

或者直接清理闲置超久的异常连接：

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction' 
  AND now() - query_start > interval '10 minutes';

五、实时验证连接数情况 #

查看当前总连接数：

SELECT COUNT(*) FROM pg_stat_activity;

查看当前设置的最大连接数：

SHOW max_connections;

内容的提问来源于stack exchange，提问作者Ahmad Raimi Jasmi