34、PostgreSQL连接管理：连接字符串配置、SSL连接、连接池（PgBouncer集成）、故障转移

说实话，PostgreSQL 的连接管理这块，我早年踩过不少坑。记得刚接手一个金融项目时，生产环境突然出现大量「too many clients already」错误，当时真是头皮发麻。后来才发现，不是 PG 不行，是我根本没理解连接池和故障转移的玩法。

今天咱们就把这块彻底捋清楚。从最基础的连接字符串，到 SSL 加密，再到 PgBouncer 集成和故障转移，一步到位。

34.1 连接字符串配置：不只是拼字符串

很多新手觉得连接字符串就是拼个 IP、端口、用户名。其实不然。我习惯把连接字符串分成三部分：基础参数、行为控制、安全选项。

34.1.1 标准连接字符串格式

// libpq 风格（C 程序常用）
host=192.168.1.100 port=5432 dbname=mydb user=admin password=secret

// URI 风格（越来越流行）
postgresql://admin:secret@192.168.1.100:5432/mydb

我个人更推荐 URI 风格。为什么？因为它可以塞进配置文件、环境变量，甚至 URL 参数里，解析起来也统一。

34.1.2 关键参数详解

参数	说明	我的建议
host	服务器地址，可多个（逗号分隔）	故障转移时用多个 host
port	默认 5432	生产环境建议改端口
connect_timeout	连接超时秒数	设 5-10 秒，别用默认 0
keepalives	是否启用 TCP 保活	必须设为 1
application_name	标识客户端	方便 DBA 定位问题

34.2 SSL 连接：别裸奔

你想想看，如果数据库连接不加密，密码和查询在网络上就是明文传输。我曾经在一个客户现场做过抓包演示，三分钟就拿到了数据库密码。嗯，从那以后，SSL 就成了我的标配。

34.2.1 服务端配置

首先，PostgreSQL 需要开启 SSL 支持。在 postgresql.conf 里：

ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'
ssl_ca_file = '/etc/ssl/certs/ca.crt'   # 如果做双向认证

然后修改 pg_hba.conf，强制要求 SSL：

# 强制 SSL 连接
hostssl mydb myuser 192.168.1.0/24 md5

34.2.2 客户端连接字符串

// 启用 SSL，但不验证证书（开发环境）
postgresql://admin:secret@192.168.1.100:5432/mydb?sslmode=require

// 验证证书（生产环境）
postgresql://admin:secret@192.168.1.100:5432/mydb?sslmode=verify-full&sslrootcert=/etc/ssl/certs/ca.crt

34.2.3 C 代码中的 SSL 配置

#include <libpq-fe.h>

PGconn *conn = PQconnectdb(
    "host=192.168.1.100 "
    "port=5432 "
    "dbname=mydb "
    "user=admin "
    "password=secret "
    "sslmode=verify-full "
    "sslrootcert=/etc/ssl/certs/ca.crt"
);

if (PQstatus(conn) != CONNECTION_OK) {
    fprintf(stderr, "连接失败: %s\n", PQerrorMessage(conn));
    // 这里我习惯记录日志并尝试备用节点
}

34.3 连接池：PgBouncer 集成

为什么需要连接池？说白了，PostgreSQL 每个连接都是一个独立进程。如果 1000 个客户端直连，PG 就要 fork 1000 个进程，内存和 CPU 都扛不住。PgBouncer 就是中间那个「调度员」。

34.3.1 PgBouncer 的三种模式

模式	特点	适用场景
session	会话级复用，连接归你直到断开	需要 SET 语句、临时表的场景
transaction	事务级复用，事务结束就回收	大部分 Web 应用，性能最好
statement	语句级复用，最激进	极少用，容易出问题

我个人 90% 的场景都用 transaction 模式。性能好，而且大多数 ORM 和框架都兼容。

34.3.2 配置示例

// pgbouncer.ini
[databases]
mydb = host=192.168.1.100 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

// 连接池大小
pool_mode = transaction
default_pool_size = 25
max_client_conn = 200

// 超时控制
server_idle_timeout = 600
client_idle_timeout = 0

34.3.3 C 程序连接 PgBouncer

其实很简单，把连接地址改成 PgBouncer 的地址就行：

PGconn *conn = PQconnectdb(
    "host=127.0.0.1 "
    "port=6432 "           // PgBouncer 端口
    "dbname=mydb "
    "user=app_user "
    "password=app_pass"
);

嗯，这里要注意：PgBouncer 不支持 PREPARE 语句跨连接复用。如果你用了大量 prepared statement，建议用 session 模式，或者干脆直连。

34.4 故障转移：别让单点搞垮你

我记得有一次凌晨三点被电话吵醒，主库挂了，整个服务瘫痪。那时候还没有做自动故障转移，只能手动切。从那以后，我每个项目都强制要求故障转移方案。

34.4.1 连接字符串中的多主机

PostgreSQL 的 libpq 支持在连接字符串里指定多个主机：

postgresql://admin:secret@192.168.1.100,192.168.1.101,192.168.1.102:5432/mydb

libpq 会按顺序尝试连接。第一个连不上，自动试第二个。但有个问题：它不会自动切回主库。所以需要配合 target_session_attrs 参数：

postgresql://admin:secret@192.168.1.100,192.168.1.101:5432/mydb?target_session_attrs=read-write

这个参数告诉 libpq：我只连能读写的主库。如果当前节点是只读的，自动跳过。

34.4.2 应用层的重试逻辑

光靠连接字符串还不够。我习惯在 C 代码里加一层重试：

PGconn *connect_with_retry(const char *conninfo, int max_retries) {
    PGconn *conn = NULL;
    
    for (int i = 0; i < max_retries; i++) {
        conn = PQconnectdb(conninfo);
        
        if (PQstatus(conn) == CONNECTION_OK) {
            return conn;  // 连接成功
        }
        
        fprintf(stderr, "第 %d 次连接失败: %s\n", i+1, PQerrorMessage(conn));
        PQfinish(conn);
        
        // 指数退避：1s, 2s, 4s...
        sleep(1 << i);
    }
    
    return NULL;  // 彻底失败
}

34.4.3 结合 PgBouncer 做故障转移

PgBouncer 本身不处理故障转移，但可以配合 pgbouncer-rr 或者 HAProxy 做前端负载均衡。我常用的方案是：

1. 前端 HAProxy 做 VIP（虚拟 IP）
2. HAProxy 后端指向多个 PgBouncer 实例
3. 每个 PgBouncer 连接 PostgreSQL 主库
4. 用 Patroni 或 repmgr 管理 PG 主备切换

这样，任何一层挂了，都能自动切换。当然，代价是架构复杂了，但生产环境值得。

34.5 知识体系总览

下面这张图是我自己总结的 PostgreSQL 连接管理全景。你看一眼，基本就全通了。

34.6 总结一下

连接管理这件事，说白了就是三个层次：

• 连接字符串：别只写 IP 端口，把超时、保活、应用名都配好
• SSL：生产环境必须开，别裸奔
• 连接池 + 故障转移：PgBouncer 解决并发问题，多主机 + 重试解决单点问题

我见过太多项目，上线前觉得「连接池没必要」「故障转移以后再说」，结果出问题的时候手忙脚乱。嗯，希望你看完这篇，能把这些配置一次性做到位。

---

公众号:蓝海资料掘金营，微信deep3321