PostgreSQL WAL 与复制指标

PostgreSQL WAL（Write-Ahead Log）与复制指标是数据库高可用性和灾难恢复的重要监控对象。WAL 是 PostgreSQL 的事务日志，记录了所有数据库修改操作；复制指标则反映了主从复制的状态和性能。

WAL 与复制指标类型

1. WAL 相关指标

WAL 生成速率：每秒生成的 WAL 日志量
WAL 写入速率：WAL 日志写入磁盘的速率
WAL 归档状态：WAL 归档的成功/失败状态
WAL 归档延迟：WAL 生成到归档完成的延迟时间
WAL 段文件数量：当前存在的 WAL 段文件数量
检查点频率：检查点执行的频率
检查点耗时：每次检查点的执行时间

2. 复制相关指标

复制延迟：备库与主库之间的延迟时间
复制状态：复制连接的状态（如 streaming、catchup 等）
WAL 发送速率：主库发送 WAL 到备库的速率
WAL 接收速率：备库接收 WAL 的速率
WAL 应用速率：备库应用 WAL 的速率
复制槽状态：复制槽的使用情况和状态
WAL 积压量：备库未应用的 WAL 日志量
复制节点数量：当前活跃的复制节点数量

指标收集方法

1. 内置视图查询

PostgreSQL 提供了丰富的内置视图，可以直接查询 WAL 与复制指标。

sql

-- 1. 查询 WAL 生成速率
SELECT 
  wal_lsn_diff(pg_current_wal_lsn(), pg_walfile_name_offset(pg_current_wal_lsn())).offset / 1024 / 1024 AS current_wal_size_mb,
  pg_wal_lsn_diff(pg_current_wal_lsn(), pg_stat_get_wal_stats(true).wal_records) / 1024 / 1024 AS wal_generated_mb,
  pg_stat_get_wal_stats(true).wal_records AS wal_records,
  pg_stat_get_wal_stats(true).wal_fpi AS wal_full_page_writes
FROM pg_stat_wal;

-- 2. 查询复制状态和延迟
SELECT 
  application_name,
  client_addr,
  state,
  sync_state,
  pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS send_lag_bytes,
  pg_wal_lsn_diff(sent_lsn, write_lsn) AS write_lag_bytes,
  pg_wal_lsn_diff(write_lsn, flush_lsn) AS flush_lag_bytes,
  pg_wal_lsn_diff(flush_lsn, replay_lsn) AS replay_lag_bytes,
  extract(epoch FROM (now() - replay_lag)) AS replay_lag_seconds
FROM pg_stat_replication;

-- 3. 查询复制槽状态
SELECT 
  slot_name,
  plugin,
  slot_type,
  active,
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS wal_backlog_bytes,
  extract(epoch FROM (now() - last_active)) AS idle_seconds
FROM pg_replication_slots;

-- 4. 查询检查点信息
SELECT 
  checkpoint_timeout,
  checkpoint_completion_target,
  checkpoint_warning,
  pg_stat_get_checkpoint_stats().checkpoints_timed AS checkpoints_timed,
  pg_stat_get_checkpoint_stats().checkpoints_req AS checkpoints_requested,
  pg_stat_get_checkpoint_stats().checkpoint_write_time / 1000 AS checkpoint_write_seconds,
  pg_stat_get_checkpoint_stats().checkpoint_sync_time / 1000 AS checkpoint_sync_seconds
FROM pg_settings
WHERE name IN ('checkpoint_timeout', 'checkpoint_completion_target', 'checkpoint_warning');

2. 系统命令

使用系统命令收集 WAL 相关指标。

bash

# 1. 查看 WAL 文件数量
ls -la /var/lib/pgsql/14/data/pg_wal/ | grep -v "^total" | wc -l

# 2. 查看 WAL 文件大小
du -sh /var/lib/pgsql/14/data/pg_wal/

# 3. 查看 WAL 归档状态
tail -f /var/lib/pgsql/14/data/pg_log/postgresql-*.log | grep -i archive

# 4. 查看备库复制状态
psql -h standby_host -U postgres -c "SELECT * FROM pg_stat_wal_receiver;"

3. 监控工具

使用专业监控工具收集和分析 WAL 与复制指标。

