PostgreSQL教程FG108-PG流复制主从配置实战教程

本文档详细介绍PostgreSQL数据库流复制主从配置的方法，包括主库配置、备库搭建、复制验证等操作，风哥教程参考PostgreSQL官方文档High Availability内容，适合DBA在生产环境中实施高可用架构。

Part01-基础概念与理论知识

1.1 PostgreSQL流复制概念

PostgreSQL流复制是一种基于WAL日志的实时数据复制技术。主库将WAL日志流实时发送给备库，备库接收并重放WAL日志，实现数据的实时同步。流复制支持异步和同步两种模式，异步模式下主库不等待备库确认，同步模式下主库等待备库确认后才提交事务。更多视频教程www.fgedu.net.cn

PostgreSQL流复制特点：

实时数据同步，延迟极低
支持异步和同步两种模式
支持多个备库
备库可以提供只读查询
支持级联复制
故障切换简单快速

1.2 PostgreSQL复制架构

# PostgreSQL复制架构

# 1. 主库（Primary/Master）
# – 接收所有写操作
# – 生成WAL日志
# – 发送WAL日志给备库

# 2. 备库（Standby/Replica）
# – 接收WAL日志
# – 重放WAL日志
# – 提供只读查询（Hot Standby）

# 3. 复制模式
# 异步复制（Async）
# – 主库不等待备库确认
# – 性能好，可能丢失数据
# – 默认模式

# 同步复制（Sync）
# – 主库等待备库确认
# – 数据安全，性能有影响
# – 需要配置synchronous_standby_names

# 4. 复制拓扑
# 一主一从：最简单的架构
# 一主多从：读写分离，负载均衡
# 级联复制：减轻主库压力
# 环形复制：多主架构（需要第三方工具）

# 复制架构图
┌─────────────┐
│ 主库 │
│ 192.168.1.100│
└──────┬──────┘
│
┌───────────────┼───────────────┐
│ │ │
▼ ▼ ▼
┌─────────────┐ ┌─────────────┐ ┌─────────────┐
│ 备库1 │ │ 备库2 │ │ 备库3 │
│192.168.1.101│ │192.168.1.102│ │192.168.1.103│
└─────────────┘ └─────────────┘ └─────────────┘

1.3 PostgreSQL复制参数说明

PostgreSQL复制参数：

wal_level：WAL级别（minimal/replica/logical）
max_wal_senders：最大WAL发送进程数
wal_keep_size：保留的WAL大小
hot_standby：备库是否支持只读查询
synchronous_standby_names：同步备库列表
primary_conninfo：主库连接信息

风哥提示：流复制是PostgreSQL高可用的基础，理解其原理和参数配置对于搭建高可用架构至关重要。建议根据业务需求选择合适的复制模式。学习交流加群风哥微信: itpux-com

Part02-生产环境规划与建议

2.1 PostgreSQL复制规划

# PostgreSQL复制规划

# 1. 复制模式选择
# 核心业务：同步复制，确保数据安全
# 一般业务：异步复制，保证性能
# 读写分离：异步复制，多个备库

# 2. 备库数量规划
# 最小配置：1主1备
# 推荐配置：1主2备
# 高可用配置：1主3备 + 级联复制

# 3. 硬件配置规划
# 主库：高性能服务器，SSD存储
# 备库：与主库配置相同或略低
# 网络：千兆或万兆网络，低延迟

# 4. 存储规划
# 主库数据目录：/postgresql/fgdata
# 备库数据目录：/postgresql/fgdata
# WAL归档目录：/postgresql/archive

# 5. 网络规划
# 主库IP：192.168.1.100
# 备库1 IP：192.168.1.101
# 备库2 IP：192.168.1.102
# 虚拟IP：192.168.1.200（用于故障切换）

2.2 PostgreSQL网络规划

PostgreSQL网络规划：

复制网络：专用网络，避免与应用网络混用
带宽要求：根据数据变更量计算，预留冗余
延迟要求：同步复制要求低延迟（< 1ms）
防火墙：开放5432端口用于复制连接

2.3 PostgreSQL故障切换规划

# PostgreSQL故障切换规划

# 1. 故障检测
# – 监控主库状态
# – 监控复制延迟
# – 自动故障检测脚本

