PostgreSQL教程FG110-PG读写分离配置实战教程

本文档详细介绍PostgreSQL数据库读写分离的配置方法，包括Pgpool-II、HAProxy等中间件的配置，以及应用层读写分离的实现，风哥教程参考PostgreSQL官方文档High Availability内容，适合DBA在生产环境中实施读写分离架构。

Part01-基础概念与理论知识

1.1 PostgreSQL读写分离概念

PostgreSQL读写分离是指将读操作和写操作分离到不同的数据库服务器上执行的架构模式。主库负责所有写操作，备库负责读操作。读写分离可以提高系统的读取性能，减轻主库压力，同时利用备库资源提供只读查询服务。更多视频教程www.fgedu.net.cn

PostgreSQL读写分离特点：

提高读取性能，支持更多并发读取
减轻主库压力，提高写入性能
充分利用备库资源
需要考虑数据延迟问题
需要中间件或应用层支持
适合读多写少的业务场景

1.2 PostgreSQL读写分离架构

# PostgreSQL读写分离架构

# 1. 架构模式
# 主库：处理所有写操作（INSERT/UPDATE/DELETE）
# 备库：处理只读查询（SELECT）

# 2. 实现方式
# 中间件方式：Pgpool-II、HAProxy、ProxySQL
# 应用层方式：应用代码实现读写分离
# 驱动方式：JDBC驱动支持多主机

# 3. 架构图
┌─────────────┐
│ 应用层 │
└──────┬──────┘
│
┌──────┴──────┐
│ 中间件层 │
│ Pgpool-II │
└──────┬──────┘
│
┌───────────────────┼───────────────────┐
│ │ │
▼ ▼ ▼
┌─────────────┐ ┌─────────────┐ ┌─────────────┐
│ 主库 │────▶│ 备库1 │ │ 备库2 │
│ 192.168.1.100│ │192.168.1.101│ │192.168.1.102│
│ 写操作 │ │ 读操作 │ │ 读操作 │
└─────────────┘ └─────────────┘ └─────────────┘

# 4. 路由规则
# 写操作 -> 主库
# 读操作 -> 备库（轮询或权重）
# 事务操作 -> 主库
# 特殊函数 -> 主库

1.3 PostgreSQL读写分离工具

PostgreSQL读写分离工具：

Pgpool-II：PostgreSQL专用中间件，支持连接池、负载均衡、读写分离
HAProxy：通用负载均衡器，支持TCP层代理
ProxySQL：MySQL/PostgreSQL代理，支持查询路由
应用层：在应用代码中实现读写分离
JDBC驱动：支持多主机连接和故障转移

风哥提示：读写分离是提高数据库性能的重要手段，需要根据业务特点选择合适的实现方式。建议使用中间件实现透明的读写分离，减少应用改动。学习交流加群风哥微信: itpux-com

Part02-生产环境规划与建议

2.1 PostgreSQL读写分离规划

# PostgreSQL读写分离规划

# 1. 架构规划
# 主库：1台，处理写操作
# 备库：2台以上，处理读操作
# 中间件：2台以上，高可用部署

# 2. 网络规划
# 主库IP：192.168.1.100
# 备库1 IP：192.168.1.101
# 备库2 IP：192.168.1.102
# Pgpool虚拟IP：192.168.1.200

# 3. 端口规划
# PostgreSQL：5432
# Pgpool-II：9999（应用连接）
# Pgpool管理：9898
# Pgpool监控：9000

# 4. 连接池规划
# 最大连接数：1000
# 最小连接数：10
# 连接生命周期：1小时
# 空闲超时：5分钟

# 5. 负载均衡策略
# 轮询：平均分配请求
# 权重：根据服务器性能分配
# 最少连接：分配给连接最少的备库

2.2 PostgreSQL负载均衡规划

PostgreSQL负载均衡规划：

读负载均衡：将读请求分发到多个备库
写负载均衡：写操作只能在主库执行
健康检查：定期检查后端服务器状态
故障转移：自动剔除故障服务器

2.3 PostgreSQL读写分离测试

# PostgreSQL读写分离测试

# 1. 功能测试
# – 写操作路由到主库
# – 读操作路由到备库
# – 事务操作路由到主库

# 2. 性能测试
# – 并发读取测试
# – 混合读写测试
# – 压力测试

# 3. 故障测试
# – 主库故障切换
# – 备库故障剔除
# – 中间件故障切换

# 4. 数据一致性测试
# – 主备数据同步验证
# – 读写分离延迟测试
# – 数据一致性检查

# 5. 监控测试
# – 连接数监控
# – 查询延迟监控
# – 负载均衡效果监控

风哥教程针对风哥教程针对生产环境建议：读写分离需要考虑数据延迟问题，对于实时性要求高的查询，应该路由到主库执行。建议建立完善的监控机制，及时发现和处理延迟问题。学习交流加群风哥QQ113257174

Part03-生产环境项目实施方案

3.1 PostgreSQL Pgpool-II配置实战

3.1.1 安装Pgpool-II

# 安装Pgpool-II

# 1. 下载安装
$ yum install -y pgpool-II

# 或从源码安装
$ wget https://www.pgpool.net/mediawiki/images/pgpool-II-4.5.0.tar.gz
$ tar -xzf pgpool-II-4.5.0.tar.gz
$ cd pgpool-II-4.5.0
$ ./configure –prefix=/postgresql/pgpool
$ make && make install

# 2. 配置环境变量
$ vi ~/.bashrc
export PATH=/postgresql/pgpool/bin:$PATH

$ source ~/.bashrc

# 3. 创建配置目录
$ mkdir -p /postgresql/pgpool/etc
$ mkdir -p /postgresql/pgpool/log

# 4. 复制配置文件
$ cp /postgresql/pgpool/etc/pgpool.conf.sample /postgresql/pgpool/etc/pgpool.conf
$ cp /postgresql/pgpool/etc/pool_hba.conf.sample /postgresql/pgpool/etc/pool_hba.conf

# 5. 验证安装
$ pgpool –version
pgpool-II version 4.5.0

3.1.2 配置Pgpool-II

# 配置Pgpool-II
$ vi /postgresql/pgpool/etc/pgpool.conf

# 基本配置
listen_addresses = ‘*’
port = 9999
socket_dir = ‘/tmp’
pcp_listen_addresses = ‘*’
pcp_port = 9898
pcp_socket_dir = ‘/tmp’

# 后端服务器配置
backend_hostname0 = ‘192.168.1.100’
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = ‘/postgresql/fgdata’
backend_flag0 = ‘ALLOW_TO_FAILOVER’

backend_hostname1 = ‘192.168.1.101’
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = ‘/postgresql/fgdata’
backend_flag1 = ‘ALLOW_TO_FAILOVER’

backend_hostname2 = ‘192.168.1.102’
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = ‘/postgresql/fgdata’
backend_flag2 = ‘ALLOW_TO_FAILOVER’

# 连接池配置
num_init_children = 100
max_pool = 4
child_life_time = 300
connection_life_time = 0
child_max_connections = 0
connection_cache = on

# 负载均衡配置
load_balance_mode = on
ignore_leading_white_space = on
white_function_list = ”
black_function_list = ‘nextval,setval’

# 健康检查配置
health_check_period = 30
health_check_timeout = 20
health_check_user = ‘fgedu’
health_check_password = ‘Fgedu@2026’
health_check_database = ‘fgedudb’

# 故障转移配置
failover_command = ‘/postgresql/scripts/failover.sh %d %h %p %D %m %H %M %P %r %R’
failback_command = ‘/postgresql/scripts/failback.sh %d %h %p %D %m %H %M %P %r %R’

# 监控配置
log_destination = ‘syslog’
syslog_facility = ‘LOCAL0’
log_error_verbosity = verbose
log_connections = on
log_disconnections = on

3.1.3 配置PCP管理工具

# 配置PCP管理工具

# 1. 创建pcp.conf
$ vi /postgresql/pgpool/etc/pcp.conf

# 格式：用户名:加密密码
pgpool:$(pg_md5 Fgedu@Pgpool2026)

# 生成加密密码
$ pg_md5 Fgedu@Pgpool2026
a1b2c3d4e5f6g7h8i9j0k1l2m3n4o5p6

# 2. 配置pool_hba.conf
$ vi /postgresql/pgpool/etc/pool_hba.conf

# TYPE DATABASE USER ADDRESS METHOD
local all all trust
host all all 127.0.0.1/32 scram-sha-256
host all all 192.168.1.0/24 scram-sha-256
host replication all 192.168.1.0/24 scram-sha-256

# 3. 创建pool_passwd
$ pg_md5 –md5auth –username=fgedu Fgedu@2026 >> /postgresql/pgpool/etc/pool_passwd

# 4. 启动Pgpool-II
$ pgpool -f /postgresql/pgpool/etc/pgpool.conf -n -d > /postgresql/pgpool/log/pgpool.log 2>&1 &

# 5. 验证Pgpool-II状态
$ pcp_watchdog_info -h fgedu.localhost -p 9898 -U pgpool -w
# Name Host Port Status
# pgpool1 192.168.1.200 9999 MASTER

# 6. 查看后端服务器状态
$ pcp_node_info -h fgedu.localhost -p 9898 -U pgpool -w 0
# Hostname Port Status Weight
# 192.168.1.100 5432 2 0.5

$ pcp_node_info -h fgedu.localhost -p 9898 -U pgpool -w 1
# Hostname Port Status Weight
# 192.168.1.101 5432 2 0.5

3.2 PostgreSQL HAProxy配置实战

# PostgreSQL HAProxy配置实战

# 1. 安装HAProxy
$ yum install -y haproxy

# 2. 配置HAProxy
$ vi /etc/haproxy/haproxy.cfg

global
log 127.0.0.1 local2
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
user haproxy
group haproxy
daemon

defaults
mode tcp
log global
option tcplog
option dontlognull
option redispatch
retries 3
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
timeout check 10s
maxconn 3000

# PostgreSQL主库（写操作）
listen pg_primary
bind *:5433
mode tcp
option tcp-check
tcp-check connect
tcp-check send PING\r\n
tcp-check expect string +PONG
server primary 192.168.1.100:5432 check inter 5s rise 2 fall 3

# PostgreSQL备库（读操作，负载均衡）
listen pg_replicas
bind *:5434
mode tcp
balance roundrobin
option tcp-check
tcp-check connect
tcp-check send PING\r\n
tcp-check expect string +PONG
server replica1 192.168.1.101:5432 check inter 5s rise 2 fall 3
server replica2 192.168.1.102:5432 check inter 5s rise 2 fall 3

# PostgreSQL统计页面
listen stats
bind *:8404
mode http
stats enable
stats uri /stats
stats refresh 10s
stats auth admin:admin123

# 3. 启动HAProxy
$ systemctl start haproxy
$ systemctl enable haproxy

# 4. 验证HAProxy状态
$ curl http://fgedu.localhost:8404/stats

# 5. 测试连接
# 连接主库（写操作）
$ psql -h 192.168.1.200 -p 5433 -U fgedu -d fgedudb

# 连接备库（读操作）
$ psql -h 192.168.1.200 -p 5434 -U fgedu -d fgedudb

3.3 PostgreSQL应用层读写分离实战

3.3.1 Java应用读写分离

# Java应用读写分离配置

# 1. 配置数据源
# fgapplication.yml
spring:
datasource:
master:
jdbc-url: jdbc:postgresql://192.168.1.100:5432/fgedudb
username: fgedu
password: Fgedu@2026
driver-class-name: org.postgresql.Driver
slave:
jdbc-url: jdbc:postgresql://192.168.1.101:5432,192.168.1.102:5432/fgedudb
username: fgedu
password: Fgedu@2026
driver-class-name: org.postgresql.Driver
targetServerType: slave
loadBalanceHosts: true

# 2. 数据源配置类
@Configuration
public class DataSourceConfig {

@Bean
@ConfigurationProperties(prefix = “spring.datasource.master”)
public DataSource masterDataSource() {
return DataSourceBuilder.create().build();
}

@Bean
@ConfigurationProperties(prefix = “spring.datasource.slave”)
public DataSource slaveDataSource() {
return DataSourceBuilder.create().build();
}

@Bean
@Primary
public DataSource routingDataSource(
@Qualifier(“masterDataSource”) DataSource masterDataSource,
@Qualifier(“slaveDataSource”) DataSource slaveDataSource) {

Map targetDataSources = new HashMap<>();
targetDataSources.put(DataSourceType.MASTER, masterDataSource);
targetDataSources.put(DataSourceType.SLAVE, slaveDataSource);

RoutingDataSource routingDataSource = new RoutingDataSource();
routingDataSource.setDefaultTargetDataSource(masterDataSource);
routingDataSource.setTargetDataSources(targetDataSources);

return routingDataSource;
}
}

# 3. 数据源路由类
public class RoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.getDataSourceType();
}
}

# 4. 数据源上下文
public class DataSourceContextHolder {
private static final ThreadLocal contextHolder = new ThreadLocal<>();

public static void setDataSourceType(DataSourceType type) {
contextHolder.set(type);
}

public static DataSourceType getDataSourceType() {
return contextHolder.get() == null ? DataSourceType.MASTER : contextHolder.get();
}

public static void clearDataSourceType() {
contextHolder.remove();
}
}

# 5. 使用示例
@Service
public class FgeduOrderService {

@Transactional
public void createOrder(FgeduOrder order) {
// 写操作使用主库
DataSourceContextHolder.setDataSourceType(DataSourceType.MASTER);
orderRepository.save(order);
DataSourceContextHolder.clearDataSourceType();
}

public FgeduOrder getOrder(Long id) {
// 读操作使用备库
DataSourceContextHolder.setDataSourceType(DataSourceType.SLAVE);
FgeduOrder order = orderRepository.findById(id);
DataSourceContextHolder.clearDataSourceType();
return order;
}
}

风哥提示：应用层读写分离需要在代码中实现路由逻辑，对应用有一定侵入性。建议使用中间件实现透明的读写分离，减少应用改动。更多学习教程公众号风哥教程itpux_com

Part04-生产案例与实战讲解

4.1 PostgreSQL Pgpool-II实战案例

# PostgreSQL Pgpool-II实战案例

# 场景：配置Pgpool-II实现读写分离和负载均衡

# 1. 验证读写分离
# 连接Pgpool-II
$ psql -h 192.168.1.200 -p 9999 -U fgedu -d fgedudb

# 执行写操作（应该路由到主库）
fgedudb=# INSERT INTO fgedu_orders (order_no) VALUES (‘ORD_RW_TEST_001’);
INSERT 0 1

# 2. 验证负载均衡
# 查看连接分布
$ pcp_proc_info -h fgedu.localhost -p 9898 -U pgpool -w | grep database

# 执行多次查询
$ for i in {1..10}; do psql -h 192.168.1.200 -p 9999 -U fgedu -d fgedudb -c “SELECT inet_server_addr();” ; done

inet_server_addr
——————
192.168.1.101
(1 row)

inet_server_addr
——————
192.168.1.102
(1 row)

…

# 3. 监控Pgpool-II状态
$ pcp_pool_status -h fgedu.localhost -p 9898 -U pgpool -w

# 4. 手动切换后端服务器
$ pcp_promote_node -h fgedu.localhost -p 9898 -U pgpool -w 1

# 5. 查看连接池状态
$ psql -h 192.168.1.200 -p 9999 -U fgedu -d fgedudb -c “SHOW POOL_NODES;”
node_id | hostname | port | status | lb_weight | role | select_cnt
———+—————+——+——–+———–+———+————
0 | 192.168.1.100 | 5432 | up | 0.333333 | primary | 0
1 | 192.168.1.101 | 5432 | up | 0.333333 | standby | 5234
2 | 192.168.1.102 | 5432 | up | 0.333333 | standby | 5233
(3 rows)

4.2 PostgreSQL连接池配置案例

# PostgreSQL连接池配置案例

# 1. Pgpool-II连接池配置
$ vi /postgresql/pgpool/etc/pgpool.conf

# 连接池参数
num_init_children = 100 # 初始子进程数
max_pool = 4 # 每个子进程的最大连接池数
child_life_time = 300 # 子进程生命周期（秒）
connection_life_time = 0 # 连接生命周期（0表示无限）
child_max_connections = 0 # 子进程最大连接数（0表示无限）
connection_cache = on # 启用连接缓存

# 计算最大连接数
# 最大连接数 = num_init_children * max_pool = 100 * 4 = 400

# 2. PgBouncer连接池配置
$ vi /etc/pgbouncer/pgbouncer.ini

[databases]
fgedudb = host=192.168.1.100 port=5432 dbname=fgedudb

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = session
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
max_db_connections = 100
idle_transaction_timeout = 120
server_check_delay = 30
server_check_query = SELECT 1

# 3. 启动PgBouncer
$ systemctl start pgbouncer
$ systemctl enable pgbouncer

# 4. 测试连接池
$ psql -h 192.168.1.200 -p 6432 -U fgedu -d fgedudb

fgedudb=# SELECT count(*) FROM fgedu_orders;
count
——-
500000
(1 row)

# 5. 监控连接池
$ psql -h 192.168.1.200 -p 6432 -U pgbouncer -d pgbouncer -c “SHOW POOLS;”
database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait
———–+———-+———–+————+———–+———+———+———–+———-+———
fgedudb | fgedu | 5 | 0 | 3 | 2 | 0 | 0 | 0 | 0
(1 row)

# 6. 连接池调优建议
# – 根据并发量调整num_init_children
# – 根据数据库连接数限制调整max_pool
# – 监控连接等待时间，避免连接耗尽

4.3 PostgreSQL读写分离监控案例

#!/bin/bash
# rw_split_monitor.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

# PostgreSQL读写分离监控脚本

PGHOME=/postgresql/fgapp
PGPOOL_HOME=/postgresql/pgpool
PGHOST=192.168.1.200
PGPORT=9999
PGUSER=fgedu
PGDATABASE=fgedudb
LOG_FILE=/postgresql/scripts/logs/rw_split_monitor.log

log_message() {
echo “$(date ‘+%Y-%m-%d %H:%M:%S’) – $1” >> $LOG_FILE
}

# 检查Pgpool-II状态
check_pgpool_status() {
local status=$($PGPOOL_HOME/bin/pcp_watchdog_info -h fgedu.localhost -p 9898 -U pgpool -w 2>/dev/null | grep -c “MASTER\|STANDBY”)

if [ “$status” -lt 1 ]; then
log_message “ERROR: Pgpool-II状态异常”
return 1
fi

log_message “Pgpool-II状态正常”
}

# 检查后端服务器状态
check_backend_status() {
local result=$($PGPOOL_HOME/bin/pcp_node_info -h fgedu.localhost -p 9898 -U pgpool -w 0 2>/dev/null)

if echo “$result” | grep -q “up”; then
log_message “主库状态正常”
else
log_message “ERROR: 主库状态异常”
fi

for i in 1 2; do
result=$($PGPOOL_HOME/bin/pcp_node_info -h fgedu.localhost -p 9898 -U pgpool -w $i 2>/dev/null)
if echo “$result” | grep -q “up”; then
log_message “备库$i 状态正常”
else
log_message “WARNING: 备库$i 状态异常”
fi
done
}

# 检查复制延迟
check_replication_lag() {
local lag=$($PGHOME/bin/psql -h $PGHOST -p $PGPORT -U $PGUSER -d $PGDATABASE -t -c ”
SELECT max(pg_wal_lsn_diff(sent_lsn, replay_lsn))
FROM pg_stat_replication;
” 2>/dev/null | tr -d ‘ ‘)

if [ “$lag” -gt 10485760 ]; then
log_message “WARNING: 复制延迟过大: ${lag} bytes”
else
log_message “复制延迟: ${lag} bytes”
fi
}

# 检查连接数
check_connections() {
local count=$($PGHOME/bin/psql -h $PGHOST -p $PGPORT -U $PGUSER -d $PGDATABASE -t -c ”
SELECT count(*) FROM pg_stat_activity;
” 2>/dev/null | tr -d ‘ ‘)

log_message “当前连接数: $count”
}

# 主函数
main() {
log_message “==========================================”
log_message “开始读写分离监控检查”
log_message “==========================================”

check_pgpool_status
check_backend_status
check_replication_lag
check_connections

log_message “==========================================”
log_message “监控检查完成”
log_message “==========================================”
}

main

风哥教程针对风哥教程针对生产环境建议：读写分离需要建立完善的监控机制，及时发现和处理问题。建议监控复制延迟、连接数、负载均衡效果等关键指标。from PostgreSQL视频:www.itpux.com

Part05-风哥经验总结与分享

5.1 PostgreSQL读写分离最佳实践

PostgreSQL读写分离最佳实践：

选择合适工具：根据业务需求选择中间件
监控复制延迟：及时发现延迟问题
合理配置连接池：避免连接耗尽
事务处理：事务内操作路由到主库
测试验证：定期验证读写分离效果
文档记录：记录详细的配置和操作流程

5.2 PostgreSQL读写分离脚本

# PostgreSQL读写分离脚本库

/postgresql/scripts/rw_split/
├── pgpool_start.sh # 启动Pgpool-II
├── pgpool_stop.sh # 停止Pgpool-II
├── pgpool_status.sh # 查看Pgpool-II状态
├── backend_switch.sh # 后端服务器切换
├── rw_split_monitor.sh # 读写分离监控
└── conf/
└── rw_split.conf # 读写分离配置

5.3 PostgreSQL读写分离检查清单

# PostgreSQL读写分离检查清单

# 配置前检查
– [ ] 确认主备复制正常
– [ ] 确认网络连通性
– [ ] 确认中间件安装正确
– [ ] 确认配置参数正确

# 配置后验证
– [ ] 验证写操作路由到主库
– [ ] 验证读操作路由到备库
– [ ] 验证负载均衡效果
– [ ] 验证故障转移功能

# 日常监控
– [ ] 监控复制延迟
– [ ] 监控连接数
– [ ] 监控中间件状态
– [ ] 监控后端服务器状态

# 故障处理
– [ ] 准备故障切换流程
– [ ] 准备回滚方案
– [ ] 准备告警通知机制
– [ ] 准备恢复流程

风哥提示：读写分离是提高数据库性能的重要手段，需要在平时多加练习，熟悉各种配置和操作方法。建议建立完善的监控和告警机制，及时发现和处理问题。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html