三个很重要的配置文件：postgresql.conf，pg_hba.conf和pg_ident.conf

mahan · 发表于 2023-8-13 15:52:34

PG数据库管理
配置文件
首先要告诉你的就是这个配置文件正在postgresql数据库配置文件data目录下
postgresql.conf
在9.4的版本里引入了一个新的名为postgresql.auto.conf的配置文件，其中配置项会覆盖postgresql.conf的同名配置项。所以建议不要修改postgresql.conf，而是优先修改postgresql.auto.conf
该文件中包含一些通用的设置，比如内存分配，新建database的默认存储位置，postgresql服务器的ip地址，日志以及许多其他设置。

查看postgresql.conf视图即可查看所有配置项内容，无需打开配置文件。
SELECT
name,context,unit,setting,boot_val,reset_val
FROM pg_settings
WHERE name IN ('listen_addressed','deadlock_timeout','shared_buffers','effective_cache_size','work_mem','maintenance_work_mem')
ORDER BY context,name;

登录/注册后可看大图

4224864d88ac9480f3.png (498.8 KB, 下载次数: 222)

下载附件

2023-8-13 15:48 上传

context字段代表配置项的作用范围。
user表示用户级配置项，他可以被每个用户单独修改，也就是说该配置项针对每个用户都可以有不同的值，用户修改后会在自己的所有会话中生效。如果是超级用户修改了一个user级的配置项，那么此后链接上的用户都会将这个修改过的值作为默认值。
context值为superuser表示是超级用户级配置项，只能由超级用户修改，修改并且重新加载后会在所有用户会话中生效。非超级用户不能在自己的会话中修改覆盖这个值。
context值为postmaster表示是整个服务实例级配置项，更改后需要重启postgresSQL服务才能生效。
context为usr和superuser的配置项可以在database级，用户级，会话级和函数级分别设置。比如说如果会写很长sql的用户来说，work_mem参数应该设置大一些；再比如有密集的排序操作，也就可以调大work_mem的值。database级，用户级，会话级和函数级的参数设置不需要执行重新加载操作。数据库级的参数设置会在用户下次连接到该数据库时生效。会话级和函数级的参数设置立即生效。

我们要注意内存相关参数所用的单位。在上图我们可以看到，内存相关参数，有些是8KB有些是KB

setting是指当前设置；boot_val是指默认设置；reset_val是指重新启动服务器或重新加载设置之后的新设置。修改了设置后，一定要去查看setting和reset_val并确保二者是一致的，否则就说明设置并未生效，需要重启服务器或者重新加载设置。

pg的9.5版本引入了一个新的pg_file_settings视图，通过该视图也可以进行配置信息查询。查询该视图会列出每个配置项所属的配置文件。其中applied字段表示该配置项是否已经生效，如果值为f，表示需要重启服务器或者重新加载配置文件。如果postgresql.conf和postgresql.auto.conf中存在同名配置，那么后者会覆盖前者，并且前者在pg_file_settings中对应的条目会显示applied字段为f。

SELECT name,sourcefile,sourceline,setting,applied
FROM pg_file_settings
WHERE name IN ('listen_addresses','deadlock_timeout','shared_buffers','effective_cache_size','work_mem','maintenance_work_mem')
ORDER BY name;

登录/注册后可看大图

1712364d88afaea45f.png (393.2 KB, 下载次数: 194)

下载附件

2023-8-13 15:49 上传

以下几个配置项需要注意，不然可能导致客户端无法连接：

listen_addresses

表示postgresql服务使用的ip地址，我这里设置的*，表示使用任意的ip均可连接到POSTgresql数据库。

port

pg库的侦听端口，默认值为5432.

max_connections

系统允许的最大并发连接数

log_destination

定义日志文件的输出格式，默认值是stderr。如果是要记日志的话切记要讲logging_collection配置项设为on。
下面介绍的这些配置项会影响系统的整体性能，其默认值一般不是最优的。建议根据实际情况进行调整。

shared_buffers

此设置定义了用于缓存最近访问过的数据页的内存区大小，所有用户会话均可共享此缓存区。此设置对查询速度有着重大影响，一般来说设置的越大越好，至少应该达到系统的总内存的25% ，但不适宜超过8GB，因为超过会出现边际收益递减效应，即消耗的内存很多，但得到的速度提升却很少，这是得不偿失的，意义不大的。

effective_cache_size

该配置是一个估算值，表示操作系统分配多少内存给PG专用。系统并不会根据这个值来真实地分配这么多内存，但是规划器会根据这个值来判断系统是否能提供查询执行过程所需的内存。如果将此值设置的过小，远远小于系统的真实可用内存量，那么可能会给规划器造成误导，让规划器认为系统可用内存有限，从而选择不使用索引而是执行全表扫描（因为使用索引虽然速度快，但需要占用更多的中间内存）。在一台专用于运行pgsql数据库服务的机器上，建议是将effective_cache_size的值设为系统总内存的一般或者更多。

work_mem

此设置指定了用于执行排序，散列关联，表扫描等操作的最大内存大小。要得到此设置的最优值需要考虑以下因素：数据库的使用方式，需要预留多少内存给除数据库系统外的程序，以及服务器是否专用于pgsql服务等问题。如果使用场景仅仅是有很多用户并发执行简单查询，那么这个值可以设得小一点，这样每个用户都得以较为公平的使用内存，否则第一个用户就可能会把内存占光。这个值该设多大同样取决于你的机器总共有多少内存可用

maintenance_work_mem

此设置指定了可用于vaccum(即清空已标记为“被删除”状态的记录)这类系统内部维护操作的内存总量。其值不应大于1GB，此设置的更改可动态生效，执行重新加载既可。

