PostgreSQL 16 容器化部署优化与配置问题解决

摘要：本文总结了在 Docker 环境中部署 PostgreSQL 16 时遇到的配置错误，包括 checkpoint_segments 参数过时、conf.d 目录缺失等问题，并提供了修复方案和优化后的 postgresql.conf 配置。

一、问题概述

      用户在使用 Docker 部署 PostgreSQL 16 时遇到了配置文件报错，具体错误如下：

• FATAL: configuration file "/var/lib/postgresql/data/postgresql.conf" contains errors
• unrecognized configuration parameter "checkpoint_segments" in file ... line 42

二、问题分析

1. checkpoint_segments 参数已移除

      在 PostgreSQL 16 中，checkpoint_segments 已被移除，替代参数为 max_wal_size 和 min_wal_size。因此，配置文件中包含该参数会导致启动失败。

2. include_dir = 'conf.d' 引发目录缺失错误

      配置文件中引用了 conf.d 目录，但容器内未创建该目录，导致 PostgreSQL 报错。

三、解决方案

1. 删除或注释 checkpoint_segments 参数

# checkpoint_segments = 16

      替换为现代推荐参数：

max_wal_size = 2GB
min_wal_size = 1GB

2. 注释或删除 include_dir = 'conf.d'

# include_dir = 'conf.d'

      或者创建 conf.d 目录并挂载到容器中。

四、优化后的 postgresql.conf 配置

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
data_directory = '/var/lib/postgresql/data'
hba_file = '/var/lib/postgresql/data/pg_hba.conf'
ident_file = '/var/lib/postgresql/data/pg_ident.conf'

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
listen_addresses = '*'
port = 5432
max_connections = 100
superuser_reserved_connections = 3

unix_socket_directories = '/var/run/postgresql'

#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
shared_buffers = 1GB
huge_pages = try
temp_buffers = 32MB
work_mem = 64MB
maintenance_work_mem = 256MB
autovacuum_work_mem = -1
dynamic_shared_memory_type = posix
max_files_per_process = 10000
vacuum_buffer_usage_limit = 512kB

#------------------------------------------------------------------------------
# WRITE-AHEAD LOG
#------------------------------------------------------------------------------
wal_level = replica
fsync = on
synchronous_commit = on
full_page_writes = on
wal_log_hints = on
wal_compression = on
max_wal_size = 2GB
min_wal_size = 1GB
checkpoint_timeout = 15min

#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
effective_cache_size = 4GB
random_page_cost = 1.1
cpu_tuple_cost = 0.005
jit = off

#------------------------------------------------------------------------------
# REPORTING AND LOGGING
#------------------------------------------------------------------------------
logging_collector = on
log_destination = 'csvlog'
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0600
log_rotation_age = 1d
log_rotation_size = 100MB
log_truncate_on_rotation = off

log_min_messages = warning
log_min_error_statement = error
log_min_duration_statement = 1000
log_duration = off
log_line_prefix = '%m [%p] user=%u,db=%d,app=%a,client=%h '
log_timezone = UTC
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0

#------------------------------------------------------------------------------
# STATISTICS
#------------------------------------------------------------------------------
track_activities = on
track_counts = on
track_io_timing = on

#------------------------------------------------------------------------------
# AUTOVACUUM
#------------------------------------------------------------------------------
autovacuum = on
autovacuum_max_workers = 5
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
autovacuum_freeze_max_age = 200000000
autovacuum_multixact_freeze_max_age = 400000000
autovacuum_vacuum_cost_delay = 10ms

#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
statement_timeout = 30s
lock_timeout = 15s
idle_in_transaction_session_timeout = 5min

#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------
deadlock_timeout = 1s
max_locks_per_transaction = 128
max_pred_locks_per_transaction = 128

#------------------------------------------------------------------------------
# ERROR HANDLING
#------------------------------------------------------------------------------
restart_after_crash = on

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
# include_dir = 'conf.d'

五、其他建议

• 确保 shm_size: 5g 设置合理，避免内存不足。
• 定期检查日志以排查性能问题或异常。
• 使用 pgtune 或官方工具生成适配版本的配置。