posgresql 监控主备延迟_postgresql主从延迟监控指标有哪些

本文主要摘自PostgresSQL实战一书，便于以后查询

      同步流复制和异步流复制主备库之间的延迟是客观存在的，事实上当流复制主库、备库机器负载较低的情况下主备延迟通常能在毫秒级，数据库越繁忙或数据库主机负载越高主备延迟越大，有两个维度衡量主备库之间的延迟：通过WAL延迟时间衡量，通过WAL日志应用延迟量衡量，下面详细介绍。

方式一：通过WAL延迟时间衡量

       WAL的延迟分为write延时、flush延时、replay延时，分别对应pg_stat_replication的write_lag、flush_lag、replay_lag字段，上一节已经详细解释了这三个字段，通过备库WAL日志接收延时和应用延时判断主备延时，在流复制主库上执行如下
SQL：

postgres=# SELECT pid,usename,client_addr,state,write_lag,flush_lag,replay_lag FROM pg_stat_replication ;
-[ RECORD 1 ]---------------
pid         | 25659
usename     | replica
client_addr | 192.168.40.131
state       | streaming
write_lag   | 
flush_lag   | 
replay_lag  | 
      对于一个有稳定写事务的数据库，备库收到主库发送的WAL日志流后首先是写入备库主机操作系统缓存，之后写入备库WAL日志文件，最后备库根据WAL日志文件应用日志，因此这种场景下write_lag、flush_lag和replay_lag大小关系如下
所示：
replay_lag > flush_lag > write_lag

10版本前pg_stat_replication视图不提供这三个字段，但是也有办法监控主备延时，在流复制备库执行以下SQL，如下
所示：

postgres=# SELECT EXTRACT(SECOND FROM now()- pg_last_xact_replay_timestamp());
-[ RECORD 1 ]--------
date_part | 57.229771

postgres=# 

       pg_last_xact_replay_timestamp函数显示备库最近WAL日志应用时间，通过与当前时间比较可粗略计算主备库延时，这种方式的优点是即使主库宕掉，也可以大概判断主备延时。缺点是如果主库上只有读操作，主库不会发送WAL日志流到备库，pg_last_xact_replay_timestamp函数返回的结果就是一个静态的时间，这个公式的判断结果就不严谨了。

方式二：通过WAL日志应用延迟量衡量

通过流复制备库WAL的应用位置和主库本地WAL写入位置之间的WAL日志量能够准确判断主备延时，在流复制主库
执行以下SQL：

SELECT pid,usename,client_addr,state,
pg_wal_lsn_diff(pg_current_wal_lsn(),write_lsn) write_delay,
pg_wal_lsn_diff(pg_current_wal_lsn(),flush_lsn) flush_delay,
pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) replay_dely
FROM pg_stat_replication ;

-[ RECORD 1 ]---------------
pid         | 25659
usename     | replica
client_addr | 192.168.40.131
state       | streaming
write_delay | 0
flush_delay | 0
replay_dely | 0

pg_current_wal_lsn函数显示流复制主库当前WAL日志文件写入的位置，pg_wal_lsn_diff函数计算两个WAL日志位置之间的偏移量，返回单位为字节数，这种方式有个缺点，当主库宕掉时，此方法行不通

方式三：通过创建主备延时测算表方式

      这种方法在主库上创建一张主备延时测算表，并定时往表插入数据或更新数据，之后在备库上计算这条记录的插入时间或更新时间与当前时间的差异来判断主备延时，这种方法不是很严谨，但很实用，当主库宕机时，这种方式依然可以大概判断出主备延时