PostgreSQL生产环境完整配置：性能调优、高可用主从与pgBouncer连接池实战

PostgreSQL是功能最完整的开源关系数据库，在Django、Ruby on Rails等现代Web框架中被广泛使用。本文针对香港VPS的实际硬件规格，给出PostgreSQL从安装到高可用的完整配置方案。

一、安装PostgreSQL 16

<code"># Ubuntu 22.04安装PostgreSQL 16
apt install -y postgresql-common
/usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

apt install -y postgresql-16 postgresql-contrib-16

# 验证安装
systemctl status postgresql
pg_lsclusters

二、基础安全配置

<code"># 切换到postgres用户
sudo -u postgres psql

-- 修改超级用户密码
ALTER USER postgres PASSWORD '强密码至少32位';

-- 创建应用专用用户（非超级用户）
CREATE USER appuser WITH PASSWORD '应用用户密码';
CREATE DATABASE appdb OWNER appuser;
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;

-- 最小权限：只授予必要权限
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO appuser;
\q

<code"># /etc/postgresql/16/main/pg_hba.conf
# 只允许本地和特定IP访问

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer      # 本地postgres用户
local   all             all                                     scram-sha-256
host    all             all             127.0.0.1/32            scram-sha-256
host    appdb           appuser         Web服务器内网IP/32       scram-sha-256
# 禁止所有其他来源
# （注释掉或删除其他host行）

三、性能调优（postgresql.conf）

<code"># 使用pgtune自动计算推荐值：https://pgtune.leopard.in.ua
# 以下为4核8G内存，SSD磁盘，Web应用场景的参考值

# ===== 内存配置（最重要）=====
shared_buffers = 2GB              # 25%物理内存（关键：越大越好，到物理内存40%）
effective_cache_size = 6GB        # 75%物理内存（告诉优化器可用缓存大小，不实际分配）
work_mem = 16MB                   # 每个排序/哈希操作的内存（并发高时适当降低）
maintenance_work_mem = 512MB      # VACUUM/CREATE INDEX等维护操作的内存

# ===== WAL（Write-Ahead Log）=====
wal_buffers = 64MB                # WAL缓冲区（通常1/32 shared_buffers）
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB
wal_compression = on              # 压缩WAL（节省磁盘，轻微增加CPU）

# ===== 连接与并发 =====
max_connections = 100             # 生产环境通常100~200（配合pgBouncer）
superuser_reserved_connections = 3

# ===== 查询优化器 =====
default_statistics_target = 100   # 统计采样率（越高优化器决策越准确）
random_page_cost = 1.1            # SSD磁盘设为1.1（HDD默认4.0）
effective_io_concurrency = 200    # SSD并发I/O能力

# ===== 并行查询 =====
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

# ===== 日志 =====
log_min_duration_statement = 1000  # 记录超过1秒的慢查询（毫秒）
log_checkpoints = on
log_connections = off              # 高并发时关闭，避免日志过多
log_temp_files = 0                 # 记录所有临时文件（帮助识别work_mem不足）
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

# ===== 自动清理 =====
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 30s           # 每30秒检查一次
autovacuum_vacuum_cost_delay = 2ms # SSD磁盘降低延迟
autovacuum_vacuum_scale_factor = 0.05  # 5%行变化触发VACUUM

<code"># 应用配置并重启
systemctl restart postgresql-16

# 验证配置
sudo -u postgres psql -c "SHOW shared_buffers;"
sudo -u postgres psql -c "SHOW work_mem;"

四、主从流复制配置

主节点配置

<code"># postgresql.conf（主节点）
wal_level = replica
max_wal_senders = 5              # 最多5个从节点
wal_keep_size = 1GB              # 保留足够的WAL供从节点追赶
synchronous_commit = on          # 同步提交（强一致性，性能稍低）

# 创建复制专用用户
sudo -u postgres psql -c "
CREATE USER replicator REPLICATION LOGIN PASSWORD '复制用户密码';
"

# pg_hba.conf添加从节点访问权限
echo "host replication replicator 从节点内网IP/32 scram-sha-256" >> /etc/postgresql/16/main/pg_hba.conf
systemctl reload postgresql-16

