如何识别监听Postgres通知却未消费导致队列增长的服务

1. 查询所有活跃的监听会话 #

Postgres 提供系统视图pg_listening_channels记录所有当前已注册LISTEN的会话，你可以关联pg_stat_activity视图拿到监听进程的完整客户端信息，执行以下查询即可获取所有监听服务的明细：

SELECT
  psa.pid AS session_pid,
  psa.usename AS db_username,
  psa.application_name AS client_app_name,
  psa.client_addr AS client_ip,
  psa.client_port AS client_port,
  psa.state AS session_state,
  array_agg(plc.channel) AS listened_channels
FROM pg_listening_channels plc
JOIN pg_stat_activity psa ON plc.pid = psa.pid
GROUP BY psa.pid, psa.usename, psa.application_name, psa.client_addr, psa.client_port, psa.state;

查询结果中会列出所有当前正在监听通知通道的会话，排除你已知的2个服务对应的会话，剩下的就是异常的未知监听服务。 #

2. 验证异常会话并临时止损 #

1. 对比查询结果和已知服务的进程IP、端口，标记出未知的会话PID
2. 确认异常会话的状态：如果会话状态为idle/idle in transaction且长时间无活跃查询，基本可以确定是只注册监听不消费的异常会话
3. 执行SELECT pg_terminate_backend(异常会话PID);杀掉异常会话，完成后执行select pg_notification_queue_usage();查看队列使用率，如果降到0即可确定是这些异常会话导致的队列堆积

3. 根因排查方向 #

确认异常会话归属后，可从以下方向排查根本原因：

• 排查异常IP对应的服务，是否为未下线的旧版本服务、测试服务，这类服务通常保留了LISTEN逻辑但移除了消费代码
• 检查对应服务的代码逻辑，是否存在仅执行LISTEN注册、未做消息消费的异常分支，比如消费逻辑被异常捕获跳过、初始化逻辑仅注册监听未启动消费协程/线程
• 排查是否有运维脚本、定时任务等边缘程序，执行了LISTEN操作后未主动关闭会话也没有消费逻辑

内容的提问来源于stack exchange，提问作者kabanek