PostgreSQL 基于Pgbouncer的连接池与读写分离

Pgbouncer概述

什么是Pgbouncer

Pgbouncer是一个轻量级的PostgreSQL连接池工具，专注于连接池管理，提供以下核心功能：

连接池管理，减少PostgreSQL服务器连接数
支持多种连接池模式
低资源消耗，适合高并发场景
支持透明的应用连接
支持事务级和会话级连接复用

适用场景

高并发连接场景，需要限制PostgreSQL服务器连接数
资源受限环境，需要轻量级连接池解决方案
已有应用架构，希望透明集成连接池
需要简单的负载均衡功能

连接池模式

Pgbouncer支持三种连接池模式：

Session模式
- 每个客户端连接对应一个服务器连接，直到客户端断开
- 支持所有PostgreSQL功能
- 资源消耗较高
Transaction模式
- 服务器连接在事务结束后释放到连接池
- 支持大部分PostgreSQL功能
- 资源消耗中等
- 适合OLTP场景
Statement模式
- 服务器连接在语句执行结束后释放
- 不支持事务和临时表
- 资源消耗最低
- 适合简单查询场景

Pgbouncer安装

从软件包管理器安装

CentOS/RHEL

bash

# CentOS 7/RHEL 7
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y pgbouncer

# CentOS 8/RHEL 8
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
dnf install -y pgbouncer

Ubuntu/Debian

bash

# 添加PostgreSQL仓库
echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
apt-get update

# 安装Pgbouncer
apt-get install -y pgbouncer

从源代码安装

1. 安装依赖

bash

# CentOS/RHEL
yum install -y gcc make libevent-devel libpq-devel

# Ubuntu/Debian
apt-get install -y gcc make libevent-dev libpq-dev

2. 下载并编译Pgbouncer

bash

# 下载最新稳定版
wget https://www.pgbouncer.org/downloads/files/1.18.0/pgbouncer-1.18.0.tar.gz
tar xzf pgbouncer-1.18.0.tar.gz
cd pgbouncer-1.18.0

# 配置和编译
./configure --prefix=/usr/local/pgbouncer
make && sudo make install

3. 创建配置文件和目录

bash

# 创建配置目录
sudo mkdir -p /etc/pgbouncer

# 创建日志和pid目录
sudo mkdir -p /var/log/pgbouncer
mkdir -p /var/run/pgbouncer

# 复制示例配置文件
sudo cp etc/pgbouncer.ini /etc/pgbouncer/
sudo cp etc/userlist.txt /etc/pgbouncer/

Pgbouncer配置

核心配置文件

Pgbouncer的主要配置文件包括：

/etc/pgbouncer/pgbouncer.ini：主配置文件
/etc/pgbouncer/userlist.txt：用户认证文件

主配置文件pgbouncer.ini

1. 全局配置

ini

[databases]
* = host=主库IP port=5432 pool_size=20 reserve_pool_size=5

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

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

# 日志配置
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

# 连接池配置
pool_mode = transaction
server_reset_query = DISCARD ALL
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 5.0
max_db_connections = 100
max_user_connections = 50
server_lifetime = 3600
server_idle_timeout = 600

2. 数据库配置

在[databases]部分配置数据库连接：

ini

[databases]
# 主库配置
mydb_main = host=主库IP port=5432 dbname=mydb pool_size=30

# 备库1配置
mydb_slave1 = host=备库1IP port=5432 dbname=mydb pool_size=20

# 备库2配置
mydb_slave2 = host=备库2IP port=5432 dbname=mydb pool_size=20

# 只读服务名，自动负载均衡到备库
mydb_read = host=备库1IP port=5432 dbname=mydb pool_size=20
mydb_read = host=备库2IP port=5432 dbname=mydb pool_size=20

用户认证配置

1. 生成userlist.txt

bash

# 生成用户认证信息
pgbench --version  # 确保PostgreSQL客户端已安装

# 生成md5密码
echo "\"postgres\" \"md5$(echo -n 'passwordpostgres' | md5sum | cut -d' ' -f1)\"" > /etc/pgbouncer/userlist.txt

