[PostgreSQL] Parameter List

Container 환경에 PostgreSQL 배포 시 참고를 위해 정리한 파라미터 목록

---

주요 설정 파일 정보

File	Path	Description
Data	/var/lib/postgresql/data	데이터와 설정 관련 파일들이 저장되는 위치
Parameter Config File	/var/lib/postgresql/data/postgresql.conf	DBMS 파라미터 값을 관리하는 파일
Authentication File	/var/lib/postgresql/data/pg_hba.conf	클라이언트 인증과 관련된 보안 설정 파일

 

PostgreSQL 컨테이너 이미지 사용 시 설정 권장 파라미터 목록 (feat. PostgreSQL Korea Blog)

도커 허브에 등록된 공식 PostgreSQL 컨테이너 이미지의 기본 설정 값이 보수적이라 추가 설정을 권장하는 파라미터 목록

• effective_cache_size : 디스크 캐시 크기에 대한 플래너의 가정
• maintenance_work_mem : 유지보수 작업에 사용되는 최대 메모리
• max_wal_size : WAL이 이 크기를 초과하면 체크포인트 강제 실행
• min_wal_size : WAL 파일의 최소 크기
• shared_buffers : 서버가 사용하는 공유 메모리 버퍼의 양
• wal_buffers : WAL 데이터를 위한 공유 메모리의 양
• work_mem : 쿼리 작업에 사용되는 최대 메모리
• logging_collector : 로그 수집기 프로세스 시작 여부
• timezone : 서버의 시간대 설정

 

Parameter 값 산정 기준 정리 

• [PostgreSQL] 주요 파라미터 역할 및 산정방법
• DB 인사이드 | PostgreSQL 참고자료 - Configuration File: postgresql.conf
• 체크포인트 조정의 기본 원리
• PostgreSQL 튜닝 - Autovacuum 최적화에 대하여
• PostgreSQL에서 데이터베이스 매개변수 및 구성을 조정하는 방법에 대한 종합 가이드

Prameter	Type	Default	Recommand
listen_addresses	string	'localhost'	접속 허용 IP, CIDR or '*'
port	integer	5432	5432
superuser_reserved_connections	integer	3	3
max_connections	integer	100	100
work_mem	integer	4MB	total system memory / (max_connections * 2)
maintenance_work_mem	integer	64MB	total system memory * (0.045 ~ 0.062)
shared_buffers	integer	128MB	total system memory * (0.25 ~ 0.5)
wal_buffers	integer	-1 (auto)	shared_buffers * 0.03
min_wal_size	integer	80MB	- 1GB로 시작해서 최대 100GB까지 설정 - max_wal_size의 1/4
max_wal_size	integer	1GB	total system memory * (0.5 ~ 0.75)
temp_buffers	integer	8MB	total system memory * 0.0015
effective_cache_size	integer	4GB	total system memory * (0.5)
checkpoint_timeout	integer	5min (300)	5min-30min (300 ~ 1800)
checkpoint_completion_target	integer	0.5	0.9
default_statistics_target	integer	100	100
autovacuum_work_mem	integer	-1 (auto)	-1
autovacuum_vacuum_scale_factor	float	0.2	0.1-0.2
autovacuum_vacuum_threshold	integer	50	50
wal_level	enum	replica	- replica, archiving 작업 시 = replica 이상 - none = minimal
archive_mode	boolean	off	on
archive_command	string	(empty)	'cp %p /archive/%f'
archive_timeout	integer	0	60 ~ 120
log_destination	string	stderr	stderr, csvlog
logging_collector	boolean	off	on
log_directory	string	/var/log/postgresql	/var/log/postgresql
log_filename	string	postgresql-%Y-%m-%d_%H%M%S.log	postgresql-%Y-%m-%d_%H%M%S.log
log_file_mode	integer	0600	0600
log_truncate_on_rotation	boolean	off	on
log_rotation_age	integer	1d	1d
log_rotation_size	integer	10MB	10MB
log_timezone	string	'UTC'	'Asia/Seoul'
cluster_name	string	(empty)	'production'
password_encryption	enum	md5	scram-sha-256

 

PostgreSQL Parameter 설정

postgresql.conf 파일을 아래와 같이 설정 후 Dockerfile과 같은 폴더에 위치시킨다.

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -
listen_addresses = '*'
port = 5432
max_connections = 100
superuser_reserved_connections = 3
password_encryption = 'scram-sha-256'

#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

# - Memory -
shared_buffers = 2GB    
work_mem = 40MB   
maintenance_work_mem = 400MB 

# effective_io_concurrency = 200

# max_worker_processes=8
# max_parallel_workers_per_gather = 2
# max_parallel_workers = 8

autovacuum_vacuum_scale_factor = 0.1


#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------

# - Settings -
wal_level = replica
wal_buffers = 60MB
min_wal_size = 1GB
max_wal_size = 4GB

# - Checkpoint -
checkpoint_timeout = 600
checkpoint_completion_target = 0.9

# - Archiving -
archive_mode = on
archive_command = 'cp %p /archive/%f'
archive_timeout = 120

#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------

# random_page_cost = 1.1
# default_statistics_target = 100
effective_cache_size = 4GB   # max. Physical M * 0.75

#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB

# - What to Log -
# log_line_prefix = '%t '

#------------------------------------------------------------------------------
# PROCESS TITLE
#------------------------------------------------------------------------------

cluster_name = 'production'

#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------

timezone='Asia/Seoul'

컨테이너 이미지를 빌드하는 과정에 config 파일을 반영해서 실행하도록 구성한다.

# BUILD COMMAND: docker build -t postgres16:1 .
FROM postgres:16.5

# LOCALE 설정
RUN localedef -i ko_KR -c -f UTF-8 -A /usr/share/locale/locale.alias ko_KR.UTF-8
ENV LANG=ko_KR.utf8

# localtime 에 링크할 시간대를 링크.
RUN ln -sf /usr/share/zoneinfo/Asia/Seoul /etc/localtime

# 원하는 시간대 링크후 아래 수행시, /etc/timezone 파일변경됨.
RUN dpkg-reconfigure -f noninteractive tzdata

# postgresql.conf 파일 업로드
COPY postgresql.conf /etc/postgresql/postgresql.conf
CMD ["postgres", "-c", "config_file=/etc/postgresql/postgresql.conf"]

 

파라미터 설정 전/후 비교

1. 설정 조회 Query

SELECT name, setting
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem', 'archive_command', 'archive_mode', 'archive_timeout', 'autovacuum_vacuum_scale_factor', 'wal_level', 'wal_buffers', 'archive_mode');

2. 설정 전 결과

              name              |  setting   
--------------------------------+------------
 archive_command                | (disabled)
 archive_mode                   | off
 archive_timeout                | 0
 autovacuum_vacuum_scale_factor | 0.2
 maintenance_work_mem           | 65536
 shared_buffers                 | 16384
 wal_buffers                    | 512
 wal_level                      | replica
 work_mem                       | 4096

3. 설정 후 결과

              name              |      setting      
--------------------------------+-------------------
 archive_command                | cp %p /archive/%f
 archive_mode                   | on
 archive_timeout                | 120
 autovacuum_vacuum_scale_factor | 0.1
 maintenance_work_mem           | 409600
 shared_buffers                 | 262144
 wal_buffers                    | 7680
 wal_level                      | replica
 work_mem                       | 40960