云贝教育 |「技术文章」PostgreSQL配置优化(一)[通俗易懂]

大家好，欢迎来到IT知识分享网。

作者：崔鹏

01

参数默认值与重置

视图pg_settings提供了对服务器上运行时参数的访问。它本质上是SHOW和SET命令的可替换接口。

02

更改上下文信息

对于context有多种可能的取值。为了降低改变设置的难度，它们是：

• internal

这些设置不能被直接修改，它们反映了内部决定的值。某些可能在使用不同配置选项重建系统时或者改变initdb的选项时可以调整。

• postmaster

这些设置只能在服务器启动时应用，因此任何修改都需要重启服务器。这些设置的值通常都存储在postgresql.conf文件中，或者在启动服务器时通过命令行传递。当然，具有更低context类型的设置也可以在服务器启动时间被设置。

• sighup

对于这些设置的修改可以在postgresql.conf中完成并且不需要重启服务器。发送一个SIGHUP信号给postmaster会导致它重新读取postgresql.conf并应用修改。Postmaster将会把SIGHUP信号传递给它的孩子进程，这样它们也会获得新的值。

• superuser-backend

对于这些设置的更改可以在postgresql.conf中进 行而无需重启服务器。也可以在连接请求包（例如通过libpq 的PGOPTIONS环境变量）中为一个特定的会话设定它们，但是 只有在连接用户是超级用户时才能这样做。如果，在会话启动后这些设置就 不会改变。如果你在postgresql.conf改变了它们， 向 postmaster 发送一个SIGHUP信号让 postmaster 重新读取postgresql.conf。新的值将 只会影响后续启动的会话。

• backend

对于这些设置的修改可以在postgresql.conf中完成并且不需要重启服务器。它们也可以在一个连接请求包（例如，通过libpq的PGOPTIONS环境变量）中为一个特定会话设置 ，任何用户都可以为这个会话做这种修改。然而，这些设置在会话启动后永不变化。如果你在postgresql.conf中修改它们，可以向postmaster发送一个SIGHUP信号让它重读postgresql.conf。新值只会影响后续启动的会话。

• superuser

这些设置可以从postgresql.conf设置，或者在会话中用SET命令设置。仅当没有通过SET设置会话本地值时，postgresql.conf中的改变才会影响现有的会话。

• user

这些设置可以从postgresql.conf设置，或者在会话中用SET命令设置。任何用户都被允许修改它们的会话本地值。仅当没有通过SET设置会话本地值时，postgresql.conf中的改变才会影响现有的会话。

03

reload配置文件

select pg_reload_conf();
pg_ctl reload

IT知识分享网

04

数据库连接

• listen_addresses (string)

指定服务器在哪些 TCP/IP 地址上监听客户端连接。值的形式是一个逗号分隔的主机名和/或数字 IP 地址列表。特殊项*对应所有可用 IP 接口。项0.0.0.0允许监听所有 IPv4 地址并且::允许监听所有 IPv6 地址。如果列表为空，服务器将根本不会监听任何 IP 接口，在这种情况中只能使用 Unix 域套接字来连接它。默认值是localhost，它只允许建立本地 TCP/IP “环回”连接。虽然客户端认证（第 20 章）允许细粒度地控制谁能访问服务器，listen_addresses控制哪些接口接受连接尝试，这能帮助在不安全网络接口上阻止重复的恶意连接请求。这个参数只能在服务器启动时设置。

• port (integer)

服务器监听的 TCP 端口；默认是 5432 。请注意服务器会同一个端口号监听所有的 IP 地址。这个参数只能在服务器启动时设置。

• max_connections (integer)

决定数据库的最大并发连接数。默认值通常是 100 个连接，但是如果内核设置不支持（initdb时决定），可能会比这个 数少。这个参数只能在服务器启动时设置。

当运行一个后备服务器时，你必须设置这个参数等于或大于主服务器上的参数。否则，后备服务器上可能无法允许查询。

• superuser_reserved_connections (integer)

决定为PostgreSQL超级用户连接而保留的连接“槽”数。同时活跃的并发连接最多max_connections个。任何时候，活跃的并发连接数最多为max_connections减去 superuser_reserved_connections，新连接就只能由超级用户发起了，并且不会有新的复制连接被接受。