# 2. 切换策略
# 手动切换：DBA手动执行切换
# 自动切换：使用Patroni/Repmgr等工具

# 3. 切换流程
# 步骤1：确认主库故障
# 步骤2：选择新的主库
# 步骤3：提升备库为主库
# 步骤4：更新应用连接
# 步骤5：重建其他备库

# 4. 切换时间目标
# 手动切换：10-30分钟
# 自动切换：1-5分钟

# 5. 回切策略
# 修复原主库后作为备库加入
# 选择低峰期进行回切

风哥教程针对风哥教程针对生产环境建议：建议至少搭建1主2备的架构，确保高可用。核心业务使用同步复制，一般业务使用异步复制。建议使用Patroni或Repmgr等工具实现自动故障切换。学习交流加群风哥QQ113257174

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

3.1 PostgreSQL主库配置实战

3.1.1 配置主库参数

# 配置主库参数
$ vi /postgresql/fgdata/postgresql.conf

# 监听配置
listen_addresses = ‘*’
port = 5432

# WAL配置
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
max_replication_slots = 10

# 备库查询配置
hot_standby = on
hot_standby_feedback = on

# 同步复制配置（可选）
synchronous_commit = on
synchronous_standby_names = ‘fgedu_standby01’

# 重启主库
$ pg_ctl -D /postgresql/fgdata restart
waiting for server to shut down…. done
server stopped
waiting for server to start…. done
server started

# 验证参数
$ psql -U fgedu -d fgedudb -c “SHOW wal_level;”
wal_level
———–
replica
(1 row)

$ psql -U fgedu -d fgedudb -c “SHOW max_wal_senders;”
max_wal_senders
—————–
10
(1 row)

3.1.2 创建复制用户

# 创建复制用户
$ psql -U fgedu -d fgedudb

# 创建复制用户
fgedudb=# CREATE USER fgedu_repl WITH REPLICATION ENCRYPTED PASSWORD ‘Fgedu@Repl2026’;
CREATE ROLE

# 授予复制权限
fgedudb=# GRANT pg_read_all_data TO fgedu_repl;
GRANT ROLE

# 验证用户
fgedudb=# SELECT rolname, rolreplication FROM pg_roles WHERE rolname = ‘fgedu_repl’;
rolname | rolreplication
————-+—————-
fgedu_repl | t
(1 row)

# 创建复制槽（可选）
fgedudb=# SELECT pg_create_physical_replication_slot(‘fgedu_slot01’);
pg_create_physical_replication_slot
————————————-
(fgedu_slot01,)
(1 row)

3.1.3 配置访问控制

# 配置pg_hba.conf
$ vi /postgresql/fgdata/pg_hba.conf

# 添加以下内容
# TYPE DATABASE USER ADDRESS METHOD
host replication fgedu_repl 192.168.1.0/24 scram-sha-256
host replication fgedu_repl 127.0.0.1/32 scram-sha-256
local replication fgedu_repl trust

# 重载配置
$ pg_ctl -D /postgresql/fgdata reload
server signaled

# 验证配置
$ psql -U fgedu_repl -h 192.168.1.100 -d fgedudb -c “SELECT 1;”
?column?
———-
1
(1 row)

# 测试复制连接
$ psql -U fgedu_repl -h 192.168.1.100 -d fgedudb -c “IDENTIFY_SYSTEM;”
systemid | timeline | xlogpos | dbname
———————+———-+———–+——–
7286447892263292736 | 1 | 0/2000148 |
(1 row)

3.2 PostgreSQL备库配置实战

3.2.1 使用pg_basebackup创建备库

# 在备库服务器上执行

# 1. 停止备库（如果存在）
$ pg_ctl -D /postgresql/fgdata stop -m immediate 2>/dev/null

# 2. 备份旧数据目录
$ mv /postgresql/fgdata /postgresql/fgdata.bak 2>/dev/null

# 3. 使用pg_basebackup创建备库
$ pg_basebackup -h 192.168.1.100 -p 5432 -U fgedu_repl -D /postgresql/fgdata -Fp -Xs -P -R

# 执行过程输出
Password:
31234567/31234567 kB (100%), 2/2 tablespaces

