PostgreSQL 复制监控与管理

复制状态检查

流复制状态检查

主库端状态检查

在主库上，可以通过以下视图监控复制状态：

sql

-- 查看所有连接的备库信息
SELECT * FROM pg_stat_replication;

-- 简化的复制状态视图
SELECT 
    application_name AS 备库名称,
    client_addr AS 备库IP,
    state AS 复制状态,
    sync_state AS 同步状态,
    write_lag AS 写入延迟,
    flush_lag AS 刷新延迟,
    replay_lag AS 回放延迟
FROM pg_stat_replication;

关键字段说明：

state：复制状态，包括startup、catchup、streaming等
sync_state：同步状态，包括async（异步）、sync（同步）、quorum（法定人数）
write_lag：主库写入到备库接收到的延迟
flush_lag：备库接收到到刷新到磁盘的延迟
replay_lag：备库刷新到磁盘到回放的延迟

备库端状态检查

在备库上，可以通过以下视图监控复制状态：

sql

-- 查看备库复制状态
SELECT * FROM pg_stat_wal_receiver;

-- 简化的备库复制状态
SELECT 
    status AS 接收状态,
    receive_start_lsn AS 接收起始LSN,
    latest_end_lsn AS 最新接收LSN,
    current_lsn AS 当前LSN,
    write_lag AS 写入延迟,
    flush_lag AS 刷新延迟,
    replay_lag AS 回放延迟
FROM pg_stat_wal_receiver, pg_current_wal_lsn() AS current_lsn;

-- 查看备库是否处于只读模式
SELECT pg_is_in_recovery();

-- 查看备库应用进度
SELECT 
    last_wal_receive_lsn AS 最后接收LSN,
    last_wal_replay_lsn AS 最后回放LSN,
    last_wal_replay_time AS 最后回放时间,
    replay_lag AS 回放延迟
FROM pg_stat_wal_receiver;

逻辑复制状态检查

发布端状态检查

sql

-- 查看所有发布
SELECT * FROM pg_publication;

-- 查看发布包含的表
SELECT * FROM pg_publication_tables;

-- 查看逻辑复制槽状态
SELECT * FROM pg_replication_slots WHERE slot_type = 'logical';

订阅端状态检查

sql

-- 查看所有订阅
SELECT * FROM pg_subscription;

-- 查看订阅状态
SELECT * FROM pg_stat_subscription;

-- 简化的逻辑复制状态
SELECT 
    subname AS 订阅名称,
    subdbid::regdb AS 数据库,
    pubname AS 发布名称,
    sync_priority AS 同步优先级,
    sync_state AS 同步状态,
    latest_end_lsn AS 最新LSN,
    last_msg_send_time AS 最后发送时间,
    last_msg_receipt_time AS 最后接收时间,
    last_sync_time AS 最后同步时间
FROM pg_stat_subscription;

复制延迟监控

流复制延迟监控

基于LSN的延迟计算

sql

-- 在主库上计算备库延迟
SELECT 
    application_name,
    client_addr,
    pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS 发送延迟_bytes,
    pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn) AS 写入延迟_bytes,
    pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn) AS 刷新延迟_bytes,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS 回放延迟_bytes,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) / 1024 / 1024 AS 回放延迟_MB
FROM pg_stat_replication;

-- 在备库上计算延迟
SELECT 
    pg_wal_lsn_diff(pg_current_wal_lsn(), last_wal_replay_lsn) AS 延迟_bytes,
    pg_wal_lsn_diff(pg_current_wal_lsn(), last_wal_replay_lsn) / 1024 / 1024 AS 延迟_MB,
    now() - last_wal_replay_time AS 时间延迟
FROM pg_stat_wal_receiver;

基于时间的延迟监控

sql

-- 在备库上查看时间延迟
SELECT 
    now() AS 当前时间,
    pg_last_xact_replay_timestamp() AS 最后事务回放时间,
    now() - pg_last_xact_replay_timestamp() AS 时间延迟
FROM pg_stat_wal_receiver;

逻辑复制延迟监控

sql

-- 查看逻辑复制延迟
SELECT 
    subname,
    now() - last_sync_time AS 同步延迟,
    latest_end_lsn,
    last_msg_receipt_time
FROM pg_stat_subscription;

监控工具集成

Prometheus + Grafana监控

安装pg_exporter

bash

wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-v0.15.0.linux-amd64.tar.gz
tar xzf postgres_exporter-v0.15.0.linux-amd64.tar.gz
cd postgres_exporter-v0.15.0.linux-amd64

配置pg_exporter

bash

export DATA_SOURCE_NAME="postgresql://postgres:password@localhost:5432/postgres?sslmode=disable"
./postgres_exporter

Grafana监控面板

导入PostgreSQL监控面板模板（推荐ID：9628）
添加复制延迟监控图表
设置复制延迟告警

Zabbix监控

配置Zabbix Agent

bash

