对于postgresql数据库管理,我本身就是个小白,列出我自己常用的几个小命令。
pg_terminate_backend(pid)
from pg_stat_activity
where (now() - backend_start) > '00:10:00' and pid != (select pg_backend_pid()); --强杀连接超过10分钟的事务 不要杀死自己的进程
select
pg_cancel_backend(pid)
from pg_stat_activity
where (now() - backend_start) > '00:10:00' and pid != (select pg_backend_pid()); --杀死连接超过10分钟的事务 不要杀死自己的进程
select
locktype,relation::regclass as rel,
page||','||tuple as ctid,
virtualxid as vxid,
transactionid as xid,
virtualtransaction as vxid2,
pid,
mode,
granted
from pg_locks
where granted is false; --查询死锁
select * from pg_stat_activity where pid = 9378; --查询该事务信息
select
locktype
select pg_terminate_backend(pid); --杀死进程 ,pid为进程id
pg_terminate_backend(pid)
from pg_stat_activity
where (now() - backend_start) > '00:10:00' and pid != (select pg_backend_pid()); --强杀连接超过10分钟的事务 不要杀死自己的进程
select
pg_cancel_backend(pid)
from pg_stat_activity
where (now() - backend_start) > '00:10:00' and pid != (select pg_backend_pid()); --杀死连接超过10分钟的事务 不要杀死自己的进程
select
locktype,relation::regclass as rel,
page||','||tuple as ctid,
virtualxid as vxid,
transactionid as xid,
virtualtransaction as vxid2,
pid,
mode,
granted
from pg_locks
where granted is false; --查询死锁
select * from pg_stat_activity where pid = 9378; --查询该事务信息
select
locktype