PostgreSQL 连接池管理

连接池是数据库应用中至关重要的组件，它可以显著提高应用的性能和可扩展性。本章将介绍 PostgreSQL 连接池的概念、常用工具以及最佳实践。

为什么需要连接池？

连接池解决的问题

每次建立数据库连接都需要以下步骤：

1. TCP 握手
2. 身份验证
3. 建立会话
4. 初始化连接参数

这个过程需要消耗时间和资源。对于高并发应用，频繁创建和销毁连接会成为严重的性能瓶颈。

连接池的优势

优势	说明
减少延迟	复用已有连接，避免连接建立开销
资源控制	限制最大连接数，防止数据库过载
提高吞吐	更高效地处理并发请求
连接复用	避免频繁创建销毁连接

PostgreSQL 最大连接数

PostgreSQL 本身有最大连接数限制：

-- 查看当前最大连接数
SHOW max_connections;

-- 查看当前连接数
SELECT COUNT(*) FROM pg_stat_activity;

-- 查看连接详情
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query,
    query_start
FROM pg_stat_activity
ORDER BY query_start;

-- 查看连接来源统计
SELECT 
    usename,
    application_name,
    COUNT(*) AS connection_count
FROM pg_stat_activity
GROUP BY usename, application_name
ORDER BY connection_count DESC;

修改最大连接数

# postgresql.conf

# 最大连接数（默认 100）
max_connections = 200

# 每个用户的连接限制
# ALTER USER username CONNECTION LIMIT 50;

# 超级用户保留连接数
superuser_reserved_connections = 3

注意：修改 max_connections 需要重启数据库。

PgBouncer 连接池

PgBouncer 是 PostgreSQL 最流行的连接池工具，轻量高效。

PgBouncer 架构

┌─────────────────────────────────────────────────────┐
│                    PgBouncer                         │
│  ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐   │
│  │ Client  │ │ Client  │ │ Client  │ │ Client  │   │
│  │ Connection│Connection│Connection│Connection│   │
│  └────┬────┘ └────┬────┘ └────┬────┘ └────┬────┘   │
│       │           │           │           │         │
│       └───────────┴─────┬─────┴───────────┘         │
│                         │                           │
│  ┌──────────────────────┴──────────────────────┐   │
│  │            Server Connection Pool            │   │
│  │  ┌───────┐ ┌───────┐ ┌───────┐ ┌───────┐   │   │
│  │  │  DB   │ │  DB   │ │  DB   │ │  DB   │   │   │
│  │  │ Conn  │ │ Conn  │ │ Conn  │ │ Conn  │   │   │
│  │  └───────┘ └───────┘ └───────┘ └───────┘   │   │
│  └─────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────┘

安装 PgBouncer

Ubuntu/Debian：

sudo apt-get update
sudo apt-get install pgbouncer

CentOS/RHEL：

sudo yum install pgbouncer

Docker：

docker run -d \
    --name pgbouncer \
    -p 6432:6432 \
    -v /path/to/pgbouncer.ini:/etc/pgbouncer/pgbouncer.ini \
    -v /path/to/userlist.txt:/etc/pgbouncer/userlist.txt \
    edoburu/pgbouncer

配置 PgBouncer

pgbouncer.ini 主配置文件：

[databases]
; 数据库连接配置
; 格式：数据库名 = host=主机 port=端口 dbname=实际数据库名
mydb = host=127.0.0.1 port=5432 dbname=mydb

; 生产环境配置示例
production = host=192.168.1.100 port=5432 dbname=production user=appuser

[pgbouncer]
; 监听地址和端口
listen_addr = 0.0.0.0
listen_port = 6432

; 管理接口
admin_users = postgres
stats_users = stats

; 连接池模式
; session: 会话级连接池（适合大多数场景）
; transaction: 事务级连接池（高并发，但有使用限制）
; statement: 语句级连接池（适合只读场景）
pool_mode = session

; 最大客户端连接数
max_client_conn = 1000

; 默认连接池大小
default_pool_size = 25

; 最小连接池大小
min_pool_size = 5

