postgresql 依据 pg_stat_activity 诊断进程

postgresql check monitor 专栏收录该内容
29 篇文章 0 订阅

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

postgresql 11

postgresql 10

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 
;

  • 0
    点赞
  • 0
    评论
  • 1
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
©️2020 CSDN 皮肤主题: 酷酷鲨 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值