DB/postgresql

PostgreSQL에서 누가 어떤 SQL을 실행했는지 확인할 수 있을까?

노프라블롬 2026. 9. 21. 19:03

이전에 PostgreSQL에서 현재 접속 중인 사용자를 확인하는 방법을 정리한 적이 있다.

pg_stat_activity를 조회하면 어떤 사용자가 어느 IP에서 접속하고 있는지 확인할 수 있었다.

 
SELECT
    datname,
    usename,
    client_addr,
    application_name,
    state
FROM pg_stat_activity;
 

그런데 접속자를 확인하고 나니 또 하나 궁금해졌다.

누가 접속했는지는 알겠는데, 그 사용자가 어떤 SQL을 실행했는지도 확인할 수 있을까?

결론부터 말하면 현재 실행 중인 SQL은 비교적 쉽게 확인할 수 있다.

문제는 이미 실행이 끝난 SQL을 확인하고 싶을 때다.

현재 실행 중인 SQL 확인하기

pg_stat_activity에는 접속 정보뿐만 아니라 현재 세션에서 실행 중인 쿼리 정보도 들어 있다.

이번에는 query 컬럼까지 같이 조회해봤다.

 
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    state,
    query_start,
    query
FROM pg_stat_activity
ORDER BY query_start DESC;
 

결과는 대략 이런 식이다.

pid   usename   client_addr       state    query
---------------------------------------------------------------
5210  user01    192.168.x.x       active   SELECT * FROM sample;
5198  user02    192.168.x.x       idle     COMMIT;
 

여기서 query를 보면 해당 세션에서 실행 중이거나 최근 실행한 쿼리를 확인할 수 있다.

active만 보고 싶다면

현재 실제로 실행 중인 SQL만 확인하고 싶을 때는 state 조건을 추가하면 된다.

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

다만 이 쿼리를 실행하면 내가 지금 실행하고 있는 pg_stat_activity 조회 SQL도 active로 잡힌다.

그래서 자기 자신을 제외하고 싶다면:

 
SELECT
    pid,
    usename,
    client_addr,
    query_start,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND pid <> pg_backend_pid()
ORDER BY query_start;
 

처럼 조회할 수도 있다.

PostgreSQL pg_stat_activity에서 현재 실행 중인 SQL을 확인한 화면

 

idle인데 query가 보이는 건 뭘까?

조회하다 보면 state가 idle인데 query에는 SQL이 들어 있는 경우가 있다.

처음에는 이것도 현재 실행 중인 SQL인가 싶었다.

하지만 idle은 현재 쿼리를 실행하고 있는 상태가 아니라 클라이언트의 다음 명령을 기다리고 있는 상태다.

그래서 현재 실행 중인 SQL을 찾는 목적이라면 query만 보는 것보다 state를 같이 확인하는 게 좋다.

active
→ 현재 쿼리 실행 중

idle
→ 현재 실행 중인 쿼리 없음
   다음 명령을 기다리는 상태
 

query_start도 같이 보면 쿼리가 언제 시작됐는지 확인할 수 있어서 오래 실행되고 있는 SQL을 찾을 때도 유용하다.

그런데 이미 끝난 SQL도 볼 수 있을까?

여기서 내가 궁금했던 건 이쪽이었다.

예를 들어 누군가 오전에 DB에 접속해서

 
UPDATE ...
DELETE ...
INSERT ...
 

같은 작업을 하고 이미 접속을 종료했다고 해보자.

오후에 pg_stat_activity를 조회해서

오전에 저 사용자가 무슨 SQL을 실행했지?

 

라고 확인할 수 있을까?

pg_stat_activity만으로는 확인할 수 없다.

pg_stat_activity는 기본적으로 현재 존재하는 서버 프로세스와 세션의 활동을 확인하기 위한 뷰다.

이미 연결이 종료된 세션의 과거 SQL 실행 내역을 계속 보관해주는 이력 테이블은 아니다.

이전 글에서 접속 이력을 확인했던 것과 비슷하다.

과거 SQL까지 확인하려면 그 시점에 로그가 남도록 설정되어 있어야 한다.

SQL 로그 설정 확인하기

먼저 현재 PostgreSQL에서 SQL 로그를 어떻게 기록하고 있는지 확인할 수 있다.

 
SHOW log_statement;
 

결과는 보통 다음 값 중 하나다.

none
ddl
mod
all
 