默认值是 3 连接 。这个值必须小于max_connections。这个参数只能在服务器启动时设置。

05

共享内存

shared_buffers (integer)

设置数据库服务器将使用的共享内存缓冲区量。默认通常是 128 兆字节（128MB），但是如果你的内核设置不支持（在initdb时决定），那么可以会更少。这个设置必须至少为 128 千字节。不过为了更好的性能，通常会使用明显高于最小值的设置。如果指定值时没有单位，则以块为单位，即BLCKSZ字节，通常为8kB.(BLCKSZ 的非默认值改变最小值。) 此参数只能在服务器启动时设置。

如果有一个专用的 1GB 或更多内存的数据库服务器，一个合理的shared_buffers开始值是系统内存的 25%。即使更大的shared_buffers有效，也会造成一些工作负载， 但因为PostgreSQL同样依赖操作系统的高速缓冲区，将shared_buffers设置为超过 40% 的RAM不太可能比一个小点值工作得更好。为了能把对写大量新的或改变的数据的处理分布在一个较长的时间段内，shared_buffers更大的设置通常要求对max_wal_size也做相应增加。

如果系统内存小于 1GB，一个较小的 RAM 百分数是合适的，这样可以为操作系统留下足够的空间。

06

日志记录

IT知识分享网运行日志路径默认在目录$PGDATA/log下
日志配置参数文件
logging_collector     --是否开启日志收集开关，默认off，开启要重启DB
log_destination           --日志记录类型，默认是stderr，只记录错误输出
log_directory             --日志路径，默认是$PGDATA/pg_log
log_filename             --日志名称，默认是postgresql-%Y-%m-%d_%H%M%S.log
log_connections           --用户session登陆时是否写入日志，默认off
log_disconnections          --用户session退出时是否写入日志，默认off
log_rotation_age          --保留单个文件的最大时长,默认是1d,也有1h,1min,1s
log_rotation_size         --保留单个文件的最大尺寸，默认是10MB
log_line_prefix  = ‘%m %p %u %d %r ’
#日志输出格式
           #   %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
                #   %i = command tag

log_statement 配置

1.用于记录用户登陆数据库后的各种操作，通过参数pg_statement来控制

2.默认的pg_statement参数值是none，即不记录

3.PG日志里分成了3类，可以设置ddl(记录create,drop和alter)、mod(记录ddl+insert,delete,update和truncate)和all(mod+select)

示例：
vi $PGDATA/postgresql.conf 
log_statement = ddl
或者
alter system set log_statement = ddl;
select pg_reload_conf();
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0600


log_truncate_on_rotation = o

当logging_collector被启用时，这个参数将导致PostgreSQL截断（覆盖而不是追加）任何已有的同名日志文件。不过，截断只在一个新文件由于基于时间的轮转被打开时发生，在服务器启动或基于尺寸的轮转时不会发生。如果被关闭，在所有情况下以前存在的文件将被追加。例如，使用这个设置和一个类似postgresql-%H.log的log_filename将导致产生 24 个每小时的日志文件，并且循环地覆盖它们。这个参数只能在postgresql.conf文件中或在服务器命令行上设置。

例子：要保留 7 天的日志，每天的一个日志文件被命令为server_log.Mon、server_log.Tue等等，并且自动用本周的日志覆盖上一周的日志。可以这样做：将log_filename设置为server_log.%a、将log_truncate_on_rotation设置为on并且将log_rotation_age设置为1440。

• log_rotation_age = 1d

当logging_collector被启用时，这个参数决定使用一个单个日志文件的最大时间量，之后将创立一个新的日志文件。如果指定值时没有单位，则以分钟为单位。默认为24小时。将这个参数设置为零将禁用基于时间的新日志文件创建。这个参数只能在postgresql.conf文件中或在服务器命令行上设置。

• log_rotation_size = 10MB

当logging_collector被启用时，这个参数决定一个个体日志文件的最大尺寸。当这些数据量被发送到一个日志文件后，将创建一个新的日志文件。如果指定值的时候没有单位，则以千字节为单位。默认值是10兆字节。设置为零时将禁用基于大小创建新的日志文件。这个参数只能在postgresql.conf文件中或在服务器命令行上设置。

*PostgreSQL配置优化(二)

持续更新中…