如何在Ubuntu上定制PostgreSQL配置文件

如何在Ubuntu上定制PostgreSQL配置文件

在Ubuntu系统中，PostgreSQL的主配置文件集中存储在/etc/postgresql/<version>/main/目录下（<version>为安装的PostgreSQL版本，如15、16），核心配置文件为postgresql.conf（服务器参数设置）和pg_hba.conf（客户端认证规则）。以下是定制配置文件的详细步骤及关键参数说明：

1. 定位配置文件

首先确认PostgreSQL的安装版本及配置文件路径。可通过以下命令快速查找postgresql.conf的位置：

sudo -u postgres psql -c "SHOW config_file;"

输出结果类似：/etc/postgresql/15/main/postgresql.conf，即为配置文件的路径。

2. 修改postgresql.conf（核心配置）

使用文本编辑器（如nano或vim）打开配置文件：

sudo nano /etc/postgresql/15/main/postgresql.conf

以下是需要重点调整的关键参数及说明：

• 监听地址：修改listen_addresses以允许远程连接（默认仅本地localhost）。

  listen_addresses = '*'  # 允许所有IP连接；若需限制特定IP，可改为如'192.168.1.100'
  

• 最大连接数：根据服务器性能调整max_connections（默认100，建议不超过200）。

  max_connections = 150  # 增加并发连接能力，需同步调整操作系统限制（如ulimit -n）
  

• 内存缓冲区：
  • shared_buffers：设置共享内存缓冲区大小（建议为物理内存的25%，但不超过1GB）。

    shared_buffers = 2GB  # 示例：8GB内存设置为2GB
    

  • work_mem：每个排序/哈希操作的内存大小（默认1MB，建议4-16MB，根据查询复杂度调整）。

    work_mem = 8MB  # 提高排序、聚合操作的性能
    

• 日志设置：开启日志收集以方便排查问题。

  logging_collector = on  # 开启日志收集
  log_directory = 'pg_log'  # 日志存储目录（相对数据目录）
  log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'  # 日志文件名格式（按天分割）
  log_min_duration_statement = 1000  # 记录执行时间超过1秒的SQL语句
  

• WAL（预写日志）优化：提升数据可靠性与写入性能。

  wal_level = replica  # 副本级别（支持流复制）；生产环境建议用'replica'
  archive_mode = on  # 开启归档模式（需配合archive_command使用）
  archive_command = 'cp %p /var/lib/postgresql/15/main/archive/%f'  # 归档命令（需创建archive目录）
  

修改完成后，按Ctrl+O保存，Ctrl+X退出编辑器。

3. 修改pg_hba.conf（访问控制）

打开pg_hba.conf文件（路径与postgresql.conf同级）：

sudo nano /etc/postgresql/15/main/pg_hba.conf

添加或修改以下规则，控制客户端访问权限：

• 本地连接：允许postgres用户通过Unix socket连接（默认信任）。

  local   all             postgres                                peer
  

• 远程连接：允许所有IP地址通过密码（md5或scram-sha-256）连接（生产环境建议限制IP段，如192.168.1.0/24）。

  host    all             all             0.0.0.0/0               scram-sha-256  # 推荐使用更安全的scram-sha-256
  

• 特定数据库/用户：限制特定数据库（如mydb）仅允许特定用户（如myuser）从特定IP（如192.168.1.100）连接。

  host    mydb            myuser          192.168.1.100/32        scram-sha-256
  

修改完成后保存退出。

4. 重启PostgreSQL服务使配置生效

修改配置文件后，需重启服务使更改生效：

sudo systemctl restart postgresql

若需验证服务状态，可使用以下命令：

sudo systemctl status postgresql

若显示active (running)，则表示服务已正常启动。

5. 验证配置是否生效

• 验证postgresql.conf参数：通过psql连接到数据库，使用\show命令查看参数值。

  sudo -u postgres psql
  postgres=# \show listen_addresses  # 查看监听地址
  postgres=# \show max_connections    # 查看最大连接数
  

• 验证pg_hba.conf规则：尝试从远程主机连接数据库，确认是否允许访问。

  psql -U myuser -d mydb -h <服务器IP> -p 5432
  

  输入密码后若能成功登录，则表示远程访问配置成功。

注意事项

• 备份配置文件：修改前建议备份原文件，防止配置错误导致服务无法启动。

  sudo cp /etc/postgresql/15/main/postgresql.conf /etc/postgresql/15/main/postgresql.conf.bak
  sudo cp /etc/postgresql/15/main/pg_hba.conf /etc/postgresql/15/main/pg_hba.conf.bak
  

• 操作系统限制：若max_connections超过100，需调整操作系统的文件描述符限制（ulimit -n），避免连接数过多导致服务崩溃。
• 安全性：生产环境中，建议关闭trust认证方式（如local all all trust），改用scram-sha-256或md5，并限制远程访问的IP段。

通过以上步骤，即可在Ubuntu上完成PostgreSQL配置文件的定制，满足不同场景下的性能、安全及访问需求。