각각 의미는 다음과 같다.

설정기록 대상
none SQL 문을 log_statement 설정으로 기록하지 않음
ddl CREATE, ALTER, DROP 등 DDL 기록
mod DDL + INSERT, UPDATE, DELETE 등 데이터 변경 명령 기록
all 모든 SQL 문 기록

예를 들어:

log_statement = 'none'
 

이라면 log_statement를 통한 SQL 전체 기록은 하지 않는 상태다.

PostgreSQL log_statement 설정을 확인한 화면

 

모든 SQL을 기록하는 게 좋을까?

처음 보면 그냥 all로 설정하면 가장 편할 것 같다.

log_statement = 'all'
 

그러면 실행되는 SQL을 전부 기록할 수 있으니까 나중에 찾기도 쉽다.

그런데 운영 DB에서는 무조건 all로 설정하는 게 좋은 건 아니다.

SQL이 많이 실행되는 시스템이라면 로그 양도 상당히 증가할 수 있고, 민감한 값이 SQL에 포함되는 환경이라면 로그에 어떤 정보가 남는지도 고려해야 한다.

그래서 모든 SQL을 남기기보다는 오래 걸리는 SQL을 찾는 목적으로 다른 설정을 활용하기도 한다.

오래 걸리는 SQL만 로그로 남기기

대표적으로 log_min_duration_statement가 있다.

현재 설정은 다음과 같이 확인할 수 있다.

 
SHOW log_min_duration_statement;
 

이 값은 몇 ms 이상 실행된 SQL을 로그에 남길지 지정한다.

예를 들어:

log_min_duration_statement = 1000
 

이라면 실행 시간이 1초 이상인 SQL을 기록하는 방식이다.

100ms   → 기록 안 함
500ms   → 기록 안 함
1500ms  → 로그 기록
3000ms  → 로그 기록
 

반면 -1이면 이 설정을 통한 statement duration 로깅은 비활성화된 상태다.

모든 SQL을 무조건 남기는 것보다 느린 쿼리를 찾고 싶은 상황에서는 이런 방식이 더 적합할 수 있다.

PostgreSQL SQL 실행 로그 관련 설정을 확인한 화면

 

오늘 설정하면 어제 실행한 SQL도 볼 수 있을까?

이 부분도 중요하다.

예를 들어 오늘 확인해보니:

log_statement = none
 

이었다.

그래서 오늘부터 SQL 로그를 기록하도록 설정했다고 해도 어제 실행됐던 SQL이 새롭게 생겨나는 것은 아니다.

로그는 해당 설정이 적용되어 있던 시점부터 기록된다.

즉,

어제
SQL 실행
로그 설정 X
        ↓
기록 없음

오늘
로그 설정 O
        ↓
오늘 이후 SQL부터 기록
 

이런 구조다.

그래서 사고가 발생한 뒤에

로그 설정 켰으니까 어제 누가 DELETE 했는지 찾아보자.

 

라고 해도 PostgreSQL에 해당 기록이 애초에 남지 않았다면 나중에 소급해서 만들어낼 수는 없다.

물론 별도의 감사 로그, 애플리케이션 로그 또는 다른 모니터링 체계를 사용하고 있었다면 그쪽에 기록이 남아 있을 가능성은 별도로 확인해야 한다.

결국 현재 SQL과 과거 SQL은 확인 방법이 달랐다

처음에는 pg_stat_activity에 query 컬럼이 있으니까 사용자가 실행했던 SQL을 전부 확인할 수 있는 줄 알았다.

그런데 실제로는 목적을 나눠서 생각해야 했다.

현재 누가 접속해 있는지

 
SELECT *
FROM pg_stat_activity;
 

현재 실행 중인 SQL 확인

 
SELECT
    pid,
    usename,
    client_addr,
    query_start,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND pid <> pg_backend_pid();
 

과거에 실행된 SQL 확인

PostgreSQL 로그 확인
 

단, 과거 SQL은 그 당시에 로그를 남기도록 설정되어 있었어야 한다.

결국 pg_stat_activity는 현재 상황을 확인할 때 유용하고, 과거에 누가 어떤 작업을 했는지 추적해야 한다면 평소 로그 설정이 중요했다.

접속자를 확인하고 나니까 당연히 실행한 SQL도 다 볼 수 있을 줄 알았는데, 안 남겨둔 과거 기록은 나중에 찾으려고 해도 없는 건 없는 거였다.