Prometheus + PostgreSQL Exporter

yaml

# 1. PostgreSQL Exporter 配置
# 确保已启用 WAL 和复制指标收集

# 2. Prometheus 配置
scrape_configs:
  - job_name: 'postgres-primary'
    static_configs:
      - targets: ['primary_host:9187']
    scrape_interval: 15s
  - job_name: 'postgres-standby'
    static_configs:
      - targets: ['standby_host:9187']
    scrape_interval: 15s

pgAdmin

pgAdmin 提供了直观的 WAL 和复制监控界面，可以查看：

主库的 WAL 生成情况
备库的复制状态和延迟
复制槽的使用情况

关键指标监控

1. WAL 生成速率监控

sql

-- 1. 查看当前 WAL 生成速率
SELECT 
  pg_wal_lsn_diff(pg_current_wal_lsn(), 
                 pg_current_wal_lsn() - '1GB'::pg_lsn) / 1024 / 1024 AS wal_size_gb,
  extract(epoch FROM (now() - pg_postmaster_start_time())) AS uptime_seconds,
  (pg_wal_lsn_diff(pg_current_wal_lsn(), 
                  pg_current_wal_lsn() - '1GB'::pg_lsn) / 1024 / 1024) / 
  extract(epoch FROM (now() - pg_postmaster_start_time())) * 3600 AS wal_generation_rate_gb_per_hour
FROM pg_postmaster_start_time();

-- 2. 监控 WAL 生成速率的告警设置
-- 当 WAL 生成速率超过 10GB/小时时触发告警
-- Prometheus 告警规则示例
ALERT HighWALGenerationRate
  IF rate(pg_stat_wal_wal_bytes_generated_total[5m]) > 10 * 1024 * 1024 * 1024 / 3600
  FOR 5m
  LABELS { severity="warning" }
  ANNOTATIONS {
    summary = "High WAL generation rate on {{ $labels.instance }}",
    description = "WAL generation rate is {{ $value }} bytes/second for 5 minutes"
  }

2. 复制延迟监控

sql

-- 1. 查看备库复制延迟
SELECT 
  application_name,
  client_addr,
  state,
  sync_state,
  extract(epoch FROM (now() - replay_lag)) AS replay_lag_seconds
FROM pg_stat_replication;

-- 2. 监控复制延迟的告警设置
-- 当复制延迟超过 30 秒时触发告警
-- Prometheus 告警规则示例
ALERT HighReplicationLag
  IF pg_stat_replication_replay_lag_seconds > 30
  FOR 5m
  LABELS { severity="warning" }
  ANNOTATIONS {
    summary = "High replication lag on {{ $labels.instance }}",
    description = "Replication lag is {{ $value }} seconds for 5 minutes"
  }

3. 复制槽监控

sql

-- 1. 查看复制槽 WAL 积压
SELECT 
  slot_name,
  active,
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) / 1024 / 1024 AS wal_backlog_mb
FROM pg_replication_slots;

-- 2. 监控复制槽 WAL 积压的告警设置
-- 当复制槽 WAL 积压超过 10GB 时触发告警
-- Prometheus 告警规则示例
ALERT HighWALBacklog
  IF pg_replication_slots_wal_backlog_bytes > 10 * 1024 * 1024 * 1024
  FOR 5m
  LABELS { severity="critical" }
  ANNOTATIONS {
    summary = "High WAL backlog on {{ $labels.instance }} {{ $labels.slot_name }}",
    description = "WAL backlog is {{ $value }} bytes for 5 minutes"
  }

4. WAL 归档监控

sql

-- 1. 查看 WAL 归档配置
SHOW archive_mode;
SHOW archive_command;
SHOW archive_timeout;

-- 2. 监控 WAL 归档失败的告警设置
-- 当检测到 WAL 归档失败时触发告警
-- Prometheus 告警规则示例
ALERT WALArchiveFailure
  IF increase(pg_stat_database_xact_rollback_total[5m]) > 0
  AND pg_settings_archive_mode = 1
  FOR 5m
  LABELS { severity="critical" }
  ANNOTATIONS {
    summary = "WAL archive failure on {{ $labels.instance }}",
    description = "WAL archive has failed for 5 minutes"
  }

最佳实践

