Marico's space

线上跑 PostgreSQL 时间长了，这个问题迟早会碰到：

• 查询开始卡住不动
• 连接数蹭蹭往上涨
• 数据库响应越来越慢
• 连接数快要触及 max_connections 上限了

一个常见的原因就是某个会话把其他会话给堵住了。

典型场景就是连接处于 idle in transaction 状态——应用开了事务但既没 COMMIT 也没 ROLLBACK。

这篇文章聊聊怎么用 pg_stat_activity 和几个 PostgreSQL 内置函数来排查这类问题。

也会覆盖这几个工具的适用场景：

• pg_cancel_backend()
• pg_terminate_backend()
• pg_blocking_pids()

目标不只是把坏会话 kill 掉，而是搞清楚为什么会发生这种事，从根源上避免再踩坑。

PostgreSQL 会话为什么会互相阻塞？

PostgreSQL 用的是 MVCC（多版本并发控制）加上锁机制来支持多用户并发操作。

举个例子：

BEGIN; UPDATE orders
SET status = 'PROCESSING'
WHERE id = 100;

这个事务会一直持有锁，直到执行：

COMMIT;

或者：

ROLLBACK;

问题就出在这儿——应用开了事务但一直没结束。

这时候连接状态会显示为：

idle in transaction

会话已经不跑查询了，但事务还开着。

这会引发一堆问题：

• 锁还可能被握着不放
• 其他查询只能干等着
• 旧事务的快照一直不释放
• VACUUM 清理不掉某些死元组
• 表体积会越来越大

所以搞清楚 active 和 idle in transaction 的区别很重要：

active

和：

idle in transaction

active 表示正在执行查询。

idle in transaction 表示在等客户端发指令，但事务还开着。

第一步：查看所有非空闲会话

我排查问题时通常先跑这个查询：

SELECT pid, usename, application_name, state, query_start, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

这样就把正常的空闲连接过滤掉了。

剩下的就是真正在干活的：

• 正在跑查询的
• 在等待的
• 卡在事务里的

application_name 字段在服务多的时候特别有用。

比如你在连接字符串里可以配置：

application_name=order-service

然后查出来可能是这样：

pid application_name state
12345 order-service active
12346 payment-service idle in transaction

这比对着 PID 瞎猜是哪个应用爽多了。

用 Go、Node.js 或者其他微服务框架的话，强烈建议给每个服务都配上 application_name。

第二步：快速筛查

故障时不一定需要所有字段，就想快速看看什么情况。

用这个：

SELECT pid, age(clock_timestamp(), query_start), state, query
FROM pg_stat_activity
WHERE state <> 'idle';

输出就是：

• PID
• 查询运行了多久
• 会话状态
• 查询语句

我平时就用这个先快速扫一眼。

比如看到：

PID 1596183
State: active
Duration: 00:08:32
Query: SELECT ...

这个查询已经跑了超过 8 分钟。

这时候要问自己：

这个查询是真的慢，还是在等别的会话？

这是两个完全不同的处理方向。

第三步：专门找 idle in transaction 的会话

只查卡在事务里的会话：

SELECT pid, usename, application_name, state, xact_start, state_change, now() - xact_start AS transaction_age, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

重点关注 xact_start：

xact_start

这个告诉你事务是什么时候开的。

开了几秒钟的事务可能是正常的。

开了 30 分钟甚至几小时的，那就很有问题了。

不要一看到 idle in transaction 就去 kill。

先确认几件事：

• 是哪个应用创建的？
• 事务开了多久了？
• 对这个应用来说正常吗？
• 有没有在阻塞别的会话？

第四步：终止卡住的事务

确认某个会话确实卡住了、不应该继续开着，就可以终止它了。

指定 PID：

SELECT pg_terminate_backend(1596183);

这会关闭整个 PostgreSQL 会话，回滚未提交的事务，释放持有的锁。

对于老旧的 idle 事务，也可以用带条件的查询：

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction' AND state_change < now() - interval '5 minutes' AND pid <> pg_backend_pid();

