乐趣区

关于postgresql:PostgreSQL开发与实战3参数整理

作者:太阳

引言

在治理和配置 PostgreSQL 数据库时,理解参数的分类和理解如何查看和更改这些参数是至关重要的。参数的分类波及到不同的失效形式,如外部参数、须要重启失效的参数、不须要重启而能够从新加载配置文件失效的参数等。本文将介绍各类参数的分类及其在 PostgreSQL 中的作用。

一、参数的分类

参数的类型名称 阐明
internal 外部参数,只读无奈批改。postgres 程序写死或者是在初始化指定后无奈批改的参数
postmaster 更改该类参数,需重启失效
sighup 不需重启,从新加载配置文件即可失效,且会对以后所有过程失效
backend 不需重启,从新加载配置文件即可失效,但只对新连贯失效
superuser 该类参数可由超级用户应用 set 命令来扭转,只影响本身 session 配置,不影响其它会话;从新加载配置文件后只对新连贯失效
user 该类参数可由普通用户能够应用 set 命令来扭转,只影响本身 session 配置,不影响其它会话;从新加载配置文件后只对新连贯失效

二、查看与更改参数的形式

1、pg_settings 视图

所有的配置参数都在零碎视图 pg_settings 中,该视图各字段含意如下,其中 context 字段用于记录参数的类型:

name:运行时配置参数名
setting:参数的以后值
unit:存储参数的单位,如 ms
category:参数的逻辑组
short_desc:参数的简短形容
extra_desc:附加的参数的详细描述
context:用于记录参数的类型,可依据该值判断参数批改是否需重启失效
vartype:参数类型 (bool, enum, integer, real, or string)
source:以后参数值的起源
min_val:参数的最小允许值(对非数字值为空)
max_val:参数的最大允许值(对非数字值为空)
enumvals:用于存储参数的可取值(对非数字值为空)
boot_val:如果参数没有被别的其余设置,此列为在服务器启动时设定的参数值
reset_val:在以后会话中,RESET 将会设置的参数值
sourcefile:以后值被设置的配置文件(空值示意从非配置文件的其余起源设置,由不是超级用户也不是 pg_read_all_settings 成员的用户查看时也为空值),在配置文件中应用 include 指令时有用
sourceline:以后值被设置的配置文件中的行号(空值示意从非配置文件的其余起源设置,由不是超级用户也不是 pg_read_all_settings 成员的用户查看时也为空值)。
pending_restart:如果配置文件中批改了该值但须要重启,则为 true,否则为 false

2、查看参数

show xxx; 或 select current_setting('xxx');

3、批改参数

批改以后 session 的参数配置:set maintenance_work_mem to '128M'; 或 select set_config('maintenance_work_mem','128M',false);

ALTER SYSTEM 动静批改全局参数:ALTER SYSTEM SET configuration_parameter {TO | =} {value | 'value' | DEFAULT}
将参数从 postgresql.auto.conf 文件中移除
ALTER SYSTEM RESET configuration_parameter
将所有参数从 postgresql.auto.conf 文件中移除
ALTER SYSTEM RESET ALL
阐明:ALTER SYSTEM 会把给出的参数 设置写入到 postgresql.auto.conf 文件中,该文件会随着 postgresql.conf 一起被读入。ALTER SYSTEM RESET 能够把参数从 postgresql.auto.conf 文件中移除。应用 RESET ALL 能够移除所有这类配置项。用 ALTER SYSTEM 设置的值将在从新加载配置文件后失效(用 pg_ctl reload 或 select pg_reload_conf();),那些只能在服务器启动时更改的参数则会在下一次服务器重启后失效。

4、令批改后的参数失效

1)从新加载配置文件
对于不需重启失效的参数,可在操作系统层执行 pg_ctl reload 或 psql 中执行 select pg_reload_conf(); 从新加载配置文件令参数的批改失效。

2)重启失效
局部参数需重启失效,可查看 pg_settings 视图的 pending_restart 字段,如果为 true 示意需重启失效。

三、常见的各类参数

1、监听连贯相干

