印刻数据库

外观

PostgreSQL 复制延迟 ​

复制延迟监控 ​

1. 物理复制延迟监控 ​

sql
-- 在主库上查看复制状态
SELECT 
    usename,
    application_name,
    client_addr,
    state,
    sync_state,
    write_lag,
    flush_lag,
    replay_lag,
    write_location,
    flush_location,
    replay_location
FROM 
    pg_stat_replication;

-- 在从库上查看复制延迟
SELECT 
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_delay_bytes,
    pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn) AS write_delay_bytes,
    pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn) AS flush_delay_bytes,
    replay_lag,
    write_lag,
    flush_lag
FROM 
    pg_stat_wal_receiver;

-- 查看从库的恢复状态
SELECT 
    status,
    last_replay_location,
    last_replay_time,
    replay_lag
FROM 
    pg_stat_wal_receiver;

2. 逻辑复制延迟监控 ​

sql
-- 查看逻辑复制订阅状态
SELECT 
    subname,
    subenabled,
    subconninfo,
    subslotname,
    subsynccommit
FROM 
    pg_subscription;

-- 查看逻辑复制槽状态
SELECT 
    slot_name,
    plugin,
    slot_type,
    active,
    restart_lsn,
    confirmed_flush_lsn,
    pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes
FROM 
    pg_replication_slots
WHERE 
    slot_type = 'logical';

-- 查看逻辑复制应用状态
SELECT 
    application_name,
    state,
    sync_state,
    write_lag,
    flush_lag,
    replay_lag
FROM 
    pg_stat_replication
WHERE 
    application_name LIKE 'pg_%';

3. 使用 Prometheus 监控 ​

yaml
# 复制延迟告警规则
- alert: PostgreSQLReplicationLag
  expr: pg_stat_replication_replay_lag > 60
  for: 5m
  labels:
    severity: warning
  annotations:
    summary: "PostgreSQL 复制延迟过高"
    description: "从库 {{ $labels.instance }} 复制延迟超过 60 秒，当前值: {{ $value }} 秒"

- alert: PostgreSQLLogicalReplicationLag
  expr: pg_replication_slots_confirmed_flush_lsn > 0 and pg_wal_lsn_diff(pg_current_wal_lsn(), pg_replication_slots_confirmed_flush_lsn) / 1024 / 1024 > 100
  for: 5m
  labels:
    severity: warning
  annotations:
    summary: "PostgreSQL 逻辑复制延迟过高"
    description: "逻辑复制槽 {{ $labels.slot_name }} 延迟超过 100MB，当前值: {{ $value }} MB"

复制延迟诊断 ​

1. 识别复制延迟原因 ​

sql
-- 检查主库 WAL 生成速率
SELECT 
    pg_wal_lsn_diff(pg_current_wal_lsn(), pg_stat_file('pg_wal/000000010000000000000001')) AS wal_generated,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), pg_stat_file('pg_wal/000000010000000000000001'))) AS wal_size;

-- 检查从库网络连接
SELECT 
    client_addr,
    state,
    sync_state,
    write_lag,
    flush_lag,
    replay_lag
FROM 
    pg_stat_replication;

-- 检查从库资源使用情况
-- 在从库上执行
SELECT 
    usename,
    datname,
    application_name,
    state,
    query,
    now() - query_start AS duration
FROM 
    pg_stat_activity
WHERE 
    state != 'idle'
ORDER BY 
    duration DESC;

2. 分析复制延迟类型 ​

  • 写入延迟（write_lag）：主库到从库的网络延迟
  • 刷新延迟（flush_lag）：WAL 写入从库磁盘的延迟
  • 回放延迟（replay_lag）：从库应用 WAL 日志的延迟

复制延迟处理 ​

1. 网络相关优化 ​

sql
-- 调整 WAL 发送缓冲区大小
ALTER SYSTEM SET wal_sender_buffer_size = '16MB';

-- 调整从库接收缓冲区大小
ALTER SYSTEM SET wal_receiver_buffer_size = '16MB';

-- 启用 WAL 压缩
ALTER SYSTEM SET wal_compression = 'on';

-- 重新加载配置
SELECT pg_reload_conf();

2. WAL 相关优化 ​

sql
-- 调整 WAL 日志大小
ALTER SYSTEM SET wal_segment_size = '64MB';

-- 调整检查点参数
ALTER SYSTEM SET checkpoint_completion_target = '0.9';
ALTER SYSTEM SET max_wal_size = '2GB';

-- 启用并行 WAL 回放（PostgreSQL 14+）
ALTER SYSTEM SET wal_receiver_create_temp_slot = 'on';
ALTER SYSTEM SET max_worker_processes = '16';
ALTER SYSTEM SET max_parallel_workers = '12';

-- 重新加载配置
SELECT pg_reload_conf();

3. 从库性能优化 ​

sql
-- 调整从库共享缓冲区
ALTER SYSTEM SET shared_buffers = '8GB';

-- 调整从库维护工作内存
ALTER SYSTEM SET maintenance_work_mem = '2GB';

