postgresql进程查看与管理

postgresql进程查看与管理

1、查看数据库当前的进程，看一下有无正在执行的慢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;

2、杀事务

SELECT pg_cancel_backend( pid );

3、查看具体表的sql执行情况

--查询具体表的执行情况
SELECT * FROM pg_stat_activity where query ~ '表名';