从节点初始化

<code"># 在从节点上执行（先停止PostgreSQL）
systemctl stop postgresql-16

# 清空从节点数据目录（谨慎！）
rm -rf /var/lib/postgresql/16/main/*

# 从主节点拉取基础备份
sudo -u postgres pg_basebackup \
    --host=主节点内网IP \
    --username=replicator \
    --pgdata=/var/lib/postgresql/16/main \
    --wal-method=stream \
    --progress \
    --verbose

# 创建恢复配置
cat > /var/lib/postgresql/16/main/postgresql.auto.conf << EOF
primary_conninfo = 'host=主节点内网IP port=5432 user=replicator password=复制用户密码 sslmode=prefer'
promote_trigger_file = '/tmp/postgresql.trigger.5432'
EOF

# 标记为从节点
touch /var/lib/postgresql/16/main/standby.signal

# 启动从节点
systemctl start postgresql-16

# 在主节点验证复制状态
sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;"

五、pgBouncer连接池

Django/Rails应用的每个请求都会建立数据库连接，高并发时大量连接创建/销毁造成巨大开销。pgBouncer在应用和数据库之间维护连接池，显著提升性能。

<code"># 安装pgBouncer
apt install pgbouncer -y

<code"># /etc/pgbouncer/pgbouncer.ini
[databases]
# 应用连接 pgbouncer:5432，pgbouncer转发到 localhost:5432
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

listen_addr = 127.0.0.1
listen_port = 5432           # 应用连接此端口（看起来像直连PG）

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# 连接池模式
pool_mode = transaction      # 事务级复用（Django推荐，会话变量不保持）

# 连接数限制
max_client_conn = 1000       # 最多1000个客户端连接
default_pool_size = 25       # 每个数据库池的后端连接数
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

# 超时设置
server_idle_timeout = 600    # 后端空闲连接10分钟后关闭
client_idle_timeout = 0      # 客户端空闲连接不主动关闭

# 管理界面
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

<code"># /etc/pgbouncer/userlist.txt（存储客户端密码）
"appuser" "SCRAM-SHA-256$4096:盐值$加密密码"

# 从PostgreSQL获取加密密码
sudo -u postgres psql -t -c "SELECT rolpassword FROM pg_authid WHERE rolname='appuser';"

<code"># 启动pgBouncer
systemctl enable pgbouncer
systemctl start pgbouncer

# 查看连接池状态
psql -h 127.0.0.1 -p 5432 -U pgbouncer_admin pgbouncer -c "SHOW POOLS;"
psql -h 127.0.0.1 -p 5432 -U pgbouncer_admin pgbouncer -c "SHOW STATS;"

六、慢查询分析

<code"># 开启pg_stat_statements扩展（查询统计）
sudo -u postgres psql -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

# postgresql.conf添加
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

# 重启后查看最慢的10条查询
sudo -u postgres psql -c "
SELECT
    substring(query, 1, 80) AS short_query,
    round(total_exec_time::numeric, 2) AS total_ms,
    calls,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER())::numeric, 2) AS pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
"

七、定期维护任务

<code">#!/bin/bash
# /usr/local/bin/postgresql-maintenance.sh
# 每周日凌晨3点执行

sudo -u postgres psql appdb << 'SQL'
-- 更新统计信息（帮助查询优化器做出正确决策）
ANALYZE VERBOSE;

-- 清理死元组，回收空间
VACUUM (ANALYZE, VERBOSE);

-- 重置慢查询统计（方便下周分析）
SELECT pg_stat_statements_reset();
SQL

echo "PostgreSQL维护完成：$(date)"

八、总结

PostgreSQL调优的核心是合理配置内存（shared_buffers + work_mem）和WAL，配合pgBouncer连接池应对高并发，主从流复制保障高可用。IDC.Net的香港VPS同账号内网互通，主从节点通过内网传输WAL，延迟极低，复制延迟通常在1ms以内，是部署PostgreSQL高可用集群的理想平台。