PostgreSQL 세션 및 쿼리 모니터링

PostgreSQL을 운영하다 보면 현재 실행 중인 쿼리를 확인하거나, 특정 세션을 모니터링해야 할 때가 있습니다. 이때 유용한 뷰가 바로 pg_stat_activity입니다. 이번 포스팅에서는 이 뷰를 활용해 현재 접속 상태, 실행 중인 쿼리, 잠금 상태 등을 확인하는 방법을 알아보겠습니다.

---

1. pg_stat_activity란?

pg_stat_activity는 PostgreSQL에서 현재 실행 중인 세션과 관련된 정보를 보여주는 시스템 뷰입니다. 이 뷰를 활용하면 다음과 같은 정보를 확인할 수 있습니다:

• 현재 접속 중인 사용자 및 클라이언트 IP
• 실행 중인 쿼리
• 쿼리 실행 시작 시간
• 세션 상태 (Idle, Active, Waiting 등)

---

2. pg_stat_activity 기본 사용법

(1) 모든 세션 보기

 

SELECT * FROM pg_stat_activity;

 datid  | datname  | pid  | usename  | application_name | client_addr | state   | query
--------+----------+------+----------+------------------+-------------+---------+---------------------------------
  16384 | mydb     | 1234 | postgres | psql             | 127.0.0.1   | active  | SELECT * FROM employees;
  16384 | mydb     | 5678 | appuser  | MyApp            | 192.168.1.1 | idle    |

---

3. 특정 세션 정보 조회

(1) 특정 데이터베이스의 세션만 조회

SELECT * FROM pg_stat_activity WHERE datname = 'mydb';

(2) 특정 사용자의 세션만 조회

SELECT * FROM pg_stat_activity WHERE usename = 'appuser';​

---

4. 실행 중인 쿼리 모니터링

(1) 실행 중인 쿼리만 조회

세션 상태가 active인 쿼리만 확인합니다.

 

SELECT pid, usename, query, query_start
FROM pg_stat_activity
WHERE state = 'active';

 pid  | usename  | query                        | query_start
------+----------+------------------------------+------------------------
 1234 | postgres | SELECT * FROM employees;     | 2024-11-20 10:30:15

---

5. 장시간 실행 중인 쿼리 찾기

(1) 실행 시간이 1분을 초과한 쿼리

 

SELECT pid, usename, query, now() - query_start AS runtime
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '1 minute';

 pid  | usename  | query                        | runtime
------+----------+------------------------------+-----------------
 1234 | appuser  | SELECT * FROM sales_data;    | 00:01:15

---

6. 특정 세션 강제 종료

(1) 세션 종료

세션 pid를 종료하려면 pg_terminate_backend를 사용합니다.

 

SELECT pg_terminate_backend(1234);

 pg_terminate_backend
----------------------
 t

(2) 주의사항

• 관리 권한(superuser)이 필요합니다.
• 무분별한 세션 종료는 데이터 무결성에 영향을 줄 수 있으니 신중히 사용해야 합니다.

---

7. 대기 상태 및 잠금 확인

(1) 대기 중인 세션 확인

SELECT pid, usename, state, wait_event, wait_event_type
FROM pg_stat_activity
WHERE state = 'active' AND wait_event IS NOT NULL;​

 pid  | usename  | state   | wait_event | wait_event_type
------+----------+---------+------------+-----------------
 5678 | appuser  | active  | Lock       | LWLock

(2) 잠금 문제 해결

대기 상태가 발생하면 잠금을 유발한 쿼리를 확인해야 합니다. 이를 위해 pg_locks 뷰와 함께 사용할 수 있습니다:

SELECT pid, locktype, relation::regclass, mode, granted
FROM pg_locks
WHERE granted = false;

---

8. 주기적인 모니터링 설정

PostgreSQL에서 pg_stat_activity 정보를 주기적으로 확인하려면 다음 방법을 사용할 수 있습니다:

• 스크립트 작성: SQL 쿼리를 크론탭과 함께 실행하여 결과를 기록
• 모니터링 도구 활용: pgAdmin, Grafana, Zabbix와 같은 도구를 연동해 시각화

---

마무리

pg_stat_activity는 PostgreSQL 관리자가 실시간으로 세션과 쿼리를 모니터링할 수 있는 강력한 도구입니다. 이번 포스팅에서 소개한 방법들을 활용하면, 장시간 실행 쿼리, 대기 상태, 비효율적인 세션을 빠르게 파악하고 성능 문제를 해결할 수 있습니다.