# 安装Zabbix Agent
yum install zabbix-agent -y

# 配置Zabbix Agent
vi /etc/zabbix/zabbix_agentd.conf
# 修改Server和ServerActive指向Zabbix Server

配置PostgreSQL监控脚本

bash

# 创建监控脚本目录
mkdir -p /etc/zabbix/scripts

# 创建复制状态监控脚本
cat > /etc/zabbix/scripts/check_pg_replication.sh << 'EOF'
#!/bin/bash
PGPASSWORD=password psql -h localhost -U postgres -c "SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) FROM pg_stat_replication;" -t
EOF

chmod +x /etc/zabbix/scripts/check_pg_replication.sh

配置Zabbix监控项

添加自定义监控项，使用上述脚本获取复制延迟
设置告警阈值，例如延迟超过1GB或5分钟

复制管理操作

流复制管理

手动创建复制槽

sql

-- 创建物理复制槽
SELECT * FROM pg_create_physical_replication_slot('physical_slot1');

-- 删除物理复制槽
SELECT * FROM pg_drop_replication_slot('physical_slot1');

调整复制同步模式

修改postgresql.conf

# 主库配置
max_wal_senders = 10
wal_keep_size = 1GB

# 同步备库数量
 synchronous_standby_names = '2 (备库1, 备库2)'

动态调整同步模式

sql

-- 设置为异步复制
ALTER SYSTEM SET synchronous_standby_names = '';

-- 设置为同步复制
ALTER SYSTEM SET synchronous_standby_names = '备库1';

-- 应用配置
SELECT pg_reload_conf();

备库追赶主库

当备库延迟过大时，可以使用以下方法加速追赶：

使用pg_basebackup重新初始化

bash

# 停止备库服务
systemctl stop postgresql-14

