postgresql服务器配置

PostgreSQL服务器配置

环境准备与安装

PostgreSQL对操作系统有一定要求,推荐在Linux系统（如CentOS 7+/Ubuntu 20.04+）上部署，因其对系统资源管理更友好且兼容性佳。

操作系统与依赖安装

• CentOS：

  sudo yum update -y
  sudo yum install -y gcc make bison flex libtool ncurses-devel readline-devel zlib-devel openssl-devel perl

• Ubuntu：

  sudo apt update
  sudo apt install -y build-essential bison flex libreadline-dev libssl-dev zlib1g-dev libncurses5-dev libgdbm-dev libsqlite3-dev libxml2-dev libxml2-dev libxslt1-dev libffi-dev liblzma-dev

下载与编译安装

• 从官网下载PostgreSQL源码包（如 postgresql-15.8.tar.gz），解压后编译安装：

  tar -xzf postgresql-15.8.tar.gz
  cd postgresql-15.8
  ./configure --prefix=/usr/local/pgsql
  make -j$(nproc)
  sudo make install

• 配置环境变量（可选，方便命令使用）：

  echo 'export PATH=$PATH:/usr/local/pgsql/bin' >> ~/.bashrc
  source ~/.bashrc

核心配置文件调整

PostgreSQL主要配置通过两个文件控制：postgresql.conf（服务器行为）和 pg_hba.conf（客户端认证）。

配置文件位置

• 默认路径：
  • postgresql.conf：/usr/local/pgsql/data/postgresql.conf
  • pg_hba.conf：/usr/local/pgsql/data/pg_hba.conf
• 数据目录初始化：

  sudo -u postgres initdb -D /usr/local/pgsql/data
  sudo systemctl start postgresql

postgresql.conf 关键参数说明
| 参数名 | 作用 | 常见配置 | 说明 |
|——–|——|———-|——|
| shared_buffers | 共享缓冲区大小（内存） | shared_buffers = 256MB | 推荐设置为物理内存的1/4~1/3 |
| work_mem | 单个查询工作内存 | work_mem = 8MB | 根据查询复杂度调整，复杂查询可增大 |
| effective_cache_size | 有效缓存大小 | effective_cache_size = 80% | 建议为系统物理内存的80% |
| max_connections | 最大连接数 | max_connections = 100 | 根据并发需求调整，默认100 |
| wal_level | WAL日志详细程度 | wal_level = logical | logical 支持逻辑复制，full 提高恢复安全性 |
| archive_mode | WAL归档模式 | archive_mode = on | 开启后自动归档日志，避免数据丢失 |

pg_hba.conf 认证规则
该文件定义客户端连接认证方式，格式为 host/deny + 数据库 + 用户 + 认证方法。

• 示例配置（允许本地用户md5认证）：

  # IPv4 local connections:
  host    all             all             127.0.0.1/32            md5
  # IPv6 local connections:
  host    all             all             ::1/128                 md5
  # Allow remote connections from specific IP (e.g., 192.168.1.0/24)
  host    all             all             192.168.1.0/24         md5

• 认证方法推荐：md5（密码加密传输）或 scram-sha-256（更安全的密码哈希算法）。

数据库连接与网络设置

监听地址与端口

• listen_addresses：指定监听地址，默认 localhost（仅允许本地连接）。
  • 修改为 允许所有地址：

    sudo sed -i 's/^listen_addresses = \'localhost\'/listen_addresses = \'*\'/' /usr/local/pgsql/data/postgresql.conf

• port：默认 5432，修改后需通知客户端。

数据库初始化与角色创建

• 创建数据库（示例）：

  createdb mydb

• 创建用户并授权（示例）：

  createuser --interactive
  # 输入用户名（如 app_user）、密码、是否创建数据库（N）、是否超级用户（N）

性能优化配置

内存与缓存优化

• 根据系统内存调整 shared_buffers 和 effective_cache_size：

  # 示例：16GB内存
  shared_buffers = 6GB
  effective_cache_size = 12GB

• 启用预取（enable_seqscan）和并行查询（max_parallel_workers_per_gather）：

  enable_seqscan = on
  max_parallel_workers_per_gather = 4

日志与归档配置

• 调整日志记录级别（log_min_duration_statement）：

  log_min_duration_statement = 5000  # 记录5秒以上查询

• 启用WAL归档（确保 archive_mode = on 且 archive_command 正确）：

  archive_command = 'cp %p /var/lib/postgresql/archives/%f'

安全加固与权限管理

身份验证强化

• 将 md5 替换为 scram-sha-256（推荐）：

  sudo sed -i 's/md5/scram-sha-256/' /usr/local/pgsql/data/pg_hba.conf

• 设置密码策略：

  alter user app_user with password 'newpassword' password_lifecycle_options (min_password_length = 12, password_reuse_time = 180);

最小权限原则

• 为应用创建专用角色（非超级用户）：

  create role app_user login password 'securepwd';
  create database myappdb owner app_user;
  grant connect on database myappdb to app_user;
  grant usage on schema public to app_user;

故障排查与维护

常见问题

• 连接失败（”could not connect to server”）：检查 pg_hba.conf 认证规则和 listen_addresses 配置。
• 性能瓶颈（查询慢）：查看 pg_stat_statements 统计信息，调整 work_mem 或增加 shared_buffers。
• 内存溢出（OOM）：通过 pg_stat_activity 查看进程占用，优化大查询或增加物理内存。

相关问答FAQs

如何允许远程PostgreSQL连接？
答：

1. 修改 postgresql.conf 中的 listen_addresses 参数为 （允许所有地址），或指定具体IP。
2. 修改 pg_hba.conf，添加允许远程IP的认证规则（如 host all all 192.168.1.0/24 md5）。
3. 重启PostgreSQL服务：sudo systemctl restart postgresql。

如何优化PostgreSQL的内存使用？
答：

1. 调整共享缓冲区：根据系统内存设置 shared_buffers（如16GB内存可设为6GB）。
2. 启用缓存优化：设置 effective_cache_size 为物理内存的80%以上，提升缓存命中率。
3. 限制连接数：根据并发需求调整 max_connections，避免资源过度占用。
4. 监控内存使用：使用 pg_stat_activity 和 pg_stat_statements 视图，定位高内存消耗的查询。