# 4. 查看生成的文件
$ ls -la /postgresql/fgdata/
total 128
drwx—— 20 pgsql fgedudb 4096 Apr 7 02:00 .
drwxr-xr-x 3 pgsql fgedudb 4096 Apr 7 02:00 ..
-rw——- 1 pgsql fgedudb 3 Apr 7 02:00 PG_VERSION
drwx—— 5 pgsql fgedudb 4096 Apr 7 02:00 base
…
-rw——- 1 pgsql fgedudb 0 Apr 7 02:00 standby.signal

# 5. 查看自动生成的连接配置
$ cat /postgresql/fgdata/postgresql.auto.conf
primary_conninfo = ‘user=fgedu_repl password=xxxx host=192.168.1.100 port=5432 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable’

# 6. 修改备库参数（可选）
$ vi /postgresql/fgdata/postgresql.auto.conf

# 添加应用名称
primary_conninfo = ‘user=fgedu_repl password=xxxx host=192.168.1.100 port=5432 fgapplication_name=fgedu_standby01’

# 添加复制槽
primary_slot_name = ‘fgedu_slot01’

# 7. 设置目录权限
$ chown -R pgsql:fgedudb /postgresql/fgdata
$ chmod 700 /postgresql/fgdata

3.2.2 启动备库

# 启动备库
$ pg_ctl -D /postgresql/fgdata start
waiting for server to start…. done
server started

# 查看启动日志
$ tail -50 /postgresql/fgdata/log/postgresql-*.log
2026-04-07 02:00:00.123 CST [12345] LOG: entering standby mode
2026-04-07 02:00:00.456 CST [12345] LOG: redo starts at 0/2000028
2026-04-07 02:00:00.789 CST [12345] LOG: consistent recovery state reached at 0/2000148
2026-04-07 02:00:01.012 CST [12345] LOG: database system is ready to accept read-only connections
2026-04-07 02:00:01.345 CST [12346] LOG: started streaming WAL from primary at 0/2000000 on timeline 1

# 验证备库状态
$ psql -U fgedu -d fgedudb -c “SELECT pg_is_in_recovery();”
pg_is_in_recovery
——————-
t
(1 row)

3.3 PostgreSQL复制验证实战

3.3.1 主库验证复制状态

# 主库验证复制状态
$ psql -U fgedu -d fgedudb

# 查看复制延迟
fgedudb=# SELECT
client_addr,
fgapplication_name,
state,
sent_lsn,
replay_lsn,
pg_wal_lsn_diff(sent_lsn, replay_lsn) as lag_bytes
FROM pg_stat_replication;

# 查看当前WAL位置
fgedudb=# SELECT pg_current_wal_lsn();
pg_current_wal_lsn
——————–
0/2000148
(1 row)

3.3.2 数据同步测试

# 数据同步测试

# 在主库创建测试数据
$ psql -U fgedu -d fgedudb

fgedudb=# CREATE TABLE fgedu_repl_test (id serial, data text, created_at timestamp default now());
CREATE TABLE

fgedudb=# INSERT INTO fgedu_repl_test (data) SELECT ‘test_’ || generate_series(1, 1000);
INSERT 0 1000

fgedudb=# SELECT count(*) FROM fgedu_repl_test;
count
——-
1000
(1 row)

# 在备库验证数据
$ psql -U fgedu -h 192.168.1.101 -d fgedudb

fgedudb=# SELECT count(*) FROM fgedu_repl_test;
count
——-
1000
(1 row)

# 验证备库只读
fgedudb=# INSERT INTO fgedu_repl_test (data) VALUES (‘test’);
ERROR: cannot execute INSERT in a read-only transaction

风哥提示：流复制配置完成后，务必进行数据同步测试，确保主备数据一致。建议定期监控复制延迟，及时发现和处理复制问题。更多学习教程公众号风哥教程itpux_com

Part04-生产案例与实战讲解

4.1 PostgreSQL异步复制案例

# PostgreSQL异步复制案例

# 场景：搭建1主2备异步复制架构

# 1. 主库配置
$ vi /postgresql/fgdata/postgresql.conf

# 异步复制配置
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
synchronous_commit = on
synchronous_standby_names = ”

# 2. 创建复制用户
$ psql -U fgedu -d fgedudb -c “CREATE USER fgedu_repl WITH REPLICATION PASSWORD ‘Fgedu@Repl2026’;”

