印刻数据库

外观

PostgreSQL 主配置文件介绍 ​

postgresql.conf是PostgreSQL的核心配置文件，包含数据库服务器的所有可配置参数。该文件位于数据目录中，通常位于/etc/postgresql/14/main/postgresql.conf（Debian/Ubuntu系统）或/var/lib/postgresql/14/main/postgresql.conf（CentOS/RHEL系统）。postgresql.auto.conf是由pg_ctl或ALTER SYSTEM命令自动生成的配置文件，优先级高于postgresql.conf。

配置文件结构 ​

PostgreSQL配置文件采用键值对格式，每行包含一个配置项，以等号连接键和值。配置文件支持注释，以#开头的行表示注释。

bash
# 示例配置项
listen_addresses = 'localhost'  # 监听地址
port = 5432                     # 监听端口
max_connections = 100           # 最大连接数

配置文件类型 ​

1. 主配置文件（postgresql.conf） ​

包含数据库服务器的所有可配置参数，是手动编辑的主要配置文件。

2. 自动配置文件（postgresql.auto.conf） ​

由ALTER SYSTEM命令自动生成，优先级高于postgresql.conf，用于存储动态修改的配置参数。

3. 客户端认证配置文件（pg_hba.conf） ​

控制客户端访问数据库的认证方式，位于数据目录中。

4. 标识文件（pg_ident.conf） ​

用于映射操作系统用户到数据库用户，位于数据目录中，与pg_hba.conf配合使用。

连接配置 ​

基本连接参数 ​

bash
# 监听地址，'*'表示监听所有地址
listen_addresses = '*'
# 监听端口
port = 5432
# 最大连接数
max_connections = 100
# Unix套接字目录
unix_socket_directories = '/var/run/postgresql'
# Unix套接字权限
unix_socket_permissions = 0777

连接超时设置 ​

bash
# 客户端连接超时（秒）
client_connection_check_interval = 60
# TCP连接超时（秒）
tcp_keepalives_idle = 7200
tcp_keepalives_interval = 75
tcp_keepalives_count = 9

内存配置 ​

共享内存参数 ​

bash
# 共享缓冲区大小，建议设置为系统内存的25%
shared_buffers = '4GB'
# 预写式日志缓冲区大小
wal_buffers = '16MB'
# 共享内存段大小
shared_memory_type = 'mmap'  # 或 'sysv'

工作内存参数 ​

bash
# 每个查询操作的工作内存
work_mem = '64MB'
# 维护操作（VACUUM、CREATE INDEX等）的内存
maintenance_work_mem = '1GB'
# 用于并行查询的最大内存
max_parallel_workers_per_gather = 4
# 系统支持的最大并行工作进程数
max_parallel_workers = 8

临时文件配置 ​

bash
# 临时文件大小限制，超过则写入磁盘
temp_file_limit = '16GB'
# 临时目录
# temp_tablespaces = 'temp_ts'  # 需要预先创建表空间

磁盘I/O配置 ​

检查点配置 ​

bash
# 检查点间隔时间
checkpoint_timeout = 30min
# 检查点之间的最大写入量
max_wal_size = '16GB'
# 最小WAL大小
min_wal_size = '4GB'
# 检查点完成目标时间
checkpoint_completion_target = 0.9

预写式日志（WAL）配置 ​

bash
# WAL级别
wal_level = 'replica'  # 或 'minimal', 'logical'
# 同步提交方式
 synchronous_commit = 'on'  # 或 'off', 'local', 'remote_write', 'remote_apply'
# WAL写入缓冲区大小
wal_buffers = '16MB'
# WAL文件大小
wal_segment_size = 16MB  # 必须在初始化数据库时设置

日志配置 ​

日志格式配置 ​

bash
# 日志行前缀
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
# 日志输出目标
log_destination = 'csvlog'  # 或 'stderr', 'syslog', 'eventlog'
# 日志文件目录
log_directory = 'pg_log'
# 日志文件名称
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
# 日志轮转大小
log_rotation_size = 10MB
# 日志轮转时间
log_rotation_age = 1d

日志内容配置 ​

bash
# 记录所有DML语句
log_statement = 'ddl'  # 或 'none', 'mod', 'all'
# 记录耗时超过100毫秒的语句
log_min_duration_statement = 100
# 记录锁等待事件
log_lock_waits = on
# 记录死锁事件
log_deadlocks = on
# 日志级别
log_min_messages = notice  # 或 'debug5'到'panic'

安全配置 ​

认证配置 ​

pg_hba.conf文件用于控制客户端访问认证，格式如下：

bash
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# 本地连接
local   all             all                                     trust
# IPv4连接
host    all             all             127.0.0.1/32            trust
host    all             all             0.0.0.0/0               md5
# IPv6连接
host    all             all             ::1/128                 trust
# 复制连接
host    replication     replicator      192.168.1.0/24          md5

安全增强配置 ​

bash
# 禁用远程超级用户访问
disable_superuser_connection = off
# 密码加密方法
password_encryption = scram-sha-256  # 或 'md5'
# 密码有效期
default_password_lifetime = 90
# 连接限制
# max_connections_per_user = 20

性能优化配置 ​

查询优化配置 ​

bash
# 启用查询计划缓存
plan_cache_mode = 'auto'  # 或 'force_generic_plan', 'force_custom_plan'
# 并行查询配置
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 2
# 自动统计信息收集
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min

索引优化配置 ​

bash
# 索引扫描成本因子
srandom_page_cost = 4.0
seq_page_cost = 1.0
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025

配置文件管理 ​

查看当前配置 ​

sql
-- 查看所有配置参数
SELECT name, setting, unit, category, short_desc FROM pg_settings;

