장시간 'idle in transaction' 세션을 통제된 방식으로 일괄 종료

명령어

-- 1) Dry run first: who will be affected?
SELECT pid, usename, application_name, client_addr,
       now() - state_change AS idle_for, left(query, 80) AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND now() - state_change > interval '15 minutes'
  AND backend_type = 'client backend'
  AND pid <> pg_backend_pid();

-- 2) Terminate; wait up to 5 s for each process to exit
SELECT pid, usename, now() - state_change AS idle_for,
       pg_terminate_backend(pid, 5000) AS terminated
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND now() - state_change > interval '15 minutes'
  AND backend_type = 'client backend'
  AND pid <> pg_backend_pid();

하는 일

먼저 트랜잭션을 열어 둔 채 일정 시간 idle 상태인 세션을 나열한 후 종료한다. timeout 파라미터는 프로세스가 실제로 종료될 때까지(또는 timeout 만료까지) 대기한다.

사용 시점

락을 잡고 잊힌 세션이 블로킹을 일으키거나 xmin horizon을 붙잡아 VACUUM을 멈추게 할 때의 긴급 조치용.

주의

pg_cancel_backend는 'idle in transaction' 세션에는 아무 효과가 없다(취소할 실행 중 쿼리가 없음). pg_terminate_backend가 필요하며 커밋되지 않은 모든 변경이 롤백된다. usename/application_name으로 필터를 좁히고 복제, 백업, 모니터링 세션은 제외한다. 커넥션 풀은 종료된 연결을 다시 연다. timeout이 만료되면 함수는 경고를 내고 false를 반환한다. 영구 해결책으로 롤별 idle_in_transaction_session_timeout을 설정한다.