pg_stat_activity 视图

在 Postgres 中，pg_stat_activity 是一个视图，每行对应一个服务器进程，用来显示进程当前活动有关的信息。因此，我们可以使用如下的查询来获取长时间执行的查询或者操作。

SELECT
	datname,
	usename,
	client_addr,
	state,
	backend_start,
	xact_start,
	xact_duration,
	query_start,
	query_duration,
	query
FROM (
	SELECT
		pgsa.datname AS datname,
		pgsa.usename AS usename,
		pgsa.client_addr AS client_addr,
		pgsa.state AS state,
		pgsa.backend_start AS backend_start,
		pgsa.xact_start AS xact_start,
		EXTRACT(EPOCH FROM (now() - pgsa.xact_start)) AS xact_duration,
		pgsa.query_start AS query_start,
		EXTRACT(EPOCH FROM (now() - pgsa.query_start)) AS query_duration,
		REGEXP_REPLACE(REPLACE(pgsa.query, CHR(10), ' '), '^SELECT.*FROM', 'FROM', 'i') AS query
	FROM pg_stat_activity AS pgsa
	WHERE
		pgsa.state != 'idle'
		AND pgsa.state != 'idle in transaction'
		AND pgsa.state != 'idle in transaction (aborted)'
) idleconnections ORDER BY query_duration DESC LIMIT 5;

这个查询用到的字段在 9.2 版本就都支持了，9.6 版本还增加了其他字段，有需要可以自己对比一下文档：

用到的几个字段的含义如下：

  • datname: 数据库名称。
  • username: 用户名。
  • client_addr: 客户端地址。
  • state: 连接的当前状态，一共有6种，这里需要显示的是其中3种非空闲的。
  • backend_start: 连接开始时间。
  • xact_start: 当前事务开始时间。
  • EXTRACT(EPOCH FROM (now() - pgsa.xact_start)) AS xact_duration: 计算当前事务进行了多久，单位是秒。
  • query_start: 当前查询开始的时间，一个事务内可能有多个查询。
  • EXTRACT(EPOCH FROM (now() - pgsa.query_start)) AS query_duration: 计算当前查询进行了多久，单位是秒。
  • REGEXP_REPLACE(REPLACE(pgsa.query, CHR(10), ' '), '^SELECT.*FROM', 'FROM', 'i') AS query: 查询语句的内容，这里把换行符替换成空格，而且会尝试把 SELECT … FROM 替换为 FROM（方便查看查询的是哪个表，不过如果语句过长，原始查询语句只会保留前 1024 个字符，这个是由 track_activity_query_size 参数决定的）。

知识共享许可协议本作品采用知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议进行许可。