<div id="readability-page-1" class="page"><div><h3 data-ke-size="size23"><b>postgresql&nbsp;lock&nbsp;tree&nbsp;출력&nbsp;SQL&nbsp;및&nbsp;kill&nbsp;session&nbsp;방법</b></h3>
<figure data-ke-mobilestyle="widthOrigin" data-origin-width="936" data-origin-height="806"><span data-url="https://blog.kakaocdn.net/dn/llhkQ/btr8ZVpxMNG/UKQjiTOr3brOAzhVghOjt0/img.png" data-lightbox="lightbox"><img src="https://blog.kakaocdn.net/dn/llhkQ/btr8ZVpxMNG/UKQjiTOr3brOAzhVghOjt0/img.png" onerror="this.onerror=null; this.src='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png'; this.srcset='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png';" data-origin-width="936" data-origin-height="806"></span></figure>

<p data-ke-size="size16">안녕하세요.<br>Postgresql 에서 lock tree를 조회 하고 원하는 session을 kill 하는 방법을 간략하게 소개 해 드립니다.<br>Postgresql 쓰시면서 lock 이 걸려서 계속 대기 해야 하는 때가 생깁니다.<br>이럴때 빠르게 lock tree 검색해서 필요없는 작업이라면 빨리 kill 해줘야 겠죠.</p>
<p data-ke-size="size16">Postgresql lock tree 출력 SQL</p>
<pre id="code_1681100436453" data-ke-language="sql" data-ke-type="codeblock"><code>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] &lt;@ 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] &lt;@ tree.all_blockers_above
               and activity.blocked_by &lt;&gt; '{}'::int[]
               and activity.blocked_by &lt;@ 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] &lt;@ t1.path and t1.pid &lt;&gt; tree.pid) as blkd,
       format(
               '%s %s%s',
               lpad('[' || pid::text || ']', 7, ' '),
               repeat('.', level - 1) || case when level &gt; 1 then ' ' end,
               query
           )
from tree
order by top_blocker_pid, level, pid;</code></pre>
<figure data-ke-mobilestyle="widthOrigin" data-origin-width="4042" data-origin-height="350"><span data-url="https://blog.kakaocdn.net/dn/bzeKjs/btr8MqqGWei/QsSHFE7K89Iwd47ogLfSgk/img.png" data-lightbox="lightbox"><img src="https://blog.kakaocdn.net/dn/bzeKjs/btr8MqqGWei/QsSHFE7K89Iwd47ogLfSgk/img.png" onerror="this.onerror=null; this.src='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png'; this.srcset='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png';" data-origin-width="4042" data-origin-height="350"></span></figure>

<p data-ke-size="size16">위 SQL로 출력해서 원하는 pid 를 아래 쿼리로 죽여주시면 됩니다.<br>간단하죠?</p>
<pre id="code_1681100488053" data-ke-language="sql" data-ke-type="codeblock"><code>select pg_terminate_backend(4021449);</code></pre>
<p data-ke-size="size16">자, DBA 여러분들 힘내세요!</p>
<p data-ke-size="size16"><span><i><b>by.sTricky</b></i></span></p></div></div>

The DataBase that i am good at

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

postgresql lock tree 출력 SQL 및 kill session 방법

댓글