; 每个用户-数据库对的最大连接数
max_db_connections = 50

; 预留连接数
reserve_pool_size = 5
reserve_pool_timeout = 3

; 连接空闲超时
server_idle_timeout = 600

; 连接生命周期
server_lifetime = 3600

; 连接重试次数
server_connect_timeout = 15

; 日志配置
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
log_stats = 1
stats_period = 60

; 认证配置
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; 管理数据库
admin_users = postgres

userlist.txt 用户认证文件：

# 格式："用户名" "密码"
# 密码可以是明文或 md5 格式
"postgres" "md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
"appuser" "your_password_here"
"readonly" "readonly_password"

# 生成 md5 密码
echo -n "passwordpostgres" | md5sum
# 输出：md5 后面跟着的字符串

连接池模式详解

Session 模式（会话级）

客户端连接 -> 分配服务器连接 -> 保持到客户端断开

特点：

• 连接在会话期间保持
• 支持所有 PostgreSQL 特性
• 适合大多数应用
• 默认推荐模式

pool_mode = session

Transaction 模式（事务级）

事务开始 -> 分配连接 -> 事务结束 -> 释放连接

特点：

• 事务结束后立即释放连接
• 更高效的连接利用率
• 有使用限制（见下文）
• 适合高并发短事务场景

pool_mode = transaction

Transaction 模式的限制：

-- 不支持的操作：
-- 1. SET 语句（会丢失）
SET work_mem = '256MB';

-- 2. PREPARE 语句
PREPARE my_query AS SELECT * FROM users WHERE id = $1;

-- 3. WITH HOLD 游标
DECLARE my_cursor CURSOR WITH HOLD FOR SELECT * FROM users;

-- 4. 临时表（on commit preserve rows）
CREATE TEMP TABLE temp_data ON COMMIT PRESERVE ROWS AS SELECT * FROM users;

-- 5. 通知监听
LISTEN channel_name;

-- 6. 会话级咨询锁
SELECT pg_advisory_lock(123);

-- 解决方案：使用 Session 模式或应用层处理

Statement 模式（语句级）

语句开始 -> 分配连接 -> 语句完成 -> 释放连接

特点：

• 每条语句完成后释放连接
• 不支持事务
• 适合只读查询
• 最高效但限制最多

pool_mode = statement

管理 PgBouncer

# 启动服务
sudo systemctl start pgbouncer

# 停止服务
sudo systemctl stop pgbouncer

# 重启服务
sudo systemctl restart pgbouncer

# 查看状态
sudo systemctl status pgbouncer

# 连接到管理数据库
psql -h 127.0.0.1 -p 6432 -U postgres pgbouncer

管理命令：

-- 连接到 PgBouncer 管理接口
psql -h 127.0.0.1 -p 6432 -U postgres pgbouncer

-- 查看连接池状态
SHOW POOLS;

-- 查看客户端连接
SHOW CLIENTS;

-- 查看服务器连接
SHOW SERVERS;

-- 查看数据库配置
SHOW DATABASES;

-- 查看统计信息
SHOW STATS;

-- 查看 DNS 配置
SHOW DNS_HOSTS;

-- 暂停数据库
PAUSE mydb;

-- 恢复数据库
RESUME mydb;

-- 禁用数据库
DISABLE mydb;

-- 启用数据库
ENABLE mydb;

-- 重载配置
RELOAD;

-- 关闭连接
SHUTDOWN;

监控 PgBouncer

-- 查看连接池统计
SHOW STATS;

-- 输出示例：
-- database | total_xact_count | total_query_count | total_received | total_sent
-- ---------+------------------+-------------------+----------------+------------
-- mydb     | 12345            | 23456             | 12345678       | 23456789

-- 查看连接池详情
SHOW POOLS;

-- 输出示例：
-- database | pool_mode | cl_active | cl_waiting | sv_active | sv_idle | sv_used
-- ---------+-----------+-----------+------------+-----------+---------+--------
-- mydb     | session   | 10        | 5          | 10        | 15      | 0

监控脚本示例：