-- 调整从库检查点参数
ALTER SYSTEM SET checkpoint_completion_target = '0.9';

-- 启用从库热备反馈
ALTER SYSTEM SET hot_standby_feedback = 'on';

-- 重新加载配置
SELECT pg_reload_conf();

4. 同步复制调整 ​

sql
-- 查看当前同步复制配置
SHOW synchronous_commit;
SHOW synchronous_standby_names;

-- 调整同步复制模式
-- 选项：off, local, remote_write, remote_apply, on
ALTER SYSTEM SET synchronous_commit = 'remote_write';

-- 调整同步备库数量
ALTER SYSTEM SET synchronous_standby_names = 'FIRST 1 (standby1, standby2)';

-- 重新加载配置
SELECT pg_reload_conf();

复制延迟恢复 ​

1. 重建从库 ​

bash
# 在从库上停止 PostgreSQL
systemctl stop postgresql-15

# 清理从库数据目录
rm -rf /var/lib/postgresql/15/main/*

# 使用 pg_basebackup 重新构建从库
pg_basebackup -h primary_host -U replication_user -D /var/lib/postgresql/15/main -F p -X stream -R -P

# 启动从库
systemctl start postgresql-15

2. 修复逻辑复制延迟 ​

sql
-- 检查逻辑复制订阅状态
SELECT 
    subname,
    subenabled,
    subconninfo,
    subslotname
FROM 
    pg_subscription;

-- 重启逻辑复制订阅
ALTER SUBSCRIPTION my_subscription DISABLE;
ALTER SUBSCRIPTION my_subscription ENABLE;

-- 重新同步逻辑复制数据
ALTER SUBSCRIPTION my_subscription REFRESH PUBLICATION;

-- 检查逻辑复制槽状态
SELECT 
    slot_name,
    plugin,
    slot_type,
    active,
    restart_lsn,
    confirmed_flush_lsn
FROM 
    pg_replication_slots
WHERE 
    slot_type = 'logical';

最佳实践 ​

1. 监控与告警 ​

  • 设置合理的告警阈值：根据业务需求设置复制延迟告警阈值
  • 多维度监控：同时监控 write_lag、flush_lag 和 replay_lag
  • 监控 WAL 生成速率：了解主库 WAL 生成情况
  • 监控从库资源：监控从库 CPU、内存和磁盘 IO

2. 配置优化 ​

  • 合理设置同步复制级别：根据业务可用性要求选择同步级别
  • 优化 WAL 参数：调整 WAL 大小、检查点参数
  • 优化从库性能：确保从库硬件配置足够
  • 启用并行 WAL 回放：对于高并发场景，启用并行回放

3. 定期维护 ​

  • 定期检查复制状态：每周至少检查一次复制状态
  • 定期重建从库：对于长期运行的从库，定期重建以避免累积延迟
  • 测试故障切换：定期测试主从切换，确保复制正常
  • 监控复制槽：定期清理不再使用的复制槽

常见问题（FAQ） ​

Q1：如何快速判断复制延迟的原因？ ​

A1：可以通过以下步骤判断：

  1. 查看 pg_stat_replication 中的 write_lag、flush_lag 和 replay_lag
  2. 如果 write_lag 大，说明网络延迟高
  3. 如果 flush_lag 大，说明从库磁盘 IO 慢
  4. 如果 replay_lag 大，说明从库应用 WAL 慢
  5. 检查从库的 CPU、内存和磁盘 IO 情况

Q2：复制延迟会导致数据丢失吗？ ​

A2：这取决于复制模式：

  • 异步复制：可能会导致数据丢失，因为主库提交后不等从库确认
  • 同步复制：不会丢失数据，主库会等待从库确认后再提交
  • 半同步复制：只等待一个从库确认，提供一定的数据安全性

Q3：如何降低复制延迟？ ​

A3：降低复制延迟的方法：

  1. 优化网络连接，减少网络延迟
  2. 优化主库 WAL 生成，调整检查点参数
  3. 优化从库性能，确保硬件配置足够
  4. 启用并行 WAL 回放
  5. 合理设置同步复制级别

Q4：从库复制延迟越来越大怎么办？ ​

A4：处理方法：

  1. 检查主库 WAL 生成速率是否过高
  2. 检查从库资源使用情况
  3. 检查网络连接是否稳定
  4. 考虑重建从库
  5. 调整复制配置，如降低同步级别

Q5：逻辑复制延迟与物理复制延迟有什么区别？ ​

A5：主要区别：

  • 物理复制：基于 WAL 日志，延迟通常较低
  • 逻辑复制：基于逻辑变更，需要解析和应用变更，延迟通常较高
  • 物理复制：适用于高可用性场景
  • 逻辑复制：适用于数据同步和迁移场景

Q6：如何监控多个从库的复制延迟？ ​

A6：监控多个从库的方法：

  1. 使用 Prometheus + Grafana 集中监控
  2. 创建监控脚本，定期检查所有从库
  3. 使用 PostgreSQL 监控工具，如 pgAdmin、pgBadger
  4. 配置统一的告警机制，确保及时收到通知