Programing/Postgresql

PostgreSQL에서 실행 중인 쿼리 확인 및 종료

Tech Energy 2024. 11. 19. 09:00
728x90
반응형

PostgreSQL을 운영하다 보면 예상치 못한 긴 실행 시간의 쿼리가 데이터베이스 성능에 영향을 줄 때가 있습니다. 이런 상황에서는 실행 중인 쿼리를 모니터링하고 필요한 경우 종료하는 방법을 알아두는 것이 중요합니다. 이 글에서는 PostgreSQL의 pg_stat_activity 뷰를 활용하여 실행 중인 쿼리를 확인하고, 특정 쿼리를 종료하는 방법을 단계적으로 설명합니다.

 

1. 1분 이상 실행 중인 쿼리 확인

PostgreSQL에서 현재 실행 중인 쿼리를 확인하려면 pg_stat_activity 뷰를 사용할 수 있습니다. 아래 쿼리를 통해 1분 이상 실행 중인 쿼리 정보를 확인할 수 있습니다.

SELECT current_timestamp - query_start AS runtime, 
       datname, 
       usename, 
       query, 
       pid 
FROM pg_stat_activity 
WHERE state = 'active' 
  AND current_timestamp - query_start > interval '1 minute' 
ORDER BY 1 DESC;

 

  • current_timestamp - query_start: 쿼리가 실행된 시간을 계산합니다.
  • datname: 쿼리가 실행 중인 데이터베이스 이름입니다.
  • usename: 쿼리를 실행한 사용자 이름입니다.
  • query: 실행 중인 SQL 쿼리의 텍스트입니다.
  • pid: 해당 쿼리의 프로세스 ID입니다.
  • state = 'active': 현재 실행 중인(active) 상태의 쿼리만 필터링합니다.
  • current_timestamp - query_start > interval '1 minute': 실행 시간이 1분을 초과한 쿼리만 표시합니다.
  • ORDER BY 1 DESC: 실행 시간이 긴 쿼리부터 내림차순으로 정렬합니다.

결과 예시

00:02:15 mydatabase admin SELECT * FROM large_table; 12345
00:01:45 mydatabase user1 UPDATE orders SET status = 'done'; 67890

 

 

1. 실행 중인 쿼리 종료

특정 프로세스 ID(pid)를 가진 쿼리를 종료하려면 pg_cancel_backend 함수를 사용합니다.

SELECT pg_cancel_backend(12345);

pg_cancel_backend(pid): 해당 프로세스 ID의 쿼리를 종료 요청합니다.

  • 쿼리는 즉시 종료되지 않을 수 있으며, PostgreSQL은 안전하게 작업을 중단합니다.

 

3. 강제 종료

pg_cancel_backend로 쿼리가 종료되지 않을 경우, pg_terminate_backend를 사용하여 강제 종료할 수 있습니다

SELECT pg_terminate_backend(12345);

주의사항

  • pg_terminate_backend는 연결을 강제로 종료합니다. 이로 인해 트랜잭션 롤백이나 데이터 정합성 문제가 발생할 수 있으므로 신중하게 사용해야 합니다.

 

4. 모든 프로세스 종료

특정 사용자나 데이터베이스에 연결된 모든 프로세스를 종료해야 하는 경우 아래와 같은 쿼리를 사용할 수 있습니다.

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydatabase' 
  AND pid <> pg_backend_pid();

쿼리 설명

  • datname = 'mydatabase': 특정 데이터베이스의 모든 연결을 종료합니다.
  • pid <> pg_backend_pid(): 현재 세션의 프로세스를 제외합니다.

5. 권한 설정 확인

  • pg_cancel_backend와 pg_terminate_backend를 사용하려면 슈퍼유저 권한이 필요합니다.
  • 일반 사용자 계정으로는 이러한 작업을 수행할 수 없으므로, 데이터베이스 관리자 계정으로 접속해야 합니다.

 

PostgreSQL에서는 pg_stat_activity 뷰와 백엔드 관리 함수를 활용하여 실행 중인 쿼리를 모니터링하고 필요할 경우 안전하게 종료할 수 있습니다. 그러나 강제 종료는 데이터 손실이나 트랜잭션 충돌을 유발할 수 있으므로, 가능하면 쿼리 최적화와 트랜잭션 관리를 통해 장시간 실행되는 쿼리를 예방하는 것이 가장 좋습니다.

 

728x90
반응형