3.pg重要参数解析-平芜编程栈

pg_hba.conf 的配置
- 含义
- - 第一个字段
  - 第二个字段
  - 第三个字段
  - 第四个字段
  - 第五个字段
  - 第六个字段
- 案例
postgresql.conf 的配置
- 配置参数类型
- - internal（只读参数）
  - postmaster
  - sigup
  - backend
  - superuser
  - user
- 查看方式
- 连接配置项
- - 安全认证参数
  - 其他参数
- 数据库日志相关参数
- - 日志切换和是否覆盖的几种方案
  - 日志归档：WAL 归档
- 内存参数的设置
- - shared_buffers
  - work_mem
  - wal_buffers
  - commit_delay
  - commit_siblings
  - effective_cache_size
  - maintenance_work_mem

pg_hba.conf 的配置

类似于 MySQL 中的 userhost 的配置，pg_hba.conf文件是一个黑白名单的访问控制文件，可以控制允许哪些 IP 地址的机器访问数据库。默认创建的数据库无法接受远程连接，因为默认情况下pg_hba.conf中没有相应的配置项。我们可以在pg_hba.conf文件中加入以下命令行：

host all all 0/0 md5

含义

第一个字段

local：这条记录匹配企图通过 Unix 域套接字进行的连接。
host：这条记录匹配企图通过 TCP/IP 进行的连接。
hostssl：这条记录匹配企图使用 TCP/IP 的 SSL 连接。但必须是使用 SSL 加密的连接。要使用这个选项，编译服务器的时候必须打开 SSL 支持。而且在服务器启动的时候必须打开 ssl 配置选项。
hostnossl：这条记录与hostssl行为相反：它只匹配那些在 TCP/IP 上不使用 SSL 的连接请求。
hostgssenc：这条记录匹配企图使用 TCP/IP 建立的连接，但仅当使用 GSSAPI 加密建立连接时。要使用这个选项，服务器必须具备 GSSAPI 支持。

第二个字段

database：声明记录所匹配的数据库名称。值all表明该记录匹配所有数据库。

第三个字段

user：为这条记录声明所匹配的数据库用户。值all表明它匹配于所有用户。

第四个字段

address：声明这条记录匹配的客户端机器地址。可以是localhost、IP、CIDR 地址。
IP-address、IP-mask：这两个域可以被用作IP-address/mask-length记号法的替代方案。和指定掩码长度不同，实际的掩码被指定在一个单独的列中。

第五个字段

auth-method：指定当一个连接匹配这个记录时，要使用的认证方法。常见的有：

trust：无条件地允许链接。
reject：无条件地拒绝链接。
md5：执行 SCRAM-SHA-256 或 MD5 认证来验证用户的口令。
password：要求客户端提供一个未加密的口令进行认证。
ident：通过联系客户端的 ident 服务器获取客户端的操作系统名，并且检查它是否匹配被请求的数据库用户名。Ident 认证只能在 TCP/IP 连接上使用。当为本地连接指定这种认证方式时，将用peer认证来替代。
peer：从操作系统获得客户端的操作系统用户，并且检查它是否匹配被请求的数据库用户名。这只对本地连接可用。

第六个字段

auth-options：在auth-method域的后面，可以是形如name=value的域，它们指定认证方法的选项。

案例

需求：我们假定我们需要本地postgres/root用户可以直连数据库，其他用户不允许直连数据；远程备数据库10.0.0.2需要使用密码认证，用户名为replica，则pg_hba.conf应该设置如下：

# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all ident map=mymap host all all 0.0.0.0/0 md5 # Allow replication connections from localhost, by a user with the # replication privilege. local replication all ident map=mymap host replication replica 10.0.0.2/32 md5

上面的配置中，map表示允许系统和数据库用户名之间的映射，映射关系定义在pg_ident.conf中。

pg_ident.conf配置如下：

# MAPNAME SYSTEM-USERNAME PG-USERNAME mymap root postgres mymap postgres postgres

重新加载配置生效：

/usr/local/bin/pg_ctl reload -D

postgresql.conf 的配置

配置参数类型

internal（只读参数）

这些参数不配置在postgresql.conf中，他们由 postgres 程序和在初始化实例时写死。

postmaster

这些参数的值需要重启 PostgreSQL 实例。在postgresql.conf文件中改变这些参数后，需要重启 PostgreSQL 实例才能生效。

sigup

在postgresql.conf中改变参数值，不需要重启数据库；只需要向数据库 postmaster 进程发送 SIGHUP 信号。

backend

在postgresql.conf中改变参数值，不需要重启数据库；只需要向数据库 postmaster 进程发送 SIGHUP 信号，但是新的配置值只会在之后新的连接中有效，在已有的连接中不生效。

superuser

需要超级用户使用set来改变；超级用户改变此参数数值时，只会影响自身的 session 配置，不会影响其他用户。向 postmaster 进程发送 SIGHUP 信号，也只会影响后续建的连接。

user

普通用户可以使用set命令来改变。

查看方式

所有配置参数都在系统视图pg_settings中：

SELECTenumvalsFROMpg_settingsWHEREname='client_min_messages';

通过pg_settings表中的context字段来查看参数类型：

SELECTname,contextFROMpg_settingsWHEREnameLIKE'wal_buffers';

连接配置项