# 3. 配置pg_hba.conf
$ vi /postgresql/fgdata/pg_hba.conf
host replication fgedu_repl 192.168.1.0/24 scram-sha-256

# 4. 在备库1上创建备库
$ pg_basebackup -h 192.168.1.100 -p 5432 -U fgedu_repl -D /postgresql/fgdata -Fp -Xs -P -R

# 修改应用名称
$ vi /postgresql/fgdata/postgresql.auto.conf
primary_conninfo = ‘… fgapplication_name=fgedu_standby01’

# 启动备库1
$ pg_ctl -D /postgresql/fgdata start

# 5. 在备库2上创建备库
$ pg_basebackup -h 192.168.1.100 -p 5432 -U fgedu_repl -D /postgresql/fgdata -Fp -Xs -P -R

# 修改应用名称
$ vi /postgresql/fgdata/postgresql.auto.conf
primary_conninfo = ‘… fgapplication_name=fgedu_standby02’

# 启动备库2
$ pg_ctl -D /postgresql/fgdata start

# 6. 验证复制状态
$ psql -U fgedu -d fgedudb -c “SELECT client_addr, fgapplication_name, state, sync_state FROM pg_stat_replication;”

# 7. 测试数据同步
# 主库插入数据
$ psql -U fgedu -d fgedudb -c “INSERT INTO fgedu_orders (order_no) VALUES (‘ORD20260407001’);”

# 备库1验证
$ psql -U fgedu -h 192.168.1.101 -d fgedudb -c “SELECT * FROM fgedu_orders WHERE order_no = ‘ORD20260407001’;”

# 备库2验证
$ psql -U fgedu -h 192.168.1.102 -d fgedudb -c “SELECT * FROM fgedu_orders WHERE order_no = ‘ORD20260407001’;”

4.2 PostgreSQL同步复制案例

# PostgreSQL同步复制案例

# 场景：搭建1主1备同步复制架构

# 1. 主库配置
$ vi /postgresql/fgdata/postgresql.conf

# 同步复制配置
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
synchronous_commit = on
synchronous_standby_names = ‘fgedu_standby01’

# 重启主库
$ pg_ctl -D /postgresql/fgdata restart

# 2. 备库配置
$ vi /postgresql/fgdata/postgresql.auto.conf
primary_conninfo = ‘… fgapplication_name=fgedu_standby01’

# 重启备库
$ pg_ctl -D /postgresql/fgdata restart

# 3. 验证同步状态
$ psql -U fgedu -d fgedudb -c “SELECT client_addr, fgapplication_name, state, sync_state FROM pg_stat_replication;”

# 4. 测试同步提交
# 在主库执行事务
$ psql -U fgedu -d fgedudb -c “BEGIN; INSERT INTO fgedu_orders (order_no) VALUES (‘ORD_SYNC_001’); COMMIT;”
BEGIN
INSERT 0 1
COMMIT

# 事务会等待备库确认后才提交

# 5. 模拟备库故障
$ pg_ctl -D /postgresql/fgdata stop -m immediate

# 主库写入会阻塞（等待备库确认）
$ psql -U fgedu -d fgedudb -c “INSERT INTO fgedu_orders (order_no) VALUES (‘ORD_SYNC_002’);”
# 会一直等待…

# 6. 配置多个同步备库
$ vi /postgresql/fgdata/postgresql.conf
synchronous_standby_names = ‘FIRST 1 (fgedu_standby01, fgedu_standby02)’

# 表示至少1个备库确认即可提交

4.3 PostgreSQL级联复制案例

# PostgreSQL级联复制案例

# 场景：搭建1主1备1级联备库架构

# 架构图
# 主库(192.168.1.100) -> 备库1(192.168.1.101) -> 备库2(192.168.1.102)

# 1. 主库配置（正常配置）
$ vi /postgresql/fgdata/postgresql.conf
wal_level = replica
max_wal_senders = 10

# 2. 备库1配置
$ pg_basebackup -h 192.168.1.100 -p 5432 -U fgedu_repl -D /postgresql/fgdata -Fp -Xs -P -R

# 启用级联复制
$ vi /postgresql/fgdata/postgresql.conf
wal_level = replica
max_wal_senders = 10
hot_standby = on

