-----查看正在执行的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);