max_parallel_workers_per_gather

这是9.6版本新引入的一个配置项，用于控制语句执行的并行度。该配置项决定了执行计划的每个gather节点中最多允许启动多少个worker进程并行工作。默认值为0，表示不启用并行功能。

修改postgresql.conf中配置项的值

ALTER SYSTEM

这是9.4版本之后引入的一个命令，这个命令可以更改设置。
例如：设置一个全局生效的work_mem：
ALTER SYSTEM work_mem = '500MB';

这个命令呢是不会直接修改postgresql.conf文件本身，而是去修改postgresql.auto.conf文件。
每个设置有何各自不同的特性，有的更改后必须重启数据库服务才能生效，有的只要重新加载一次就可以了，下面这个命令可以实现设置重新加载：
SELECT pg_reload_conf();

如果你需要时常修改很多配置项，那么可以尝试将他们分门别类存放到多个配置文件中，然后通过在postgresql.conf中使用include或者include_if_exists前缀来引入这些配置文件。具体语法如下：
include '配置文件名'

这里的配置文件可以是绝对路径也可以是相对路径，相对路径的起始位置就是postgresql.conf文件本身所在的位置。

修改了postgresql。conf文件，结果数据库无法启动，该如何？
首要的解决方式为：日志文件，该文件位于postgresql数据文件夹的更目录或者pg_log子文件下。去查看日志文件的最后一行。常见的错误有：shared_buffers设得太大了。还有一个常见问题是由于上次系统异常关闭导致遗留了一个没来得及删除的postmaster.pid文件，该文件处于数据文件下，你可以手动删除该文件并启动postgresql。

pg_hba.conf
该文件用于控制Postgresql服务器的访问权限，具体包括：允许哪些用户连接到哪个数据库，允许哪些ip地址连接到本服务器，以及指定连接时使用的身份验证模式。该文件的修改是可动态生效的，执行一次配置重加载既可。一个典型的pg_hba.conf文件如下图所示：

登录/注册后可看大图

2438364d88b20b7300.png (346.75 KB, 下载次数: 184)

下载附件

2023-8-13 15:49 上传

1️ 身份验证模式。一般有以下几种常用选项：ident，trust，MD5，peer以及passwd。
2️ 用于定义ipv6网段。轧制油服务器支持ipv6才可以配置该项，如果在非ipv6网络环境下配置了这样的条目，会导致pg_hba.conf文件无法加载，从而进一步导致任何客户端都无法连接。
3️ 用于定义ipv4网段。第一部分是网络地址，后面跟着子网掩码。
4️ 这是针对SSL连接的规则。在本图中，任何使用SSL方式连接的客户端都可以连接到PGSQL实例上去。
SSL相关配置在postgresql.conf和postgresql.auto.conf中，包含一下几项：ssl，ssl_cert_file,ssl_key_file。一旦确认客户端支持SSL，postgresql服务端就会接受其连接请求，并且该连接上的所有传输内容都会使用ssl key加密。
5️ 这是允许与本节点构成复制关系的其他Postgresql服务器节点的IP网段。
对于每一个连接请求，就像防火墙一样，这玩意是从上到下匹配规则链的。
PostgreSQL10 中引入了一个新的名为pg_hba_file_rules的视图，通过查询该视图可以直接看到pg_hba.conf中的内容。

修改pg_hba.conf文件，结果服务器奔溃了、
don't害怕，因为如果postgres服务无法正确的解析pg_hba.conf文件，那么为确保系统安全，他会禁止所有的连接请求甚至是禁止系统启动。你去看一手日志，然后将错误的内荣改正就好，如果你经常会改错，那就事先记得备份一手。

身份验证方法
最常用的身份验证方法有以下这些：

trust
这是最不安全的身份验证模式，用户无需提供密码就可以连接到数据库，只要源端IP地址，连接用户名，要访问的databases名都与该条规则匹配，用户就可以连上来。trust模式很不安全，因此要有所限制。

MD5
该模式很常用，要求连接发起者携带MD5算法加密的密码。

password
该模式要求连接发起者携带明文密码进行身份验证。

ident
该身份验证模式下，系统会将请求发起者的操作系统用户映射为postgresSQL数据库内部用户，并以该内部用户的权限登陆，且此时无需提供登陆密码。Windows上是不支持ident验证方式。

peer
该模式下系统会直接从操作系统内核获取当前连接发起者的操作系统用户名，如果与其请求连接的postgresql用户名一致，即可连接成功。该模式仅可用于Linux，BSD，macOS和Solaris，并且仅可用于本地服务器发起的连接。

cert
该模式要求客户端必须使用SSL方式进行连接。连接发起者必须提供一个合法的SSL做证书。该模式使用一个身份认证文件来讲SSL证书文件映射为postgresql数据库的内部用户，该模式支持只在所有支持SSL连接的平台上都可用。

pg_ident.conf
如果该文件存在，则系统会基于文件内容将当前登录的操作系统用户映射为一个postgreSQL数据库内部用户的身份来登录。有些人会把操作系统的root用户映射为postgreSQL的postgres超级用户账号。

在PG的官方术语体系中，角色role就表示用户，但并不是所有的角色都需要具备登录权限，比如组角色通常就不需要。

如果你在安装过程中使用了默认配置，则上述的文件会位于Postgresql主数据文件夹中。当然，如果你不确定这些文件的具体位置，以超级用户身份连接到任何一个数据库上并执行下面的查询语句就会查到：
SELECT name ,setting FROM pg_settings WHERE category='File Locations';

登录/注册后可看大图

帐号		自动登录	找回密码
密码			注册