pgsql锁死问题查找

-----查看正在执行的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 ,pg_stat_activity pa  WHERE      current_query <> '<IDLE>' and  procpid<> pg_backend_pid() and pa.pid=s.procpid and pa.state<>'idle'  ORDER BY      lap DESC; 

—查询所有不是空闲状态进程

select * from pg_stat_activity where state!='idle'

– 查询阻塞、被阻塞的的sql

SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));

—查看数据表锁情况

SELECT pg_locks.pid,relation::regclass AS table_name, mode, granted,pg_stat_activity.query
FROM pg_locks
left join pg_stat_activity on pg_locks.pid=pg_stat_activity.pid
WHERE relation::regclass IS NOT NULL

—杀死进程

select pg_terminate_backend(28585);

—批量生成杀死进程命令

SELECT 'SELECT pg_terminate_backend(' || pid || ');'
FROM pg_stat_activity
WHERE 
      pid != pg_backend_pid()
      AND backend_start < NOW() - '10 seconds'::interval 
      --and pid not in (12369,12202,4882,5111);