postgresql 10.x stream status check

有些名称发生了变化，xlog =>wal，location => lsn

pg_xlog_location_diff            pg_wal_lsn_diff
pg_current_xlog_location         pg_current_wal_lsn
pg_current_xlog_insert_location  pg_current_wal_insert_lsn

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 17836
usesysid         | 16674
usename          | replicator
application_name | walreceiver
client_addr      | 192.168.56.101
client_hostname  | 
client_port      | 12955
backend_start    | 2018-03-01 17:03:29.129844+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/4CCFB4B8
write_lsn        | 0/4CCFB4B8
flush_lsn        | 0/4CCFB4B8
replay_lsn       | 0/4CCFB4B8
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async

主库查询流复制落后字节数，主要看replay_delay数！
在主库 postgres超级用户连接到postgres库。
pg_current_wal_insert_lsn() 写入 wal buffer 的位置
pg_current_wal_lsn() 写入 wal 文件的位置

select client_addr, 
       pg_wal_lsn_diff(pg_current_wal_insert_lsn(), pg_current_wal_lsn() ) as local_noflush_delay,
       pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) as local_sent_delay,
       pg_wal_lsn_diff(sent_lsn, write_lsn) as stream_write_delay,
       pg_wal_lsn_diff(sent_lsn, flush_lsn) as stream_flush_delay,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) as stream_replay_delay 
from pg_stat_replication
;

执行如下

postgres=# select client_addr, 
postgres-#        pg_wal_lsn_diff(pg_current_wal_insert_lsn(), pg_current_wal_lsn() ) as local_noflush_delay,
postgres-#        pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) as local_sent_delay,
postgres-#        pg_wal_lsn_diff(sent_lsn, write_lsn) as stream_write_delay,
postgres-#        pg_wal_lsn_diff(sent_lsn, flush_lsn) as stream_flush_delay,
postgres-#        pg_wal_lsn_diff(sent_lsn, replay_lsn) as stream_replay_delay 
postgres-# from pg_stat_replication;
-[ RECORD 1 ]-------+------------
client_addr         | 192.168.56.101
local_noflush_delay | 0
local_sent_delay    | 0
stream_write_delay  | 0
stream_flush_delay  | 0
stream_replay_delay | 0

参考
https://www.postgresql.org/docs/10/static/functions-admin.html