概述pg数据库在不同的业务场景下，参数最好也需要进行针对的调整。
默认值是在确保资源消耗最小的情况下，pg都能够运行起来，不会导致任何致命性的威胁。
而实际中，默认的参数需要进行优化来达到性能的最大化，今天主要介绍一些生产环境常用的参数配置。
环境信息：系统： centos7.3 x86_64 配置： 8核 16G数据库： PostgreSQL 12.21、监听配置及连接相关# - Authentication - #authentication_timeout = 1min # 1s-600s #如果用户密码的MD5会泄露，建议使用scram-sha-256，但是相互不兼容，请注意。
password_encryption = md5 # md5 or scram-sha-256 #db_user_namespace = off # - ssl - #ssl = off #ssl_ca_file = '' #ssl_cert_file = 'server.crt' #ssl_crl_file = '' #ssl_key_file = 'server.key' #ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers #ssl_prefer_server_ciphers = on #ssl_ecdh_curve = 'prime256v1' #ssl_dh_params_file = '' #ssl_passphrase_command = '' #ssl_passphrase_command_supports_reload = off 2、TCP Keepalives相关# - Authentication - #authentication_timeout = 1min # 1s-600s #如果用户密码的MD5会泄露，建议使用scram-sha-256，但是相互不兼容，请注意。
password_encryption = md5 # md5 or scram-sha-256 #db_user_namespace = off # - SSL - #ssl = off #ssl_ca_file = '' #ssl_cert_file = 'server.crt' #ssl_crl_file = '' #ssl_key_file = 'server.key' #ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers #ssl_prefer_server_ciphers = on #ssl_ecdh_curve = 'prime256v1' #ssl_dh_params_file = '' #ssl_passphrase_command = '' #ssl_passphrase_command_supports_reload = off 3、认证协议相关# - Authentication - #authentication_timeout = 1min # 1s-600s #如果用户密码的MD5会泄露，建议使用scram-sha-256，但是相互不兼容，请注意。
password_encryption = md5 # md5 or scram-sha-256 #db_user_namespace = off 4、PG语言环境相关数据库参数说明:LC_COLLATE 字符串排序的顺序LC_CTYPE 字符分类LC_MESSAGES 消息的语言LC_MonETARY 货币使用的格式LC_NUMERIC 数字使用的格式LC_TIME 时间日期使用的格式数据库： PG12.2# - Locale and Formatting -lc_messages = 'en_US.UTF-8' # locale for system error message # stringslc_monetary = 'en_US.UTF-8' # locale for monetary formattinglc_numeric = 'en_US.UTF-8' # locale for number formattinglc_time = 'en_US.UTF-8' # locale for time formattingdefault_text_search_config = 'pg_catalog.english'5、内存相关PostgreSQL既使用自身的缓冲区，也使用内核缓冲IO。
这意味着数据会在内存中存储两次，首先是存入PostgreSQL缓冲区，然后是内核缓冲区。
这被称为双重缓冲区处理。
对大多数操作系统来说，这个参数是最有效的用于调优的参数。
此参数的作用是设置PostgreSQL中用于缓存的专用内存量。
shared_buffers的默认值设置得非常低，因为某些机器和操作系统不支持使用更高的值。
建议的设置值为机器总内存大小的25％，在生产环境中，将shared_buffers设置为较大的值通常可以提供非常好的性能，但应当时刻注意找到平衡点。
# - Memory - # 1/4 主机内存 # shared_buffers 公式： IF use hugepage: 主机内存*(1/4) ELSE: min(32GB, 主机内存*(1/4)) shared_buffers = 6GB # min 128kB # (change requires restart) # 建议shared buffer设置超过32GB时 使用大页，页大小 /proc/meminfo Hugepagesize huge_pages = try # on, off, or try # (change requires restart) #temp_buffers = 8MB # min 800kB # 如果用户需要使用两阶段提交，需要设置为大于0，建议与max_connections一样大 # max_prepared_transactions 公式： max_prepared_transactions=max_connections max_prepared_transactions = 800 # zero disables the feature # (change requires restart) # 可以在会话中设置，如果有大量JOIN，聚合操作，并且期望使用hash agg或hash join。
(排序，HASH都会用到work_mem) # 可以设大一些，但是不建议大于四分之一内存除以最大连接数 (一条QUERY中可以使用多倍WORK_MEM，与执行计划中的NODE有关) # work_mem 公式： max(min(物理内存/4096, 64MB), 4MB) work_mem = 8MB # min 64kB # 创建索引时使用的内存空间。
# maintenance_work_mem 公式： min( 8G, (主机内存*1/8)/max_parallel_maintenance_workers ) maintenance_work_mem = 2GB # min 1MB # 在对一张表进行垃圾回收时，用于缓存垃圾版本的ctid， # 如果autovacuum work mem填满了，则VACUUM需要进入索引垃圾回收阶段，扫描索引（因此为了避免索引被多次扫描，需要缓存住一张表的所有垃圾TUPLE的CTID）。
# 一张表（细到单个最底层分区）垃圾回收结束后释放。
# autovacuum_work_mem 公式： min( 8G, (主机内存*1/8)/autovacuum_max_workers ) autovacuum_work_mem = 1GB # min 1MB, or -1 to use maintenance_work_mem #max_stack_depth = 2MB # min 100kB dynamic_shared_memory_type = posix # supported by the operating system: posixsysvwindowsmmap #effective_cache_size提供可用于磁盘高速缓存的内存量的估计值。
它只是一个建议值，而不是确切分配的内存或缓存大小。
#它不会实际分配内存，而是会告知优化器内核中可用的缓存量。
在一个索引的代价估计中，更高的数值会使得索引扫描更可能被使用，#更低的数值会使得顺序扫描更可能被使用。
在设置这个参数时，还应该考虑PostgreSQL的共享缓冲区以及将被用于PostgreSQL数据文件# 的内核磁盘缓冲区。
默认值是4GB。
# 扣掉会话连接RSS，shared buffer, autovacuum worker, 剩下的都是OS可用的CACHE。
# effective_cache_size 公式： 主机内存*0.75 effective_cache_size = 12GB 6、路由清理相关PostgreSQL 需要定期维护清理，一般都是由守护进程自动清理的，也可以执行脚本定时去清理回收。
在PG执行 VACUUM 和 ANYLYZE 期间，系统会维护一个用于估算各种I/O操作所消耗的内部 计数器,当该值达到vacuum_cost_limit的值时，该进程会休眠 vacuum_cost_delay 指 定的时间，并重置计数器的值，继续运行 VACUM 或者 ANYLYZE 操作当一张表中包含了大量数据时，同时进行删除或者更新操作时，VACUUM 并不是最好的方案, 如果有该情况，则应该使用 VACUU FULL ,当执行 ALTER TABLE 时，会重新 COPY整 个表和重新构建索引，会进行执行锁，临时占用和原始表大小的磁盘空间，直到新数据COPY完成。
# - Background Writer - #指定background writer将dirty buffer写入磁盘的频率，默认200ms，即200ms写一次，写入周期200ms。
bgwriter_delay = 200ms # 10-10000ms between rounds #指定一次写入周期 写入的dirty buffer的最大数量(单位buffers)，默认值100bgwriter_lru_maxpages = 1000 # max buffers written/round, 0 disables #一次写入周期要写到磁盘的dirt buffers的数量T，是基于上一个周期内server进程需要的新的buffers数量N。
#bgwriter_lru_multiplier是一个乘法因子p，T = N * p;较大的值为峰值提供了缓冲，较小的值则需要服务器进程完成写入。
#如上一个写入周期内服务器使用了100个new buffer（100次写产生不同的脏数据），bgwriter_lru_multiplier使用了默认值2.那么这次会将100*2个dirty buffers写到磁盘中,也就是腾出了200个干净的buffers，#如果再下一次写入周期前，服务器申请的buffers数量小于这个值，则自然够用，如果大于200，那么需要服务器自己去触发将dirty buffers写入磁盘。
bgwriter_lru_multiplier = 10.0 # 0-10.0 multiplier on buffers scanned/round #上面的写入磁盘行为指的是普通意义的向磁盘文件写，但是会被操作系统缓存，所以实际行为可能没有写到磁盘中。
#当超过bgwriter_flush_after bytes的数据被bgwriter写时，强制OS将缓存数据写入物理磁盘。
默认值为512KBbgwriter_flush_after = 512kB # measured in pages, 0 disables 7、PG background writer进程相关background writer是pg的一个独立子进程，用于将shared_buffers中的dirty buffer写入磁盘。
（数据的修改不用立即写入磁盘，因为写入了wal日志，可以用于故障恢复），background writer会根据LRU链表，扫描shared buffers，如果发现脏页，就会调用系统write，写入磁盘。
数据库# - Background Writer - #指定background writer将dirty buffer写入磁盘的频率，默认200ms，即200ms写一次，写入周期200ms。
bgwriter_delay = 200ms # 10-10000ms between rounds #指定一次写入周期 写入的dirty buffer的最大数量(单位buffers)，默认值100bgwriter_lru_maxpages = 1000 # max buffers written/round, 0 disables #一次写入周期要写到磁盘的dirt buffers的数量T，是基于上一个周期内server进程需要的新的buffers数量N。
#bgwriter_lru_multiplier是一个乘法因子p，T = N * p;较大的值为峰值提供了缓冲，较小的值则需要服务器进程完成写入。
#如上一个写入周期内服务器使用了100个new buffer（100次写产生不同的脏数据），bgwriter_lru_multiplier使用了默认值2.那么这次会将100*2个dirty buffers写到磁盘中,也就是腾出了200个干净的buffers，#如果再下一次写入周期前，服务器申请的buffers数量小于这个值，则自然够用，如果大于200，那么需要服务器自己去触发将dirty buffers写入磁盘。
bgwriter_lru_multiplier = 10.0 # 0-10.0 multiplier on buffers scanned/round #上面的写入磁盘行为指的是普通意义的向磁盘文件写，但是会被操作系统缓存，所以实际行为可能没有写到磁盘中。
#当超过bgwriter_flush_after bytes的数据被bgwriter写时，强制OS将缓存数据写入物理磁盘。
默认值为512KBbgwriter_flush_after = 512kB # measured in pages, 0 disables 8、检查点相关PostgreSQL将其WAL（预写日志）记录写入缓冲区，然后将这些缓冲区刷新到磁盘。
由wal_buffers定义的缓冲区的默认大小为16MB，但如果有大量并发连接的话，则设置为一个较高的值可以提供更好的性能。
# - Checkpoints - # 不建议频繁做检查点，否则XLOG会产生很多的FULL PAGE WRITE(when full_page_writes=on)。
checkpoint_timeout = 60min # range 30s-1d # 建议等于SHARED BUFFER，或2倍。
# 同时需要考虑崩溃恢复时间,越大,检查点可能拉越长导致崩溃恢复耗时越长.但是越小,开启FPW时WAL日志写入量又越大.建议采用COW文件系统，关闭FPW。
# max_wal_size 公式： # min(shared_buffers*2 , 用户存储空间/10) max_wal_size = 8GB # 建议是SHARED BUFFER的2分之一 # min_wal_size 公式： # min(shared_buffers/2 , 用户存储空间/10) min_wal_size = 2GB # 硬盘好(nvme ssd)的情况下，值越小可以让检查点快速结束，恢复时也可以快速达到一致状态。
否则建议0.5~0.9 # 如果有hot standby作为HA节点，这个值也可以设置为0.5~0.9 避免写高峰时CHECKPOINT对写带来的冲击。
checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0 # IO很好的机器，不需要考虑平滑调度, 否则建议128~256kB checkpoint_flush_after = 256kB # measured in pages, 0 disables #checkpoint_flush_after = 0 # measured in pages, 0 disables #checkpoint_warning = 30s # 0 disables 9、归档相关# - Archiving - # 打开后一个WAL文件写满后,会在pg_wal/archive_status目录中创建xxxxxx.ready的文件# 归档命令archive_command正常结束后，会清除这个状态文件。
archive_mode = on # enables archiving; off, on, or always # 后期再修改，如 'test ! -f /disk1/digoal/arch/%f && cp %p /disk1/digoal/arch/%f' archive_command = '/bin/date' # command to use to archive a logfile segment #archive_timeout = 0 # force a logfile segment switch after this,number of seconds; 0 disables 10、日志相关#------------------------------------------------------------------------------ # REPORTING AND LOGGING #------------------------------------------------------------------------------ # - Where to Log - log_destination = 'csvlog' # Valid values：stderr, csvlog, syslog, and eventlog, # This is used when logging to stderr: logging_collector = on # Enable capturing of stderr and csvlog into log files. # These are only used if logging_collector is on: log_directory = 'log' # directory where log files are written,can be absolute or relative to PGDATA # 时间格式 http://pubs.opengroup.org/onlinepubs/009695399/functions/strftime.html # 设置参考 #RUNTIME-CONFIG-LOGGING-WHERe # 日志保留一天，每个小时一个文件取决于log_rotation_age，每小时切换一下log_filename = 'postgresql-%H.log' # log file name pattern, # 日志保留一个月例子，每天一个文件取决于log_rotation_age 每天切换一下 # log_filename = 'postgresql-%d.log' # log file name pattern, # 日志保留一周例子，每天一个文件取决于log_rotation_age 每天切换一下 # log_filename = 'postgresql-%a.log' # log file name pattern, log_truncate_on_rotation = on # If on, an existing log file with the same name as the new log file will be #truncated rather than appended to. But such truncation only occurs on time-driven rotation, not on restarts or size-driven rotation. # 配合log_filename设置，建议与%后面的名字周期一致log_rotation_age = 1h # Automatic rotation of logfiles will # happen after that time. 0 disables. # 单个日志文件最大多大时，被截断，可以设置一个上限防止日志无限制增长 log_rotation_size = 1GB # 根据实际情况设定，例如业务上认为60秒以上是慢SQL，那么就设置为60秒。
log_min_duration_statement =60s # -1 is disabled, 0 logs all statements # and their durations, > 0 logs only # statements running at least this number # of milliseconds # - What to Log - #debug_print_parse = off #debug_print_rewritten = off #debug_print_plan = off #debug_pretty_print = on # 记录检查点的详细统计信息 log_checkpoints = on # 如果业务是短连接，建议设置为OFF，否则建议设置为ON log_connections = off # 如果业务是短连接，建议设置为OFF，否则建议设置为ON log_disconnections = off #log_duration = off # 记录错误代码的代码位置，是什么代码输出的日志，更好的跟踪问题 log_error_verbosity = verbose # terse, default, or verbose messages #log_hostname = off #log_line_prefix = '%m [%p] ' # special values: # %a = application name # %u = user name # %d = database name # %r = remote host and port # %h = remote host # %p = process ID # %t = timestamp without milliseconds # %m = timestamp with milliseconds # %n = timestamp with milliseconds (as a Unix epoch) # %i = command tag # %e = SQL state # %c = session ID # %l = session line number # %s = session start timestamp # %v = virtual transaction ID # %x = transaction ID (0 if none) # %q = stop here in non-session # processes # %% = '%' # e.g. '<%u%%%d> ' # 是否打印锁等待事件 log_lock_waits = on # log lock waits >= deadlock_timeout # 如果需要审计SQL，则可以设置为all log_statement = 'ddl' # none, ddl, mod, all #log_replication_commands = off # 当使用的临时文件超过多大时，打印到日志中，跟踪大SQL。
log_temp_files = 256MB # log temporary files equal or larger # than the specified size in kilobytes; # -1 disables, 0 logs all temp files log_timezone = 'PRC' 11、垃圾回收相关（慎重）#------------------------------------------------------------------------------ # AUTOVACUUM #------------------------------------------------------------------------------ # 打开自动垃圾回收 autovacuum = on # Enable autovacuum subprocess? 'on' # requires track_counts to also be on. log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and # their durations, > 0 logs only # actions running at least this number # of milliseconds. # CPU核多，并且IO好的情况下，可多点，但是注意最多可能消耗这么多内存： # autovacuum_max_workers * autovacuum mem(autovacuum_work_mem)， # 会消耗较多内存，所以内存也要有基础。
# 当DELETEUPDATE非常频繁时，建议设置多一点，防止膨胀严重 # autovacuum_max_workers 公式： max(min( 8 , CPU核数/2 ) , 5) autovacuum_max_workers = 8 # max number of autovacuum subprocesses # (change requires restart) # 建议不要太高频率，否则会因为vacuum产生较多的XLOG。
或者在某些垃圾回收不掉的情况下(例如长事务、feed back on，等)，导致一直触发vacuum，CPU和IO都会升高 #autovacuum_naptime = 1min # time between autovacuum runs #autovacuum_vacuum_threshold = 50 # min number of row updates before # vacuum #autovacuum_analyze_threshold = 50 # min number of row updates before # analyze autovacuum_vacuum_scale_factor = 0.02 # fraction of table size before vacuum autovacuum_analyze_scale_factor = 0.01 # fraction of table size before analyze # 除了设置较大的FREEZE值。
# 表级定制freeze autovacuum_freeze_max_age = 1200000000 # maximum XID age before forced vacuum # (change requires restart) autovacuum_multixact_freeze_max_age = 1250000000 # maximum multixact age # before forced vacuum # (change requires restart) # 如果数据库UPDATE非常频繁，建议设置为0。
并且建议使用SSD autovacuum_vacuum_cost_delay = 0ms # default vacuum cost delay for # autovacuum, in milliseconds; # -1 means use vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit 觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~