# 添加更多用户
echo "\"appuser\" \"md5$(echo -n 'apppasswordappuser' | md5sum | cut -d' ' -f1)\"" >> /etc/pgbouncer/userlist.txt

2. 从PostgreSQL自动同步用户

创建脚本自动从PostgreSQL同步用户：

bash

cat > /etc/pgbouncer/update_users.sh << 'EOF'
#!/bin/bash
PGPASSWORD=postgres psql -h 主库IP -U postgres -tc "SELECT quote_ident(usename)||' '||quote_literal(passwd) FROM pg_shadow" > /etc/pgbouncer/userlist.txt
chmod 600 /etc/pgbouncer/userlist.txt
EOF

chmod +x /etc/pgbouncer/update_users.sh

读写分离实现

应用层读写分离设计

Pgbouncer本身不提供自动读写分离功能，需要结合应用层设计实现：

双连接设计
- 应用维护两个连接：一个用于写入（连接主库），一个用于读取（连接只读服务名）
- 根据SQL类型自动选择连接
服务名映射
- 配置不同的服务名映射到不同的数据库节点
- 应用根据业务需求选择合适的服务名

配置示例

1. Pgbouncer配置

ini

[databases]
# 主库服务（读写）
mydb = host=主库IP port=5432 dbname=mydb pool_size=50 reserve_pool_size=10

# 只读服务（自动负载均衡到备库）
mydb_readonly = host=备库1IP port=5432 dbname=mydb pool_size=30 reserve_pool_size=5
mydb_readonly = host=备库2IP port=5432 dbname=mydb pool_size=30 reserve_pool_size=5

2. 应用层实现示例

Python应用示例（使用psycopg2）

python

import psycopg2
from psycopg2 import pool

class DBConnectionPool:
    def __init__(self):
        # 主库连接池（读写）
        self.write_pool = psycopg2.pool.SimpleConnectionPool(
            1, 10,  # minconn, maxconn
            host='pgbouncer-ip',
            port=6432,
            database='mydb',
            user='appuser',
            password='apppassword'
        )
        
        # 备库连接池（只读）
        self.read_pool = psycopg2.pool.SimpleConnectionPool(
            1, 50,  # minconn, maxconn
            host='pgbouncer-ip',
            port=6432,
            database='mydb_readonly',
            user='appuser',
            password='apppassword'
        )
    
    def get_write_connection(self):
        return self.write_pool.getconn()
    
    def get_read_connection(self):
        return self.read_pool.getconn()
    
    def put_back(self, conn, is_write=True):
        if is_write:
            self.write_pool.putconn(conn)
        else:
            self.read_pool.putconn(conn)

# 使用示例
db_pool = DBConnectionPool()

# 写入操作
conn_write = db_pool.get_write_connection()
cursor_write = conn_write.cursor()
cursor_write.execute("INSERT INTO users (name) VALUES ('test')")
conn_write.commit()
db_pool.put_back(conn_write, is_write=True)

# 读取操作
conn_read = db_pool.get_read_connection()
cursor_read = conn_read.cursor()
cursor_read.execute("SELECT * FROM users")
result = cursor_read.fetchall()
db_pool.put_back(conn_read, is_write=False)

Java应用示例（使用JDBC）

java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnectionManager {
    private static final String WRITE_URL = "jdbc:postgresql://pgbouncer-ip:6432/mydb";
    private static final String READ_URL = "jdbc:postgresql://pgbouncer-ip:6432/mydb_readonly";
    private static final String USER = "appuser";
    private static final String PASSWORD = "apppassword";
    
    public static Connection getWriteConnection() throws SQLException {
        return DriverManager.getConnection(WRITE_URL, USER, PASSWORD);
    }
    
    public static Connection getReadConnection() throws SQLException {
        return DriverManager.getConnection(READ_URL, USER, PASSWORD);
    }
}

// 使用示例
Connection writeConn = DBConnectionManager.getWriteConnection();
// 执行写入操作
writeConn.close();