这个例子只会 kill 那些卡在事务里超过 5 分钟的会话。

加这个条件：

pid <> pg_backend_pid()

是为了防止把自己正在用的会话给关掉。

线上执行这个要谨慎。

最好加上更精确的过滤条件，比如按：

datname

或者：

usename

或者：

application_name

比如：

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction' AND application_name = 'order-service' AND state_change < now() - interval '5 minutes' AND pid <> pg_backend_pid();

这比把整个集群的 idle 事务都清掉安全多了。

第五步：取消正在运行的查询

有时候连接本身没问题，但某条 SQL 执行太久了。

这种情况试试：

SELECT pg_cancel_backend(1596183);

pg_cancel_backend() 会停掉当前查询，但保持数据库连接不断。

这个适合：

• 特别重的 SELECT
• 报表查询
• 误触发的全表操作
• 用户主动取消的查询

可以这么理解：

pg_cancel_backend() | v
停掉当前查询 | v
保持连接

pg_cancel_backend() vs pg_terminate_backend()

这两个函数看起来差不多，但解决的问题不同。

函数	效果	适用场景
pg_cancel_backend(pid)	停掉当前查询，连接保持	长时间运行或误触的查询
pg_terminate_backend(pid)	关闭整个数据库会话	卡死会话、idle 事务、严重阻塞

我的习惯是：

有问题的活跃查询 | v
pg_cancel_backend() | | 还在搞事 v
pg_terminate_backend()

对于 idle in transaction 的会话，pg_cancel_backend() 通常没用——根本没有活跃查询可以取消。

这时候得用：

pg_terminate_backend()

第六步：找出谁在阻塞查询

一个查询跑了很久，不一定是它自己慢。

也可能在等另一个事务。

PostgreSQL 提供了很实用的函数：

pg_blocking_pids()

比如：

SELECT pid, usename, application_name, state, pg_blocking_pids(pid) AS blocking_pids, query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

可能看到：

pid blocking_pids
-----------------------
20002 {20001}

意思是：

PID 20001 | | 阻塞了 v
PID 20002

不要急着 kill PID 20002。

它是被阻塞的会话，不一定是问题根源。

应该去查 PID 20001 在干什么。

找到源头阻塞者

线上问题可能更复杂：

会话 A | | 阻塞了 v
会话 B | | 阻塞了 v
会话 C

kill 掉会话 B 会让 C 好一点，但问题根源没解决。

真正的阻塞者是：

会话 A

这就是 pg_blocking_pids() 的价值。

要顺着阻塞链往上追，找到那个没在等其他后端的会话。

那个才是源头阻塞者。

先查那个。

第七步：看哪个数据库连接用得最多

如果服务器上有多个数据库，看看连接是怎么分布的：

SELECT datname, count(*)
FROM pg_stat_activity
GROUP BY datname
ORDER BY count(*) DESC;

结果可能是：

datname count
------------- -----
order_prod 82
notification 15
postgres 3

现在知道大部分连接被哪个库吃掉了。

如果某个库连接数异常高，就要排查：

• 应用连接池大小
• PgBouncer 配置
• 连接泄漏
• 长时间运行的查询
• idle 事务

连接数高不一定是 PostgreSQL 本身的问题。

可能是应用那侧开的连接太多了。

第八步：检查连接余量

还要看看离 PostgreSQL 配置的连接上限还有多远：

SELECT (SELECT count(*) FROM pg_stat_activity) AS used_connections, ( SELECT setting::int FROM pg_settings WHERE name = 'max_connections' ) AS max_connections;

比如：

used_connections | max_connections
-----------------+----------------
87 | 100

说明已经用到：

87 / 100

没什么余量了。

这就很危险——出故障的时候，可能连新连接都建不了，没法登进去排查问题。

生产环境我都会提前监控连接使用率，不让它逼近上限。

预防 idle 事务

手动 kill 会话在故障时有用。

但这不是根本解决办法。

PostgreSQL 可以自动关闭在事务里 idle 太久的连接。

