PostgreSQL 核心配置文件

PostgreSQL 的核心配置文件包含了数据库运行的基本参数设置，直接影响数据库的安全性、性能和可靠性。了解这些配置文件的结构和内容是 PostgreSQL DBA 的必备技能。

配置文件概述

PostgreSQL 的核心配置文件主要包括：

配置文件	主要作用	位置	版本差异
`postgresql.conf`	主配置文件，包含大部分服务器参数	数据目录下	参数随版本增加，旧版本参数可能被废弃
`pg_hba.conf`	主机基础认证配置，控制客户端访问	数据目录下	认证方法随版本增强，PostgreSQL 14+默认使用scram-sha-256
`pg_ident.conf`	身份映射配置，用于外部认证系统	数据目录下	各版本差异较小
`postgresql.auto.conf`	自动生成配置，由 ALTER SYSTEM 修改	数据目录下	PostgreSQL 9.4+引入

postgresql.conf - 主配置文件

文件结构

postgresql.conf 是 PostgreSQL 最核心的配置文件，包含了控制数据库行为的大部分参数。文件采用键值对格式：

ini

# 这是注释
parameter_name = value
# 示例：
shared_buffers = 4GB
work_mem = 8MB

生产环境核心参数配置

以下是生产环境中常见的核心参数配置建议：

连接与认证

ini

# 监听地址，生产环境建议指定具体IP，避免使用 '*' 暴露所有接口
listen_addresses = '192.168.1.100,127.0.0.1'

# 端口，默认5432，可根据需要修改
port = 5432

# 最大连接数，根据硬件资源和业务需求调整
max_connections = 200

# 超级用户连接数限制，建议留一些连接给超级用户应急
superuser_reserved_connections = 3

资源消耗

ini

# 共享缓冲区，建议设置为系统内存的 25%-40%
shared_buffers = 4GB

# 工作内存，每个连接执行复杂查询时使用的内存
# OLTP 场景：4MB-16MB；OLAP 场景：可适当增大
work_mem = 8MB

# 维护工作内存，用于 VACUUM、CREATE INDEX 等操作
maintenance_work_mem = 1GB

# 有效缓存大小，告诉查询优化器系统可用的缓存总量
# 建议设置为系统内存的 50%-75%
effective_cache_size = 12GB

WAL 管理

ini

# WAL 级别，生产环境建议 replica 或 logical
# replica：支持物理和逻辑复制
# logical：支持逻辑复制和逻辑解码
wal_level = replica

# WAL 缓冲区大小，默认 16MB，可根据写入量调整
wal_buffers = 16MB

# 强制 WAL 写入磁盘的策略
# on：事务提交时同步写入（最安全）
# off：异步写入（性能最佳，有数据丢失风险）
# local：仅写入本地文件系统，不等待刷新到磁盘
synchronous_commit = on

# 同步复制模式，生产环境建议 quorum 或 remote_apply
# primary：只保证写入主库
# quorum：保证写入大多数节点
# remote_apply：保证写入并应用到备库
# synchronous_standby_names = 'FIRST 1 (standby1, standby2)'

检查点管理

ini

# 检查点完成目标比例，建议设置为 0.9 以平滑 I/O
checkpoint_completion_target = 0.9

# 检查点间隔，默认 5 分钟，可调整为 15-30 分钟减少 I/O 峰值
checkpoint_timeout = 15min

# 最大 WAL 段数，触发检查点的 WAL 数量
max_wal_size = 16GB

# 最小 WAL 段数，恢复时允许的最小 WAL 数量
min_wal_size = 4GB

查询优化

ini

# 优化器成本参数，影响查询计划选择
# 对于 SSD 存储，可以降低 seq_page_cost
seq_page_cost = 1.0
random_page_cost = 4.0
cpu_tuple_cost = 0.01

# 并行工作者数量，根据 CPU 核心数调整
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# 自动分析参数，控制自动更新统计信息
autovacuum_analyze_scale_factor = 0.05
autovacuum_analyze_threshold = 50

日志配置

ini

# 日志级别，生产环境建议 info 或 warning
log_min_messages = warning

# 慢查询日志，记录执行时间超过此值的查询
log_min_duration_statement = 1000  # 单位毫秒

# 日志格式，建议使用 json 便于日志分析系统处理
log_destination = 'csvlog'
logging_collector = on
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB

# 日志包含的内容
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on

自动清理 (Autovacuum)

ini

# 启用自动清理
autovacuum = on

# 自动清理工作者数量
autovacuum_max_workers = 8

# 自动清理间隔
autovacuum_naptime = 1min

# 表级 autovacuum 比例，建议设置为 0.05 以更频繁清理
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02

# 自动清理成本限制，可根据系统负载调整
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_cost_limit = -1

配置管理最佳实践