#!/bin/bash
# PgBouncer 监控脚本

PGBOUNCER_HOST="127.0.0.1"
PGBOUNCER_PORT="6432"
PGBOUNCER_USER="stats"

# 获取连接池状态
psql -h $PGBOUNCER_HOST -p $PGBOUNCER_PORT -U $PGBOUNCER_USER pgbouncer -c "SHOW POOLS;"

# 获取统计信息
psql -h $PGBOUNCER_HOST -p $PGBOUNCER_PORT -U $PGBOUNCER_USER pgbouncer -c "SHOW STATS;"

# 检查等待连接的客户端
WAITING=$(psql -h $PGBOUNCER_HOST -p $PGBOUNCER_PORT -U $PGBOUNCER_USER pgbouncer -t -c \
    "SELECT SUM(cl_waiting) FROM SHOW POOLS;")

if [ "$WAITING" -gt 10 ]; then
    echo "警告：有 $WAITING 个客户端在等待连接"
fi

Pgpool-II

Pgpool-II 是另一个流行的 PostgreSQL 连接池工具，除了连接池外还提供负载均衡、复制和故障转移功能。

Pgpool-II vs PgBouncer

特性	PgBouncer	Pgpool-II
连接池	是	是
负载均衡	否	是
复制	否	是
故障转移	否	是
内存占用	低	较高
配置复杂度	简单	复杂

安装 Pgpool-II

# Ubuntu/Debian
sudo apt-get install pgpool2

# CentOS/RHEL
sudo yum install pgpool-II

基本配置

# /etc/pgpool-II/pgpool.conf

# 监听配置
listen_addresses = '*'
port = 9999

# 后端服务器配置
backend_hostname0 = '192.168.1.10'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/18/main'

backend_hostname1 = '192.168.1.11'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/18/main'

# 连接池配置
connection_cache = on
num_init_children = 32
max_pool = 4

# 负载均衡
load_balance_mode = on

# 健康检查
health_check_period = 30
health_check_timeout = 10
health_check_user = 'postgres'

应用层连接池

除了中间件连接池，应用层也常使用连接池：

Java (HikariCP)

// Maven 依赖
// <dependency>
//     <groupId>com.zaxxer</groupId>
//     <artifactId>HikariCP</artifactId>
//     <version>5.0.1</version>
// </dependency>

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
config.setUsername("appuser");
config.setPassword("password");
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);

HikariDataSource dataSource = new HikariDataSource(config);

Python (psycopg2.pool)

from psycopg2 import pool

# 创建连接池
connection_pool = pool.ThreadedConnectionPool(
    minconn=5,
    maxconn=20,
    host='localhost',
    port=5432,
    database='mydb',
    user='appuser',
    password='password'
)

# 获取连接
conn = connection_pool.getconn()
try:
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM users')
    results = cursor.fetchall()
finally:
    connection_pool.putconn(conn)

Node.js (pg-pool)

const { Pool } = require('pg');

const pool = new Pool({
    host: 'localhost',
    port: 5432,
    database: 'mydb',
    user: 'appuser',
    password: 'password',
    max: 20,                    // 最大连接数
    idleTimeoutMillis: 30000,   // 空闲超时
    connectionTimeoutMillis: 2000, // 连接超时
});

// 使用连接
const client = await pool.connect();
try {
    const result = await client.query('SELECT * FROM users');
    console.log(result.rows);
} finally {
    client.release();
}

Go (pgxpool)

package main

import (
    "context"
    "github.com/jackc/pgx/v5/pgxpool"
)

func main() {
    poolConfig, _ := pgxpool.ParseConfig(
        "postgres://appuser:password@localhost:5432/mydb",
    )
    poolConfig.MaxConns = 20
    poolConfig.MinConns = 5

    pool, _ := pgxpool.NewWithConfig(context.Background(), poolConfig)
    defer pool.Close()

    // 使用连接
    rows, _ := pool.Query(context.Background(), "SELECT * FROM users")
    defer rows.Close()

    for rows.Next() {
        // 处理结果
    }
}