Connection readConn = DBConnectionManager.getReadConnection();
// 执行读取操作
readConn.close();

Pgbouncer管理

启动和停止服务

1. 系统服务方式

bash

# 启动服务
systemctl start pgbouncer

# 停止服务
systemctl stop pgbouncer

# 重启服务
systemctl restart pgbouncer

# 查看服务状态
systemctl status pgbouncer

2. 手动启动方式

bash

# 启动pgbouncer
sudo -u postgres pgbouncer /etc/pgbouncer/pgbouncer.ini

# 使用systemd管理
# 创建systemd服务文件
cat > /etc/systemd/system/pgbouncer.service << 'EOF'
[Unit]
Description=PgBouncer - Lightweight PostgreSQL connection pooler
After=syslog.target network.target

[Service]
Type=forking
User=postgres
Group=postgres
ExecStart=/usr/local/pgbouncer/bin/pgbouncer /etc/pgbouncer/pgbouncer.ini
ExecReload=/bin/kill -SIGUSR2 $MAINPID
PIDFile=/var/run/pgbouncer/pgbouncer.pid
Restart=on-failure

[Install]
WantedBy=multi-user.target
EOF

# 重新加载systemd配置
systemctl daemon-reload

# 启动并启用服务
systemctl start pgbouncer
systemctl enable pgbouncer

连接Pgbouncer管理界面

Pgbouncer提供了一个管理数据库，用于监控和管理连接池：

bash

# 连接到pgbouncer管理界面
psql -h localhost -p 6432 -U postgres pgbouncer

常用管理命令

在Pgbouncer管理界面中，可以执行以下命令：

sql

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

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

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

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

-- 查看配置
SHOW CONFIG;

-- 重新加载配置
RELOAD;

-- 重新加载用户认证
RELOAD auth;

-- 暂停连接池
PAUSE mydb;

-- 恢复连接池
RESUME mydb;

-- 关闭客户端连接
KILL client 1234;

-- 关闭服务器连接
KILL server 5678;

监控与告警

日志监控

Pgbouncer的日志默认位于/var/log/pgbouncer/pgbouncer.log，可以通过以下方式监控：

1. 使用tail实时查看日志

bash

tail -f /var/log/pgbouncer/pgbouncer.log

2. 配置日志轮转

bash

# 创建日志轮转配置
cat > /etc/logrotate.d/pgbouncer << 'EOF'
/var/log/pgbouncer/pgbouncer.log {
    daily
    rotate 7
    compress
    delaycompress
    missingok
    notifempty
    postrotate
        /bin/kill -HUP `cat /var/run/pgbouncer/pgbouncer.pid 2>/dev/null` 2>/dev/null || true
    endscript
}
EOF

Prometheus监控集成

1. 安装pgbouncer_exporter

bash

# 下载pgbouncer_exporter
wget https://github.com/prometheus-community/pgbouncer_exporter/releases/download/v0.5.0/pgbouncer_exporter-v0.5.0.linux-amd64.tar.gz
tar xzf pgbouncer_exporter-v0.5.0.linux-amd64.tar.gz
cd pgbouncer_exporter-v0.5.0.linux-amd64

2. 配置pgbouncer_exporter

bash

# 设置环境变量
export DATA_SOURCE_NAME="postgresql://postgres:password@localhost:6432/pgbouncer?sslmode=disable"

# 启动exporter
./pgbouncer_exporter --web.listen-address=:9127 &

3. 配置Prometheus

在Prometheus配置文件中添加pgbouncer_exporter：

yaml

scrape_configs:
  - job_name: 'pgbouncer'
    static_configs:
      - targets: ['pgbouncer-ip:9127']
    metrics_path: /metrics
    scrape_interval: 15s

4. 配置Grafana面板

导入Pgbouncer监控面板（推荐ID：10554）
添加连接池使用率、客户端连接数、服务器连接数等监控图表
设置告警阈值，例如连接池使用率超过80%

故障排除

常见故障类型

