如何实时查看PostgreSQL中正在溢写磁盘的运行中查询？

直接利用PostgreSQL内置的pg_stat_activity视图就能实时捕获正在运行且已创建临时文件的查询——这个视图里的temp_files和temp_bytes字段会实时更新当前会话的临时文件统计数据，结合state = 'active'筛选条件，就能精准定位正在运行的溢写查询。

查询语句 #

SELECT
  pid,
  datname AS 数据库名,
  usename AS 用户名,
  application_name AS 应用名,
  state AS 会话状态,
  temp_files AS 临时文件数量,
  temp_bytes AS 临时文件总大小,
  query_start AS 查询开始时间,
  now() - query_start AS 查询运行时长,
  query AS 执行语句
FROM pg_stat_activity
WHERE
  state = 'active'
  AND temp_files > 0
ORDER BY temp_bytes DESC;

关键字段说明 #

• pid：查询对应的进程ID，是执行终止操作的核心标识
• temp_files：当前会话已创建的临时文件数，大于0意味着已触发work_mem溢写磁盘
• temp_bytes：临时文件总字节数，按此字段降序排序可快速定位最消耗磁盘资源的查询
• 查询运行时长：辅助判断是否需要立即终止长时间运行的溢写查询

终止查询的操作 #

找到目标查询的pid后，执行以下语句即可终止：

SELECT pg_terminate_backend(替换为目标pid);

注意事项 #

• 执行该查询需要用户拥有pg_monitor角色权限，或直接使用超级用户账号
• temp_files和temp_bytes是会话级累计值，只要当前活跃会话曾创建过临时文件就会被筛选出来，完全满足实时监控正在运行的溢写查询需求
• 若需实现自动监控+终止，可编写Shell脚本定时执行上述查询，对符合阈值（如运行时长超10分钟、临时文件超1GB）的进程自动执行终止命令

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