连接池最佳实践

连接数计算

最大连接数 = (CPU核心数 * 2) + 有效磁盘数

例如：8核 CPU + 1块 SSD = 17 个连接

连接池大小建议

# PgBouncer 示例

# 应用服务器数量：4
# 每个应用服务器连接池大小：25
# PostgreSQL 最大连接数：100 + 超级用户预留

# 推荐配置
default_pool_size = 25
max_client_conn = 500   # 客户端连接数可以更大
max_db_connections = 100 # 数据库连接数需要控制

超时设置

# PgBouncer
server_connect_timeout = 15    # 连接超时
server_idle_timeout = 600      # 空闲超时
server_lifetime = 3600         # 连接生命周期
query_timeout = 30             # 查询超时
client_idle_timeout = 0        # 客户端空闲超时（0表示不超时）

# PostgreSQL
statement_timeout = 30000      # 语句超时（毫秒）
lock_timeout = 10000           # 锁等待超时
idle_in_transaction_session_timeout = 60000  # 空闲事务超时

监控告警

-- 监控连接使用率
SELECT 
    (SELECT COUNT(*) FROM pg_stat_activity) AS current_connections,
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections,
    ROUND((SELECT COUNT(*) FROM pg_stat_activity)::numeric / 
          (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') * 100, 2) AS usage_pct;

-- 使用率超过 80% 时告警

连接泄漏检测

-- 查找长时间空闲的连接
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query_start,
    now() - query_start AS idle_time,
    query
FROM pg_stat_activity
WHERE state = 'idle'
  AND query_start < now() - INTERVAL '10 minutes'
ORDER BY idle_time DESC;

-- 终止空闲连接
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND query_start < now() - INTERVAL '30 minutes'
  AND pid != pg_backend_pid();

故障排查

连接数耗尽

-- 查看当前连接
SELECT * FROM pg_stat_activity;

-- 检查连接来源
SELECT 
    client_addr,
    application_name,
    COUNT(*) AS conn_count
FROM pg_stat_activity
GROUP BY client_addr, application_name
ORDER BY conn_count DESC;

-- 临时增加连接数（需要重启）
-- 修改 postgresql.conf: max_connections = 200

-- 或者终止非必要连接
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'problem_user'
  AND state = 'idle';

连接超时

# 检查网络连通性
ping postgres_server
telnet postgres_server 5432

# 检查防火墙
sudo iptables -L -n | grep 5432

# 检查 pg_hba.conf
cat /etc/postgresql/18/main/pg_hba.conf | grep -v "^#" | grep -v "^$"

# 检查 PostgreSQL 日志
tail -f /var/log/postgresql/postgresql-18-main.log

PgBouncer 连接问题

# 查看 PgBouncer 日志
tail -f /var/log/pgbouncer/pgbouncer.log

# 检查配置文件语法
pgbouncer -d /etc/pgbouncer/pgbouncer.ini

# 连接管理接口查看状态
psql -h 127.0.0.1 -p 6432 -U postgres pgbouncer -c "SHOW POOLS;"

# 常见问题：
# 1. 认证失败：检查 userlist.txt 密码格式
# 2. 连接池满：增加 default_pool_size
# 3. 数据库连接失败：检查 PostgreSQL 是否运行

小结

本章我们学习了 PostgreSQL 连接池管理：

1. 连接池原理：复用连接减少开销
2. PgBouncer：轻量级连接池配置和使用
3. 连接池模式：Session、Transaction、Statement
4. Pgpool-II：功能丰富的连接池和负载均衡
5. 应用层连接池：各种语言的连接池配置
6. 最佳实践：连接数计算、超时设置、监控告警
7. 故障排查：常见问题解决方案

练习

1. 安装并配置 PgBouncer，设置 Session 模式
2. 配置应用使用 PgBouncer 连接池
3. 监控连接池状态，设置告警阈值
4. 模拟高并发场景，对比有无连接池的性能差异

参考资源

• PgBouncer 官方文档
• Pgpool-II 官方文档
• PostgreSQL 连接管理