版本控制：将配置文件纳入版本控制（如 Git），便于追踪变更和回滚

分段配置：对于大型部署，可以将配置按功能分组到不同文件，使用 include 指令加载

ini

include = 'postgresql.base.conf'
include = 'postgresql.performance.conf'
include = 'postgresql.log.conf'

测试变更：在测试环境验证配置变更效果，避免直接在生产环境修改
逐步调整：对于性能参数，建议逐步调整，每次改变一个参数，观察效果
记录变更：记录每次配置变更的原因、时间和效果，便于后续分析

pg_hba.conf - 主机基础认证

配置格式

pg_hba.conf 用于控制客户端如何连接到数据库，每行格式如下：

TYPE  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

认证方法

常用的认证方法包括：

方法	描述	适用场景	版本要求
`trust`	信任所有连接，无需密码	仅适用于本地开发环境	所有版本
`md5`	密码使用 MD5 加密传输	兼容旧版本，安全性较低	所有版本
`scram-sha-256`	使用 SCRAM-SHA-256 加密	安全性高，PostgreSQL 10+ 支持	PostgreSQL 10+
`password`	密码明文传输，不安全	不推荐使用	所有版本
`peer`	基于操作系统用户名	仅适用于本地连接	Unix/Linux 系统
`ident`	基于外部认证系统	企业级环境，需配置 `pg_ident.conf`	所有版本
`sslcert`	基于 SSL 证书认证	高安全性要求的环境	所有版本

生产环境安全配置示例

# 允许本地 Unix socket 连接，使用 peer 认证
local   all             postgres                                peer
local   all             all                                     scram-sha-256

# IPv4 本地连接
host    all             all             127.0.0.1/32            scram-sha-256

# 允许特定 IP 段访问，使用 scram-sha-256 认证
host    all             all             192.168.1.0/24          scram-sha-256

# 允许应用服务器访问，使用 scram-sha-256 认证
host    app_db          app_user        10.0.0.100/32          scram-sha-256

# 禁用所有其他 IP 访问
host    all             all             0.0.0.0/0              reject

pg_ident.conf - 身份映射

用途

pg_ident.conf 用于将外部认证系统的用户名映射到 PostgreSQL 用户名，常用于 ident 认证方法。

配置示例

# MAPNAME  SYSTEM-USERNAME  PG-USERNAME
appmap    appuser          dbuser
adminmap  sysadmin         postgres

与 pg_hba.conf 配合使用

在 pg_hba.conf 中指定映射名称：

host    all             all             10.0.0.0/8             ident map=adminmap

postgresql.auto.conf - 自动配置文件

用途

postgresql.auto.conf 用于存储通过 ALTER SYSTEM 命令修改的配置参数，优先级高于 postgresql.conf。

示例用法

sql

-- 修改参数，自动保存到 postgresql.auto.conf
ALTER SYSTEM SET shared_buffers = '5GB';
ALTER SYSTEM SET work_mem = '16MB';

-- 查看当前配置
SHOW shared_buffers;

-- 重置参数
ALTER SYSTEM RESET work_mem;

配置文件加载顺序

PostgreSQL 配置文件的加载顺序如下：

编译时默认参数
postgresql.conf
postgresql.auto.conf（优先级最高）

配置变更生效方式

不同类型的参数需要不同的方式使变更生效：

参数类型	生效方式	示例参数
重启生效	需要重启 PostgreSQL 服务	`listen_addresses`、`shared_buffers`、`max_connections`
重载生效	通过 `pg_ctl reload` 或 `SELECT pg_reload_conf();` 重载配置	`log_min_duration_statement`、`work_mem`、`maintenance_work_mem`
会话生效	仅对新会话生效，已连接会话保持原有配置	`default_transaction_isolation`、`search_path`
立即生效	对所有会话立即生效	`log_connections`、`log_disconnections`

版本差异与参数兼容性

主要版本参数差异

PostgreSQL 版本	新增参数
10	`scram-sha-256` 认证方法、`logical_replication`
12	`generated_columns`、`incremental_sort`
13	`incremental_sort` 增强、`recovery_min_apply_delay`
14	`pg_stat_kcache` 内置支持、`synchronous_commit = remote_apply`
15	`pg_stat_statements.track_planning`、`vacuum_cleanup_index_scale_factor`
16	`enable_parallel_hash` 默认开启、`pg_wal_lsn_diff` 增强

参数废弃与替代

废弃参数	替代参数	版本
`hot_standby_feedback`	`primary_slot_name`	12+
`wal_buffers = -1`	自动计算	12+
`log_line_prefix = '%m'`	`%t` 替代	10+

配置文件备份与恢复

备份策略

bash

