쿼리 실행 로그 남기기

복잡한 시스템에서는 PostgreSQL에서도 뭔가 꼬일 때가 있어: 쿼리가 너무 오래 걸리거나, 서버 부하가 확 올라가고, 유저들이 슬슬 짜증내기 시작하지. 로그 남기기는 DB 내부 동작을 관찰하는 방법이야. 이걸로:

1. 느린 쿼리 찾기 — 앱을 느리게 만드는 쿼리들!
2. 성능 최적화 — 쿼리 동작을 분석해서 더 빠르게 만들 수 있어.
3. 에러 진단 — 제약조건 위반이나 문법 오류 같은 거 잡아낼 수 있음.
4. 감사(Audit) 보장 — 누가 서버에서 뭘 했는지 추적 가능.

로그 관련 핵심 파라미터

PostgreSQL 설정 파일(postgresql.conf)에는 로그 관련 파라미터가 엄청 많아. 그중에서 진짜 중요한 것들만 골라서 볼게.

1. log_statement — SQL 쿼리 로그 남기기

log_statement 파라미터는 어떤 SQL 쿼리를 로그에 남길지 정하는 거야. 값은 다음 중 하나로 설정할 수 있어:

• none — 쿼리 로그 남기기 끔.
• ddl — 데이터 정의 명령어만 로그(CREATE, ALTER, DROP 등).
• mod — 데이터 변경 명령어만 로그(INSERT, UPDATE, DELETE 등).
• all — 모든 SQL 쿼리 로그(심지어 SELECT도 포함).

예시:

모든 쿼리 로그를 남기고 싶으면, postgresql.conf 파일에 이렇게 써:

log_statement = 'all'

데이터 변경 명령어만 로그 남기고 싶으면:

log_statement = 'mod'

설정 바꾼 다음엔 서버 재시작 잊지 마:

sudo systemctl restart postgresql

2. log_duration — 실행 시간 로그 남기기

log_duration 파라미터는 각 쿼리의 실행 시간을 로그에 남길 수 있어. 어떤 쿼리가 시간 많이 잡아먹는지 파악할 때 유용하지.

예시:

쿼리 실행 시간 로그 남기고 싶으면 이렇게 해:

log_duration = on

느린 쿼리만 로그 남기고 싶으면 log_min_duration_statement 파라미터를 써(이건 아래에서 설명할게).

3. log_min_duration_statement — 느린 쿼리만 로그

이 파라미터는 지정한 시간(밀리초)보다 오래 걸리는 쿼리만 로그에 남겨줘. DB에서 "느려터진" 쿼리 찾을 때 완전 편함.

예시:

1초(1000ms) 넘게 걸리는 쿼리만 로그 남기고 싶으면:

log_min_duration_statement = 1000

실행 시간 상관없이 모든 쿼리 로그 남기고 싶으면 0으로 설정:

log_min_duration_statement = 0

-1로 하면 실행 시간 기준 로그 남기기 꺼짐.

4. log_line_prefix — 로그 메시지 포맷

log_line_prefix 파라미터로 로그 메시지 포맷을 커스터마이즈할 수 있어. 유저명, PID, 날짜/시간 등 컨텍스트 추가할 때 유용하지.

예시:

유저명, DB명, 시간, 프로세스 정보까지 남기고 싶으면:

log_line_prefix = '%t [%p]: [%d]: [%u]: '

여기서:

• %t — 쿼리 시간.
• %p — 프로세스 PID.
• %d — DB 이름.
• %u — 유저 이름.

전체 옵션 리스트는 PostgreSQL 문서 참고.

5. logging_collector — 로그 파일로 저장

logging_collector 파라미터는 로그를 파일로 저장하는 기능을 켜주는 거야. 이게 꺼져 있으면 로그가 stdout(표준 출력)으로만 나가서 좀 불편할 수 있음.

로그 파일 저장하려면 이렇게 해:

logging_collector = on

log_directory랑 log_filename으로 로그 파일 경로랑 이름도 지정해줘야 해:

log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d.log'

파라미터 실전 활용

실제로 로그 파라미터를 어떻게 쓰는지 시나리오별로 볼게.

시나리오 1: 모든 쿼리 로그 남기기

새로운 DB에서 무슨 일이 일어나는지 다 보고 싶으면 이렇게 세팅:

log_statement = 'all'
log_line_prefix = '%t [%p]: [%d]: [%u]: '

이제 DB에서 실행되는 모든 쿼리가 로그 파일에 남아. 디버깅할 때 완전 편함!

시나리오 2: 느린 쿼리 찾기

서버가 가끔씩 "버벅"거릴 때, 문제 쿼리 찾으려면 이렇게 세팅:

log_min_duration_statement = 500  # 500ms 넘는 쿼리만 로그
log_line_prefix = '%t [%p]: [%d]: [%u]: [%r] '

%r은 클라이언트 IP 주소도 추가해줘. 이렇게 하면 서버를 힘들게 하는 쿼리를 쉽게 찾을 수 있어.

시나리오 3: 최소 로그 모드

서버가 안정적으로 돌아가고, 최소한의 audit 로그만 필요할 때:

log_statement = 'mod'
log_duration = off

이렇게 하면 데이터 변경(INSERT, UPDATE 등)만 로그에 남아.

로그 분석

로그에 데이터가 쌓이기 시작하면, 분석하는 게 중요하지! 할 수 있는 방법은:

텍스트 에디터로 로그 파일 열기:

cat /var/log/postgresql/postgresql.log

분석 툴로 grep 같은 걸 써서 느린 쿼리만 뽑아보기:

grep "duration: " /var/log/postgresql/postgresql.log

더 고급 분석은 pgBadger 같은 유틸을 써도 돼.

꿀팁

너무 자세한 로그는 디스크 부하를 올릴 수 있어. 운영 환경에서는 꼭 필요한 파라미터만 켜는 게 좋아.

오래된 로그 파일은 주기적으로 지우거나 백업해서 디스크가 꽉 차지 않게 관리하자.

로그는 필요한 데이터만 모으는 게 효율적이야. 예를 들어 log_min_duration_statement로 진짜 필요한 것만 남기면 리소스도 아낄 수 있어.

어때? 이제 PostgreSQL에서 로그 세팅하고, 쿼리 분석해서 성능 올리는 거 문제없지! 어떤(아니, 여러) 유명하진 않지만 분명 괜찮은 개발자가 말했듯이: "로그는 쿼리의 과거를 보는 창이고, DB의 미래를 고치는 기회야".