1. 无法连接到Pgbouncer

故障现象：

psql: error: connection to server at "pgbouncer-ip" (pgbouncer-ip), port 6432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?

解决方案：

检查Pgbouncer服务是否正在运行
检查防火墙是否开放6432端口
检查pgbouncer.ini中listen_addr配置

2. 认证失败

故障现象：

psql: error: connection to server at "pgbouncer-ip" (pgbouncer-ip), port 6432 failed: FATAL:  password authentication failed for user "postgres"

解决方案：

检查userlist.txt中用户密码是否正确
确保auth_type配置正确
重新生成userlist.txt文件

3. 连接池已满

故障现象：

psql: error: connection to server at "pgbouncer-ip" (pgbouncer-ip), port 6432 failed: ERROR:  no more connections allowed (max_client_conn)

解决方案：

增加max_client_conn参数值
增加default_pool_size或特定数据库的pool_size
优化应用连接管理，减少连接数

4. 服务器连接失败

故障现象：

LOG:  C-0x55a9c8a3f880: mydb/postgres@192.168.1.100:5432 login attempt: db=mydb user=postgres tls=no
LOG:  S-0x55a9c8a4d880: mydb/postgres@主库IP:5432 new connection to server
LOG:  S-0x55a9c8a4d880: mydb/postgres@主库IP:5432 cannot do COPY in transaction pool mode

解决方案：

检查pool_mode配置，确保支持所需功能
对于需要COPY命令的场景，使用session模式
检查PostgreSQL服务器是否正常运行

最佳实践

连接池参数优化

根据业务需求调整连接池大小
- 对于OLTP场景，建议pool_size设置为CPU核心数的1-2倍
- 对于OLAP场景，建议pool_size设置为较小值，例如5-10
合理设置reserve_pool_size
- reserve_pool_size建议设置为pool_size的25%
- reserve_pool_timeout建议设置为5-10秒
调整server_lifetime和server_idle_timeout
- server_lifetime建议设置为3600秒（1小时）
- server_idle_timeout建议设置为600秒（10分钟）

高可用性配置

部署多个Pgbouncer实例
- 使用HAProxy或Keepalived实现Pgbouncer的高可用
- 配置Pgbouncer实例监控，实现自动故障转移

配置备用Pgbouncer实例

ini

[databases]
mydb = host=主库IP port=5432 pool_size=20 host=备库IP port=5432 pool_size=20

安全配置

限制访问权限
- 在pgbouncer.ini中设置listen_addr为特定IP
- 使用防火墙限制6432端口访问
- 定期更新userlist.txt中的密码

启用TLS加密

ini

[pgbouncer]
# TLS配置
ssl = 1
ssl_cert_file = /path/to/server.crt
ssl_key_file = /path/to/server.key
ssl_ca_file = /path/to/ca.crt

使用证书认证

ini

[pgbouncer]
auth_type = cert
auth_user = pgbouncer

性能优化

选择合适的连接池模式
- 优先使用transaction模式，平衡性能和功能
- 仅在必要时使用session模式
- 避免使用statement模式，除非应用非常简单
优化应用连接管理
- 减少应用连接数，使用连接池复用连接
- 及时释放连接，避免长时间占用
- 避免长时间运行的事务

调整PostgreSQL参数

# 增加PostgreSQL最大连接数
max_connections = 500

# 调整工作内存
work_mem = 4MB

# 调整共享缓冲区
shared_buffers = 2GB

版本差异注意事项

Pgbouncer 1.18.x

支持PostgreSQL 15
增强了TLS支持
改进了连接池算法
新增了更多监控指标

Pgbouncer 1.17.x

支持PostgreSQL 14
改进了事务模式下的性能
增强了日志功能
修复了多个安全漏洞

Pgbouncer 1.16.x

支持PostgreSQL 13
引入了新的连接池统计信息
改进了配置管理
优化了内存使用

总结

