一、pg_hba.conf的配置¶

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

host all all 0/0 md5

1.1 含义¶

第一个字段

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 声明这条记录匹配的客户端机器地址。可以是 localost, IP，CIDR 地址。

第四个字段

IP-address IP-mask 这两个域可以被用作IP-address/ mask-length记号法的替代方案。和指定掩码长度不同，实际的掩码被指定在一个单独的列中。

第五个字段

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

1. trust：无条件允许连接，无需密码
2. reject：无条件拒绝连接
3. md5：使用 SCRAM-SHA-256 或 MD5 认证，密码加密传输
4. password：要求客户端使用明文密码认证
5. ident：通过客户端 ident 服务获取系统用户名，仅 TCP/IP 连接可用
6. peer：获取操作系统用户进行匹配，仅本地连接可用

第六个字段

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

1.2 案例¶

需求：我们假定我们需要本地 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

reload生效

/usr/local/bin/pg_ctl reload -D

二、postgresql.conf的配置¶

2.1 配置参数类型¶

2.1.1 internal只读参数¶

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

2.1.2 postmaster¶

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

2.1.3 sigup¶

2.1.4 backend¶

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

新的配置值只会在之后新的连接中有效，在已有的连接中不生效。

2.1.5 superuser¶

需要超级用户使用set来改变；超级用户改变此参数数值时，只会影响自身的session配置，不会影响其他用户。

2.1.6 user¶

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

2.1.7 查看方式¶

1、所有配置参数都在系统试图pg_settings中

select enumvals from pg_settings where name='client_min_messages';

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

select name,context from pg_settings where name like 'wal_buffers';

2.2 连接配置项¶

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_group = ''
unix_socket_permissions:默认：unix_socket_permissions = 0777设置Unix-domain socket的访问权限
tcp_keepalives_idle ：默认tcp_keepalives_idle = 0 用OS的sysctl中的tcp_keepalive_time的值
tcp_keepalives_interval：TCP心跳包的频率，防止连接被网络设备中断
tcp_keepalives_count：发送TCP心跳包的次数，超过该值断开连接

2.3 数据库日志相关参数¶

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日志名

2.4 内存参数的设置¶

2.4.1 shared_buffers¶

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

2.4.2 work_mem¶

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

2.4.3 wal_buffers¶

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

2.4.4 commit_delay¶

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

2.4.5 commit_siblings¶

参数含义：触发commit_delay等待的并发事务数，即系统的并发活跃事务数达到了该值，事务才会等待 commit_delay的时间后才将日志刷新到磁盘。

参数优化：若系统中并发活跃事务达不到该值，commit_delay将不起作用，为防止在系统并发压力较小的情况下事务提交后空等其他事务，不宜设置过大。

2.4.6 effective_cache_size¶

参数含义：除操作系统本身和其他应用程序可用的内存外，期望操作系统和数据库本身可用于缓存数据的内存大小。和shared_buffer等内存无关，只是给优化器生成计划使用的一个假设值。

参数优化：如果设置有误会影响优化器的判断,得出不合理的执行计划。建议的设置为可用空闲内存的25%。

2.4.7 maintenance_work_mem¶

参数含义：执行vacuum ,create index,alter table add foreign key,restoring database dumps等操作时可使用的内存大小。

参数优化：由于这类操作并发数不会很大，增大此参数相对较为安全，如希望提升这些操作的性能,可适当加大此参数。

PostgreSQL 核心配置入门：pg_hba.conf 与 postgresql.conf