PostgreSQL教程FG109-PG主备切换实战教程
本文档详细介绍PostgreSQL数据库主备切换的方法和流程，包括计划内切换、紧急切换、故障回切等操作，风哥教程参考PostgreSQL官方文档High Availability内容，适合DBA在生产环境中实施故障切换操作。
Part01-基础概念与理论知识
1.1 PostgreSQL主备切换概念
PostgreSQL主备切换是指将备库提升为主库，原主库降级为备库的过程。主备切换分为计划内切换和紧急切换两种场景。计划内切换是在主库正常运行时主动切换，紧急切换是在主库故障时被迫切换。切换后需要更新应用连接，确保业务正常运行。更多视频教程www.fgedu.net.cn
- 计划内切换：平滑切换，最小化业务影响
- 紧急切换：快速切换，可能丢失部分数据
- 需要更新应用连接配置
- 需要重建原主库为备库
- 可以使用pg_rewind快速重建
- 建议定期进行切换演练
1.2 PostgreSQL切换类型分类
# 1. 计划内切换（Switchover）
# – 主库正常运行
# – 主动切换到备库
# – 业务影响最小
# – 可以回切
# 2. 紧急切换（Failover）
# – 主库故障
# – 被迫切换到备库
# – 可能丢失数据
# – 需要重建原主库
# 3. 回切（Failback）
# – 切换完成后恢复原架构
# – 需要在低峰期进行
# – 需要重建备库
# 切换流程对比
| 切换类型 | 触发条件 | 数据丢失 | 业务影响 | 操作复杂度 |
|———|———|———|———|———–|
| 计划内切换 | 主动触发 | 无 | 最小 | 中等 |
| 紧急切换 | 主库故障 | 可能有 | 较大 | 简单 |
| 回切 | 恢复原架构 | 无 | 中等 | 复杂 |
# 切换关键步骤
# 1. 停止主库写入（计划内）
# 2. 确认备库同步完成
# 3. 提升备库为主库
# 4. 更新应用连接
# 5. 重建原主库为备库
1.3 PostgreSQL切换工具介绍
PostgreSQL切换工具：
- pg_ctl promote：手动提升备库为主库
- pg_rewind：快速同步主备库数据目录
- pg_basebackup：重建备库
- Patroni：自动故障切换工具
- Repmgr：复制管理和切换工具
- PAF（PostgreSQL Automatic Failover）：高可用解决方案
Part02-生产环境规划与建议
2.1 PostgreSQL切换规划
# 1. 切换时间规划
# 计划内切换：业务低峰期（凌晨2:00-4:00）
# 紧急切换：随时可能发生
# 回切：业务低峰期
# 2. 切换窗口规划
# 计划内切换：30分钟
# 紧急切换：5-10分钟
# 回切：60分钟
# 3. 人员分工
# 主操作人：执行切换操作
# 监控人：监控切换过程
# 应用负责人：更新应用配置
# 业务负责人：确认业务恢复
# 4. 通知机制
# 切换前通知：提前1天通知相关方
# 切换中通知：实时通知切换进度
# 切换后通知：通知切换结果
# 5. 回滚方案
# 准备回滚脚本
# 记录切换前状态
# 确保可以快速回滚
2.2 PostgreSQL切换策略
PostgreSQL切换策略：
- 优先级策略：选择数据最新的备库切换
- 延迟策略：延迟备库可以防止误操作
- 级联策略：级联复制时需要重建
- 多备库策略：选择延迟最小的备库
2.3 PostgreSQL切换演练规划
# 1. 演练频率
# 每月：计划内切换演练
# 每季：紧急切换演练
# 每年：完整灾难恢复演练
# 2. 演练内容
# – 计划内切换流程
# – 紧急切换流程
# – 回切流程
# – 应用连接更新
# – 监控告警验证
# 3. 演练环境
# – 独立的演练环境
# – 与生产环境配置一致
# – 使用真实数据副本
# 4. 演练记录
# – 记录演练时间
# – 记录切换时间
# – 记录遇到的问题
# – 记录改进措施
# 5. 演练报告
# – 演练成功率
# – 切换时间统计
# – 问题分析和改进建议
Part03-生产环境项目实施方案
3.1 PostgreSQL计划内切换实战
3.1.1 切换前检查
# 1. 检查主库状态
$ psql -U fgedu -d fgedudb -c “SELECT pg_is_in_recovery();”
pg_is_in_recovery
——————-
f
(1 row)
# 2. 检查备库状态
$ psql -U fgedu -h 192.168.1.101 -d fgedudb -c “SELECT pg_is_in_recovery();”
pg_is_in_recovery
——————-
t
(1 row)
# 3. 检查复制延迟
$ psql -U fgedu -d fgedudb -c ”
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;
”
client_addr | fgapplication_name | state | sent_lsn | replay_lsn | lag_bytes
————–+——————+———-+———–+————+———–
192.168.1.101| fgedu_standby01 | streaming| 0/65000128| 0/65000128 | 0
(1 row)
# 4. 检查备库接收状态
$ psql -U fgedu -h 192.168.1.101 -d fgedudb -c ”
SELECT status, received_lsn, latest_end_lsn
FROM pg_stat_wal_receiver;
”
status | received_lsn | latest_end_lsn
———-+————–+—————-
streaming| 0/65000128 | 0/65000128
(1 row)
# 5. 检查数据库大小
$ psql -U fgedu -d fgedudb -c “SELECT pg_size_pretty(pg_database_size(‘fgedudb’));”
pg_size_pretty
—————-
50 GB
(1 row)
# 6. 检查连接数
$ psql -U fgedu -d fgedudb -c “SELECT count(*) FROM pg_stat_activity;”
count
——-
25
(1 row)
3.1.2 执行计划内切换
# 1. 停止应用写入
# 通知应用停止写入，等待当前事务完成
# 2. 检查主库活动连接
$ psql -U fgedu -d fgedudb -c ”
SELECT pid, usename, fgapplication_name, state, query
FROM pg_stat_activity
WHERE state = ‘active’ AND pid != pg_backend_pid();
”
pid | usename | fgapplication_name | state | query
—–+———+——————+——-+——-
(0 rows)
# 3. 强制切换WAL文件
$ psql -U fgedu -d fgedudb -c “SELECT pg_switch_wal();”
pg_switch_wal
—————
0/66000000
(1 row)
# 4. 等待备库同步
$ psql -U fgedu -d fgedudb -c ”
SELECT pg_wal_lsn_diff(sent_lsn, replay_lsn) as lag_bytes
FROM pg_stat_replication;
”
lag_bytes
———–
0
(1 row)
# 5. 停止主库
$ pg_ctl -D /postgresql/fgdata stop -m fast
waiting for server to shut down…. done
server stopped
# 6. 提升备库为主库
$ pg_ctl -D /postgresql/fgdata promote
waiting for server to promote…. done
server promoted
# 7. 验证新主库状态
$ psql -U fgedu -d fgedudb -c “SELECT pg_is_in_recovery();”
pg_is_in_recovery
——————-
f
(1 row)
# 8. 检查新主库时间线
$ psql -U fgedu -d fgedudb -c “SELECT pg_control_checkpoint();” | grep timeline
Latest checkpoint’s TimeLineID: 2
# 9. 更新应用连接
# 更新应用配置，指向新主库IP
# 10. 验证业务正常
$ psql -U fgedu -d fgedudb -c “SELECT count(*) FROM fgedu_orders;”
count
——-
500000
(1 row)
3.2 PostgreSQL紧急切换实战
3.2.1 确认主库故障
# 1. 检查主库连接
$ psql -U fgedu -h 192.168.1.100 -d fgedudb -c “SELECT 1;”
psql: could not connect to server: Connection refused
Is the server running on host “192.168.1.100” and accepting
TCP/IP connections on port 5432?
# 2. 检查主库进程
$ ssh 192.168.1.100 “ps -ef | grep postgres”
pgsql 1234 1 0 Apr06 ? 00:00:00 /postgresql/fgapp/bin/postgres -D /postgresql/fgdata
# 如果进程不存在，确认主库已宕机
# 3. 检查备库状态
$ psql -U fgedu -h 192.168.1.101 -d fgedudb -c “SELECT pg_is_in_recovery();”
pg_is_in_recovery
——————-
t
(1 row)
# 4. 检查备库WAL接收状态
$ psql -U fgedu -h 192.168.1.101 -d fgedudb -c ”
SELECT status, received_lsn, latest_end_lsn, latest_end_time
FROM pg_stat_wal_receiver;
”
status | received_lsn | latest_end_lsn | latest_end_time
———-+————–+—————-+————————-
streaming| 0/65000128 | 0/65000128 | 2026-04-07 10:00:00+08
(1 row)
# 5. 评估数据丢失
# 如果主库完全不可用，无法确定丢失的数据量
# 如果有WAL归档，可以从归档恢复
3.2.2 执行紧急切换
# 1. 在备库上执行提升
$ pg_ctl -D /postgresql/fgdata promote
waiting for server to promote…. done
server promoted
# 2. 验证新主库状态
$ psql -U fgedu -d fgedudb -c “SELECT pg_is_in_recovery();”
pg_is_in_recovery
——————-
f
(1 row)
# 3. 检查新主库时间线
$ psql -U fgedu -d fgedudb -c “SELECT timeline_id FROM pg_control_checkpoint();”
timeline_id
————-
2
(1 row)
# 4. 检查数据库完整性
$ psql -U fgedu -d fgedudb -c ”
SELECT schemaname, relname, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC
LIMIT 10;
”
schemaname | relname | n_live_tup
————+—————+————
public | fgedu_orders | 500000
public | fgedu_customers| 100000
(2 rows)
# 5. 更新应用连接
# 更新应用配置，指向新主库IP
# 6. 验证业务正常
$ psql -U fgedu -d fgedudb -c “INSERT INTO fgedu_orders (order_no) VALUES (‘ORD_FAILOVER_001’);”
INSERT 0 1
# 7. 通知相关方
# 通知运维、开发、业务等相关方切换完成
# 8. 记录切换信息
$ echo “$(date ‘+%Y-%m-%d %H:%M:%S’) – 紧急切换完成，新主库: 192.168.1.101” >> /postgresql/scripts/logs/failover.log
3.3 PostgreSQL故障回切实战
3.3.1 准备回切环境
# 1. 确认原主库已修复
$ ssh 192.168.1.100 “pg_ctl -D /postgresql/fgdata status”
pg_ctl: server is not running
# 2. 使用pg_rewind同步数据
$ pg_rewind -D /postgresql/fgdata –source-server=”host=192.168.1.101 port=5432 user=fgedu dbname=fgedudb”
# 执行过程输出
The servers diverged at WAL location 0/65000128 on timeline 1.
Rewinding from last common checkpoint at 0/60000000 on timeline 1
Done!
# 3. 配置恢复参数
$ vi /postgresql/fgdata/postgresql.auto.conf
primary_conninfo = ‘host=192.168.1.101 port=5432 user=fgedu_repl fgapplication_name=fgedu_standby_old’
primary_slot_name = ‘fgedu_slot_old’
# 4. 创建复制槽（在新主库上）
$ psql -U fgedu -h 192.168.1.101 -d fgedudb -c “SELECT pg_create_physical_replication_slot(‘fgedu_slot_old’);”
pg_create_physical_replication_slot
————————————-
(fgedu_slot_old,)
(1 row)
# 5. 创建standby.signal文件
$ touch /postgresql/fgdata/standby.signal
# 6. 启动原主库作为备库
$ pg_ctl -D /postgresql/fgdata start
waiting for server to start…. done
server started
# 7. 验证备库状态
$ psql -U fgedu -d fgedudb -c “SELECT pg_is_in_recovery();”
pg_is_in_recovery
——————-
t
(1 row)
3.3.2 执行回切操作
# 1. 确认备库同步完成
$ psql -U fgedu -h 192.168.1.101 -d fgedudb -c ”
SELECT client_addr, pg_wal_lsn_diff(sent_lsn, replay_lsn) as lag_bytes
FROM pg_stat_replication
WHERE client_addr = ‘192.168.1.100’;
”
client_addr | lag_bytes
————–+———–
192.168.1.100| 0
(1 row)
# 2. 执行计划内切换（将主库切回原主库）
# 参考计划内切换流程
# 3. 停止当前主库写入
# 通知应用停止写入
# 4. 强制切换WAL
$ psql -U fgedu -h 192.168.1.101 -d fgedudb -c “SELECT pg_switch_wal();”
# 5. 等待备库同步
$ psql -U fgedu -h 192.168.1.101 -d fgedudb -c ”
SELECT pg_wal_lsn_diff(sent_lsn, replay_lsn)
FROM pg_stat_replication;
”
# 6. 停止当前主库
$ pg_ctl -D /postgresql/fgdata stop -m fast
# 7. 提升备库为主库
$ pg_ctl -D /postgresql/fgdata promote
# 8. 验证新主库状态
$ psql -U fgedu -d fgedudb -c “SELECT pg_is_in_recovery();”
pg_is_in_recovery
——————-
f
(1 row)
# 9. 更新应用连接
# 更新应用配置，指向原主库IP
# 10. 验证业务正常
$ psql -U fgedu -d fgedudb -c “SELECT count(*) FROM fgedu_orders;”
Part04-生产案例与实战讲解
4.1 PostgreSQL手动切换案例
# manual_switchover.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# PostgreSQL手动切换脚本
PGHOME=/postgresql/fgapp
PRIMARY_HOST=192.168.1.100
STANDBY_HOST=192.168.1.101
PGUSER=fgedu
PGDATABASE=fgedudb
LOG_FILE=/postgresql/scripts/logs/switchover.log
log_message() {
echo “$(date ‘+%Y-%m-%d %H:%M:%S’) – $1″ >> $LOG_FILE
}
# 检查复制延迟
check_replication_lag() {
local lag=$($PGHOME/bin/psql -h $PRIMARY_HOST -U $PGUSER -d $PGDATABASE -t -c ”
SELECT pg_wal_lsn_diff(sent_lsn, replay_lsn)
FROM pg_stat_replication;
” | tr -d ‘ ‘)
if [ “$lag” -gt 0 ]; then
log_message “ERROR: 复制延迟不为0: ${lag} bytes”
return 1
fi
log_message “复制延迟检查通过”
}
# 停止主库
stop_primary() {
log_message “停止主库…”
ssh pgsql@$PRIMARY_HOST “$PGHOME/bin/pg_ctl -D /postgresql/fgdata stop -m fast”
}
# 提升备库
promote_standby() {
log_message “提升备库…”
ssh pgsql@$STANDBY_HOST “$PGHOME/bin/pg_ctl -D /postgresql/fgdata promote”
}
# 验证新主库
verify_new_primary() {
log_message “验证新主库…”
local result=$($PGHOME/bin/psql -h $STANDBY_HOST -U $PGUSER -d $PGDATABASE -t -c ”
SELECT pg_is_in_recovery();
” | tr -d ‘ ‘)
if [ “$result” = “f” ]; then
log_message “新主库验证成功”
else
log_message “ERROR: 新主库验证失败”
return 1
fi
}
# 主函数
main() {
log_message “==========================================”
log_message “开始执行计划内切换”
log_message “==========================================”
check_replication_lag && \
stop_primary && \
promote_standby && \
verify_new_primary
if [ $? -eq 0 ]; then
log_message “切换成功完成”
else
log_message “ERROR: 切换失败”
fi
log_message “==========================================”
log_message “切换任务结束”
log_message “==========================================”
}
main
4.2 PostgreSQL pg_rewind案例
# 场景：使用pg_rewind快速重建备库
# 1. 确认原主库状态
$ pg_ctl -D /postgresql/fgdata status
pg_ctl: server is not running
# 2. 检查原主库数据目录
$ ls /postgresql/fgdata/
PG_VERSION base global pg_wal …
# 3. 使用pg_rewind同步
$ pg_rewind -D /postgresql/fgdata \
–source-server=”host=192.168.1.101 port=5432 user=fgedu dbname=fgedudb password=Fgedu@2026″
# 执行过程输出
The servers diverged at WAL location 0/65000128 on timeline 1.
Rewinding from last common checkpoint at 0/60000000 on timeline 1
Reading source file list
Reading target file list
Reading WAL in target
Need to copy 58 MB from source to target
Copying file 58 MB
Syncing target data directory
Done!
# 4. 配置恢复参数
$ cat > /postgresql/fgdata/postgresql.auto.conf << EOF
primary_conninfo = 'host=192.168.1.101 port=5432 user=fgedu_repl fgapplication_name=fgedu_standby_rebuilt'
EOF
# 5. 创建standby.signal
$ touch /postgresql/fgdata/standby.signal
# 6. 启动备库
$ pg_ctl -D /postgresql/fgdata start
waiting for server to start.... done
server started
# 7. 验证备库状态
$ psql -U fgedu -d fgedudb -c "SELECT pg_is_in_recovery();"
pg_is_in_recovery
-------------------
t
(1 row)
# 8. 检查复制状态
$ psql -U fgedu -h 192.168.1.101 -d fgedudb -c "
SELECT client_addr, state, pg_wal_lsn_diff(sent_lsn, replay_lsn) as lag
FROM pg_stat_replication;
"
client_addr | state | lag
--------------+----------+-----
192.168.1.100| streaming| 0
(1 row)
# pg_rewind优势
# - 快速同步，不需要全量复制
# - 只同步差异部分
# - 节省时间和带宽
4.3 PostgreSQL自动切换案例
# 1. Patroni配置示例
$ cat /etc/patroni/fgedudb.yml
scope: fgedudb_cluster
namespace: /db/
name: node1
restapi:
listen: 192.168.1.100:8008
connect_address: 192.168.1.100:8008
etcd:
hosts: 192.168.1.100:2379,192.168.1.101:2379,192.168.1.102:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
parameters:
wal_level: replica
max_wal_senders: 10
wal_keep_size: 1GB
postgresql:
listen: 192.168.1.100:5432
connect_address: 192.168.1.100:5432
data_dir: /postgresql/fgdata
authentication:
replication:
username: fgedu_repl
password: Fgedu@Repl2026
superuser:
username: fgedu
password: Fgedu@2026
# 2. 启动Patroni
$ systemctl start patroni
# 3. 查看集群状态
$ patronictl -c /etc/patroni/fgedudb.yml list
+ Cluster: fgedudb_cluster ——-+—-+———–+
| Member | Host | Role | State | TL | Lag in MB |
+——–+—————+———+———+—-+———–+
| node1 | 192.168.1.100 | Leader | running | 1 | |
| node2 | 192.168.1.101 | Replica | running | 1 | 0 |
| node3 | 192.168.1.102 | Replica | running | 1 | 0 |
+——–+—————+———+———+—-+———–+
# 4. 手动切换
$ patronictl -c /etc/patroni/fgedudb.yml switchover
Master [node1]:
Candidate [‘node2’, ‘node3’] []: node2
When should the switchover take place (e.g. 2026-04-07T12:00 ) [now]: now
Are you sure you want to switchover cluster fgedudb_cluster, demoting current master node1? [y/N]: y
2026-04-07 10:00:00.12345 Successfully switched over to “node2”
# 5. 自动故障切换
# 当主库故障时，Patroni会自动选择新的主库
$ patronictl -c /etc/patroni/fgedudb.yml list
+ Cluster: fgedudb_cluster ——-+—-+———–+
| Member | Host | Role | State | TL | Lag in MB |
+——–+—————+———+———+—-+———–+
| node1 | 192.168.1.100 | Replica | running | 2 | 0 |
| node2 | 192.168.1.101 | Leader | running | 2 | |
| node3 | 192.168.1.102 | Replica | running | 2 | 0 |
+——–+—————+———+———+—-+———–+
Part05-风哥经验总结与分享
5.1 PostgreSQL切换最佳实践
PostgreSQL切换最佳实践：
- 定期演练：每月进行切换演练
- 文档记录：记录详细的切换流程
- 监控告警：监控复制状态和延迟
- 自动化工具：使用Patroni等工具实现自动切换
- 回滚方案：准备回滚脚本和流程
- 通知机制：及时通知相关方切换进度
5.2 PostgreSQL切换脚本库
/postgresql/scripts/failover/
├── manual_switchover.sh # 手动切换脚本
├── emergency_failover.sh # 紧急切换脚本
├── failback.sh # 回切脚本
├── pg_rewind_rebuild.sh # pg_rewind重建脚本
├── verify_replication.sh # 复制验证脚本
└── conf/
└── failover.conf # 切换配置
5.3 PostgreSQL切换检查清单
# 切换前检查
– [ ] 确认主库状态
– [ ] 确认备库状态
– [ ] 确认复制延迟
– [ ] 确认应用连接
– [ ] 通知相关方
# 切换中监控
– [ ] 监控切换进度
– [ ] 监控数据同步
– [ ] 记录切换日志
– [ ] 准备回滚
# 切换后验证
– [ ] 验证新主库状态
– [ ] 验证数据完整性
– [ ] 验证应用连接
– [ ] 验证业务功能
# 回切准备
– [ ] 修复原主库
– [ ] 同步数据
– [ ] 配置复制
– [ ] 选择低峰期
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