-- 查看特定配置参数
SELECT name, setting, unit, short_desc FROM pg_settings WHERE name = 'shared_buffers';

-- 查看需要重启的配置参数
SELECT name, setting FROM pg_settings WHERE context = 'postmaster';

动态修改配置 ​

sql
-- 使用ALTER SYSTEM命令修改配置（会写入postgresql.auto.conf）
ALTER SYSTEM SET listen_addresses = '*';
ALTER SYSTEM SET max_connections = 200;

-- 恢复默认值
ALTER SYSTEM RESET shared_buffers;

-- 重新加载配置
SELECT pg_reload_conf();

配置文件备份与恢复 ​

bash
# 备份配置文件
cp /etc/postgresql/14/main/postgresql.conf /etc/postgresql/14/main/postgresql.conf.backup
cp /etc/postgresql/14/main/pg_hba.conf /etc/postgresql/14/main/pg_hba.conf.backup

# 恢复配置文件
cp /etc/postgresql/14/main/postgresql.conf.backup /etc/postgresql/14/main/postgresql.conf
cp /etc/postgresql/14/main/pg_hba.conf.backup /etc/postgresql/14/main/pg_hba.conf

# 重启PostgreSQL服务
systemctl restart postgresql

配置最佳实践 ​

1. 连接配置最佳实践 ​

  • 根据服务器资源和应用需求合理设置max_connections
  • 生产环境建议限制listen_addresses，不要使用'*'允许所有地址访问
  • 适当调整tcp_keepalives参数，避免连接泄漏

2. 内存配置最佳实践 ​

  • shared_buffers建议设置为系统内存的25%
  • work_mem根据查询复杂度和并发数调整，避免设置过大
  • maintenance_work_mem建议设置为系统内存的5%-10%
  • 监控内存使用情况，避免OOM问题

3. 日志配置最佳实践 ​

  • 启用详细日志记录，便于故障排查和性能分析
  • 配置合理的日志轮转策略，避免磁盘空间不足
  • 生产环境建议使用log_min_duration_statement记录慢查询
  • 启用log_lock_waits和log_deadlocks记录锁相关问题

4. WAL配置最佳实践 ​

  • 根据复制需求选择合适的wal_level
  • 生产环境建议使用synchronous_commit = on确保数据一致性
  • 合理设置max_wal_size和min_wal_size，避免频繁检查点
  • 调整checkpoint_completion_target减少I/O峰值

5. 安全配置最佳实践 ​

  • 使用sudo-sha-256加密密码，避免使用md5
  • 严格配置pg_hba.conf，遵循最小权限原则
  • 定期更新密码，设置合理的密码有效期
  • 禁用不必要的超级用户远程访问

常见问题（FAQ） ​

Q1: 如何查看当前生效的配置参数？ ​

A1: 使用以下SQL命令查看：

sql
SELECT name, setting, unit, short_desc FROM pg_settings;

Q2: 配置文件修改后需要重启数据库吗？ ​

A2: 大多数参数使用pg_reload_conf()即可生效，少数需要重启数据库（如listen_addresses、max_connections、shared_buffers等）。可以通过查看pg_settings视图的context列来确定：

  • postmaster：需要重启数据库
  • sighup：需要重新加载配置
  • superuser：超级用户可以直接修改
  • user：普通用户可以修改会话级参数

Q3: postgresql.conf和postgresql.auto.conf的区别是什么？ ​

A3: postgresql.conf是手动编辑的主配置文件，postgresql.auto.conf是由ALTER SYSTEM命令自动生成的配置文件，优先级高于postgresql.conf。当两个文件中存在相同的配置项时，postgresql.auto.conf中的配置会覆盖postgresql.conf中的配置。

Q4: 如何备份和恢复PostgreSQL配置？ ​

A4: 可以通过以下步骤备份和恢复配置：

bash
# 备份配置文件
cp /etc/postgresql/14/main/postgresql.conf /etc/postgresql/14/main/postgresql.conf.backup
cp /etc/postgresql/14/main/pg_hba.conf /etc/postgresql/14/main/pg_hba.conf.backup

# 恢复配置文件
cp /etc/postgresql/14/main/postgresql.conf.backup /etc/postgresql/14/main/postgresql.conf
cp /etc/postgresql/14/main/pg_hba.conf.backup /etc/postgresql/14/main/pg_hba.conf

Q5: 如何优化PostgreSQL配置？ ​

A5: 优化PostgreSQL配置需要根据服务器硬件资源、应用需求和工作负载特点进行调整。建议：

  • 合理设置内存参数（shared_buffers, work_mem, maintenance_work_mem）
  • 调整WAL配置（wal_level, synchronous_commit, max_wal_size）
  • 优化检查点配置（checkpoint_completion_target, max_wal_size）
  • 配置合理的日志级别和格式
  • 根据工作负载调整并行查询参数
  • 定期监控和调整配置参数

Q6: 如何设置会话级配置参数？ ​

A6: 可以使用SET命令设置会话级配置参数，例如：

sql
-- 设置当前会话的work_mem
SET work_mem = '128MB';

-- 设置当前事务的隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Q7: 如何查看配置参数的默认值？ ​

A7: 使用以下SQL命令查看：

sql
SELECT name, reset_val AS default_value FROM pg_settings;

Q8: 如何验证配置文件语法正确性？ ​

A8: 可以使用postgresql-check命令验证配置文件语法：

bash
/usr/lib/postgresql/14/bin/postgres -C config_file=/etc/postgresql/14/main/postgresql.conf

或者使用pg_ctl命令：

bash
pg_ctl -D /var/lib/postgresql/14/main -t -c