配置文件说明
PostgreSQL常用的配置文件
PostgreSQL常用的配置文件
1 postgresql.conf参数文件
postgresql.conf 是 PostgreSQL 数据库的主要配置文件，用于控制数据库实例的行为和特性，一般在$PGDATA目录下。这个文件包括了众多的配置选项，比如内存使用限制、连接设置、日志记录规则等。根据系统资源和应用需求对这些参数进行调整，可以优化数据库的性能、安全性和可用性。
以下是一些常见的postgresql.conf 配置参数及其说明，以帮助我们了解如何配置 PostgreSQL：
1. 监听地址（listen_addresses）：
控制 PostgreSQL 监听哪些网络地址。您可以设置为 'localhost' 以便仅接受来自本机的连接，或者设为 '*' 以接受任何地址的连接。
端口（port）：
PostgreSQL 服务监听的端口，默认是 5432。
2.最大连接数（max_connections）：
同时连接到数据库的客户端最大数量。需要根据系统资源进行适当配置。
3.共享缓冲区（shared_buffers）：
PostgreSQL 用于缓存数据的内存大小。通常设置为系统内存的15%-25%。
4.工作内存（work_mem）：
每个数据库操作可用的内存量，影响排序、哈希操作的性能。
5.维护工作内存（maintenance_work_mem）：
数据库维护操作（如索引创建和VACUUM操作）能使用的最大内存。
6.自动清理（autovacuum）：
控制自动清理进程参数。自动清理用于回收空间，更新表的统计信息，非常关键。
7.日志记录设置（log_*）：
控制日志的生成和管理，如 log_statement 定义记录哪些类型的SQL语句，log_directory和log_filename定义日志文件的存储位置和名称规则等。
8.WAL配置（wal_level, max_wal_senders等）：
WAL（Write-Ahead Logging）相关的配置，决定了日志的详细程度、复制功能以及点对点延迟等。
9.时间和区域设置（timezone, lc_*）：
控制服务器的时区和区域设置，例如lc_messages控制系统消息的语言，timezone确定时间戳的时区等。
2 pg_hba.conf配置文件
pg_hba.conf 是 PostgreSQL 数据库的一个重要配置文件，用于控制客户端对数据库的访问权限，一般在$PGDATA目录下。HBA 代表“host-based authentication”，即基于主机的认证。通过编辑这个文件，我们可以细致地指定哪些用户可以从哪些主机上以何种方式连接到数据库实例中的哪个数据库。
pg_hba.conf文件中的每条记录定义了一种连接类型、一个客户端IP地址（范围）、数据库、用户以及用于该连接的认证方法。下面是pg_hba.conf中一些常用的认证方法：
trust：允许连接而不进行密码认证。这是最不安全的认证方法，通常仅在非常可信的环境中使用，比如仅限本机访问的配置。
reject：拒绝所有连接。
md5：使用MD5加密的密码进行认证。
password：使用明文密码进行认证（不推荐，除非连接是通过SSL加密）。
peer：基于操作系统的用户身份进行认证（仅适用于本地连接）。
ident：使用ident服务器进行认证（适用于TCP/IP连接）。
scram-sha-256：使用SCRAM-SHA-256进行密码认证。这是一种安全的认证方式，推荐使用。
认证方式
• trust – 无条件地允许连接。这种方法允许任何可以与PostgreSQL数据库服务器连接的
用户以他们期望的任意PostgreSQL数据库用户身份登入,而不需要口令或者其他
任何认证。
• reject – 无条件拒绝连接。常用于从一个组中“过滤出”特定主机,例如一个reject行可以阻
塞一个特定的主机连接,而后面一行允许一个特定网络中的其余主机进行连。
• md5 – md5认证方式为双重md5加密
password口令认证
– password指明文密码，所以不能在非信任网络使用password方式(Require the
client to supply an unencrypted password for authentication. Since the password is
sent in clear text over the network, this should not be used on untrusted networks)。
• peer – 从操作系统获得客户端的操作系统用户,并且检查它是否匹配被请求的数据库用
户名。这只对本地连接可用。
• scram-sha-256 – Perform SCRAM-SHA-256 authentication to verify the user's password.。
– 其它认证方式
下面是一个pg_hba.conf文件中可能的示例条目：
# 类型 数据库 用户 地址 认证方法
local all all trust
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
host mydb user1 192.168.0.0/24 scram-sha-256
对pg_hba.conf进行的修改在重新加载配置文件或重启数据库服务后立即生效。我们可以通过PostgreSQL的pg_ctl reload命令或SQL命令SELECT pg_reload_conf();来重新加载配置，无需重启数据库服务。
编辑此文件时要格外小心，错误的配置可能导致无法连接到数据库。建议在进行任何更改之前备份pg_hba.conf文件。
由pg_hba.conf决定
– 哪些主机可以连接数据库实例
– 客户端使用什么连接方式和认证方式
• 连接方式(TYPE)：local,host,hostssl,hostnossl
• 认证方式：trust, reject, md5, password, scram, gss, sspi, ident, peer, pam, ldap, radius or cert – 哪个数据库用户可以使用它
– 允许这个用户使用哪些数据库
– find / -name "pg_hba.conf"
七种格式
– # TYPE DATABASE USER ADDRESS METHOD
– local database user auth-method [auth-options]
– host database user address auth-method [auth-options]
– hostssl database user address auth-method [auth-options]
– hostnossl database user address auth-method [auth-options]
– host database user IP-address IP-mask auth-method [auth-options]
– hostssl database user IP-address IP-mask auth-method [auth-options]
– hostnossl database user IP-address IP-mask auth-method [auth-options]
local匹配使用Unix域套接字的连接。
– 如果没有TYPE为local的条目则不允许通过Unix域套接字连接
• host匹配使用 TCP/IP建立的连接，同时匹配SSL和非SSL连接
– 缺省安装只监听本地环回地址localhost的连接，不允许使用TCP/IP远程连接，启
用远程连接需要修改postgresql.conf中的listen_addresses参数。
• hostssl匹配必须是使用SSL的TCP/IP连接
– 客户端和服务器端都安装OpenSSL – 编译PostgreSQL的时候指定configure参数--with-openssl打开SSL支持
– 在postgresql.conf中配置ssl = on
• hostnossl只匹配使用非SSL的TCP/IP连接
案例
1.如果要使所有可提供有效密码的用户均能从以 100 开头的地址连接到 PostgreSQL 实例中的任何数据库，
则需要向 pg_hba.conf 文件添加与与下面的行内容类似的行：
host all all 100.0.0.0/8 md5
2.上述示例针对 IPV4 地址。如果需要配置数据库群集以接受具有 IPV6 地址的计算机的连接，那么格式
应如下所示：
host all all fe60::61f9:1253:c522:9b92%10/128 md5
要允许所有具有以 fe60 开始的地址的计算机进行连接，应向 pg_hba.conf 文件添加以下行：
host all all fe60::0000:0000:0000:0000%10/8 md5
如果您的 PostgreSQL 数据库集群安装在具有 IPV6 地址的计算机上，而您想要使具有 IPV4 地址的计算
机与其连接，则需要在 pg_hba.conf 文件中取消以下行的注释：
host all all ::1/128 md5
允许IP地址为10.10.56.17的所有用户可以通过MD5的密码验证方式连接主机上所有的数据库
host all all 10.10.56.17/32 md5
即表示允许地址为 10.10.56.17 的用户 pgtest通过 MD5方式 加密的密码方式连接主机上的 test 数据库
# TYPE DATABASE USER ADDRESS METHOD
host test pgtest 10.10.56.17/32 md5
即表示允许 任意iP 通过用户名为 pgtest 和md5的 密码 验证方式连接主机上 test 的数据库
# TYPE DATABASE USER ADDRESS METHOD
host test pgtest 0.0.0.0/0 md5
表示任意IP地址的用户 pgtest 无需密码验证可直接连接访问该主机的 test 数据库
# TYPE DATABASE USER ADDRESS METHOD
host test pgtest 0.0.0.0/0 trust
查看没有用SCRAM加密口令的用户
create user foo password 'foopassword';
select usename,passwd from pg_shadow where passwd not like 'SCRAM%' or passwd is null;
select usename,passwd from pg_shadow ;
查看现在的加密方法
postgres=# show password_encryption;
修改加密方法
postgres=# alter system set password_encryption = 'scram-sha-256';
postgres=# show password_encryption;
postgres=# select pg_reload_conf();
修改密码
postgres=#alteruserfoopassword''Xzzp2008';
Thepassword_encryptionparameterdecideshowtheALTERUSERstatementwillencryptthepassword u修改pg_hba.conf本地连接的method方法为scram-sha-256
修改pg_hba.conf 本地连接的method方法为scram-sha-256
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
host all all 0.0.0.0/0 scram-sha-256
postgres=# select pg_reload_conf();
远程登录
psql -h 192.168.1.221 -p 5433 -d postgres -U foo -W
有一个插件，可以用于加强口令复杂度管理 passwordcheck:
http://www.postgresql.org/docs/current/static/passwordcheck.html
思考：postgresql.conf中password_encryption参数的加密方法和pg_hba.conf里的口令认证有什
么关系？比如说如下设置参数，怎么生效的？
password_encryption=scram-sha-256
pg_hba.conf的是md5
解析：Q1、pg_hba.conf是配置客服端访问控制的验证方式。password_encryption是服务端配置用
户密码加密存储的方式，PostgreSQL数据库用户密码是加密存储的密文。一个是加密方式，一个
是验证方法。
pg_ident.conf
pg_ident.conf是用户映射配置文件,控制PostgreSQL用户名映射。它映射外部用户名转换为相应 的PostgreSQL用户名。结合pg_hba.conf中，method为ident可以用特定的操作系统用户和指定的数据库用户登录数据库。如下：
pg_ident.conf如下：
# MAPNAME SYSTEM-USERNAME PG-USERNAME
pgccc test postgres
pgccc test user001
pg_hba.conf如下：
# TYPE DATABASE USER CIDR-ADDRESS METHOD
local all all ident map=pgccc
eg:
[postgres@pca001 pgdata]$ psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.1521" failed: FATAL: Peer
authentication failed for user "postgres"
• [postgres@pca001 pgdata]$ psql -U postgres
psql: error: connection to server on socket "/tmp/.s.PGSQL.1521" failed: FATAL: Peer
authentication failed for user "postgres"
• [test@pca001 ~]$ /pgccc/app/16.1/bin/psql -U user001 -d postgres
psql (16.1)
Type "help" for help. • postgres=> \conninfo
You are connected to database "postgres" as user "user001" via socket in "/tmp" at port "1521". postgres=>
3 .pgpass配置文件
.pgpass 文件是一种用于存储 PostgreSQL 数据库的用户名和密码的配置文件，目的是简化客户端连接到数据库时的认证过程。通过使用 .pgpass 文件，用户或应用程序可以在不需要交互式输入密码的情况下连接数据库，这在自动化脚本和后台任务中特别有用。
pg_ident.conf配置文件
pg_ident.conf 文件是 PostgreSQL 中用于配置用户映射的文件。通过用户映射，数据库管理员可以控制哪些操作系统用户能以哪些数据库用户身份连接到数据库，特别是在使用像 “peer” 或 “ident” 这样的基于操作系统身份的认证方法时非常有用。该文件通常位于 PostgreSQL 数据目录中，与 postgresql.conf 和 pg_hba.conf 等关键配置文件一起。
配置说明
在 pg_ident.conf 文件中，每个映射都由以下三个部分组成：
- 映射名称：一个标识符，用于在 pg_hba.conf 文件中引用该映射规则。
- 系统用户名：操作系统级别的用户名，或者当使用 “ident” 认证时的 ident 服务返回的用户名。
- 数据库用户名：数据库内部的用户名，该操作系统用户将映射到此数据库用户
.pgpass 文件通常位于用户的主目录下，具体位置和格式要求如下：
文件位置：
- Linux/Unix/MacOS：文件应该命名为 .pgpass 并放在用户的主目录下，路径通常是 ~/.pgpass。
- Windows：文件应该命名为 pgpass.conf 并放在 %APPDATA%\postgresql\ 目录下。
文件权限：
- Linux/Unix/MacOS：为了确保安全性，.pgpass 文件的权限应该设置为仅用户可读（chmod 0600 ~/.pgpass），以避免其他用户查看文件内容。
文件格式：
- .pgpass 文件中的每一行都应该遵循以下格式：
hostname:port:database:username:password
hostname：要连接的数据库服务器的主机名或IP地址。使用 * 表示任意主机。
port：数据库服务器的端口。使用 * 表示任意端口。
database：要连接的数据库名称。使用 * 表示任意数据库。
username：用于认证的用户名。
password：对应用户名的密码。
我们可以在 .pgpass 文件中添加多条记录，每条记录占一行，以支持不同的数据库连接。
格式
每行的格式如下：
映射名称 系统用户名 数据库用户名
字段之间由空格或制表符分隔。可以使用 # 开头的行添加注释。
示例
假设我们有一个操作系统用户 john，我们希望他能够作为数据库用户 dbadmin 连接到数据库。我们可以在 pg_ident.conf 文件中添加以下条目：
# 映射名称 系统用户名 数据库用户名
myusermap john dbadmin
然后，在 pg_hba.conf 文件中，我们需要相应地配置使用这个映射名称。例如：
# 类型 数据库 用户 地址 认证方法 映射名称
local all all peer myusermap
这样配置之后，当操作系统用户 john 尝试通过 Unix 套接字（local 表示本地连接）连接到任何数据库时，PostgreSQL 会试图使用 peer 方法进行认证。根据 myusermap 映射规则，john 会以 dbadmin 用户身份进行认证。
注意事项
- 修改 pg_ident.conf 文件后，通常需要重新加载 PostgreSQL 配置或重启数据库服务才能使改动生效（例如，通过运行 SELECT pg_reload_conf(); 命令）。
- 在配置用户映射时要小心，因为不当的配置可能会导致安全问题，允许未授权的用户获得对数据库的访问权限。
示例：
*:*:*:myuser:mypassword
localhost:5432:mydb:myuser:mypassword
192.168.1.100:5432:*:otheruser:otherpassword
注意： 尽管使用 .pgpass 文件可以避免在每次连接数据库时输入密码，但要注意保护好该文件，防止密码泄露。
当使用如 psql 或其他基于 libpq 的客户端程序时，如果连接字符串中未提供密码，客户端会自动寻找 .pgpass 文件以获取认证信息。
4 pg_service.conf
pg_service.conf 文件是 PostgreSQL 用来存储数据库连接信息的一个服务文件。通过在 pg_service.conf 文件中定义服务，应用程序和客户端工具可以使用服务名代替完整的连接字符串来连接到数据库。这样可以简化连接过程，并增加配置的灵活性。下面是如何配置 pg_service.conf 文件的一些基础步骤
步骤 1: 定位或创建 pg_service.conf 文件
首先，确定 pg_service.conf 文件的位置。如果文件不存在，您可以自己创建一个。pg_service.conf`这个文件通常位于以下位置之一：
Linux/Unix/MacOS: ~/.pg_service.conf
Windows: %APPDATA%\postgresql\pg_service.conf
步骤 2: 编辑 pg_service.conf 文件
使用文本编辑器打开 pg_service.conf 文件。文件中的每一项服务都需要在新行里定义，格式如下：
[服务名]
host=主机名
port=端口号
dbname=数据库名
user=用户名
password=密码
- 服务名：方括号 [ ] 内的文本，代表你将用来引用数据库连接的名字。
- host：数据库服务器的地址，可以是 IP 地址或域名。
- port：数据库服务器的端口，默认是 5432。
- dbname：你要连接的数据库名称。
- user：用于连接数据库的用户名。
- password：用户的密码。
示例配置：
[myservice]
host=localhost
port=5432
dbname=mydatabase
user=myuser
password=mypassword
步骤 3: 保存并使用服务
保存 pg_service.conf 文件后，我们就可以在支持的 PostgreSQL 客户端工具中使用定义的服务名来连接数据库了。例如，如果我们使用 psql 工具，可以这样连接到上面定义的服务：
psql service=myservice
这条命令会告诉 psql 使用 pg_service.conf 文件中 [myservice] 部分定义的参数来连接数据库。
注意事项：
- 确保 pg_service.conf 文件的权限设置正确，避免敏感信息（例如数据库密码）暴露给不该有权限的用户。
- 使用 pg_service.conf 文件可以帮助我们组织和管理数据库连接信息，但请谨慎处理包含明文密码的文件。
- 使用 pg_service.conf 提供了一种方便且灵活的方式来管理 PostgreSQL 数据库连接信息，特别是在需要管理多个数据库连接时。
5. pg_ident.conf配置文件
pg_ident.conf 文件是 PostgreSQL 中用于配置用户映射的文件。通过用户映射，数据库管理员可以控制哪些操作系统用户能以哪些数据库用户身份连接到数据库，特别是在使用像 “peer” 或 “ident” 这样的基于操作系统身份的认证方法时非常有用。该文件通常位于 PostgreSQL 数据目录中，与 postgresql.conf 和 pg_hba.conf 等关键配置文件一起。
配置说明
在 pg_ident.conf 文件中，每个映射都由以下三个部分组成：
- 映射名称：一个标识符，用于在 pg_hba.conf 文件中引用该映射规则。
- 系统用户名：操作系统级别的用户名，或者当使用 “ident” 认证时的 ident 服务返回的用户名。
- 数据库用户名：数据库内部的用户名，该操作系统用户将映射到此数据库用户。
格式
每行的格式如下：
映射名称 系统用户名 数据库用户名
字段之间由空格或制表符分隔。可以使用 # 开头的行添加注释。
示例
假设我们有一个操作系统用户 john，我们希望他能够作为数据库用户 dbadmin 连接到数据库。我们可以在 pg_ident.conf 文件中添加以下条目：
# 映射名称 系统用户名 数据库用户名
myusermap john dbadmin
然后，在 pg_hba.conf 文件中，我们需要相应地配置使用这个映射名称。例如：
# 类型 数据库 用户 地址 认证方法 映射名称
local all all peer myusermap
这样配置之后，当操作系统用户 john 尝试通过 Unix 套接字（local 表示本地连接）连接到任何数据库时，PostgreSQL 会试图使用 peer 方法进行认证。根据 myusermap 映射规则，john 会以 dbadmin 用户身份进行认证。
注意事项
修改 pg_ident.conf 文件后，通常需要重新加载 PostgreSQL 配置或重启数据库服务才能使改动生效（例如，通过运行 SELECT pg_reload_conf(); 命令）。
在配置用户映射时要小心，因为不当的配置可能会导致安全问题，允许未授权的用户获得对数据库的访问权限。
谨记：心存敬畏，行有所止。
数据库连接常见故障排查
1、在服务器端使用TCP/IP方式登录数据库
①异常端口：
[postgres@pca001 pgdata]$ psql -h 192.168.10.188 -U pca001 -d postgres -p 5432
psql: error: connection to server at "192.168.10.188", port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
②访问控制异常：
psql: error: connection to server at "192.168.10.188", port 1521 failed: FATAL: no pg_hba.conf entry for host "192.168.10.188", user "pca001", database "postgres", no encryption
③监听异常：
psql: error: connection to server at "192.168.10.188", port 1521 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
2、 数据库远程连接工具连接Postgresql 服务报错
问题描述：Navicat 连接 PostgreSQL 时出现的问题：
• could not connect to server： Connection refused（0x0000274D/10061）Is the server running on host "192.168.10.133" and accepting TCP/IP connections on 5432?
无法连接到服务器：连接被拒绝（0x0000274D / 10061）
服务器是否在主机“192.168.10.133”上运行并且在5432上接受TCP / IP连接？
分析和解决
1、原因分析：
（1）防火墙没有关闭；
（2）未监听客户端IP地址：
（3）未开放客户端地址访问权限；
2、解决方法：
（1）关闭防火墙
①先查看防火墙的状态systemctl status firewalld，如果防火墙开启则关闭防火墙
systemctl start firewalld。
（2）修改配置文件postgresql.conf：
①编辑文件vim /pgccc/pgdata/postgresql.conf
②修改监听为：listen_addresses = '*' ③重启Postgresql数据库服务
systemctl restart postgresql-16.service
（3）修改配置文件pg_hba.conf
①编辑文件vim /pgccc/pgdata/pg_hba.conf
②开放访问地址权限
③重启Postgresql数据库服务
systemctl restart postgresql-16.service
3、查看操作系统错误日志
/var/log/message 系统启动后的信息和错误日志，是Red
Hat Linux中最常用的日志之一
4、查看数据库错误日志
查看文件$PGDATA/log
默认情况下，一天产生一个日志
浙公网安备 33010602011771号