深度解析PostgreSQL参数

发表时间: 2021-09-11 20:35

PostgreSQL 的几个 timeout 参数

archive_timeout：控制服务器周期性地切换到一个新的 WAL 段文件，通俗的讲，就是定时归档。

authentication_timeout：完成服务器认证的最长时间，如果在这个时间内没有完成认证，服务器将关闭连接。

checkpoint_timeout：自动 WAL 检查点之间的最长时间，增大这个参数会使数据库崩溃后恢复的时间加长。

deadlock_timeout：进行死锁检测之前在一个锁上等待的总时间

idle_in_transaction_session_timeout：空闲事务超时。终止任何已经闲置超过这个参数所指定的时间（以毫秒计）的打开事务的会话。这使得该会话所持有的任何锁被释放，并且其所持有的连接槽可以被重用，它也允许只对这个事务可见的元组被清理。

lock_timeout：锁等待超时。语句在试图获取表、索引、行或其他数据库对象上的锁时等到超过指定的毫秒数，该语句将被中止。不推荐在postgresql.conf中设置。

statement_timeout：控制语句执行时长，单位是ms。超过设定值，该语句将被中止。
不推荐在postgresql.conf中设置，如非要设置，应该设置一个较大值。

wal_receiver_timeout：中止处于非活动状态超过指定毫秒数的复制链接。这对于正在接收的后备服务器检测主服务器崩溃或网络断开有用。设置为0会禁用超时机制。这个参数只能在postgresql.conf文件中或在服务器命令行上设置。默认值是 60 秒。

wal_sender_timeout：中断那些停止活动超过指定毫秒数的复制连接。这对发送服务器检测一个后备机崩溃或网络中断有用。设置为0将禁用该超时机制。这个参数只能在postgresql.conf文件中或在服务器命令行上设置。默认值是 60 秒。

PostgreSQL的pg_hba.conf文件讲解

该配置文件有5个参数，分别为：TYPE（主机类型）、DATABASE（数据库名）、USER（用户名）、ADDRESS（IP地址和掩码）、METHOD（加密方法）

TYPE有4个值：

local：使用Unix-domainsocket

host：使用TCP/IP连接，可以是SSL的，也可以不是

hostssl：必须是SSL的

hostnossl：必须是非SSL的

DATABASE：

数据库名，可以是"all", "sameuser", "samerole", "replication"。all表示所有，但不包括replication。多个数据库用“,”隔开。

USER：

用户名，可以为"all"，表示所有，也可以具体指定一个用户。多个用户用“,”隔开。和DATABASE一样，也可以将配置放到文件中，文件名加上前缀@

ADDRESS：

可以是为一个主机名，或者由IP地址和CIDR掩码组成。掩码可以为0-32（IPv4）或者0-128（IPv6）间的一个整数，32表示子网掩码为255.255.255.255，24表示子网掩码为255.255.255.0。主机名以“.”开头。samehost可以匹配所有主机、samenet可以匹配同一个掩码内的所有主机。

例：192.168.10.122/32表示单一主机，192.168.10.0/24表示192.168.0.1~192.168.0.255网段内所有主机，0.0.0.0/0表示所有主机。

METHOD：

密码加密策略，password表示以明文方式发送密码，md5和scram-sha-256会以对应的方式加密再发送密码。

PostgreSQL 审计日志配置

logging_collector --是否开启日志收集开关，默认off，推荐on

log_destination --日志记录类型，默认是stderr，只记录错误输出，推荐csvlog，总共包含：stderr, csvlog, syslog, and eventlog,

log_directory --日志路径，默认是$PGDATA/pg_log,

log_filename --日志名称，默认是
postgresql-%Y-%m-%d_%H%M%S.log

log_file_mode --日志文件类型，默认为0600

log_truncate_on_rotation --默认为off，设置为on的话，文件内容覆盖方式：off后面附加，on：清空再加

log_rotation_age --保留单个文件的最大时长,默认是1d,也有1h,1min,1s

log_rotation_size --保留单个文件的最大尺寸，默认是10MB

log_error_verbosity --默认为default，verbose表示冗长的

log_connections --用户session登陆时是否写入日志，默认off，推荐为on

log_disconnections --用户session退出时是否写入日志，默认off，推荐为on

log_statement --记录用户登陆数据库后的各种操作

none，即不记录
ddl(记录create,drop和alter)
mod(记录ddl+insert,delete,update和truncate)
all(mod+select)

log_min_duration_statement = 2s --记录超过2秒的SQLlog_checkpoints = onlog_lock_waits ＝ ondeadlock_timeout ＝ 1s

2、推荐的设置参数

logging_collector = onlog_destination = 'csvlog'log_truncate_on_rotation = onlog_connections = onlog_disconnections = onlog_error_verbosity = verboselog_statement = ddllog_min_duration_statement = 60slog_checkpoints = onlog_lock_waits ＝ ondeadlock_timeout ＝ 1s

红色标记的是postgresql.conf文件中没有的

查看日志目录和日志文件名：

show log_directory;show log_filename;

3、参数修改方法

直接修改配置文件

postgresql.conf默认位于$PGDATA目录下。

vi /usr/data/pgsql/data/postgresql.conf

用超级用户运行：postgres=# SELECT pg_reload_conf();

show命令可以查询当前状态