1. 生产环境监控建议

监控关键指标：重点监控 WAL 生成速率、复制延迟、WAL 归档状态和复制槽状态
设置合理的告警阈值：根据业务需求和 RTO/RPO 目标设置告警阈值
配置多级告警：根据问题严重程度设置不同级别的告警
监控备库状态：定期检查备库的复制状态和延迟
备份复制槽：定期备份复制槽信息，防止复制槽丢失

2. 性能优化建议

sql

-- 1. WAL 优化
-- 调整 WAL 缓冲区大小
ALTER SYSTEM SET wal_buffers = '16MB';

-- 调整检查点配置
ALTER SYSTEM SET checkpoint_timeout = '15min';
ALTER SYSTEM SET checkpoint_completion_target = '0.9';
ALTER SYSTEM SET max_wal_size = '10GB';
ALTER SYSTEM SET min_wal_size = '2GB';

-- 2. 复制优化
-- 调整 WAL 发送者数量
ALTER SYSTEM SET max_wal_senders = '10';

-- 调整复制槽数量
ALTER SYSTEM SET max_replication_slots = '5';

-- 启用并行复制
ALTER SYSTEM SET max_worker_processes = '16';
ALTER SYSTEM SET max_parallel_workers = '8';

-- 3. 备库优化
-- 调整备库 WAL 应用进程数
ALTER SYSTEM SET max_worker_processes = '16';
ALTER SYSTEM SET max_parallel_workers = '8';

3. 高可用性建议

sql

-- 1. 配置同步复制
-- 在主库上配置同步复制
ALTER SYSTEM SET synchronous_commit = 'on';
ALTER SYSTEM SET synchronous_standby_names = 'standby1, standby2';

-- 2. 配置复制槽
-- 在主库上创建物理复制槽
SELECT * FROM pg_create_physical_replication_slot('standby_slot');

-- 在备库上使用复制槽连接主库
-- 修改 standby.signal 文件或 recovery.conf 文件
-- primary_slot_name = 'standby_slot'

-- 3. 配置 WAL 归档
ALTER SYSTEM SET archive_mode = 'on';
ALTER SYSTEM SET archive_command = 'cp %p /archive/%f';
ALTER SYSTEM SET archive_timeout = '60';

常见问题处理

1. WAL 生成速率过高

问题：主库 WAL 生成速率过高，导致磁盘 I/O 压力大

解决方法：

分析导致 WAL 生成速率过高的原因，如批量数据导入、大量更新操作等
调整检查点配置，减少检查点频率
考虑使用更高效的存储设备，如 SSD
优化应用程序，减少不必要的数据库修改操作

2. 复制延迟过大

问题：备库与主库之间的复制延迟过大

解决方法：

检查网络连接，确保主备库之间网络稳定
优化备库配置，增加 WAL 应用进程数
调整主库 WAL 发送配置，增加 max_wal_senders
考虑使用更快的存储设备，如 SSD
检查备库是否有长事务或其他性能问题

3. WAL 归档失败

问题：WAL 归档失败，导致 WAL 段文件堆积

解决方法：

检查归档目录权限，确保 PostgreSQL 用户有写入权限
检查归档命令是否正确
检查归档目标是否有足够的磁盘空间
查看数据库日志，获取详细的归档失败原因

4. 复制槽 WAL 积压

问题：复制槽积压了大量未处理的 WAL 日志

解决方法：

检查对应的复制节点是否正常运行
如果复制节点已不再使用，删除对应的复制槽
调整复制节点的配置，提高 WAL 应用速率
考虑使用物理复制代替逻辑复制，减少 WAL 处理开销

常见问题（FAQ）

Q1：如何计算复制延迟？

A1：复制延迟可以通过以下方式计算：

基于 LSN：使用 pg_wal_lsn_diff() 函数计算主库与备库之间的 LSN 差异
基于时间：使用备库的 replay_lag 字段获取时间延迟
基于 WAL 文件：比较主备库的 WAL 文件名差异

Q2：如何监控多个备库的复制状态？

A2：可以使用以下方法：

在主库上查询 pg_stat_replication 视图，查看所有连接的备库状态
使用监控工具如 Prometheus + Grafana，集中监控所有备库的复制状态
配置告警规则，当任何备库复制延迟超过阈值时触发告警

