CNDBA社区
在之前的博客，我们了解的PG的相关信息，如下：
PostgreSQL 学习笔记（1） — PG 概述
https://www.cndba.cn/dave/article/116370
PostgreSQL 学习笔记（2） — PG 版本发布策略 和 生命周期说明
https://www.cndba.cn/dave/article/116372
PostgreSQL 学习笔记（3） — PG 单实例安装手册
https://www.cndba.cn/dave/article/116374
PostgreSQL 学习笔记（4） — PG 启动 与 关闭
https://www.cndba.cn/dave/article/116375
PostgreSQL 学习笔记（5） — psql 工具使用说明
https://www.cndba.cn/dave/article/116376
本篇我们看下PG的参数管理。
1 PG 参数文件说明
PG 实例创建后，在PGDATA 目录下会创建相关的目录，可以直接进目录查看，也可以通过pg_settings 视图查看：
cndba=# select name, setting,source from pg_settings where category='File Locations' ;
name | setting | source
-------------------+----------------------------------------+----------
config_file | /var/lib/pgsql/14/data/postgresql.conf | override
data_directory | /var/lib/pgsql/14/data | override
external_pid_file | | default
hba_file | /var/lib/pgsql/14/data/pg_hba.conf | override
ident_file | /var/lib/pgsql/14/data/pg_ident.conf | override
(5 rows)
cndba=#
1） pg_hba.conf：客户端认证配置文件，主要用于控制连接数据库的相关信息，比如主机类型、数据库名、用户名、IP地址等信息。
2） pg_ident.conf： ident认证配置文件，在pg_hba.conf里面的ident认证方式，就是在pg_ident.conf中配置。
3） postgresql.conf ：数据库相关配置，该文件包含内存分配、日志文件未知、监听端口、监听地址、数据库数据目录等一些数据库通用配置。也通过pg_setting查看参数的值：
cndba=# select name, context, unit, setting, boot_val, reset_val from pg_settings where name in ('listen_address','max_connetctons','shared_buffers','effective_cache_size','work_mem','maintenance_work_mem') order by context, name;
name | context | unit | setting | boot_val | reset_val
----------------------+------------+------+---------+----------+-----------
shared_buffers | postmaster | 8kB | 16384 | 1024 | 16384
effective_cache_size | user | 8kB | 524288 | 524288 | 524288
maintenance_work_mem | user | kB | 65536 | 65536 | 65536
work_mem | user | kB | 4096 | 4096 | 4096
(4 rows)
cndba-#
字段说明:
a. context: 设置为postmaster，更改此形参后需要重启PostgreSQL服务才能生效；设置为user，那么只需要执行一次重新加载即可全局生效。重启数据库服务会终止活动连接，但重新加载不会。
b. unit : 字段表示这些设置的单位;
c. setting:是指当前设置；
d. boot_val:是指默认设置；
e. reset_val:是指重新启动服务器或重新加载设置之后的新设置;
在postgresql.conf中修改了设置后，一定记得查看一下setting和reset_val并确保二者是一致，否则说明设置并未生效，需要重新启动服务器或者重新加载设置。
2 postgresql.conf详解
2.1 文件位置(FILE LOCATION)
# 这些变量的默认值由-D命令行选项或PGDATA环境变量驱动,这里表示为ConfigDir.
#data_directory = 'ConfigDir' # 使用其他目录中的数据(更改需要重新启动PG数据库)
#hba_file = 'ConfigDir/pg_hba.conf' # 基于主机的认证文件(更改需要重新启动PG数据库)
#ident_file = 'ConfigDir/pg_ident.conf' # 标识配置文件(更改需要重新启动PG数据库)
# 如果未显式设置外部PID文件,则不会写入额外的PID文件.
#external_pid_file = '' # 写一个额外的PID文件(更改需要重新启动PG数据库)
查看参数配置
show data_directory
select name,setting from pg_setting where name='data_directory'
2.2 连接和验证（CONNECTIONS AND AUTHENTICATION）
2.2.1 连接设置（Connection Settings）
#默认情况下,只允许登录了数据库的用户执行本地连接. 若想要任何远程的安装程序进行连接.则需要修改listen_addresses配置参数. 修改为='*',表示允许并接受任何地方传入的连接请求.
listen_addresses = '*' # 监听哪个IP地址;以逗号分隔的地址列表.默认监听"localhost",(更改需要重新启动PG数据库)
port = 5678 # PG服务监听端口号-默认端口5432.(更改需要重新启动PG数据库)
#每个客户端连接都会占用很小一部分的"共享内存",系统有限的共享内存默认是不允许过多的连接的. 该参数不能设置得过大,会浪费"共享内存".
max_connections = 100 # 最大连接数(更改需要重新启动PG数据库)
#superuser_reserved_connections = 3 #(更改需要重新启动PG数据库)
#unix_socket_directories = '/tmp' #逗号分隔的目录列表(更改需要重新启动PG数据库)
#unix_socket_group = '' # (更改需要重新启动PG数据库)
#unix_socket_permissions = 0777 # 从0开始使用八进制记数法(更改需要重新启动PG数据库)
#bonjour = off # 通过Bonjour发布服务器(更改需要重新启动PG数据库)
#bonjour_name = '' # 默认为计算机名(更改需要重新启动PG数据库)
# - TCP Keepalives -
# see "man 7 tcp" for details
#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds(秒); 0-选择系统默认值
#tcp_keepalives_interval= 0 # TCP_KEEPINTVL, in seconds(秒);0-选择系统默认值
#tcp_keepalives_count = 0 # TCP_KEEPCNT;0-选择系统默认值
2.2.2 认证（Authentication）
#authentication_timeout = 1min # 1s-600s
#password_encryption = md5 # md5 or scram-sha-256
#db_user_namespace = off
# GSSAPI using Kerberos(使用kerberos的gssapi)
#krb_server_keyfile = ''
#krb_caseins_users = off
2.2.3 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.3 资源使用（RESOURCE USAGE (except WAL)）
2.3.1 内存（Memory）
# 共享内存,服务器使用共享内存的主要部分是分配给缓存块的大型块.用于读取或是写入数据库.
# 预估共享内存大小请参考:https://www.postgresql.org/docs/current/kernel-resources.html
shared_buffers = 128MB # 最小128kB(更改需要重新启动PG数据库)
#huge_pages = try # on, off, or try(更改需要重新启动PG数据库)
#temp_buffers = 8MB # 最小800kB
#max_prepared_transactions= 0 # 0-表示禁用该功能(更改需要重新启动PG数据库);注意:不建议将max_prepared_transactions设置为非零, 除非你打算用已经准备好的事务
#work_mem = 4MB # 最小64kB.可以限制用于排序内存的大小,该值在客户端连接之后可以增加,该类型分配使用的是"非共享内存"
#maintenance_work_mem = 64MB # 最小1MB
#autovacuum_work_mem = -1 # 最小1MB, or -1 to use maintenance_work_mem
#max_stack_depth = 2MB # 最小100kB
dynamic_shared_memory_type = posix #默认值是操作系统支持的第一个选项:posix,sysv,windows,mmap;使用none禁用动态共享内存
2.3.2 磁盘（Disk）
#temp_file_limit = -1 # 每个进程的临时文件空间限制(以KB为单位).如果没有限制,则为-1
2.3.3 内核资源（Kernel Resources）
#max_files_per_process = 1000 # 最小25(更改需要重新启动PG数据库)
2.3.4 基于成本的真空延迟（ Cost-Based Vacuum Delay）
#vacuum_cost_delay = 0 # 0-100 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 1-10000 credits
2.3.5 后台写入（Background Writer）
#bgwriter_delay = 200ms # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100 # max buffers written/round, 0 disables
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multiplier on buffers scanned/round
#bgwriter_flush_after = 512kB # 以页计算,0-禁用
2.3.6 异步行为（Asynchronous Behavior）
#effective_io_concurrency = 1 # 1-1000; 0-禁用预取
#max_worker_processes = 8 # (更改需要重新启动PG数据库生效)
#max_parallel_maintenance_workers = 2 # 取自max_parallel_workers
#max_parallel_workers_per_gather = 2 # 取自max_parallel_workers
#parallel_leader_participation = on
#max_parallel_workers = 8 # 可以在并行操作中使用的max_worker_processes的最大数量
#old_snapshot_threshold = -1 # 1min-60d; -1:禁用 0:立刻(更改需要重新启动PG数据库生效)
#backend_flush_after = 0 # 以页为单位测量,0-禁用
2.4 预写日志（WRITE-AHEAD LOG）
2.4.1 设置（Settings）
#wal_level = replica # 最小、副本或逻辑(更改需要重新启动PG数据库生效)
#fsync = on # 将数据刷新到磁盘以确保崩溃安全(关闭此功能可能导致不可恢复的数据损坏)
#synchronous_commit = on # 同步等级: off, local, remote_write, remote_apply, or on
#wal_sync_method = fsync # 默认是操作系统支持的第一个选项:open_datasync, fdatasync (Linux默认),fsync,fsync_writethrough,
open_sync
#full_page_writes = on # 从部分页面写恢复
#wal_compression = off # 启用整页写的压缩
#wal_log_hints = off # 也做整个页写的非关键的更新(更改需要重新启动PG数据库生效)
#用于控制缓存预写式日志数据的内存大小
#wal_buffers = -1 # 最小32kB, -1:基于shared_buffers的设置(更改需要重新启动PG数据库生效)
#wal_writer_delay = 200ms # 1-10000 milliseconds
#wal_writer_flush_after = 1MB # 以页计算, 0-禁用
#commit_delay = 0 # range 0-100000, 以微妙为单位
#commit_siblings = 5 # range 1-1000
2.4.2 检查点（Checkpoints）
/*
*若用户的系统速度赶不上写数据的速度,则可以适当提高该值.默认为5分钟。
*/
#checkpoint_timeout = 5min # range 30s-1d
max_wal_size = 1GB
min_wal_size = 80MB
#checkpoint_completion_target = 0.5 # 检查点目标持续时间, 0.0 - 1.0
#checkpoint_flush_after = 256kB # 以页计算, 0-禁用
#checkpoint_warning = 30s # 0-禁用
2.4.3 存档（Archiving）
#archive_mode = off # 启用存档-enables;关闭-off,打开-on 或始终-always (更改需要重新启动PG数据库生效)
#archive_command = '' # 用于存档日志文件段占位符的命令:%p =文件路径到存档;%f =文件名.e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0 # 在此秒数后强制执行日志文件段切换;0-禁用
2.5 复制（REPLICATION）
2.5.1 发送服务器（Sending Servers）
# 将它们设置在主服务器和任何将发送复制数据的备用服务器上.
#max_wal_senders = 10 # 最大walsender进程数.(更改需要重新启动PG数据库生效)
#wal_keep_segments = 0 # 在日志文件段中;0-禁用
#wal_sender_timeout = 60s # 以毫秒为单位;0-禁用
#max_replication_slots = 10 # 复制槽的最大数目(更改需要重新启动PG数据库生效)
#track_commit_timestamp = off # 收集事务提交的时间戳(更改需要重新启动PG数据库生效)
2.5.2 主服务器（Master Server）
# 这些设置在备用服务器上被忽略.
#synchronous_standby_names = '' # 提供sync rep方法的备用服务器,用于选择同步备用服务器,
#同步备用服务器的数量和备用服务器中的application_name的逗号分隔列表;‘*’=all
#vacuum_defer_cleanup_age = 0 # 延迟清理的xact数
2.5.3 备用服务器（Standby Servers）
# 在主服务器上忽略这些设置.
#hot_standby = on # "off"不允许在恢复期间进行查询(更改需要重新启动PG数据库生效)
#max_standby_archive_delay = 30s # 从存档读取wal时取消查询之前的最大延迟;-1允许无限延迟
#max_standby_streaming_delay = 30s # 读取流wal时取消查询之前的最大延迟;-1允许无限延迟
#wal_receiver_status_interval = 10s # 至少要经常回复 0-禁用
#hot_standby_feedback = off # 从备用服务器发送信息以防止查询冲突
#wal_receiver_timeout = 60s # 接收方等待主方通信的时间(毫秒);0-禁用
#wal_retrieve_retry_interval = 5s # 在尝试失败后重新尝试检索WAL之前，需要等待的时间
2.5.4 订阅者（Subscribers）
# 在发布服务器上这些设置将被忽略
#max_logical_replication_workers = 4 # 取自max_worker_processes(更改需要重新启动PG数据库生效)
#max_sync_workers_per_subscription = 2 # 取自max_logical_replication_workers
2.6 查询调优（QUERY TUNING）
2.6.1 计划方法配置（Planner Method Configuration）
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_indexonlyscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_parallel_append = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on
#enable_partitionwise_join = off
#enable_partitionwise_aggregate = off
#enable_parallel_hash = on
#enable_partition_pruning = on
2.6.2 计划成本常量（Planner Cost Constants）
#seq_page_cost = 1.0 # 在任意比例上测量
#random_page_cost = 4.0 # 同上量表
#cpu_tuple_cost = 0.01 # 同上量表
#cpu_index_tuple_cost = 0.005 # 同上量表
#cpu_operator_cost = 0.0025 # 同上量表
#parallel_tuple_cost = 0.1 # 同上量表
#parallel_setup_cost = 1000.0 # 同上量表
#jit_above_cost = 100000 #如果可用,执行JIT编译并查询比这更昂贵的开销.-1:禁用
#jit_inline_above_cost = 500000 # 如果查询的开销大于此值,则内联小函数.-1:将禁用
#jit_optimize_above_cost = 500000 # 如果查询的开销大于此值,则使用昂贵的JIT优化;-1将禁用
#min_parallel_table_scan_size = 8MB
#min_parallel_index_scan_size = 512kB
#effective_cache_size = 4GB
##2.6.3 查询优化器（Genetic Query Optimizer）
#geqo = on
#geqo_threshold = 12
#geqo_effort = 5 # range 1-10
#geqo_pool_size = 0 # selects default based on effort
#geqo_generations = 0 # selects default based on effort
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_seed = 0.0 # range 0.0-1.0
2.6.4 其他计划选项（Other Planner Options）
/* 备注：为了注释的属性简洁,这里的注释用了C/C++中的注释语法，若是postgresql.conf文件中，则应该用"#"号
* PostgreSQL根据数据库中每个表的统计情况来决定如何执行查询.这些信息通过“ANALYZE”或是“autovacuum”等
* 步骤来获得,任一情况下，在分析任务期间所获得的信息量由default_statistics_target设置. 加大该值会延长
* 分析时间.
*/
#default_statistics_target = 100 # range 1-10000
#constraint_exclusion = partition # on, off, or partition
#cursor_tuple_fraction = 0.1 # range 0.0-1.0
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1:禁用显式联接子句的折叠
#force_parallel_mode = off
#jit = off
2.7 报告和记录（REPORTING AND LOGGING）
2.7.1 记录位置（Where to Log）
#log_destination = 'stderr' # 1有效值是stderr、csvlog、syslog和eventlog的组合,具体取决于平台.
#csvlog要求日志采集器处于打开状态.
# 这在登录到stderr时使用
#logging_collector = off # 启用将stderr和csvlog捕获到日志文件中.CSVLogs需要打开(更改需要重新启动PG数据库生效)
# 这些仅在logging_collector为on状态时候使用.
#log_directory = 'log' # 写入日志文件的目录,可以是绝对的,也可以是相对于PGDATA的
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # 日志文件名模式,可以包含strftime()转义
#log_file_mode = 0600 # 日志文件的创建模式,从0开始使用八进制表示法
#log_truncate_on_rotation = off # 如果ON,与新日志文件具有相同名称的现有日志文件将被截断而不是附加到.但这种截断只发生在时间驱动的旋转上,
#而不是在重新启动或大小驱动的旋转上.默认为"关闭",意味着在所有情况下都追加到现有文件.
#log_rotation_age = 1d # 日志文件的自动循环将在该时间之后发生.0-禁用.
#log_rotation_size = 10MB # 日志文件的自动循环将在日志输出这么多之后发生.0-禁用
# These are relevant when logging to syslog:(登录到syslog时,这些都是相关的)
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
#syslog_sequence_numbers = on
#syslog_split_messages = on
#:这仅在登录到eventlog(win32)时才相关(更改需要重新启动PG数据库生效)
#event_source = 'PostgreSQL'
2.7.2 何时记录（When to Log）
#log_min_messages = warning # 按细节降序排列的值:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic
#log_min_error_statement = error # 按细节降序排列的值:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic (effectively off)
#log_min_duration_statement = -1 # -1被禁用,0记录所有语句及其持续时间,>0只记录至少运行此毫秒数的语句
2.7.3 记录什么（What to Log）
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_error_verbosity = default # terse, default, or verbose messages(简洁、默认或详细的消息)
#log_hostname = off
#log_line_prefix = '%m [%p] ' # 特素值:
# %a = application name-应用程序名称
# %u = user name-用户名
# %d = database name-数据库名称
# %r = remote host and port-远程主机和端口
# %h = remote host-远程主机
# %p = process ID-进程ID
# %t = timestamp without milliseconds-不带毫秒的时间戳
# %m = timestamp with milliseconds-毫秒时间戳
# %n = timestamp with milliseconds (as a Unix epoch)-时间戳(以毫秒计)(作为Unix纪元)
# %i = command tag-命令标记
# %e = SQL state-SQL状态
# %c = session ID-会话ID
# %l = session line number-会话行号
# %s = session start timestamp-会话开始时间戳
# %v = virtual transaction ID-虚拟事务ID
# %x = transaction ID (0 if none)-事务ID(如果没有，则为0)
# %q = stop here in non-session-processes -在非会话进程中此处停止
# %% = '%'
# e.g. '<%u%%%d> '
#log_lock_waits = off # 日志锁等待 >= deadlock_timeout
# log_statement可选值范围:none(不记录任何语句级的日志信息), ddl(只记录数据定义语言语句,如:CREATE,DROP),
# mod(记录修改了值的语句), all(记录每一条语句,不要轻易使用该选项,日志的写操作会对系统带来巨大的开销)
#log_statement = 'none' # none, ddl, mod, all
#log_replication_commands = off
#log_temp_files = -1 # 日志临时文件等于或大于指定的大小(以千字节为单位);-1禁用，0记录所有临时文件
log_timezone = 'PRC'
2.8 流程标题（PROCESS TITLE）
#cluster_name = '' # 如果非空，则添加到进程标题(更改需要重新启动PG数据库生效)
#update_process_title = on
2.9 统计数据（STATISTICS）
2.9.1 查询和索引统计信息收集器（Query and Index Statistics Collector）
#track_activities = on
#track_counts = on
#track_io_timing = off
#track_functions = none # none, pl, all
#track_activity_query_size = 1024 # (change requires restart)
#stats_temp_directory = 'pg_stat_tmp'
2.9.2 监控（Monitoring）
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off
2.10 自动清理（AUTOVACUUM）
//从PostgreSQL 8.1开始,便提供了autovacuum守护进程,在后台执行日志的自动清理功能.
#autovacuum = on #
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
#autovacuum_max_workers = 3 # 自动清理的最大子进程数量(更改需重启PG数据库生效)
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # 清理前的最小行更新数量
#autovacuum_analyze_threshold = 50 # 分析前的最小行更新数
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
#autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age
# before forced vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms # 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
2.11 客户端连接默认值（CLIENT CONNECTION DEFAULTS）
2.11.1 声明行为（Statement Behavior）
#client_min_messages = notice # 按细节降序排列的值:
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error
#search_path = '"$user", public' # schema names
#row_security = on
#default_tablespace = '' # a tablespace name, '' uses the default
#temp_tablespaces = '' # a list of tablespace names, '' uses
# only default tablespace
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#default_transaction_deferrable = off
#session_replication_role = 'origin'
#statement_timeout = 0 # in milliseconds, 0 is disabled
#lock_timeout = 0 # in milliseconds, 0 is disabled
#idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
#vacuum_multixact_freeze_min_age = 5000000
#vacuum_multixact_freeze_table_age = 150000000
#vacuum_cleanup_index_scale_factor = 0.1 # fraction of total number of tuples
# before index cleanup, 0 always performs
# index cleanup
#bytea_output = 'hex' # hex, escape
#xmlbinary = 'base64'
#xmloption = 'content'
#gin_fuzzy_search_limit = 0
#gin_pending_list_limit = 4MB
2.11.2 语言环境和格式（Locale and Formatting）
datestyle = 'iso, ymd'
#intervalstyle = 'postgres'
timezone = 'PRC'
#timezone_abbreviations = 'Default' # Select the set of available time zone
# abbreviations. Currently, there are
# Default
# Australia (historical usage)
# India
# You can create your own file in
# share/timezonesets/.
#extra_float_digits = 0 # min -15, max 3
#client_encoding = sql_ascii # actually, defaults to database
# encoding
# These settings are initialized by initdb, but they can be changed.
lc_messages = 'zh_CN.UTF-8' # locale for system error message
# strings
lc_monetary = 'zh_CN.UTF-8' # locale for monetary formatting
lc_numeric = 'zh_CN.UTF-8' # locale for number formatting
lc_time = 'zh_CN.UTF-8' # locale for time formatting
# default configuration for text search
default_text_search_config = 'pg_catalog.simple'
2.11.3 共享库预加载（Shared Library Preloading）
#shared_preload_libraries = '' # (change requires restart)
#local_preload_libraries = ''
#session_preload_libraries = ''
#jit_provider = 'llvmjit' # JIT library to use
2.12 其他默认值（Other Defaults ）
#dynamic_library_path = '$libdir'
2.13 锁管理（LOCK MANAGEMENT）
#deadlock_timeout = 1s
#max_locks_per_transaction = 64 # min 10(更改需要重新启动PG数据库生效)
#max_pred_locks_per_transaction = 64 # min 10(更改需要重新启动PG数据库生效)
#max_pred_locks_per_relation = -2 # 负值平均值(max_pred_locks_per_transaction / -max_pred_locks_per_relation) - 1
#max_pred_locks_per_page = 2 # min 0
2.14 版本和平台兼容性（VERSION AND PLATFORM COMPATIBILITY）
2.14.1 以前的PostgreSQL版本（Previous PostgreSQL Versions）
#array_nulls = on
#backslash_quote = safe_encoding # on, off, or safe_encoding
#default_with_oids = off
#escape_string_warning = on
#lo_compat_privileges = off
#operator_precedence_warning = off
#quote_all_identifiers = off
#standard_conforming_strings = on
#synchronize_seqscans = on
2.14.2 其他平台和客户（Other Platforms and Client）
#transform_null_equals = off
2.15 错误处理（ERROR HANDLING）
#exit_on_error = off # 出现任何错误时终止会话？
#restart_after_crash = on # 后端崩溃后重新初始化？
#data_sync_retry = off # fsync数据失败时重试或死机？(更改需要重新启动PG数据库生效)
2.16 配置文件包括（CONFIG FILE INCLUDES）
# 这些选项允许从默认postgresql.conf以外的文件加载设置.
#include_dir = '' # 包括目录中以".conf"结尾的文件,例如"conf.d"
#include_if_exists = '' # 仅在存在时才包含文件
#include = '' # 包含文件
2.17 自定义选项
# Add settings for extensions here(在此处添加扩展设置)
3 修改参数文件
3.1 查看当前的参数值
show all; #查看所有数据库参数的值
show shared_buffers; #查看某个参数的当前值(可查看当前会话值)
select current_setting('shared_buffers');
postgres=# show all;
postgres=# select current_setting('shared_buffers');
current_setting
-----------------
128MB
(1 row)
postgres=# show shared_buffers;
shared_buffers
----------------
128MB
(1 row)
postgres=# /x
Expanded display is on.
postgres=# select * from pg_settings where name in ('shared_buffers');
-[ RECORD 1 ]---+-------------------------------------------------------------
name | shared_buffers
setting | 16384
unit | 8kB
category | Resource Usage / Memory
short_desc | Sets the number of shared memory buffers used by the server.
extra_desc |
context | postmaster
vartype | integer
source | configuration file
min_val | 16
max_val | 1073741823
enumvals |
boot_val | 1024
reset_val | 16384
sourcefile | /var/lib/pgsql/14/data/postgresql.conf
sourceline | 127
pending_restart | f
postgres=#
我们这里要注意参数的context，即配置生效级别。 可以查看官方手册的pg_settings视图定义，主要有如下几种类型：
- Sihup：给服务器发送HUP信号会使服务器重新加载postgresql.conf配置，可以立即生效
- Postmaster：只有服务重启才能生效
- Internal：编译期间的设置，只有重新编译才能生效。
- Backend：与sighup类似，但是不影响正在运行的会话，只在新会话中生效
- Superuser：使用superuser(如postgres)才能更改，不用重新加载所有配置即可生效
- User：单个会话用户可以在任意时间做修改，只会影响该会话
我们这里查询的shared_buffers就是postmaster，即需要重启数据库才能生效。
3.2 修改参数值
3.2.1 postgresql.conf 与 postgresql.auto.conf 说明
PostgreSQL在 9.4及以上版本还提供两个配置文件：postgresql.conf和 postgresql.auto.conf。
PostgreSQL中也可以适用ALTER SYSTEM 命令修改数据库参数并被持久化。当执行 ALTER SYSTEM … 命令时，改变会被自动写入 postgresql.auto.conf 文件，而不是 postgresql.conf文件。通过这种方法，即使几个月或几年之后，也能看到参数修改变化。
[dave@www.cndba.cn ~]# cat /var/lib/pgsql/14/data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
[dave@www.cndba.cn ~]#
当数据库服务重启时，会先加载postgresql.auto.conf 配置文件。当在两个文件中都存在相同参数，以postgresql.conf文件为准。
3.2.2 全局修改参数
postgres=# select current_setting('shared_buffers');
-[ RECORD 1 ]---+------
current_setting | 128MB
postgres=# alter system set shared_buffers='150MB';
ALTER SYSTEM
postgres=# select current_setting('shared_buffers');
-[ RECORD 1 ]---+------
current_setting | 128MB
postgres=#
[dave@www.cndba.cn ~]# cat /var/lib/pgsql/14/data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
shared_buffers = '150MB'
[dave@www.cndba.cn ~]#
3.2.3 修改会话级参数
使用set命令,在会话层修改,修改之后将被用于未来的每一个事务,只对当前会话有效
postgres=# show work_mem;
-[ RECORD 1 ]-
work_mem | 4MB
postgres=# set work_mem='16MB';
SET
postgres=# show work_mem;
-[ RECORD 1 ]--
work_mem | 16MB
postgres=# set session work_mem='32MB';
SET
postgres=# show work_mem;
-[ RECORD 1 ]--
work_mem | 32MB
postgres=#
3.2.4 添加local关键字
只在当前事务中修改,只在当前事务内有效:
postgres=# set local work_mem='16MB';
WARNING: SET LOCAL can only be used in transaction blocks
SET
postgres=# begin;
BEGIN
postgres=*# set local work_mem='16MB';
SET
postgres=*# show work_mem;
-[ RECORD 1 ]--
work_mem | 16MB
postgres=*# commit;
COMMIT
postgres=# show work_mem;
-[ RECORD 1 ]--
work_mem | 32MB
postgres=#
3.2.5 reset回复默认值
postgres=# show work_mem;
-[ RECORD 1 ]--
work_mem | 32MB
postgres=# reset work_mem;
RESET
postgres=# show work_mem;
-[ RECORD 1 ]-
work_mem | 4MB
# reset all回复所有默认值
3.2.6 为特定的数据库里的所有的用户设置参数
为cndba数据库所有的连接设置work_mem为16MB:
postgres=# alter database cndba set work_mem='16MB';
ALTER DATABASE
3.2.7 为数据库中的某个特定用户设置参数
为dave用户,设置work_mem为2MB:
postgres=# alter role dave set work_mem='2MB';
ALTER ROLE
3.3 参数生效
3.3.1 加载参数
$ pg_ctl -D $PGDATA reload
或者
-bash-4.2$ psql -c "select pg_reload_conf()"
pg_reload_conf
----------------
t
(1 row)
这里如果是不需要重启的，参数就直接生效了。
3.3.2 查询 pg_setting 判断是否要重启
-bash-4.2$ psql -c "select name,setting,pending_restart from pg_settings where name='shared_buffers'"
name | setting | pending_restart
----------------+---------+-----------------
shared_buffers | 16384 | t
(1 row)
-bash4.2$
这里如果是t(true)则表示需要重启，如果时f（false）则不不需要。这里在查看之前，必须先加载参数，否则无效。
我们这里参数并没有生效，并且提示需要重启，我们重启库：
-bash-4.2$ pg_ctl -D $PGDATA restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2022-08-09 05:02:23.582 CST [3559] LOG: redirecting log output to logging collector process
2022-08-09 05:02:23.582 CST [3559] HINT: Future log output will appear in directory "log".
done
server started
-bash-4.2$
重启之后参数生效：
-bash-4.2$ psql -c "select name,setting,pending_restart from pg_settings where name='shared_buffers'"
name | setting | pending_restart
----------------+---------+-----------------
shared_buffers | 19200 | f
(1 row)
-bash-4.2$
版权声明：本文为博主原创文章，未经博主允许不得转载。
