postgres 生产问题定位sql

查询当前进程连接

select pid,query_start,query,client_addr from pg_stat_activity where pid in (
	select a.pid from pg_locks a 
        join pg_class b on a.relation=b.oid

)
-- and state ='active'
-- and datname like 'database%'
-- and query like '%project_subsys%'
order by query_start

postgresql死锁查询

 --查询是否锁表了
select oid from pg_class where relname='可能锁表了的表'
--pid 是进程
select pid from pg_locks where relation='上面查出的oid'
--如果查询到了结果，表示该表被锁 则需要释放锁定
select pg_cancel_backend(上面查到的pid)

批量杀死查询进程

select pg_terminate_backend(t.pid) from (