基于Pgbouncer的连接池与读写分离是PostgreSQL高并发架构中的重要组成部分，它可以有效减少PostgreSQL服务器连接数，提高系统吞吐量和可用性。Pgbouncer的轻量级设计使其适合资源受限环境，同时提供了灵活的连接池配置选项。

在实际生产环境中，建议：

根据业务需求选择合适的连接池模式
合理配置连接池参数，避免资源浪费
结合应用层实现读写分离
建立完善的监控体系，及时发现和处理问题
定期进行连接池优化和调整

通过合理的配置和管理，可以确保Pgbouncer在生产环境中稳定运行，为PostgreSQL架构提供可靠的连接池和读写分离能力。

PostgreSQL 基于Pgbouncer的连接池与读写分离 ​

Pgbouncer概述 ​

什么是Pgbouncer ​

适用场景 ​

连接池模式 ​

Pgbouncer安装 ​

从软件包管理器安装 ​

CentOS/RHEL ​

Ubuntu/Debian ​

从源代码安装 ​

1. 安装依赖 ​

2. 下载并编译Pgbouncer ​

3. 创建配置文件和目录 ​

Pgbouncer配置 ​

核心配置文件 ​

主配置文件pgbouncer.ini ​

1. 全局配置 ​

2. 数据库配置 ​

用户认证配置 ​

1. 生成userlist.txt ​

2. 从PostgreSQL自动同步用户 ​

读写分离实现 ​

应用层读写分离设计 ​

配置示例 ​

1. Pgbouncer配置 ​

2. 应用层实现示例 ​

Python应用示例（使用psycopg2） ​

Java应用示例（使用JDBC） ​

Pgbouncer管理 ​

启动和停止服务 ​

1. 系统服务方式 ​

2. 手动启动方式 ​

连接Pgbouncer管理界面 ​

常用管理命令 ​

监控与告警 ​

日志监控 ​

1. 使用tail实时查看日志 ​

2. 配置日志轮转 ​

Prometheus监控集成 ​

1. 安装pgbouncer_exporter ​

2. 配置pgbouncer_exporter ​

3. 配置Prometheus ​

4. 配置Grafana面板 ​

故障排除 ​

常见故障类型 ​

1. 无法连接到Pgbouncer ​

2. 认证失败 ​

3. 连接池已满 ​

4. 服务器连接失败 ​

最佳实践 ​

连接池参数优化 ​

高可用性配置 ​

安全配置 ​

性能优化 ​

版本差异注意事项 ​

Pgbouncer 1.18.x ​

Pgbouncer 1.17.x ​

Pgbouncer 1.16.x ​

总结 ​

PostgreSQL 基于Pgbouncer的连接池与读写分离

Pgbouncer概述

什么是Pgbouncer

适用场景

连接池模式

Pgbouncer安装

从软件包管理器安装

CentOS/RHEL

Ubuntu/Debian

从源代码安装

1. 安装依赖

2. 下载并编译Pgbouncer

3. 创建配置文件和目录

Pgbouncer配置

核心配置文件

主配置文件pgbouncer.ini

1. 全局配置

2. 数据库配置

用户认证配置

1. 生成userlist.txt

2. 从PostgreSQL自动同步用户

读写分离实现

应用层读写分离设计

配置示例

1. Pgbouncer配置

2. 应用层实现示例

Python应用示例（使用psycopg2）

Java应用示例（使用JDBC）

Pgbouncer管理

启动和停止服务

1. 系统服务方式

2. 手动启动方式

连接Pgbouncer管理界面

常用管理命令

监控与告警

日志监控

1. 使用tail实时查看日志

2. 配置日志轮转

Prometheus监控集成

1. 安装pgbouncer_exporter

2. 配置pgbouncer_exporter

3. 配置Prometheus

4. 配置Grafana面板

故障排除

常见故障类型

1. 无法连接到Pgbouncer

2. 认证失败

3. 连接池已满

4. 服务器连接失败

最佳实践

连接池参数优化

高可用性配置

安全配置

性能优化

版本差异注意事项

Pgbouncer 1.18.x

Pgbouncer 1.17.x

Pgbouncer 1.16.x

总结