[PostgreSQL] PostgreSQL에서 현재 적용된 설정 정보를 쿼리로 확인하는 방법
PostgreSQL에서 현재 적용된 설정 정보를 쿼리로 확인하는 방법은 크게 SHOW 명령어, 내장 함수(current_setting), 그리고 시스템 뷰(pg_settings) 조회 등 세 가지로 나뉩니다.
1. SHOW 명령어 (가장 직관적인 방법)
단일 파라미터의 값을 빠르게 확인하거나 전체 설정 목록을 볼 때 사용합니다.
- 전체 설정 확인: 현재 세션에 적용된 모든 설정 파라미터와 값을 출력합니다.
SHOW ALL;- 특정 설정 확인:
SHOW [파라미터명];형태로 원하는 항목만 조회합니다.
SHOW work_mem;
SHOW shared_buffers;
SHOW max_connections;
SHOW timezone;
SHOW server_version;
SHOW data_directory;
2. pg_settings 시스템 뷰 조회 (가장 상세한 정보 확인)
단순 값뿐만 아니라 기본값(boot_val), 단위(unit), 설정 적용 시 재시작 필요 여부(context), 설정 파일 위치(sourcefile) 등 메타데이터까지 한눈에 볼 수 있습니다. WHERE 절로 필터링하기 좋습니다.
- 전체 설정 상세 조회
SELECT name, setting, unit, category, short_desc, context, source
FROM pg_settings;
- 특정 키워드로 설정 검색 (예: 메모리 관련 설정):
SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name LIKE '%mem%' OR name LIKE '%buffer%';
- 기본값에서 변경된 설정만 조회 (튜닝 내역 확인 시 유용):
SELECT name, setting, unit, boot_val, reset_val, source, sourcefile
FROM pg_settings
WHERE setting <> boot_val;
(참고: context 컬럼 값이 postmaster면 DB 재시작이 필요하고, sighup이면 설정 리로드(pg_reload_conf())만으로 반영되는 항목입니다.)
3. current_setting() 함수 사용
쿼리 결과값으로 설정을 가져와 다른 SQL 문이나 조건식 내부에서 활용할 때 유용합니다.
- 특정 파라미터 값 반환:
SELECT current_setting('work_mem');
SELECT current_setting('max_connections');
- 존재하지 않는 파라미터 조회 시 에러 방지 (두 번째 인자에
true전달):
SELECT current_setting('custom.my_setting', true); -- 없으면 에러 대신 NULL 반환
4. 설정 파일(postgresql.conf) 직접 수정 내역 확인
ALTER SYSTEM 명령어로 변경되어 postgresql.auto.conf에 기록된 설정만 따로 확인할 수도 있습니다.
sql
SELECT name, setting, sourcefile, sourceline
FROM pg_file_settings;
코드를 사용할 때는 주의가 필요합니다.
💡 자주 확인하는 주요 성능/운영 파라미터 예시
SHOW 명령어나 pg_settings에서 주로 확인하는 핵심 항목들입니다.
| 분류 | 주요 파라미터명 | 설명 |
|---|---|---|
| 메모리 | shared_buffers, work_mem, maintenance_work_mem, effective_cache_size | 캐시 및 정렬/해시 작업 메모리 크기 |
| 연결 | max_connections, superuser_reserved_connections | 최대 동시 접속 수 |
| WAL/체크포인트 | wal_level, max_wal_size, checkpoint_timeout | 트랜잭션 로그 및 복구/복제 관련 |
| 로깅/모니터링 | log_min_duration_statement, log_statement, shared_preload_libraries | 슬로우 쿼리 로깅 및 확장 모듈 |
| 경로/기타 | data_directory, config_file, hba_file, TimeZone | 데이터 및 설정 파일 실제 경로 |
pg_settings의 context 컬럼은 해당 설정을 변경할 수 있는 시점과 권한 수준을 나타냅니다.
context 컬럼의 7가지 값 정리
context 값 | 반영 시점 및 조건 | 변경 가능 권한 | 대표 파라미터 예시 |
|---|---|---|---|
internal | 변경 불가. 컴파일 시점이나 initdb(DB 클러스터 초기화) 단계에서 고정되는 읽기 전용 값입니다. | 변경 불가 | block_size, data_checksums, server_version |
postmaster | DB 서버 재시작 필요. postgresql.conf 수정 후 PostgreSQL 서비스를 재기동해야 반영됩니다. | 슈퍼유저 (OS/DB) | shared_buffers, max_connections, port, wal_level |
sighup | 설정 리로드(pg_reload_conf())로 즉시 반영. 재시작 없이 전체 세션에 적용됩니다. (기존 세션은 다음 트랜잭션이나 쿼리부터 반영됨) | 슈퍼유저 | autovacuum, checkpoint_timeout, max_wal_size, log_destination |
superuser-backend | 새 세션부터 반영. 설정 파일 수정 및 리로드 후, 새로 연결되는 세션에만 적용됩니다. (기존 세션은 재접속 필요) | 슈퍼유저 | log_connections, log_disconnections, ignore_system_indexes |
backend | 새 세션부터 반영. superuser-backend와 동일하지만, 일반 유저도 접속 시점에 옵션으로 지정할 수 있습니다. | 일반 유저 (접속 시) | local_preload_libraries |
superuser | 세션 단위 즉시 변경 가능. 슈퍼유저 권한으로 SET 명령어를 사용해 현재 세션에만 즉시 적용할 수 있습니다. (파일 수정 시 리로드로 전체 반영) | 슈퍼유저 | log_min_duration_statement, log_statement, session_replication_role |
user | 세션 단위 즉시 변경 가능. 일반 유저도 SET 명령어를 통해 본인 세션의 값을 자유롭게 바꿀 수 있습니다. | 일반 유저 | work_mem, statement_timeout, TimeZone, search_path, enable_seqscan |
실무 활용 팁
- 쿼리 튜닝 테스트 시 (
user,superuser):
전체 DB 설정을 바꾸기 전에, 현재 내 세션에서만SET work_mem = '256MB';처럼 값을 변경한 뒤EXPLAIN ANALYZE로 실행 계획 변화를 안전하게 테스트할 수 있습니다. context별 파라미터 분포 확인 쿼리:sqlSELECT context, count(*) FROM pg_settings GROUP BY context ORDER BY count(*) DESC;코드를 사용할 때는 주의가 필요합니다.