# 删除旧数据
rm -rf /var/lib/pgsql/14/data/*

# 从主库重新获取基础备份
pg_basebackup -h 主库IP -U replication -D /var/lib/pgsql/14/data -Fp -Xs -P -R

# 启动备库服务
systemctl start postgresql-14

使用pg_rewind追赶

bash

# 停止备库服务
systemctl stop postgresql-14

# 执行pg_rewind
pg_rewind --target-pgdata=/var/lib/pgsql/14/data --source-server='host=主库IP user=replication password=replication_pass dbname=postgres'

# 启动备库服务
systemctl start postgresql-14

逻辑复制管理

创建和管理发布

sql

-- 创建发布
CREATE PUBLICATION my_publication FOR TABLE table1, table2;

-- 添加表到发布
ALTER PUBLICATION my_publication ADD TABLE table3;

-- 从发布中移除表
ALTER PUBLICATION my_publication DROP TABLE table1;

-- 删除发布
DROP PUBLICATION my_publication;

创建和管理订阅

sql

-- 创建订阅
CREATE SUBSCRIPTION my_subscription
  CONNECTION 'host=发布端IP port=5432 dbname=postgres user=replication password=replication_pass'
  PUBLICATION my_publication
  WITH (copy_data=true, create_slot=true);

-- 刷新订阅
ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION;

-- 禁用订阅
ALTER SUBSCRIPTION my_subscription DISABLE;

-- 启用订阅
ALTER SUBSCRIPTION my_subscription ENABLE;

-- 删除订阅
DROP SUBSCRIPTION my_subscription;

逻辑复制槽管理

sql

-- 查看所有复制槽
SELECT * FROM pg_replication_slots;

-- 查看逻辑复制槽
SELECT * FROM pg_replication_slots WHERE slot_type = 'logical';

-- 删除逻辑复制槽
SELECT pg_drop_replication_slot('my_logical_slot');

复制故障排除

流复制故障

常见故障类型

备库连接失败

故障现象：

LOG:  could not connect to primary server: connection refused
DETAIL:  Connection refused

解决方案：

检查主库是否正常运行
检查主库防火墙是否开放5432端口
检查pg_hba.conf是否允许备库连接
检查复制用户密码是否正确

WAL文件丢失

故障现象：

LOG:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000000000000001 has already been removed

解决方案：

增加主库wal_keep_size参数
使用复制槽确保WAL文件不被过早删除
重新初始化备库

复制延迟持续增长

故障现象：

备库延迟持续增加
主库pg_stat_replication中replay_lag字段持续增长

解决方案：

检查备库系统资源使用情况
检查备库是否有慢查询或长事务
调整备库max_worker_processes参数
考虑使用更强大的硬件

逻辑复制故障

常见故障类型

订阅初始化失败

故障现象：

ERROR:  could not create replication slot "my_subscription" on publisher
DETAIL:  ERROR:  replication slot "my_subscription" already exists

解决方案：

删除已存在的复制槽
使用create_slot=false选项创建订阅

数据冲突

故障现象：

ERROR:  duplicate key value violates unique constraint "table1_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  processing remote data for replication origin "pg_12345" during message type "INSERT" for replication target relation "public.table1" in transaction 12345

解决方案：

手动解决数据冲突
重新初始化订阅
调整应用逻辑避免冲突

订阅停滞

故障现象：

pg_stat_subscription中last_sync_time不再更新
订阅状态显示为streaming但无数据同步

解决方案：

检查发布端和订阅端网络连接
检查发布端是否有新的WAL生成
重启订阅

复制监控最佳实践

监控指标

核心指标
- 复制延迟（字节数和时间）
- 复制状态（连接状态、同步状态）
- 复制槽状态（是否活跃、WAL保留情况）
- WAL生成速率和应用速率
辅助指标
- 主备库系统资源使用率（CPU、内存、磁盘I/O）
- 主库WAL文件保留情况
- 备库回放速率

告警策略

复制延迟告警
- 轻度告警：延迟超过100MB或30秒
- 中度告警：延迟超过500MB或5分钟
- 重度告警：延迟超过1GB或30分钟
复制状态告警
- 备库连接断开
- 复制状态异常
- 复制槽未活跃
资源告警
- 主库WAL保留空间不足
- 备库磁盘空间不足
- 备库CPU使用率过高

日常巡检

每日巡检
- 检查复制状态是否正常
- 检查复制延迟是否在合理范围内
- 检查复制槽状态
- 检查主备库系统资源使用情况
每周巡检
- 检查WAL文件保留情况
- 检查复制配置是否符合最佳实践
- 检查监控告警规则是否需要调整
每月巡检
- 执行备库切换测试
- 检查复制架构是否需要优化
- 更新监控面板和告警规则

版本差异注意事项

PostgreSQL 9.x

不支持逻辑复制
复制槽功能有限
监控视图字段较少
不支持write_lag、flush_lag、replay_lag字段

PostgreSQL 10-11

支持基础逻辑复制功能
复制监控视图字段更丰富
支持复制延迟计算
逻辑复制不支持DDL复制

PostgreSQL 12+

支持pg_stat_replication中的write_lag、flush_lag、replay_lag字段
支持pg_rewind的增强功能
逻辑复制性能提升
支持更多的监控指标

PostgreSQL 14+

支持逻辑复制并行应用
新增pg_stat_replication_slots视图
复制槽管理功能增强
支持更细粒度的复制控制

总结

PostgreSQL复制监控与管理是保证高可用架构稳定性的关键环节。DBA需要建立完善的监控体系，及时发现和处理复制问题，确保数据一致性和系统可靠性。

在实际生产环境中，建议：

结合多种监控工具，建立全方位的复制监控体系
设置合理的告警阈值，及时发现复制异常
定期进行复制管理操作，确保复制架构的健康运行
制定完善的复制故障应急预案
定期进行复制演练，提高故障处理能力

通过合理的监控和管理，可以确保PostgreSQL复制架构的稳定性和可靠性，为业务提供高可用的数据库服务。

PostgreSQL 复制监控与管理 ​

复制状态检查 ​

流复制状态检查 ​

主库端状态检查 ​

备库端状态检查 ​

逻辑复制状态检查 ​

发布端状态检查 ​

订阅端状态检查 ​

复制延迟监控 ​

流复制延迟监控 ​

基于LSN的延迟计算 ​

基于时间的延迟监控 ​

逻辑复制延迟监控 ​

监控工具集成 ​

Prometheus + Grafana监控 ​

Zabbix监控 ​

复制管理操作 ​

流复制管理 ​

手动创建复制槽 ​

调整复制同步模式 ​

备库追赶主库 ​

逻辑复制管理 ​

创建和管理发布 ​

创建和管理订阅 ​

逻辑复制槽管理 ​

复制故障排除 ​

流复制故障 ​

常见故障类型 ​

逻辑复制故障 ​

常见故障类型 ​

复制监控最佳实践 ​

监控指标 ​

告警策略 ​

日常巡检 ​

版本差异注意事项 ​

PostgreSQL 9.x ​

PostgreSQL 10-11 ​

PostgreSQL 12+ ​

PostgreSQL 14+ ​

总结 ​

PostgreSQL 复制监控与管理

复制状态检查

流复制状态检查

主库端状态检查

备库端状态检查

逻辑复制状态检查

发布端状态检查

订阅端状态检查

复制延迟监控

流复制延迟监控

基于LSN的延迟计算

基于时间的延迟监控

逻辑复制延迟监控

监控工具集成

Prometheus + Grafana监控

Zabbix监控

复制管理操作

流复制管理

手动创建复制槽

调整复制同步模式

备库追赶主库

逻辑复制管理

创建和管理发布

创建和管理订阅

逻辑复制槽管理

复制故障排除

流复制故障

常见故障类型

逻辑复制故障

常见故障类型

复制监控最佳实践

监控指标

告警策略

日常巡检

版本差异注意事项

PostgreSQL 9.x

PostgreSQL 10-11

PostgreSQL 12+

PostgreSQL 14+

总结