postgresql 监控 pg_stat_activity

最新推荐文章于 2024-08-23 16:53:28 发布

数据库人生

最新推荐文章于 2024-08-23 16:53:28 发布

阅读量2.4k

点赞数

分类专栏： # postgresql check monitor 文章标签： pg_cancel_backend pg_terminate_backend pg_stat_activity

本文链接：https://blog.csdn.net/ctypyb2002/article/details/83269058

版权

postgresql check monitor 专栏收录该内容

34 篇文章 4 订阅

订阅专栏

pg_stat_activity 是 postgresql 最重要的信息之一。
查看时以 postgres 用户登录数据库。

postgresql 11

postgresql 10

SELECT 'select pg_cancel_backend('|| psa.pid || ');' as cancel_spid, 
       'select pg_terminate_backend('|| psa.pid || ');' as term_spid, 
       psa.pid,
       psa.wait_event_type, 
       psa.wait_event,
       psa.state,
       psa.client_addr,
       now() - psa.xact_start as xact_time,
       now() - psa.query_start as query_time,
       psa.query --
       --psa.* 
  FROM pg_stat_activity psa
 WHERE 1=1
   --and psa.pid = 132693
   and psa.state <> 'idle'
 order by case when psa.state = 'idle' then 99
               else 1
           end,
          now() - psa.xact_start desc 
;

postgresql 9.6

SELECT 'select pg_cancel_backend('|| psa.pid || ');' as cancel_spid, 
       'select pg_terminate_backend('|| psa.pid || ');' as term_spid, 
       psa.pid,
       psa.wait_event_type, 
       psa.wait_event,
       psa.client_addr,
       psa.query,
       now() - psa.xact_start as xact_time,
       now() - psa.query_start as query_time,
       psa.* 
  FROM pg_stat_activity psa
 WHERE 1=1
   --and psa.pid = 132693
   and psa.state <> 'idle'
 order by case when psa.state = 'idle' then 99
               else 1
           end,
          now() - psa.xact_start desc 
;

postgresql 9.1

SELECT  'select pg_cancel_backend('|| psa.procpid || ');' as cancel_spid, 
      'select pg_terminate_backend('|| psa.procpid || ');' as term_spid, 
       psa.procpid,
       psa.waiting,
       psa.client_addr,
       psa.current_query,
       now() - psa.xact_start as xact_time,
       now() - psa.query_start as query_time,
       psa.* 
  FROM pg_stat_activity psa
 WHERE 1=1
   --and psa.procpid = 132693
   and psa.current_query <> '<IDLE>'
 order by case when psa.current_query = '<IDLE>' then 99
               when psa.current_query = '<insufficient privilege>' then 98
               else 1
           end,
          now() - psa.xact_start desc 
;