PostgreSQL04-配置文件

1、常用配置文件

1.1、postgresql.conf

• 控制数据库的核心运行参数，包括内存分配、连接数、日志策略、WAL（事务日志）设置、性能优化参数等
• 默认路径/etc/postgresql/<版本>/main/postgresql.conf 或 /var/lib/postgresql/<版本>/main）

1、常用配置示例

# 一、基础路径与文件配置（核心不可随意修改）
data_directory = '/var/lib/postgresql/16/main'    # 数据库集群数据目录
hba_file = '/etc/postgresql/16/main/pg_hba.conf'   # 客户端认证配置文件路径
ident_file = '/etc/postgresql/16/main/pg_ident.conf'  # 身份映射文件路径（用于OS用户与数据库用户映射，一般无需修改）

# 二、连接与认证配置（控制访问能力）
listen_addresses = '*'    # 监听的IP地址：'*'表示允许所有网络接口连接（需在pg_hba.conf中授权）
port = 5432               # 服务端口
max_connections = 100     # 最大并发连接数（16GB内存推荐100-200，过大会导致内存不足）
superuser_reserved_connections = 3    # 为超级用户预留的连接数（确保高负载时管理员能登录）
connect_timeout = 15                  # 客户端连接超时时间（秒），超过则断开无效连接
idle_in_transaction_session_timeout = 30min    # 连接保持空闲的最大时间（分钟），避免僵尸连接占用资源

# 三、资源分配与性能优化（核心调优项）
shared_buffers = 4GB          # 共享内存缓冲区（推荐物理内存的1/4）
work_mem = 32MB               # 每个会话的工作内存（用于排序、哈希等操作，8核推荐32MB）。总消耗=work_mem × 并发会话数（100并发约3GB，不超过内存的1/3）
maintenance_work_mem = 2GB    # 维护操作内存（VACUUM、建索引等，推荐内存的1/8，16GB设2GB）
effective_cache_size = 12GB   # 优化器估算的可用缓存总量（推荐内存的3/4，帮助生成更优执行计划）
max_parallel_workers_per_gather = 4     # 单个查询的最大并行工作进程数（不超过CPU核心数的一半，8核设4）
max_parallel_workers = 8                # 全局最大并行工作进程数（建议等于CPU核心数，8核设8）
max_parallel_maintenance_workers = 4    # 并行维护操作（如并行VACUUM）的最大进程数

# 四、日志配置（问题排查与审计）
logging_collector = on     # 启用日志收集进程，将日志写入文件
log_directory = 'log'      # 日志文件存储目录，默认会在$PGDATA目录下自动创建log目录
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'     # 日志文件命名格式（按天滚动，保留历史日志）
log_rotation_age = 1d      # 日志文件滚动周期（1天生成一个新文件）
log_rotation_size = 10MB   # 单个日志文件最大大小（超过则滚动，避免文件过大）
log_statement = 'ddl'      # 记录的SQL语句级别：ddl（数据定义语句，如CREATE/ALTER）。生产环境不建议设all（避免日志量过大），调试时可改为mod
log_min_duration_statement = 1000ms  # 记录慢查询（执行时间超过1秒的SQL），用于性能优化。默认是-1表示不开启
log_destination = 'stderr,csvlog'   # 日志输出目标：stderr（控制台）+ csvlog（CSV文件，便于分析）
log_connections = on       # 记录连接事件（便于审计用户访问）
log_disconnections = on    # 记录断开事件（便于审计用户访问）

# 五、WAL配置（事务日志，确保数据安全）
wal_level = replica      # WAL日志级别：replica（支持主从复制，默认值）如需逻辑复制（如CDC），改为logical
fsync = on               # 强制WAL写入磁盘（on=安全模式，崩溃不丢数据，生产必开）
synchronous_commit = on  # 事务提交时是否等待WAL写入磁盘（on=强一致性，默认）非核心业务可设off提升性能（可能丢失最后几秒事务）
wal_buffers = -1         # WAL缓冲区大小（自动设为shared_buffers的1/32，16GB内存约128MB）。大事务场景可手动设为64MB-128MB
wal_segment_size = 16MB  # WAL文件大小（默认16MB，大事务可设64MB提升写入效率）


# 六、自动维护配置（避免表膨胀）
autovacuum = on  # 启用自动清理（VACUUM）和统计信息更新（ANALYZE），必开
autovacuum_vacuum_scale_factor = 0.1    # 触发自动清理的行数比例（表中10%行被修改时触发，小表可设更低）
autovacuum_analyze_scale_factor = 0.05  # 触发自动更新统计信息的行数比例（表中5%行被修改时触发）
autovacuum_max_workers = 3   # 自动清理进程的最大并发数（避免占用过多资源，8核设3）

1.2、pg_hba.conf

• 定义哪些 IP 地址、用户、数据库允许通过何种认证方式（如密码、SSL、信任）连接
• 默认位置与postgresql.conf同目录

1、配置文件示例

• 规则生效顺序：从上到下，匹配到第一条则停止检查
• 包含类型TYPE、数据库DATABASE、用户USER、地址ADDRESS、认证方式METHOD。这几个字段

# 1. 本地socket连接（仅允许服务器本地管理，最高权限）
# 类型   数据库         用户          地址（留空表示本地socket）  认证方式
local    all          postgres                           trust      # postgres超级用户本地免密（管理用）
local    all          all                                scram-sha-256  # 其他本地用户需密码（防误操作）