参数 含意
listen_addresses server 端对客户端的 Tcp/ip 监听地址,默认为 ”localhost” 示意仅容许本地通过嵌套字连贯,”*” 示意监听所有 IP,即容许所有 IP 拜访
port 服务器监听 TCP 端口,默认 5432
max_connections server 端容许最大连接数,默认 100
superuser_reserved_connections Server 端为超级账号保留的连接数,默认 3
unix_socket_directory Server 监听客户端 Unix 嵌套字目录,默认 /tmp

2、内存相干

参数 含意
shared_buffer 共享内存缓存区大小,默认 128MB
temp_buffers 每个会话应用的长期缓存区大小,默认 8MB
work_mem 内存长期表排序操作或者 hash 须要应用到的内存缓存大小,默认 4MB
maintenance_work_mem 对于维护性操作(vacuum、create index)最大应用内存,默认 64M,最小 1M。
max_stack_depth Server 端执行堆栈最大平安深度,默认 2M,若发现无奈执行简单函数时可适当调整该参数

3、谬误日志相干

参数 含意
logging_collector 是否关上日志
log_rotation_age 超过多少天生产一个新的日志文件
log_rotation_size 超过多少大小生成一个新的日志文件
log_directory 日志目录,能够是绝对路径或绝对 PGDATA 的相对路径
log_destination 日志记录类型,默认是 stderr,只记录谬误输入
log_filename 日志文件名,默认是 postgresql-%Y-%m-%d_%H%M%S.log
log_truncate_on_rotation 当日志名已存在时,是否笼罩原文件

4、慢 SQL 相干

参数 含意
log_statement 记录语句类型,none 示意不开启,ddl 标记仅仅记录 DDL 语句,all 示意全副语句均记录
log_min_duration_statement 单位 ms,记录执行超过多少 ms 的语句

5、主从以及 WAL 相干

参数 含意
wal_level 可配置 minimal、replica、logical,此配置决定多少信息写入 wal 日志中,更改参数需重启失效。minimal,只写入在数据库解体会忽然关机进行复原时所须要的信息;设置为 replica,会增加一些备库只读查问时须要的信息,须要流复制物理备库、归档、工夫点复原时,需设置为 replica;须要逻辑订阅或逻辑备库则设置为 logical,logical 减少反对逻辑解码所需的信息
fsync 是否应用 fsync()将内存中的脏页刷新到物理磁盘,默认为 ON
synchronous_commit 事务提交是否须要期待对应的 wal 日志刷盘,默认为 ON;复制一致性要求模式,可设置 remote_apply、on(默认值)remote_write、local、和 off
wal_sync_method wal 日志刷盘形式
full_page_write 当关上该参数时,当在检查点之后第一次批改一个页面时,整个页面都被写入 wal,防止数据库解体复原时数据页损坏
wal_log_hints 在 full_page_writes 的根底上,即便数据页产生了一些不重要的更改,也会将整个页面写入到 wal 日志。若数据库应用 pg_rewind,须要开启该参数或者关上 data checksums
wal_compression 当开启 full_page_writes 参数或者进行根底备份时,是否对 wal 日志进行压缩,默认为 off
wal_buffer 数据库变更操作首先会写入 wal buffer,而后 wal buffer 刷盘写入到 wal log,个别可设置为 wal log 的 1 /32,但不倡议过小或者过大,个别设置为 16MB。
wal_writer_delay wal buffer 进行刷盘从工夫维度上的频率,默认 200ms。若间隔上一次刷盘工夫小于 wal_writer_delay 且容量小于 wal_writer_flush_after,wal 仅仅会写入 os,并不会进行刷盘
wal_writer_flush_after wal buffer 进行刷盘从容量上的频率,默认为 8kb。若间隔上一次刷盘工夫小于 wal_writer_delay 且容量小于 wal_writer_flush_after,wal 仅仅会写入 os,并不会进行刷盘
max_wal_size 在两次 checkpoint 期间 wal 日志增长最大限度,该参数的限度属于软限度。当 archive 异样、standby 复制异样、重负载、wal_keep_segments 参数设置过大时,该参数可能会进行扩大。该参数设置过大会影响数据解体复原的工夫
min_wal_size 当 wal 日志空间占用小于该参数时,wal 日志会被进行复用而不会被删除,该参数可确保批处理操作时有足够的 wal 空间进行存储,默认为 80MB
wal_keep_size 指定在 pg_wal 目录中保留的 wal segment file 的最小大小,以便流复制环境中的 standby server 从主库中 fetch wal file;单位 MB
wal_sender_timeout 发送方(主库)walsender 过程的超时工夫,如果存在超过这个工夫、进行流动的复制连贯,会被中断,默认以毫秒为单位,默认 60s
wal_receiver_timeout 接管方(从库)walreceiver 过程的超时工夫,如果存在超过这个工夫、进行流动的复制连贯,会被中断,默认以毫秒为单位,默认 60s

