监控正在执行的SQL

postgres监控正在执行的sql
-----------------------------
SELECT 
    procpid, 
    start, 
    now() - start AS lap, 
    current_query 
FROM 
    (SELECT 
        backendid, 
        pg_stat_get_backend_pid(S.backendid) AS procpid, 
        pg_stat_get_backend_activity_start(S.backendid) AS start, 
       pg_stat_get_backend_activity(S.backendid) AS current_query 
    FROM 
        (SELECT pg_stat_get_backend_idset() AS backendid) AS S 
    ) AS S 
WHERE 
   current_query <> '<IDLE>' 
ORDER BY 
   lap DESC;

 SELECT datname,pid,query_start,now() - query_start AS lap,waiting,state,query from pg_stat_activity where datname ='tscdb' and state='active' order by lap desc;

procpid：进程id
start：进程开始时间
lap：经过时间
current_query：执行中的sql
-------------------------------------------
怎样停止正在执行的sql
SELECT pg_cancel_backend(进程id);
或者用系统函数
kill -9 进程id;
-------------------------------------------
更改数据库编码：
show client_encoding;
set client_encoding='utf-8';
-------------------------------------------
select * from (SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_activity(s.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s) as querystring;

查询最慢的10条SQL：
SELECT  query, calls, total_time, (total_time/calls) as average ,rows, 
        100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent 
FROM    pg_stat_statements 
ORDER   BY average DESC LIMIT 10;

清除统计结果：select pg_stat_statements_reset() ;
-查看当前活动的客户端连接数  
 SELECT count(*) FROM pg_stat_activity WHERE NOT pid=pg_backend_pid();       

 --查询客户端连接的情况  
 SELECT pid,case when waiting='f' then 'already get lock,sql executing' when waiting='t' then 'waiting get lock,sql waiting execute' end lock_satus,  
 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() and state<>'idle' and application_name<>'pg_statsinfod' order by runtime desc  

--查看持有锁和等待锁的一些信息：  
--reltype=0代表其为索引  
SELECT locker.pid,  
        pc.relname,  
        locker.mode,  
        locker_act.application_name,  
        least(query_start,xact_start) start_time,  
        locker_act.state,  
        CASE  
    WHEN granted='f' THEN  
    'wait_lock'  
    WHEN granted='t' THEN  
    'get_lock'  
    END lock_satus,current_timestamp - least(query_start,xact_start) AS runtime,  
    locker_act.query  
FROM pg_locks locker,pg_stat_activity locker_act, pg_class pc  
WHERE locker.pid=locker_act.pid  
        AND NOT locker.pid=pg_backend_pid()  
        AND application_name<>'pg_statsinfod'  
        AND locker.relation = pc.oid  
        AND pc.reltype<>0 --and pc.relname='t'  
ORDER BY  runtime desc;  

-查询系统中正在执行的或者等待执行的事务  
--注意其只是代表事务信息，系统中也有可能存在慢的查询  
select pc.relname lock_table,pc.oid,tans.pid, CASE  
    WHEN waiting='f' THEN  
    'already get lock,sql executing'  
    WHEN waiting='t' THEN  
    'waiting get lock,sql waiting execute'  
    END lock_satus,  
    least(query_start,xact_start) query_start,  
    current_timestamp - least(query_start,xact_start) AS runtime,  
    psa.query  
from pg_locks tans,pg_locks pl,pg_class pc ,pg_stat_activity psa  
where tans.transactionid is NOT null and pc.oid=pl.relation and tans.pid=pl.pid  
and tans.pid=psa.pid and pc.reltype<>0  
order by runtime desc;  

--查找是否有waiting  
ps -ef|grep postgres | grep wait 

查询表中存在的锁
select a.locktype,a.database,a.pid,a.mode,a.relation,b.relname from pg_locks a join pg_class b on a.relation = b.oid where upper(b.relname) = 'TABLE_NAME';
再根据上面查出来的pid去表pg_stat_activity查询一下该锁对应的SQL语句：
select usename,current_query ,query_start,procpid,client_addr from pg_stat_activity where procpid=17509;
杀掉方法：在PG数据库mydb服务器中，查询这个进程PID然后Kill掉。
SELECT pg_cancel_backend(进程id);
ps -ef|grep 17509
或在操作系统下：kill -9 17509