postgresql lock tree 출력 SQL 및 kill session 방법

postgresql lock tree 출력 SQL 및 kill session 방법

안녕하세요.
Postgresql 에서 lock tree를 조회 하고 원하는 session을 kill 하는 방법을 간략하게 소개 해 드립니다.
Postgresql 쓰시면서 lock 이 걸려서 계속 대기 해야 하는 때가 생깁니다.
이럴때 빠르게 lock tree 검색해서 필요없는 작업이라면 빨리 kill 해줘야 겠죠.

Postgresql lock tree 출력 SQL

with recursive
    activity as (select pg_blocking_pids(pid)                                blocked_by,
                        *,
                        age(clock_timestamp(), xact_start)::interval(0)   as tx_age,
                        age(clock_timestamp(), state_change)::interval(0) as state_age
                 from pg_stat_activity
                 where state is distinct from 'idle'),
    blockers as (select array_agg(distinct c order by c) as pids
                 from (select unnest(blocked_by)
                       from activity) as dt(c)),
    tree as (select activity.*,
                    1                               as level,
                    activity.pid                    as top_blocker_pid,
                    array [activity.pid]            as path,
                    array_agg(activity.pid) over () as all_blockers_above
             from activity,
                  blockers
             where array [pid] <@ blockers.pids
               and blocked_by = '{}'::int[]
             union all
             select activity.*,
                    tree.level + 1                                             as level,
                    tree.top_blocker_pid,
                    path || array [activity.pid]                               as path,
                    tree.all_blockers_above || array_agg(activity.pid) over () as all_blockers_above
             from activity,
                  tree
             where not array [activity.pid] <@ tree.all_blockers_above
               and activity.blocked_by <> '{}'::int[]
               and activity.blocked_by <@ tree.all_blockers_above)
select pid,
       blocked_by,
       tx_age,
       state_age,
       replace(state, 'idle in transaction', 'idletx')                                                          state,
       datname,
       usename,
       format('%s:%s', wait_event_type, wait_event)                                                          as wait,
       (select count(distinct t1.pid) from tree t1 where array [tree.pid] <@ t1.path and t1.pid <> tree.pid) as blkd,
       format(
               '%s %s%s',
               lpad('[' || pid::text || ']', 7, ' '),
               repeat('.', level - 1) || case when level > 1 then ' ' end,
               query
           )
from tree
order by top_blocker_pid, level, pid;

위 SQL로 출력해서 원하는 pid 를 아래 쿼리로 죽여주시면 됩니다.
간단하죠?

select pg_terminate_backend(4021449);

자, DBA 여러분들 힘내세요!

by.sTricky