6、复制相干

参数 含意
max_wal_senders 数据库启动 wal 发送过程的最大并发数(备用服务器复制或者根底备份)
max_replication_slots 数据库可创立的复制槽个数
wal_keep_segments 为保障备用服务器复制落后时,主数据库仍保留所需的 wal 日志的 wal 段大小
synchronous_standby_names PG 默认采纳异步复制,如要配置同步复制,需将 synchronous_standby_names 设置为非空,synchronous_commit 设置为 ON
官网阐明:http://postgres.cn/docs/13/runtime-config-replication.html#GU…
hot_standby standby 实例是否可进行查问
wal_sender_timeout 中断那些进行流动超过指定毫秒数的复制连贯。这对发送服务器检测一个后备机解体或网络中断有用。设置为 0 将禁用该超时机制, 默认为 60 秒
wal_receiver_timeout 从 master 实例接管 wal 的最大工夫限度,终止处于非活动状态的复制连贯。设置为 0 会禁用超时机制,默认值是 60 秒
hot_standby_feedback 当开启该参数时,备用服务器执行大查问或者大事物期间,主数据库进行相应的 vacuum 操作时,保留备用服务所需的版本记录。若备份服务器长时间存在大查问或者大事物,会导致对应表收缩

7、超时相干

参数 含意
deadlock_timeout 超过该参数的锁才会进行死锁检测,默认 1s
lock_timeout 锁期待超时工夫, 默认为 0 示意始终期待
statement_timeout 管制语句执行时长,单位是 ms。超过设定值,该语句将被终止
idle_in_transaction_session_timeout 终止开启事务但闲暇工夫超过指定持续时间(以毫秒为单位)的任何会话,默认为 0 示意不开启该性能
checkpoint_timeout 两次检查点之间的最长工夫距离,单位是秒
archive_timeout 强制数据库切换新的 wal 日志的工夫距离,默认为 0,示意禁用,个别 1min 设置是比拟正当的
authentication_timeout 实现服务器认证的最长工夫,如果在这个工夫内没有实现认证,服务器将敞开连贯, 默认 1min
wal_sender_timeout 中断那些进行流动超过指定毫秒数的复制连贯。这对发送服务器检测一个后备机解体或网络中断有用。设置为 0 将禁用该超时机制, 默认为 60 秒。
wal_receiver_timeout 从 master 实例接管 wal 的最大工夫限度,终止处于非活动状态的复制连贯。设置为 0 会禁用超时机制,默认值是 60 秒。

8、vacuum 相干

参数 含意
vacuum_cost_delay 超出老本计数后强制休眠工夫,该参数不倡议设置过大
vacuum_cost_page_hit vacuum 操作命中 shared buffer 的代价耗费
vacuum_cost_page_miss vacuum 操作未命中 shared buffer 的代价耗费
vacuum_cost_page_dirty vacuum 操作未命中 shared buffer 后,须要将对应的 dirty 页刷盘的代价耗费
vacuum_cost_limit 须要强制休眠的代价限度

9、异步相干

参数 含意
effective_io_concurrency 数据库容许会话级别进行并发磁盘 I / O 操作的数量
maintenance_io_concurrency 数据库保护操作容许发动的并发磁盘 I / O 操作数量
max_worker_processes 数据库反对最大后盾过程数,默认为 8,若调整改参数须要对应的调整以下参数:max_parallel_workers、max_parallel_maintenance_workers 和 max_parallel_workers_per_gather
max_parallel_workers_per_gather 并行查问最大工作过程数,过程资源从 max_worker_processes 中获取,并受限于 max_parallel_workers
max_parallel_maintenance_workers 数据库进行保护操作的最大并行过程数,过程资源从 max_worker_processes 中获取,并受限于 max_parallel_workers
max_parallel_workers 数据库反对最大并行工作过程

更多技术信息请查看云掣官网 https://yunche.pro/?t=yrgw

退出移动版