pg_stat_activity 是postgrsql 实例维护的一个进程相关的视图,是实时变化的。所以,如果从 pg_stat_activity 获取慢sql之类的语句,就需要定时查询,比如配置成zabbix的定时探测。
select 'select pg_cancel_backend('|| a0.pid || ');' as cancel_pid,
'select pg_terminate_backend('|| a0.pid || ');' as terminate_pid,
xact_start,round(extract(epoch FROM (now()-xact_start))::numeric,1) as xact_second,
query_start,round(extract(epoch FROM (now()-query_start))::numeric,1) as query_second,
datname, pid, usename, application_name, client_addr, client_port,
xact_start, query_start,
state_change,wait_event_type,wait_event, state, backend_xid, backend_xmin, query
from pg_stat_activity a0
where 1=1
and a0.state<>'idle'
and (a0.backend_xid is not null or a0.backend_xmin is not null)
order by now()-xact_start;
需要重点说明一下:
now() - xact_start 是指事务截至当前已运行时间。
now() - query_start 是指query截至当前已运行时间。
参考:
http://postgres.cn/docs/9.6/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW