外观
PostgreSQL 双向复制与多主架构
双向复制架构概述
双向复制（Bidirectional Replication）是指两个或多个PostgreSQL实例之间相互复制数据的架构，每个实例既可以作为主库接收写入，也可以作为从库复制其他实例的数据。
适用场景
- 地理分布式部署，需要在多个数据中心同时处理写入
- 需要提高写入可用性，避免单一主库瓶颈
- 应用层需要就近写入，减少网络延迟
架构类型
基于流复制的双向复制
- 利用PostgreSQL原生流复制机制
- 需要额外工具处理冲突
- 适合PostgreSQL 9.0及以上版本
基于逻辑复制的多主架构
- 利用PostgreSQL 10+的逻辑复制功能
- 支持表级复制，灵活性更高
- 冲突处理相对简单
第三方解决方案
- Bucardo：成熟的PostgreSQL多主复制解决方案
- Slony-I：基于触发器的复制系统
- pglogical：高级逻辑复制扩展
基于流复制的双向复制配置
前提条件
- PostgreSQL 9.0+版本
- 两个实例具有相同的PostgreSQL版本
- 实例间网络互通，防火墙已开放端口
- 已配置SSH免密登录（用于基础备份）
配置步骤
1. 初始化两个PostgreSQL实例
在两台服务器上分别安装并初始化PostgreSQL：
bash
# 服务器A（192.168.1.10）
pgsql -c "CREATE USER replication REPLICATION LOGIN CONNECTION LIMIT 10 ENCRYPTED PASSWORD 'replication_pass';"
# 服务器B（192.168.1.20）
pgsql -c "CREATE USER replication REPLICATION LOGIN CONNECTION LIMIT 10 ENCRYPTED PASSWORD 'replication_pass';"2. 配置pg_hba.conf
在两台服务器的pg_hba.conf中添加复制权限：
# 服务器A
host replication replication 192.168.1.20/32 scram-sha-256
# 服务器B
host replication replication 192.168.1.10/32 scram-sha-2563. 配置postgresql.conf
在两台服务器上启用流复制相关参数：
# 服务器A与B共享配置
wal_level = replica # PostgreSQL 10+使用replica，9.x使用hot_standby
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
log_replication_commands = on4. 配置双向复制
从服务器A到服务器B的复制：
bash# 在服务器B上执行 pg_basebackup -h 192.168.1.10 -U replication -D $PGDATA -Fp -Xs -P -R从服务器B到服务器A的复制：
bash# 在服务器A上执行 pg_basebackup -h 192.168.1.20 -U replication -D $PGDATA -Fp -Xs -P -R
5. 修改recovery.conf/recovery.signal
PostgreSQL 12+使用recovery.signal，之前版本使用recovery.conf：
bash
# 服务器A的recovery.signal
touch $PGDATA/recovery.signal
echo "primary_conninfo = 'host=192.168.1.20 port=5432 user=replication password=replication_pass application_name=server_a'" >> $PGDATA/postgresql.auto.conf
echo "standby_mode = 'on'" >> $PGDATA/postgresql.auto.conf
# 服务器B的recovery.signal
touch $PGDATA/recovery.signal
echo "primary_conninfo = 'host=192.168.1.10 port=5432 user=replication password=replication_pass application_name=server_b'" >> $PGDATA/postgresql.auto.conf
echo "standby_mode = 'on'" >> $PGDATA/postgresql.auto.conf6. 启动服务并验证
bash
# 启动两台服务器的PostgreSQL服务
systemctl start postgresql-14
# 验证复制状态
# 在服务器A上
sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;"
# 在服务器B上
sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;"基于逻辑复制的多主架构
前提条件
- PostgreSQL 10+版本
- 所有实例具有相同的PostgreSQL版本
- 已安装pgoutput插件（默认包含）
配置步骤
1. 配置postgresql.conf
wal_level = logical # 必须设置为logical
max_replication_slots = 10
max_wal_senders = 10
max_logical_replication_workers = 4
max_worker_processes = 162. 创建复制用户
sql
CREATE USER logical_repl WITH REPLICATION LOGIN PASSWORD 'logical_repl_pass';3. 配置pg_hba.conf
host replication logical_repl 0.0.0.0/0 scram-sha-256
host all logical_repl 0.0.0.0/0 scram-sha-2564. 创建发布者
在每个节点上创建发布，指定需要复制的表：
sql
-- 在节点1上
CREATE PUBLICATION pub_node1 FOR ALL TABLES;
-- 在节点2上
CREATE PUBLICATION pub_node2 FOR ALL TABLES;5. 创建订阅者
在每个节点上创建订阅，订阅其他节点的发布：
sql
-- 在节点1上订阅节点2
CREATE SUBSCRIPTION sub_node2
CONNECTION 'host=node2 port=5432 dbname=mydb user=logical_repl password=logical_repl_pass'
PUBLICATION pub_node2
WITH (copy_data=true, create_slot=true);
-- 在节点2上订阅节点1
CREATE SUBSCRIPTION sub_node1
CONNECTION 'host=node1 port=5432 dbname=mydb user=logical_repl password=logical_repl_pass'
PUBLICATION pub_node1
WITH (copy_data=true, create_slot=true);6. 验证复制状态
sql
-- 查看发布状态
SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;
-- 查看订阅状态
SELECT * FROM pg_subscription;
SELECT * FROM pg_stat_subscription;冲突处理策略
多主架构中最核心的挑战是处理数据冲突，当两个节点同时修改同一行数据时会产生冲突。
冲突类型
- 主键冲突：两个节点同时插入具有相同主键的记录
- 唯一约束冲突：违反唯一约束
- 更新冲突：同一行数据在不同节点被修改
- 删除冲突：一个节点删除记录，另一个节点修改同一记录
冲突解决方法
1. 应用层冲突避免
- 分片策略：根据业务逻辑将数据分片，每个分片只在一个节点上写入
- 时间戳冲突检测：使用全局唯一时间戳标记数据版本
- 乐观锁：使用版本号或时间戳进行冲突检测
2. 数据库层冲突处理
- 冲突检测与记录：使用PostgreSQL的冲突检测机制记录冲突
- 冲突自动解决策略：
- 最后更新获胜（Last Update Wins）
- 节点优先级策略（Primary Node Wins）
- 自定义冲突解决函数
3. 第三方工具冲突处理
- Bucardo：提供多种冲突解决策略，包括自定义Python脚本
- pglogical：支持冲突检测和自定义解决方法
基于Bucardo的多主复制
Bucardo概述
Bucardo是一个成熟的PostgreSQL多主复制解决方案，基于触发器实现，支持复杂的复制拓扑。
安装与配置
1. 安装依赖
bash
# CentOS/RHEL
yum install -y perl-DBI perl-DBD-Pg perl-ExtUtils-MakeMaker
# Ubuntu/Debian
apt-get install -y libdbi-perl libdbd-pg-perl libextutils-makemaker-perl2. 安装Bucardo
bash
wget https://bucardo.org/downloads/Bucardo-5.6.0.tar.gz
tar xzf Bucardo-5.6.0.tar.gz
cd Bucardo-5.6.0
perl Makefile.PL
sudo make install3. 初始化Bucardo
bash
sudo -u postgres bucardo_ctl install4. 配置Bucardo
bash
# 启动Bucardo守护进程
sudo -u postgres bucardo_ctl start
# 添加数据库连接
bucardo_ctl add db db1 dbname=mydb host=node1 user=bucardo password=bucardo_pass
bucardo_ctl add db db2 dbname=mydb host=node2 user=bucardo password=bucardo_pass
# 创建表组
bucardo_ctl add tables all --db=db1 --group=my_tables
# 创建复制集
bucardo_ctl add sync my_sync type=twoway dbs=db1:source,db2:source tables=my_tables conflict_strategy=latest
# 启动复制
bucardo_ctl start sync my_sync监控与管理
bash
# 查看Bucardo状态
bucardo_ctl status
# 查看复制状态
bucardo_ctl list syncs
# 查看冲突
bucardo_ctl list conflicts
# 重启Bucardo
bucardo_ctl restart多主架构最佳实践
架构设计
- 限制节点数量：建议不超过4个节点，节点越多冲突概率越高
- 合理规划网络：确保节点间网络延迟低且稳定
- 统一时钟：所有节点使用NTP服务同步时钟
性能优化
调整WAL参数：
wal_compression = on wal_buffers = 16MB checkpoint_completion_target = 0.9优化逻辑复制：
max_logical_replication_workers = 8 max_parallel_workers = 16合理设置复制槽：定期清理不再使用的复制槽
冲突管理
- 选择合适的冲突解决策略：根据业务需求选择
- 监控冲突：设置告警机制，及时发现并处理冲突
- 定期分析冲突：优化应用设计，减少冲突发生
高可用性
- 结合HA工具：多主架构可与Patroni、repmgr等HA工具结合使用
- 设置故障自动隔离：当节点出现问题时自动隔离，避免数据不一致
- 定期备份：即使有复制，也要定期进行全量备份
常见问题与解决方案
1. 复制延迟过高
原因：
- 网络带宽不足
- 写入量过大
- 冲突处理消耗资源
解决方案：
- 优化网络配置，增加带宽
- 调整WAL参数，提高WAL生成效率
- 优化应用设计，减少跨节点写入冲突
2. 冲突频繁发生
原因：
- 应用层设计不合理
- 数据分片策略不当
- 时钟不同步
解决方案：
- 重新设计应用，避免跨节点同时写入同一数据
- 调整数据分片策略，确保数据只在一个节点写入
- 配置NTP服务，确保所有节点时钟同步
3. 复制中断
原因：
- 网络中断
- 节点崩溃
- 复制槽故障
解决方案：
- 检查网络连接，修复网络问题
- 重启故障节点，重新建立复制
- 重新创建复制槽，恢复复制
版本差异注意事项
PostgreSQL 9.x
- 仅支持基于流复制的双向复制
- 需要使用recovery.conf配置文件
- 不支持逻辑复制
PostgreSQL 10-11
- 支持基础逻辑复制功能
- 逻辑复制不支持DDL复制
- 冲突处理需要手动干预
PostgreSQL 12+
- 支持recovery.signal替代recovery.conf
- 逻辑复制性能提升
- 支持更多的冲突检测机制
PostgreSQL 14+
- 逻辑复制支持并行应用
- 新增冲突处理相关视图
- 复制槽管理功能增强