数据库参数配置：
[postgres@ycdb data]$ more postgresql.conf
listen_addresses = '0.0.0.0'
port = 5432
max_connections = 5000
unix_socket_directories = '.'
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10
shared_buffers = 32GB # 1/4 主机内存
maintenance_work_mem = 2GB # min( 2G, (1/4 主机内存)/autovacuum_max_workers )
dynamic_shared_memory_type = posix
vacuum_cost_delay = 0
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 10.0
bgwriter_flush_after = 0 # IO很好的机器，不需要考虑平滑调度
max_worker_processes = 128
max_parallel_workers_per_gather = 0 # 如果需要使用并行查询，设置为大于1 ，不建议超过 主机cores-2
old_snapshot_threshold = -1
backend_flush_after = 0 # IO很好的机器，不需要考虑平滑调度, 否则建议128~256kB
wal_level = replica
synchronous_commit = off
full_page_writes = on # 支持原子写超过BLOCK_SIZE的块设备，在对齐后可以关闭。或者支持cow的文件系统可以关闭。
wal_keep_segments = 3000
wal_buffers = 100MB # min( 2047MB, shared_buffers/32 ) = 512MB
wal_writer_delay = 10ms
wal_writer_flush_after = 0 # IO很好的机器，不需要考虑平滑调度, 否则建议128~256kB
wal_compression = on
checkpoint_timeout = 60min # 不建议频繁做检查点，否则XLOG会产生很多的FULL PAGE WRITE(when full_page_writes=on)。
max_wal_size = 70GB # 建议是SHARED BUFFER的2倍
min_wal_size = 15GB # max_wal_size/4
checkpoint_completion_target = 0.1 # 硬盘好的情况下，可以让检查点快速结束，恢复时也可以快速达到一致状态。否则建议0.5~0.9
checkpoint_flush_after = 0 # IO很好的机器，不需要考虑平滑调度, 否则建议128~256kB
archive_mode = on
archive_command = 'sh /pg12.2/data/archivelog/archive.sh %p %f'
# 后期再修改，如 'test ! -f /pg12.2/data/archivelog/%f && cp %p /pg12.2/data/archivelog/%f'
max_wal_senders = 8
random_page_cost = 1.3 # IO很好的机器，不需要考虑离散和顺序扫描的成本差异
parallel_tuple_cost = 0
parallel_setup_cost = 0
#min_parallel_relation_size = 0
effective_cache_size = 60GB # 看着办，扣掉会话连接RSS，shared buffer, autovacuum worker, 剩下的都是OS可用的CACHE。
force_parallel_mode = off
log_destination = 'csvlog'
logging_collector = on
log_truncate_on_rotation = on
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose
log_timezone = 'PRC'
vacuum_defer_cleanup_age = 0
hot_standby_feedback = off # 建议关闭，以免备库长事务导致 主库无法回收垃圾而膨胀。
max_standby_archive_delay = 300s
max_standby_streaming_delay = 300s
autovacuum = on
log_autovacuum_min_duration = 0
autovacuum_max_workers = 16 # CPU核多，并且IO好的情况下，可多点，但是注意16*autovacuum mem，会消耗较多内存，所以内存也要有基础。
autovacuum_naptime = 45s # 建议不要太高频率，否则会因为vacuum产生较多的XLOG。
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.1
autovacuum_freeze_max_age = 1600000000
autovacuum_multixact_freeze_max_age = 1600000000
vacuum_freeze_table_age = 1500000000
vacuum_multixact_freeze_table_age = 1500000000
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
default_text_search_config = 'pg_catalog.english'
shared_preload_libraries='pg_stat_statements'## 如果你的数据库有非常多小文件（比如有几十万以上的表，还有索引等，并且每张表都会被访问到时），建议FD可以设多一些，避免进程需要打开关闭文件。
## 但是不要大于前面章节系统设置的ulimit -n(open files)
max_files_per_process=65536参考：德哥相关文章