PostgreSQL 复制故障排查

核心概念

1. 复制故障的分类

PostgreSQL复制故障主要分为以下几类：

连接故障：主从库之间无法建立或维持连接
配置故障：复制配置错误导致复制无法正常工作
性能故障：复制延迟过高或复制速度过慢
数据一致性故障：主从库数据不一致
WAL相关故障：WAL日志生成、传输或应用故障
复制槽故障：复制槽配置错误或状态异常

2. 故障排查的基本原则

从外到内：先检查外部因素（网络、硬件），再检查内部因素（配置、数据库）
从主到从：先检查主库状态，再检查从库状态
从简单到复杂：先排查简单问题（连接、配置），再排查复杂问题（性能、数据一致性）
从日志到命令：先查看日志，再使用命令工具诊断
从整体到局部：先检查复制整体状态，再定位具体故障点

3. 故障排查的工具和命令

日志文件：PostgreSQL日志文件，包含复制相关的错误信息
内置视图：pg_stat_replication、pg_stat_wal_receiver、pg_replication_slots等
命令行工具：pg_controldata、pg_waldump、pg_basebackup等
系统工具：ping、traceroute、netstat、top等

故障排查的基本步骤

1. 收集信息

1.1 查看数据库日志

bash

# 查看主库日志
tail -f /var/log/postgresql/postgresql-15-main.log

# 查看从库日志
tail -f /var/log/postgresql/postgresql-15-main.log

1.2 检查复制状态

sql

-- 在主库上检查复制状态
SELECT * FROM pg_stat_replication;

-- 在从库上检查复制状态
SELECT * FROM pg_stat_wal_receiver;
SELECT pg_is_in_recovery();

1.3 检查系统状态

bash

# 检查系统负载
top

# 检查磁盘空间
df -h

# 检查网络连接
ping 主库IP
traceroute 主库IP
netstat -an | grep 5432

2. 定位故障点

根据收集到的信息，定位具体的故障点：

如果主库日志中有错误信息，先分析主库问题
如果从库日志中有错误信息，先分析从库问题
如果pg_stat_replication视图为空，检查主库配置和从库连接
如果pg_stat_wal_receiver视图为空，检查从库配置和主库连接
如果复制延迟过高，分析延迟原因（网络、主库负载、从库负载等）

3. 实施修复

根据故障点，实施相应的修复措施：

连接问题：检查网络、防火墙、pg_hba.conf配置
配置问题：检查postgresql.conf中的复制相关配置
性能问题：优化主库或从库配置，增加资源
WAL问题：检查WAL生成、传输或应用情况
复制槽问题：检查复制槽状态，清理无效复制槽

4. 验证修复结果

修复后，验证复制是否恢复正常：

sql

-- 检查主库复制状态
SELECT * FROM pg_stat_replication;

-- 检查从库复制状态
SELECT * FROM pg_stat_wal_receiver;
SELECT pg_is_in_recovery();

-- 测试数据同步
-- 在主库上创建测试数据，在从库上验证

常见复制故障及解决方案

1. 从库无法连接到主库

故障现象：

从库日志中显示连接错误
pg_stat_replication视图中没有从库信息
pg_stat_wal_receiver视图中没有数据

可能原因：

主库未启动或端口未开放
网络连接问题（防火墙、路由）
pg_hba.conf配置错误
复制用户密码错误
max_wal_senders设置过小

解决方案：

bash

# 1. 检查主库是否运行
pg_ctl status -D /var/lib/postgresql/15/main

# 2. 检查网络连接
ping 主库IP
nc -zv 主库IP 5432

# 3. 检查pg_hba.conf配置
cat /etc/postgresql/15/main/pg_hba.conf | grep replication

# 4. 检查max_wal_senders设置
psql -h 主库IP -U postgres -c "SHOW max_wal_senders;"

# 5. 验证复制用户密码
export PGPASSWORD=replication_pass
psql -h 主库IP -U replication_user -c "SELECT 1;"

2. 复制中断

故障现象：

从库停止接收或应用WAL日志
pg_stat_replication视图中从库状态异常
从库日志中显示复制中断错误

可能原因：

主库WAL日志被过早删除
从库WAL应用失败
网络中断
主库或从库重启

解决方案：

bash

# 1. 检查从库日志，查看具体错误信息
tail -f /var/log/postgresql/postgresql-15-main.log

# 2. 检查主库的wal_keep_size设置
psql -h 主库IP -U postgres -c "SHOW wal_keep_size;"

# 3. 检查是否使用了复制槽
psql -h 主库IP -U postgres -c "SELECT * FROM pg_replication_slots;"

# 4. 重启从库复制
pg_ctl restart -D /var/lib/postgresql/15/main

