PostgreSQL连接排查常用SQL

相关概念：PostgreSQL

排查 PostgreSQL 连接问题时，pg_stat_activity 基本是第一入口。

查看当前连接

SELECT * FROM pg_stat_activity;

查看锁等待

SELECT *
FROM pg_stat_activity
WHERE datname = 'database_name'
  AND wait_event_type = 'Lock';

查找空闲过久的连接

SELECT
    pid,
    application_name,
    client_addr,
    state,
    state_change,
    now() - state_change AS idle_duration
FROM pg_stat_activity
WHERE state = 'idle'
  AND now() - state_change > interval '1 hour'
  AND pid <> pg_backend_pid();

终止指定连接

SELECT pg_terminate_backend(12870);

按应用批量终止空闲连接

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE application_name = 'IntelliJ IDEA 2024.3.5'
  AND state = 'idle'
  AND pid <> pg_backend_pid();

一个实用判断

如果数据库本身没挂，但连接数持续上升、空闲连接很多、锁等待也在积累，优先怀疑：

• 应用连接池参数不合适
• 客户端没有及时释放连接
• 某些工具长期占着空闲连接