# 启动备库1
$ pg_ctl -D /postgresql/fgdata start

# 3. 备库2配置（从备库1复制）
$ pg_basebackup -h 192.168.1.101 -p 5432 -U fgedu_repl -D /postgresql/fgdata -Fp -Xs -P -R

# 启动备库2
$ pg_ctl -D /postgresql/fgdata start

# 4. 验证级联复制
# 主库查看
$ psql -U fgedu -h 192.168.1.100 -d fgedudb -c “SELECT client_addr, fgapplication_name FROM pg_stat_replication;”

client_addr | fgapplication_name
————–+——————
192.168.1.101| fgedu_standby01
(1 row)

# 备库1查看
$ psql -U fgedu -h 192.168.1.101 -d fgedudb -c “SELECT client_addr, fgapplication_name FROM pg_stat_replication;”

client_addr | fgapplication_name
————–+——————
192.168.1.102| fgedu_standby02
(1 row)

# 5. 测试数据同步
# 主库插入数据
$ psql -U fgedu -h 192.168.1.100 -d fgedudb -c “INSERT INTO fgedu_orders (order_no) VALUES (‘ORD_CASCADE_001’);”

# 备库1验证
$ psql -U fgedu -h 192.168.1.101 -d fgedudb -c “SELECT * FROM fgedu_orders WHERE order_no = ‘ORD_CASCADE_001’;”

# 备库2验证
$ psql -U fgedu -h 192.168.1.102 -d fgedudb -c “SELECT * FROM fgedu_orders WHERE order_no = ‘ORD_CASCADE_001’;”

# 6. 级联复制优势
# – 减轻主库压力
# – 支持更多备库
# – 网络拓扑灵活

风哥教程针对风哥教程针对生产环境建议：级联复制可以减轻主库压力，适合大规模部署。建议根据业务需求选择合适的复制架构，核心业务使用同步复制，一般业务使用异步复制。from PostgreSQL视频:www.itpux.com

Part05-风哥经验总结与分享

5.1 PostgreSQL复制最佳实践

PostgreSQL复制最佳实践：

合理规划架构：根据业务需求选择复制模式
监控复制延迟：及时发现和处理复制问题
定期演练切换：确保故障切换流程顺畅
配置复制槽：防止WAL被过早删除
网络优化：确保复制网络稳定低延迟
文档记录：记录详细的配置和操作流程

5.2 PostgreSQL复制管理脚本

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

# PostgreSQL复制监控脚本

PGHOME=/postgresql/fgapp
PGUSER=fgedu
PGDATABASE=fgedudb
LOG_FILE=/postgresql/scripts/logs/replication_monitor.log
ALERT_THRESHOLD=10485760

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

# 检查复制状态
check_replication() {
local result=$($PGHOME/bin/psql -U $PGUSER -d $PGDATABASE -t -c ”
SELECT count(*) FROM pg_stat_replication WHERE state = ‘streaming’;
” | tr -d ‘ ‘)

if [ “$result” -lt 1 ]; then
log_message “ERROR: 没有活跃的复制连接”
return 1
fi

log_message “活跃复制连接数: $result”
}

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

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

log_message “复制延迟: ${lag} bytes”
}

# 主函数
main() {
log_message “==========================================”
log_message “开始复制监控检查”
log_message “==========================================”

check_replication && check_replication_lag

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

main

5.3 PostgreSQL复制检查清单

# PostgreSQL复制检查清单

# 配置前检查
– [ ] 确认主库参数正确
– [ ] 确认网络连通性
– [ ] 确认复制用户权限
– [ ] 确认磁盘空间充足

# 配置后验证
– [ ] 验证备库启动正常
– [ ] 验证复制状态正常
– [ ] 验证数据同步正常
– [ ] 验证只读查询正常

# 日常监控
– [ ] 监控复制状态
– [ ] 监控复制延迟
– [ ] 监控磁盘空间
– [ ] 监控网络状态

# 故障处理
– [ ] 准备故障切换流程
– [ ] 准备回切流程
– [ ] 准备重建备库流程
– [ ] 准备告警通知机制

风哥提示：流复制是PostgreSQL高可用的基础，需要在平时多加练习，熟悉各种配置和操作方法。建议建立完善的监控和告警机制，及时发现和处理复制问题。

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