# 5. 如果无法恢复，重新创建从库
pg_ctl stop -D /var/lib/postgresql/15/main
rm -rf /var/lib/postgresql/15/main/*
pg_basebackup -h 主库IP -U replication_user -D /var/lib/postgresql/15/main -F p -X stream -R
pg_ctl start -D /var/lib/postgresql/15/main

3. 从库无法应用WAL日志

故障现象：

从库接收WAL日志但无法应用
从库日志中显示WAL应用错误
复制延迟持续增加

可能原因：

主从库版本不兼容
从库配置错误（hot_standby、max_standby_streaming_delay等）
从库上有长查询阻塞WAL应用
WAL日志损坏

解决方案：

bash

# 1. 检查主从库版本
psql -h 主库IP -U postgres -c "SELECT version();"
psql -h 从库IP -U postgres -c "SELECT version();"

# 2. 检查从库配置
psql -h 从库IP -U postgres -c "SHOW hot_standby;"
psql -h 从库IP -U postgres -c "SHOW max_standby_streaming_delay;"

# 3. 检查从库上的长查询
psql -h 从库IP -U postgres -c "SELECT pid, query_start, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC;"

# 4. 终止阻塞WAL应用的查询
psql -h 从库IP -U postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'active' AND now() - query_start > interval '10 minutes';"

# 5. 如果无法恢复，重新创建从库

4. 复制槽故障

故障现象：

复制槽状态异常
主库WAL日志积压
从库无法连接到主库

可能原因：

复制槽配置错误
复制槽被意外删除
复制槽状态变为inactive
复制槽积压过多WAL日志

解决方案：

sql

-- 1. 检查复制槽状态
SELECT * FROM pg_replication_slots;

-- 2. 检查复制槽的WAL积压情况
SELECT slot_name, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) / 1024 / 1024 AS wal_backlog_mb FROM pg_replication_slots;

-- 3. 清理无效的复制槽
SELECT pg_drop_replication_slot('slot_name');

-- 4. 重新创建复制槽
SELECT pg_create_physical_replication_slot('slot_name');

-- 5. 在从库上配置使用新的复制槽
# 修改从库的postgresql.auto.conf
primary_conninfo = 'host=主库IP port=5432 user=replication_user password=replication_pass application_name=standby1 primary_slot_name=slot_name'

5. 主从库数据不一致

故障现象：

从库上查询不到主库已提交的数据
主从库表结构不一致
从库上的查询结果与主库不同

可能原因：

从库被意外修改（手动写入数据）
复制中断后未正确恢复
主从库配置不一致
WAL日志应用错误

解决方案：

bash

# 1. 验证数据一致性
# 使用pg_checksums工具（PostgreSQL 12+）
pg_checksums -c -D /var/lib/postgresql/15/main

# 2. 重新创建从库
pg_ctl stop -D /var/lib/postgresql/15/main
rm -rf /var/lib/postgresql/15/main/*
pg_basebackup -h 主库IP -U replication_user -D /var/lib/postgresql/15/main -F p -X stream -R
pg_ctl start -D /var/lib/postgresql/15/main

# 3. 预防措施：
# 确保从库处于只读模式
# 配置合适的hot_standby_feedback参数
# 定期验证主从库数据一致性

6. WAL日志损坏

故障现象：

主库日志中显示WAL损坏错误
从库无法应用WAL日志
复制中断

可能原因：

主库磁盘故障
主库崩溃导致WAL日志未正确写入
网络传输过程中WAL日志损坏
从库磁盘故障导致WAL日志损坏

解决方案：

bash

# 1. 检查主库WAL日志
pg_waldump -p /var/lib/postgresql/15/main 000000010000000000000001

# 2. 检查从库WAL日志
pg_waldump -p /var/lib/postgresql/15/main 000000010000000000000001

# 3. 从备份恢复主库或从库
# 使用基础备份恢复
pg_basebackup -h 备份服务器IP -U backup_user -D /var/lib/postgresql/15/main -F p -X stream

# 4. 重新创建从库

7. 复制延迟过高

故障现象：

从库复制延迟超过告警阈值
从库WAL应用速度慢于主库WAL生成速度
从库查询到的数据与主库不一致

可能原因：

主库WAL生成速率过高
从库WAL应用速率过慢
网络带宽不足
从库负载过高
从库配置不合理

解决方案：

bash

# 1. 检查主库WAL生成速率
psql -h 主库IP -U postgres -c "SELECT pg_current_wal_lsn();" && sleep 10 && psql -h 主库IP -U postgres -c "SELECT pg_current_wal_lsn();"

# 2. 检查从库WAL应用速率
psql -h 从库IP -U postgres -c "SELECT pg_last_wal_replay_lsn();" && sleep 10 && psql -h 从库IP -U postgres -c "SELECT pg_last_wal_replay_lsn();"

# 3. 优化从库配置
cat >> /var/lib/postgresql/15/main/postgresql.conf << EOF
max_worker_processes = 16
max_parallel_workers_per_gather = 8
wal_receiver_buffer_size = 16MB
hot_standby_feedback = on
EOF

# 4. 减少从库负载
# 将部分只读查询迁移到其他从库
# 优化从库查询性能

# 5. 考虑使用级联复制

高级故障排查技术

1. 使用pg_waldump分析WAL日志

bash

# 查看WAL日志内容
pg_waldump -p /var/lib/postgresql/15/main 000000010000000000000001

# 查看特定事务的WAL日志
pg_waldump -p /var/lib/postgresql/15/main 000000010000000000000001 | grep -A 20 -B 5 "transaction"

# 查看WAL日志的统计信息
pg_waldump -p /var/lib/postgresql/15/main 000000010000000000000001 | wc -l

2. 使用pg_controldata检查数据库控制信息

bash

# 检查主库控制信息
pg_controldata /var/lib/postgresql/15/main

# 检查从库控制信息
pg_controldata /var/lib/postgresql/15/main

# 比较主从库的控制信息，特别是最新检查点位置

3. 使用pg_stat_statements分析查询性能

sql

-- 启用pg_stat_statements扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 分析从库上的慢查询
SELECT query, calls, total_time, mean_time, rows FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

-- 重置pg_stat_statements统计信息
SELECT pg_stat_statements_reset();

故障预防与最佳实践

1. 配置最佳实践

合理设置wal_keep_size：保留足够的WAL日志，防止从库落后太多
使用复制槽：确保主库不会删除从库尚未接收的WAL日志
启用WAL压缩：减少网络传输量，提高复制速度
优化检查点配置：避免频繁的检查点导致大量WAL日志生成
合理设置max_wal_senders：根据从库数量设置足够的WAL发送进程

2. 监控最佳实践

设置复制延迟告警：根据业务需求设置合理的告警阈值
监控复制槽状态：定期检查复制槽的状态和WAL积压情况
监控主从库系统资源：监控CPU、内存、磁盘、网络使用情况
监控WAL生成和应用速率：了解复制的性能瓶颈
定期验证数据一致性：确保主从库数据一致

3. 运维最佳实践

定期备份主从库：确保在发生故障时能够快速恢复
定期测试故障切换：确保主库故障时能够顺利切换到从库
文档化复制配置和流程：详细记录复制配置和故障处理流程
定期演练故障恢复：提高应对故障的能力
保持主从库版本一致：避免版本差异导致的兼容性问题
使用专用网络连接主从库：确保网络稳定可靠

常见问题（FAQ）

Q1：如何快速定位复制故障的原因？

A1：可以按照以下步骤快速定位：

查看从库日志，寻找复制相关的错误信息
检查主库的pg_stat_replication视图，查看从库连接状态
检查从库的pg_stat_wal_receiver视图，查看WAL接收状态
检查主从库的系统资源使用情况
检查网络连接和延迟

Q2：复制故障会导致数据丢失吗？

A2：这取决于复制模式和故障类型：

异步复制：可能会丢失数据，特别是当主库崩溃时
半同步复制：在大多数情况下不会丢失数据，但仍存在极小概率
同步复制：不会丢失数据，除非所有同步从库同时故障

Q3：如何预防复制故障？

A3：可以采取以下预防措施：

合理配置复制参数
定期监控复制状态
定期备份主从库
定期测试故障切换
保持主从库版本一致
使用专用网络连接主从库

Q4：复制故障后如何快速恢复？

A4：快速恢复的关键是提前准备：

定期进行基础备份，确保可以快速创建从库
文档化故障恢复流程，确保运维人员熟悉恢复步骤
配置自动化监控和告警，及时发现故障
考虑使用自动化故障切换工具，如Patroni或pgpool-II

Q5：如何验证主从库数据一致性？

A5：可以使用以下方法验证：

逻辑验证：在主库和从库上执行相同的查询，比较结果
物理验证：使用pg_checksums工具检查数据块完整性
使用第三方工具：如pg_syncinfo、amcheck等
定期数据校验：定期对关键表进行数据校验

Q6：复制槽故障如何处理？

A6：复制槽故障的处理步骤：

检查复制槽状态
清理无效的复制槽
重新创建复制槽
配置从库使用新的复制槽
验证复制是否恢复正常

Q7：如何处理WAL日志损坏？

A7：WAL日志损坏的处理步骤：

确认WAL日志确实损坏
从备份恢复WAL日志
重新创建从库
检查主库的WAL生成和管理配置
考虑启用WAL校验和，提前发现WAL损坏

Q8：复制延迟过高如何优化？

A8：复制延迟过高的优化方法：

优化主库配置，减少WAL生成量
优化从库配置，提高WAL应用速度
增加网络带宽，减少传输延迟
减少从库负载，提高WAL应用效率
考虑使用级联复制，分担主库的复制压力

故障排查案例分析

案例1：从库无法连接到主库

故障现象：从库无法连接到主库，日志中显示"FATAL: could not connect to the primary server: FATAL: no pg_hba.conf entry for replication connection from host "192.168.1.101", user "replication_user", SSL off"

排查过程：

查看主库的pg_hba.conf文件，发现没有为从库IP添加复制连接权限
检查主库的max_wal_senders设置，发现设置为5，足够当前从库数量
检查网络连接，发现网络正常

解决方案：在主库的pg_hba.conf文件中添加从库的复制连接权限：

txt

host replication replication_user 192.168.1.101/32 md5

然后重启主库或重新加载配置：

bash

pg_ctl reload -D /var/lib/postgresql/15/main

案例2：复制延迟过高

故障现象：从库复制延迟超过5分钟，pg_stat_replication视图显示replay_lag持续增加

排查过程：

检查主库WAL生成速率，发现主库写入压力大，WAL生成速率高
检查从库WAL应用速率，发现从库WAL应用速率慢于主库WAL生成速率
检查从库负载，发现从库CPU使用率接近100%
检查从库的max_worker_processes设置，发现设置为4，低于CPU核心数

解决方案：

优化从库的max_worker_processes和max_parallel_workers_per_gather参数
将部分只读查询迁移到其他从库
增加从库的CPU资源
启用WAL压缩，减少网络传输量

通过以上方法，从库的复制延迟逐渐降低到正常水平。

PostgreSQL 复制故障排查 ​

核心概念 ​

1. 复制故障的分类 ​

2. 故障排查的基本原则 ​

3. 故障排查的工具和命令 ​

故障排查的基本步骤 ​

1. 收集信息 ​

1.1 查看数据库日志 ​

1.2 检查复制状态 ​

1.3 检查系统状态 ​

2. 定位故障点 ​

3. 实施修复 ​

4. 验证修复结果 ​

常见复制故障及解决方案 ​

1. 从库无法连接到主库 ​

2. 复制中断 ​

3. 从库无法应用WAL日志 ​

4. 复制槽故障 ​

5. 主从库数据不一致 ​

6. WAL日志损坏 ​

7. 复制延迟过高 ​

高级故障排查技术 ​

1. 使用pg_waldump分析WAL日志 ​

2. 使用pg_controldata检查数据库控制信息 ​

3. 使用pg_stat_statements分析查询性能 ​

故障预防与最佳实践 ​

1. 配置最佳实践 ​

2. 监控最佳实践 ​

3. 运维最佳实践 ​

常见问题（FAQ） ​

Q1：如何快速定位复制故障的原因？ ​

Q2：复制故障会导致数据丢失吗？ ​

Q3：如何预防复制故障？ ​

Q4：复制故障后如何快速恢复？ ​

Q5：如何验证主从库数据一致性？ ​

Q6：复制槽故障如何处理？ ​

Q7：如何处理WAL日志损坏？ ​

Q8：复制延迟过高如何优化？ ​

故障排查案例分析 ​

案例1：从库无法连接到主库 ​

案例2：复制延迟过高 ​

PostgreSQL 复制故障排查

核心概念

1. 复制故障的分类

2. 故障排查的基本原则

3. 故障排查的工具和命令

故障排查的基本步骤

1. 收集信息

1.1 查看数据库日志

1.2 检查复制状态

1.3 检查系统状态

2. 定位故障点

3. 实施修复

4. 验证修复结果

常见复制故障及解决方案

1. 从库无法连接到主库

2. 复制中断

3. 从库无法应用WAL日志

4. 复制槽故障

5. 主从库数据不一致

6. WAL日志损坏

7. 复制延迟过高

高级故障排查技术

1. 使用pg_waldump分析WAL日志

2. 使用pg_controldata检查数据库控制信息

3. 使用pg_stat_statements分析查询性能

故障预防与最佳实践

1. 配置最佳实践

2. 监控最佳实践

3. 运维最佳实践

常见问题（FAQ）

Q1：如何快速定位复制故障的原因？

Q2：复制故障会导致数据丢失吗？

Q3：如何预防复制故障？

Q4：复制故障后如何快速恢复？

Q5：如何验证主从库数据一致性？

Q6：复制槽故障如何处理？

Q7：如何处理WAL日志损坏？

Q8：复制延迟过高如何优化？

故障排查案例分析

案例1：从库无法连接到主库

案例2：复制延迟过高