listen_addresses：声明服务器监听客户端连接的 TCP/IP 地址，改变这个参数需要重启数据库服务。如果主机有多个 IP，用逗号隔开，PostgreSQL 服务在多个 IP 地址上监听。当配置为0.0.0.0或*，表示监听本机的所有 IP。默认值是localhost，表示只允许本地使用 loopback 连接到数据库，其他机器无法连接到。
port：指定服务器监听的 TCP 端口，默认为5432。改变这个参数需要重启服务器。同一个端口用于服务器监听的所有 IP 地址。
max_connections：允许数据库连接的最大并发连接数，改变此参数需要重启数据库服务，默认100。
superuser_reserved_connections：为 PostgreSQL 超级用户连接而保留的连接数，默认值为3。改变此参数需要重启。注意：这个值必须小于max_connections。

max_connections - superuser_reserved_connections = 普通用户最大连接数

安全认证参数

ssl = off ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers ssl_prefer_server_ciphers = on ssl_ecdh_curve = 'prime256v1' ssl_dh_params_file = '' ssl_cert_file = 'server.crt' ssl_key_file = 'server.key' ssl_ca_file = ''

其他参数

unix_socket_group = '' unix_socket_permissions = 0777 # 设置 Unix-domain socket 的访问权限 tcp_keepalives_idle = 0 # 用 OS 的 sysctl 中的 tcp_keepalive_time 的值 tcp_keepalives_interval # TCP 心跳包的频率，防止连接被网络设备中断 tcp_keepalives_count # 发送 TCP 心跳包的次数，超过该值断开连接

数据库日志相关参数

logging_collector = on # 日志的收集一般是需要打开的 log_directory = 'pg_log' # 日志的目录一般使用默认值即可

日志切换和是否覆盖的几种方案

方案一：每天生成一个新的日志文件

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_truncate_on_rotation = off log_rotation_age = 1d log_rotation_size = 0

方案二：每当日志写满一定的大小（如 10MB），则切换一个日志

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_truncate_on_rotation = off log_rotation_age = 0 log_rotation_size = 10M

方案三：只保留最近 7 天的日志，进行循环覆盖

log_filename = 'postgresql-%a.log' log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 0

日志归档：WAL 归档

为什么要归档？
所谓把 WAL 日志归档，其实就是把在线的已写完的 WAL 日志复制出来。

在 PostgreSQL 中配置归档的方法是在配置文件postgresql.conf中配置参数archive_mode和archive_command，archive_command的配置值是一个 UNIX 命令，此命令把 WAL 日志文档复制到其他地方。

案例：

archive_mode = on archive_command = 'cp %p /backup/pgarch/%f'

%p表示在线 WAL 日志文件的全路径名。
%f表示不包括路径的 WAL 日志文件名。

在实际执行备份时，PostgreSQL 会把%p替换成实际的在线 WAL 日志文件的全路径名，并把%f替换成不包括路径的 WAL 日志名。

内存参数的设置

shared_buffers

参数含义：共享内存的大小，数据库共享内存缓冲区大小，决定有多少内存可以被 PostgreSQL 用于缓存数据，该缓冲区为所有连接共用。
参数优化：服务器内存较小时设置为服务器内存的 25%-40%，内存较大时可适当设置。

work_mem

参数含义：单个 SQL 执行时，以及排序、HashJoin 时使用的内存，SQL 运行完毕后，该内存就会被释放。
参数优化：全局设置此参数可能会导致内存使用率过高，即当同时有多个会话进行排序操作时，每个会话会分配到这个参数大小的内存。设置不宜过大。如果要使用语句中有较大的排序操作，可以在会话级别设置该参数（如：set work_mem = '2GB'）。

wal_buffers

参数含义：WAL（预写日志）记录写入的缓冲区的大小。PostgreSQL 将其 WAL 记录写入缓冲区，然后将这些缓冲区刷新到磁盘。
参数优化：当单事务的数据修改量很大，产生的日志大于 wal_buffers 时，可适当调大该值。当有比较多的并发短事务时，可与参数commit_delay及commit_siblings连用，并适当调大该值。

commit_delay

参数含义：事务提交后，日志写到 wal_buffer 上到 wal_buffer 写到磁盘的时间间隔。
参数优化：当有比较多的并发短事务时，可以适当增加该值，使日志缓冲区一次刷盘可以刷出较多的事务，减少 IO 次数，提高性能。需要和commit_siblings配合使用。

commit_siblings

参数含义：触发commit_delay等待的并发事务数，即系统的并发活跃事务数达到了该值，事务才会等待commit_delay的时间后才将日志刷新到磁盘。
参数优化：若系统中并发活跃事务达不到该值，commit_delay将不起作用，为防止在系统并发压力较小的情况下事务提交后空等其他事务，不宜设置过大。

effective_cache_size

参数含义：除操作系统本身和其他应用程序可用的内存外，期望操作系统和数据库本身可用于缓存数据的内存大小。和shared_buffer等内存无关，只是给优化器生成计划使用的一个假设值。
参数优化：如果设置有误会影响优化器的判断，得出不合理的执行计划。建议的设置为可用空闲内存的 25%。

maintenance_work_mem

参数含义：执行 vacuum、create index、alter table add foreign key、restoring database dumps 等操作时可使用的内存大小。
参数优化：由于这类操作并发数不会很大，增大此参数相对较为安全，如希望提升这些操作的性能，可适当加大此参数。

3.pg重要参数解析

目录