pg卡死处理

[postgres@apm ~]$ ps -ef|grep ‘postgres:’|grep -v grep|awk ‘{print $2}’|xargs kill -9

锁：

 1 查找锁表的pid

select pid from pg_locks l join pg_class t on l.relation = t.oid where t.relkind = ‘r’ and t.relname = ’lockedtable’;

2 查找锁表的语句

select pid, state, usename, query, query_start from pg_stat_activity where pid in ( select pid from pg_locks l join pg_class t on l.relation = t.oid and t.relkind = ‘r’ where t.relname =  ’lockedtable’);

3 查找所有活动的被锁的表

select pid, state, usename, query, query_start 
from pg_stat_activity 
where pid in (
  select pid from pg_locks l 
  join pg_class t on l.relation = t.oid 
  and t.relkind = 'r' 
  
);

4 解锁

SELECT pg_cancel_backend(pid);

 5 批量(未排除自己)

select pg_cancel_backend(pid)
from pg_stat_activity 
where pid in (
  select pid from pg_locks l 
  join pg_class t on l.relation = t.oid 
  and t.relkind = 'r' 
  
);

参考:

1 

2