Q3：WAL 归档和复制槽有什么区别？

A3：主要区别如下：

WAL 归档：将 WAL 日志复制到远程存储，用于点-in-time 恢复
复制槽：确保主库保留备库所需的所有 WAL 日志，防止备库因 WAL 被回收而无法继续复制
使用场景：WAL 归档用于灾难恢复，复制槽用于确保复制连续性

Q4：如何优化 WAL 生成速率？

A4：可以采取以下措施：

优化应用程序，减少不必要的数据库修改操作
调整检查点配置，减少检查点频率
使用批量操作代替单条操作
考虑使用非登录表（UNLOGGED TABLE）存储临时数据
调整 wal_compression 参数，启用 WAL 压缩

Q5：如何确保复制的可靠性？

A5：可以采取以下措施：

使用同步复制或半同步复制
配置复制槽，防止 WAL 被过早回收
监控复制延迟和状态
定期测试备库的可用性
配置自动故障转移机制

Q6：复制延迟对业务有什么影响？

A6：复制延迟的影响取决于业务需求：

读负载分流：复制延迟会导致备库数据不新鲜，影响读负载分流效果
灾难恢复：复制延迟直接影响 RPO（恢复点目标）
高可用性：复制延迟会影响故障转移的时间和数据完整性

Q7：如何处理复制冲突？

A7：复制冲突主要发生在逻辑复制中，可以采取以下措施：

确保订阅者端没有写入操作
使用 ON CONFLICT 子句处理冲突
调整订阅者的应用顺序
考虑使用不同的冲突解决策略

Q8：如何监控 WAL 归档的性能？

A8：可以监控以下指标：

WAL 归档延迟：从 WAL 生成到归档完成的时间
WAL 归档成功率：成功归档的 WAL 段比例
归档存储使用率：归档目标的磁盘使用率
归档操作的 CPU 和 I/O 消耗

PostgreSQL WAL 与复制指标 ​

WAL 与复制指标类型 ​

1. WAL 相关指标 ​

2. 复制相关指标 ​

指标收集方法 ​

1. 内置视图查询 ​

2. 系统命令 ​

3. 监控工具 ​

Prometheus + PostgreSQL Exporter ​

pgAdmin ​

关键指标监控 ​

1. WAL 生成速率监控 ​

2. 复制延迟监控 ​

3. 复制槽监控 ​

4. WAL 归档监控 ​

最佳实践 ​

1. 生产环境监控建议 ​

2. 性能优化建议 ​

3. 高可用性建议 ​

常见问题处理 ​

1. WAL 生成速率过高 ​

2. 复制延迟过大 ​

3. WAL 归档失败 ​

4. 复制槽 WAL 积压 ​

常见问题（FAQ） ​

Q1：如何计算复制延迟？ ​

Q2：如何监控多个备库的复制状态？ ​

Q3：WAL 归档和复制槽有什么区别？ ​

Q4：如何优化 WAL 生成速率？ ​

Q5：如何确保复制的可靠性？ ​

Q6：复制延迟对业务有什么影响？ ​

Q7：如何处理复制冲突？ ​

Q8：如何监控 WAL 归档的性能？ ​

PostgreSQL WAL 与复制指标

WAL 与复制指标类型

1. WAL 相关指标

2. 复制相关指标

指标收集方法

1. 内置视图查询

2. 系统命令

3. 监控工具

Prometheus + PostgreSQL Exporter

pgAdmin

关键指标监控

1. WAL 生成速率监控

2. 复制延迟监控

3. 复制槽监控

4. WAL 归档监控

最佳实践

1. 生产环境监控建议

2. 性能优化建议

3. 高可用性建议

常见问题处理

1. WAL 生成速率过高

2. 复制延迟过大

3. WAL 归档失败

4. 复制槽 WAL 积压

常见问题（FAQ）

Q1：如何计算复制延迟？

Q2：如何监控多个备库的复制状态？

Q3：WAL 归档和复制槽有什么区别？

Q4：如何优化 WAL 生成速率？

Q5：如何确保复制的可靠性？

Q6：复制延迟对业务有什么影响？

Q7：如何处理复制冲突？

Q8：如何监控 WAL 归档的性能？