PostgreSQL之pg_stat_activity

what's the pg_stat_activity

　　pg_stat_activity 是 PostgreSQL 内置的一个系统视图， 是 PostgreSQL  实例维护的一个进程相关的视图，是实时变化的。

　　官方对他的定义是：每一行都表示一个系统进程，显示与当前会话的活动进程的一些信息，比如当前回话的状态和查询等。

 

主要字段

Column	Type	Description
datid	oid	后台连接的数据库id（OID）
datname	name	后台连接的数据库名
pid	integer	后代进程id（PID）
usesysid	oid	连接到后台的用户id
usename	name	连接到后台的用户名称
application_name	text	通过后台连接的应用程序名称
client_addr	inet	后台连接的IP地址。当该字段为空时，说明客户端是在服务器主机上通过Unix的socket协议直连，或是数据库内部进程（如autovacuum等）
client_hostname	text	根据client_addr字段，通过DNS解析获取的对应主机名称. 但仅当log_hostname被开启时才有效，并且只有通过IP协议连接时才能记录到主机名；
client_port	integer	后台连接使用的TCP端口号。当使用Unix-Socket协议连接时此值为-1。
backend_start	timestamp with time zone	进程开始的时间，即客户端连接到服务器的时刻。
xact_start	timestamp with time zone	进程的当前事务开始执行的时间。若当前无活动事务，该值为null。 如果当前query是事务中的首次查询，此项值应与query_start列相同.
query_start	timestamp with time zone	当前事务中有在执行中的语句时，此值记录该语句开始执行的时间； 若事务state不是active状态，此值记录的是上一次开始执行语句时间。（即每次开始执行新的语句时此值更新）
state_change	timestamp with time zone	记录进程中事务状态发生改变的时刻；
waiting	boolean	是否处于等待。当进程在等待解锁时此值为t，否则为false
state	text	表示后台进程的整体状态。可能的值有: active（活动）:进程正在执行某个语句 idle(空闲): 进程正在等待客户端的指令. idle in transaction （事务空闲）: 进程在处理事务过程中，但当前未执行任何语句. idle in transaction (aborted)（事务空闲-退出）:当事务中某个语句执行出错的情况外，其余情况与idle in transaction相同. fastpath function call（快通道函数调用）: 后台正在执行某个快通道函数. disabled（禁用）: This state is reported if track_activities is disabled in this backend.
query	text	后台进程最近（当前）执行的语句内容. 当状态是 active时，它记录的是正在执行的语句内容；否则记录的是上一条语句执行的内容

 

主要作用

　　平时遇到数据库，查询慢，或者数据库故障问题，可以看看数据库相关的一些状态视图，快速定位问题

-- 查询闲置连接数。注意是否忘记关闭链接。 另外还可以查看连接数是不是过多等问题。
select count(*) from pg_stat_activity where state=‘idle’; 

-- 统计当前有多少活跃的客户端
select 
  count(*) 
from 
  pg_stat_activity 
where 
  not pid = pg_backend_pid();

-- 查看一个后端进程运行了多久，以及它当前是否在等待
select 
  pid,state,CURRENT_TIMESTAMP - least(query_start,xact_start) AS runtime,substr(query,1,25) AS current_query
from 
  pg_stat_activity
where 
  not pid = pg_backend_pid();

-- 慢查询
select
  pid, datname, usename, client_addr, application_name, state, backend_start, xact_start, xact_stay, query_start, query_stay, replace(query, chr(10), ' ') as query
from
    (select 
      pgsa.pid as pid, 
      pgsa.datname as datname, 
      pgsa.usename as usename, 
      pgsa.client_addr client_addr, 
      pgsa.application_name as application_name, 
      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_stay, 
      pgsa.query_start as query_start, 
      extract(epoch from (now() - pgsa.query_start)) as query_stay , 
      pgsa.query 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_stay 
desc 
limit 5;