# 2. 本地IPv4连接（服务器本机通过127.0.0.1访问）
host     all          all          127.0.0.1/32          scram-sha-256  # 本地IP连接需密码，兼容程序本地调用

# 3. 内网IPv4网段访问（公司内部可信网络，如办公网、应用服务器网段）
host     app_db       app_user     192.168.1.0/24        scram-sha-256  # 仅允许app_user访问app_db（应用专用）
host     all          dev_team     192.168.2.0/24        scram-sha-256  # 开发团队网段可访问所有库（需密码）
host     monitor_db   monitor_user 192.168.3.0/24        md5            # 监控用户兼容旧版md5认证（临时过渡）

# 4. 远程IPv4连接（跨机房或外部合作方，强制SSL加密）
hostssl  partner_db   partner_user 203.0.113.0/24        scram-sha-256  # 合作方仅能通过SSL访问指定库
hostssl  all          dba_user     103.XX.XX.XX/32       scram-sha-256  # DBA远程运维IP（固定公网IP）

# 5. IPv6连接（如需支持IPv6，配置方式类似）
host     all          all          ::1/128               scram-sha-256  # 本地IPv6环回地址
hostssl  all          all          2001:db8::/32         scram-sha-256  # 可信IPv6网段（强制SSL）

# 6. 拒绝所有未匹配的连接（默认拒绝，增强安全性）
host     all          all          0.0.0.0/0             reject        # 拒绝所有未授权IPv4
host     all          all          ::/0                  reject        # 拒绝所有未授权IPv6

2、配置文件字段解释

TYPE（允许的连接方式）

• local：使用Unix域套接字连接
• host：匹配使用TCP/IP建立的连接，同时匹配SSL和非SSL连接。默认只监听本地环回地址的连接 ，不允许使用TCP/IP远程连接，启用远程连接需要修改postgresql.conf中的listen_addresses参数
• hostssl：匹配必须是使用SSL的TCP/IP连接。配置hostssl有三个前提条件：
  • 1.客户端和服务端都安装OpenSSL;
  • 2.编译PostgreSQL的时候指定configure参数--with-openssl打开SSL支持；
  • 3.在postgresql.conf中配置ssl=on
• hostnossl：和hostssl相反，它只匹配使用非SSL的TCP/IP连接

DATABASE（目标数据库名）

• 数据库名，可以是"all", "sameuser", "samerole", "replication"。all表示所有，但不包括replication。多个数据库用“,”隔开

USER（目标用户名）

• 用户名，可以为"all"，表示所有，也可以具体指定一个用户。多个用户用“,”隔开

ADDRESS（IP地址和掩码）

• 可以是一个主机名，或者IP+掩码。掩码0-32（IPv4）或者0-128（IPv6）
• 主机名以“.”开头。samehost可以匹配所有主机、samenet可以匹配同一个掩码内的所有主机。
• 192.168.10.122/32表示单一主机，
• 192.168.10.0/24表示10网段的所有主机
• 0.0.0.0/0表示所有主机。

METHOD（认证方法）

• 密码加密策略
• password表示以明文方式发送密码
• md5和scram-sha-256会以对应的方式加密再发送密码
• trust,允许无条件连接
• reject，无条件拒绝连接
• peer，从操作系统获取客户端的操作系统用户名,仅适用于本地连接

1.3、postgresql.auto.conf

自动生成的配置文件，存储通过ALTER SYSTEM命令修改的参数（优先级高于postgresql.conf），生产中无需手动编辑，用于动态保存参数变更。

1.4、pg_ctl.conf

数据库服务控制参数（如启动时的内存限制、日志重定向），通常无需修改，默认使用系统配置。

1.5、pg_ident.conf

• pg_ident.conf 是 PostgreSQL 中用于操作系统用户与数据库用户身份映射的配置文件，主要配合 pg_hba.conf 的 ident 认证方式使用，实现 “通过操作系统用户身份间接登录数据库” 的功能。
• 默认位置：与 postgresql.conf、pg_hba.conf 同目录
• 配置文件格式包括映射名称、操作系统用户名、数据库用户名三个字段

2、管理配置文件

2.1、查看配置文件路径

-- 查看postgresql.conf路径
SHOW config_file;
-- 查看pg_hba.conf路径
SHOW hba_file;
-- 查看pg_ident.conf路径
SHOW ident_file;

2.2、动态修改配置文件

• 通过ALTER SYSTEM命令修改，无需手动编辑文件。修改结果会保存到postgresql.auto.conf（优先级高于postgresql.conf）

alter system set listen_addresses = "*";

2.3、配置文件生效

1. 无需重启：reload（适用大部分参数）

方法 1：通过 SQL 命令（需连接数据库）
SELECT pg_reload_conf();  -- 返回t表示成功

方法 2：通过pg_ctl工具（需在服务器本地执行）
# 切换到postgres用户
sudo -u postgres bash
# 执行reload（需指定数据目录或服务名）
pg_ctl reload -D /var/lib/postgresql/16/main/
# 或通过系统服务管理（适用于systemd系统）
systemctl reload postgresql@16-main

2. 必须重启：restart（少数核心参数）

• 需重启的常见参数：data_directory（几乎不修改）、port、shared_buffers（共享内存缓冲区）、max_worker_processes（工作进程数）

# 停止服务
sudo systemctl stop postgresql@16-main
# 启动服务
sudo systemctl start postgresql@16-main
# 或直接重启
sudo systemctl restart postgresql@16-main