PostgreSQL流复制原理与架构
数据库高可用架构中PostgreSQL流复制（Streaming Replication）是保证数据冗余和读写分离的核心技术。流复制基于WAL（Write-Ahead Logging）机制，主节点将事务日志实时传输到从节点，从节点重放WAL记录保持数据同步。相比传统的基于文件日志传送方案，流复制延迟从分钟级降低到毫秒级，支持同步和异步两种模式。配合Patroni集群管理工具，可实现主节点故障时自动选举新主节点并切换流量，满足生产环境对数据库高可用架构的要求。
流复制架构组件与数据流向
架构拓扑:
+--------------+
| HAProxy | <- 读写流量入口
+------+-------+
|
+------------+------------+
| | |
+--------+---+ +-----+----+ +-----+----+
| Primary | | Replica1 | | Replica2 |
| (读写) | | (只读) | | (只读) |
+------------+ +----------+ +----------+
| | |
+--------------+------------+
|
+-------+-------+
| etcd 集群 | <- 集群状态存储
+---------------+
WAL数据流:
Primary -> WAL Sender -> WAL Receiver -> Replica -> 重放WAL -> 数据同步主节点配置详解
# postgresql.conf 主节点关键配置
listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1024 # 保留WAL大小(MB)，确保从节点断线后可追上
hot_standby = on
synchronous_commit = on # 同步提交模式
synchronous_standby_names = 'replica1' # 同步从节点名称
# 网络与超时
wal_sender_timeout = 60s
wal_receiver_timeout = 60s
# 归档配置（可选，用于PITR时间点恢复）
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
# pg_hba.conf 允许从节点连接
# TYPE DATABASE USER ADDRESS METHOD
host replication replica 10.0.1.0/24 md5
host all all 10.0.1.0/24 md5从节点初始化与基础配置
# 方式一：pg_basebackup在线初始化从节点
pg_basebackup -h 10.0.1.101 -U replica -D /var/lib/pgsql/data -Fp -Xs -P -R
# -Fp: plain格式 -Xs: stream模式传输WAL -R: 自动创建standby配置
# -P: 显示进度
# 方式二：手动初始化
# 1. 停止从节点PostgreSQL
systemctl stop postgresql
# 2. 清空从节点数据目录
rm -rf /var/lib/pgsql/data/*
# 3. 从主节点拷贝基础备份
pg_basebackup -h 10.0.1.101 -U replica -D /var/lib/pgsql/data -X stream -R
# 从节点 postgresql.auto.conf（pg_basebackup -R自动生成）
primary_conninfo = 'user=replica password=yourpass host=10.0.1.101 port=5432 sslmode=prefer'
restore_command = 'cp /archive/%f %p'
recovery_target_timeline = 'latest'
# standby.signal文件（标记为从节点）
touch /var/lib/pgsql/data/standby.signal
# 从节点 postgresql.conf 补充配置
hot_standby = on
hot_standby_feedback = on # 向主节点反馈查询状态，防止查询冲突
# 启动从节点
systemctl start postgresql验证流复制状态
# 主节点查看复制状态
SELECT
client_addr,
state,
sync_state,
sent_lsn,
write_lsn,
flush_lsn,
replay_lsn,
write_lag,
flush_lag,
replay_lag
FROM pg_stat_replication;
# 输出示例:
# client_addr | state | sync_state | sent_lsn | replay_lsn | replay_lag
# 10.0.1.102 | streaming| sync | 0/5000000| 0/5000000 | 00:00:00.001
# 从节点查看接收状态
SELECT * FROM pg_stat_wal_receiver;
# status: streaming
# received_lsn: 0/5000000
# conninfo: user=replica host=10.0.1.101 port=5432
# 检查主从延迟
SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS lag_seconds;
# lag_seconds应接近0，同步模式下通常小于0.01秒同步级别配置与数据一致性权衡
# PostgreSQL支持三种同步级别：
# remote_apply - 等待从节点重放WAL后提交（最强一致性，延迟最高）
# remote_flush - 等待从节点写入磁盘后提交
# remote_write - 等待从节点写入OS缓存后提交（延迟最低）
# 在postgresql.conf中配置
synchronous_commit = remote_flush
synchronous_standby_names = 'FIRST 2 (replica1, replica2)'
# FIRST 2: 至少2个从节点确认后主节点才提交
# ANY 2: 任意2个从节点确认即可（适合多从节点高可用）
# 运行时动态调整同步级别
SET synchronous_commit = remote_apply;
# 单事务级别调整
BEGIN;
SET LOCAL synchronous_commit = remote_write;
-- 低延迟操作
COMMIT;Patroni自动故障切换配置
# 安装Patroni
pip3 install patroni[etcd,psycopg2]
# patroni.yml 配置文件（每个节点配置）
scope: pg-cluster
name: node1
restapi:
listen: 0.0.0.0:8008
connect_address: 10.0.1.101:8008
etcd:
hosts: 10.0.1.200:2379,10.0.1.201:2379,10.0.1.202:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 20
maximum_lag_on_failover: 1048576 # 1MB
synchronous: true
postgresql:
parameters:
wal_level: replica
max_wal_senders: 10
synchronous_commit: on
synchronous_standby_names: '*'
postgresql:
listen: 0.0.0.0:5432
connect_address: 10.0.1.101:5432
data_dir: /var/lib/pgsql/data
bin_dir: /usr/pgsql-15/bin
pgpass: /tmp/pgpass
authentication:
replication:
username: replica
password: replica_password
superuser:
username: postgres
password: pg_password
parameters:
hot_standby: on
wal_keep_size: 1024
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
# 启动Patroni服务
patroni /etc/patroni/patroni.yml &
# 查看集群状态
patronictl -c /etc/patroni/patroni.yml list
# + Cluster: pg-cluster (7000) -+---------+----+-----------+
# | Member | Host | Role | State | TL | Lag in MB |
# | node1 | 10.0.1.101 | Leader | running | 5 | |
# | node2 | 10.0.1.102 | Replica | streaming| 5 | 0 |
# | node3 | 10.0.1.103 | Replica | streaming| 5 | 0 |手动故障切换与自动切换验证
# 手动切换主节点
patronictl switchover --cluster pg-cluster --candidate node2
# 模拟主节点故障测试自动切换
systemctl stop patroni # 在主节点执行
# Patroni检测到主节点不可用，30秒内自动选举新主节点
# 查看新集群状态
patronictl list
# node2 成为新Leader
# HAProxy配合自动切换配置（haproxy.cfg）
listen pg_write
bind *:5432
mode tcp
option tcp-check
tcp-check expect string 'primary'
http-check expect status 200
default-server inter 3s fall 3 rise 2
server node1 10.0.1.101:5432 check port 8008
server node2 10.0.1.102:5432 check port 8008 backup
server node3 10.0.1.103:5432 check port 8008 backup
listen pg_read
bind *:5433
mode tcp
balance roundrobin
server node1 10.0.1.101:5432 check
server node2 10.0.1.102:5432 check
server node3 10.0.1.103:5432 check数据备份恢复与日常运维
# 物理备份（pg_basebackup）
pg_basebackup -h 10.0.1.101 -U backup -D /backup/base -Ft -z -P
# -Ft: tar格式 -z: gzip压缩
# 逻辑备份（pg_dump）
pg_dump -h localhost -U postgres -Fc mydb > /backup/mydb.dump
# 恢复
pg_restore -h localhost -U postgres -d mydb /backup/mydb.dump
# 时间点恢复(PITR)
# 1. 恢复基础备份
tar -xzf /backup/base.tar.gz -C /var/lib/pgsql/data
# 2. 配置恢复目标时间
echo "recovery_target_time = '2026-08-24 10:30:00'" >> recovery.conf
echo "restore_command = 'cp /archive/%f %p'" >> recovery.conf
# 3. 启动恢复
systemctl start postgresqlPostgreSQL高可用架构设计中，流复制保证数据冗余，Patroni负责故障自动切换，HAProxy实现读写流量分离。数据备份恢复采用基础备份加WAL归档的方式，支持任意时间点的数据恢复。国产数据库迁移场景下，PostgreSQL的语法兼容性使其作为中间过渡方案具备实际价值，流复制机制可直接复用于数据迁移过程中的增量同步阶段。数据库运维中定期进行故障切换演练，验证自动切换的可用性，确保生产环境中的服务连续性。SQL查询优化方面，读写分离后从节点可承担报表查询和数据分析负载，减轻主节点压力，分库分表方案中流复制作为数据同步基础层，保障跨节点数据一致性。
原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/postgresql-gao-ke-yong-liu-fu-zhi-pei-zhi-shi-zhan-zhu-cong/
