Postgres | Query to identify the lag among the Primary and Replica

  select client_addr AS client, usename AS user, application_name AS name,
  state, sync_state AS mode,
  (pg_xlog_location_diff(pg_current_xlog_location(),sent_location) / 1024)::bigint as pending,
  (pg_xlog_location_diff(sent_location,write_location) / 1024)::bigint as write,
  (pg_xlog_location_diff(write_location,flush_location) / 1024)::bigint as flush,
  (pg_xlog_location_diff(flush_location,replay_location) / 1024)::bigint as replay,
  (pg_xlog_location_diff(pg_current_xlog_location(),replay_location))::bigint / 1024 as total_lag
FROM pg_stat_replication;




Here has been some renames for postgresql 10 and above one can use this:


```
SELECT
client_addr AS client, usename AS user, application_name AS name,
state, sync_state AS mode,
(pg_wal_lsn_diff(pg_current_wal_lsn(),sent_lsn) / 1024)::bigint as pending,
(pg_wal_lsn_diff(sent_lsn,sent_lsn) / 1024)::bigint as write,
(pg_wal_lsn_diff(sent_lsn,flush_lsn) / 1024)::bigint as flush,
(pg_wal_lsn_diff(flush_lsn,replay_lsn) / 1024)::bigint as replay,
(pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn))::bigint / 1024 as total_lag
FROM pg_stat_replication;