# 定期备份配置文件
cp -r /pgdata/data/postgresql.conf /pgbackup/config/$(date +%Y%m%d)/
cp -r /pgdata/data/pg_hba.conf /pgbackup/config/$(date +%Y%m%d)/
cp -r /pgdata/data/pg_ident.conf /pgbackup/config/$(date +%Y%m%d)/
cp -r /pgdata/data/postgresql.auto.conf /pgbackup/config/$(date +%Y%m%d)/

恢复配置文件

bash

# 从备份恢复配置文件
systemctl stop postgresql
sudo -u postgres cp /pgbackup/config/20230601/postgresql.conf /pgdata/data/
sudo -u postgres cp /pgbackup/config/20230601/pg_hba.conf /pgdata/data/
sudo -u postgres cp /pgbackup/config/20230601/pg_ident.conf /pgdata/data/
sudo -u postgres cp /pgbackup/config/20230601/postgresql.auto.conf /pgdata/data/
systemctl start postgresql

常见配置问题与解决方案

连接被拒绝

问题：客户端连接数据库时出现 "connection refused" 错误

排查步骤：

检查 listen_addresses 是否包含客户端 IP
检查 port 参数是否正确
检查防火墙是否允许 PostgreSQL 端口访问
检查 pg_hba.conf 是否允许客户端 IP 连接

认证失败

问题：客户端连接时出现 "FATAL: password authentication failed for user" 错误

排查步骤：

检查用户名和密码是否正确
检查 pg_hba.conf 中对应行的认证方法是否与密码存储方式匹配
检查密码是否过期（PostgreSQL 10+ 支持密码有效期）
对于 scram-sha-256 认证，确保客户端驱动支持该认证方法

性能问题

问题：数据库查询性能下降

排查步骤：

检查 shared_buffers 和 effective_cache_size 是否合理
检查 work_mem 是否足够，避免临时文件过多
检查 autovacuum 是否正常运行，避免统计信息过时
检查慢查询日志，分析耗时较长的查询

WAL 磁盘空间不足

问题：WAL 日志占用过多磁盘空间

排查步骤：

检查 wal_level 是否设置过高
检查 max_wal_size 是否合理
检查 WAL 归档是否正常运行
检查复制槽是否有停滞的订阅者

总结

PostgreSQL 的核心配置文件是数据库性能和安全性的基础，DBA 需要根据业务需求和硬件资源合理配置这些文件。在生产环境中，建议：

采用最小权限原则配置 pg_hba.conf，避免不必要的访问
根据硬件资源调整内存相关参数，优化查询性能
配置详细的日志，便于问题排查和性能分析
启用并合理配置 autovacuum，防止表膨胀
定期备份配置文件，便于恢复和回滚
追踪 PostgreSQL 版本更新，了解新参数和废弃参数
在测试环境验证配置变更，避免直接在生产环境修改

通过合理配置和管理核心配置文件，DBA 可以确保 PostgreSQL 数据库在生产环境中稳定、安全、高效地运行。

PostgreSQL 核心配置文件 ​

配置文件概述 ​

postgresql.conf - 主配置文件 ​

文件结构 ​

生产环境核心参数配置 ​

连接与认证 ​

资源消耗 ​

WAL 管理 ​

检查点管理 ​

查询优化 ​

日志配置 ​

自动清理 (Autovacuum) ​

配置管理最佳实践 ​

pg_hba.conf - 主机基础认证 ​

配置格式 ​

认证方法 ​

生产环境安全配置示例 ​

pg_ident.conf - 身份映射 ​

用途 ​

配置示例 ​

与 pg_hba.conf 配合使用 ​

postgresql.auto.conf - 自动配置文件 ​

用途 ​

示例用法 ​

配置文件加载顺序 ​

配置变更生效方式 ​

版本差异与参数兼容性 ​

主要版本参数差异 ​

参数废弃与替代 ​

配置文件备份与恢复 ​

备份策略 ​

恢复配置文件 ​

常见配置问题与解决方案 ​

连接被拒绝 ​

认证失败 ​

性能问题 ​

WAL 磁盘空间不足 ​

总结 ​

PostgreSQL 核心配置文件

配置文件概述

postgresql.conf - 主配置文件

文件结构

生产环境核心参数配置

连接与认证

资源消耗

WAL 管理

检查点管理

查询优化

日志配置

自动清理 (Autovacuum)

配置管理最佳实践

pg_hba.conf - 主机基础认证

配置格式

认证方法

生产环境安全配置示例

pg_ident.conf - 身份映射

用途

配置示例

与 pg_hba.conf 配合使用

postgresql.auto.conf - 自动配置文件

用途

示例用法

配置文件加载顺序

配置变更生效方式

版本差异与参数兼容性

主要版本参数差异

参数废弃与替代

配置文件备份与恢复

备份策略

恢复配置文件

常见配置问题与解决方案

连接被拒绝

认证失败

性能问题

WAL 磁盘空间不足

总结