比如：

ALTER DATABASE mydb
SET idle_in_transaction_session_timeout = '2min';

配置之后，PostgreSQL 会自动关掉在事务里 idle 超过这个时间的会话。

超时设多少要根据业务场景来。

比如：

30 秒

适合快节奏的 OLTP API。

而：

5 分钟

可能更适合业务操作耗时较长的场景。

别不看自己系统情况就照抄别人的超时值。

防止长时间查询

还可以用：

statement_timeout

比如：

ALTER DATABASE mydb
SET statement_timeout = '30s';

这能防止单条 SQL 跑太久。

但要注意。

对 API 请求合理的超时，可能不适合：

• 报表查询
• 数据迁移
• ETL 任务
• 批处理

不同应用可能需要不同的超时配置。

记录锁等待

还有一个有用的配置：

log_lock_waits

开启方式：

ALTER SYSTEM SET log_lock_waits = on;

这样 PostgreSQL 会在会话等待锁太久时记录日志。

通常配合：

deadlock_timeout

一起用：

ALTER SYSTEM SET deadlock_timeout = '1s';

这样锁问题排查起来就方便多了——PostgreSQL 会在会话等待锁时记录有用信息。

记得改完要 reload 配置让它生效。

从应用层解决问题

数据库命令在故障时能救急。

但如果 idle in transaction 反复出现，问题通常出在应用代码里。

比如 Go 里常见的写法：

tx, err := db.Begin(ctx)
if err != nil { return err
} defer tx.Rollback(ctx)

然后后面：

if err := tx.Commit(ctx); err != nil { return err
}

核心原则很简单：

BEGIN | +---- 成功 ----> COMMIT | +---- 失败 ----> ROLLBACK

每个事务都必须有明确的结束。

用这些库的时候尤其要注意：

• pgx
• GORM
• database/sql
• Node.js pg

每条错误路径都要正确地 commit 或 rollback。

连接池配置也很关键

顺便检查一下连接池配置。

比如你有：

10 个服务

每个服务允许：

20 个连接

那应用层可能试图创建：

10 × 20 = 200 个连接

但 PostgreSQL 可能只允许：

max_connections = 100

这种架构迟早要出问题。

PgBouncer 这类工具能减少 PostgreSQL 后端连接数，但它替代不了正确的事务处理。

还是要做好：

• 及时 commit 事务
• 正确 rollback 事务
• 设置合理的连接池上限
• 监控连接使用情况

我的生产故障排查流程

PostgreSQL 开始变慢时，我通常按这个思路来：

PostgreSQL 变慢 | v
检查 pg_stat_activity | v
查询是 active 还是 idle in transaction？ | +------ idle in transaction
 | |
 | v | 看事务开了多久
 | |
 | v | 看有没有在阻塞
 | |
 | v | pg_terminate_backend() | +------ active | v 检查 blocking_pids | +-------+-------+
 | |
 被阻塞了 没被阻塞
 | |
 v v 找阻塞者 检查查询本身 | v pg_cancel_backend()

关键是：

不要因为一个查询跑得久就 kill 它的 PID。

先搞清楚那个 PID 在干什么。

先问几个问题：

它在运行吗？ 它在等待吗？ 它在阻塞别人吗？ 它被阻塞了吗？ 它在事务里 idle 吗？

然后再决定怎么处理。

常用命令汇总

查看非空闲会话：

SELECT pid, usename, application_name, state, query_start, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

查找 idle 事务：

SELECT pid, application_name, xact_start, now() - xact_start AS transaction_age, query
FROM pg_stat_activity
WHERE state = 'idle in transaction';

查找被阻塞的会话：

SELECT pid, pg_blocking_pids(pid) AS blocking_pids, query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

取消查询：

SELECT pg_cancel_backend(1596183);

终止会话：

SELECT pg_terminate_backend(1596183);

按数据库统计连接：

SELECT datname, count(*)
FROM pg_stat_activity
GROUP BY datname
ORDER BY count(