Postgresql operation to check the progress of database master-slave replication

How to view the status of master-slave replication and how many bytes are lagging behind the backup library application

This information must be queried in the main library

You can use the view on the main library to view stream copy information

select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;

Several words in pg_stat_replication record the location of the sending wal and the location of the wal received by the backup library.

sent_location--the location of sending wal

write_location--The location of wal received by the backup library

flush_location—Wal log to disk location where the library writes wal log to disk

replay_location—location of library application log

Check how many bytes of the backup library fall behind the main library

select pg_xlog_location_diff(pg_current_xlog_location(),replay_location)/1024/1024 as MB from pg_stat_replication;
select pg_xlog_location_diff(pg_current_xlog_location(),replay_location)/1024/1024/1024 as GB from pg_stat_replication;

Cascade copy

select pg_xlog_location_diff(pg_last_xlog_replay_location(),replay_location)/1024/1024/1024 as GB from pg_stat_replication;

Supplement: pgsql view main and backup replication delay

View replication delay:

10.0 and above:

SELECT 
  pg_wal_lsn_diff(A .c1, replay_lsn) /(1024 * 1024) AS slave_latency_MB,
  pg_wal_lsn_diff(A .c1, sent_lsn) /(1024 * 1024) AS send_latency_MB,
  pg_wal_lsn_diff(A .c1, flush_lsn) /(1024 * 1024) AS flush_latency_MB,
  state,
  backend_start,
  now()::timestamp with time zone
 FROM pg_stat_replication, pg_current_wal_lsn() AS A(c1)
 WHERE client_addr='192.168.46.173' and application_name = 'standby1'
 ORDER BY slave_latency_MB, send_latency_MB DESC
 LIMIT 1;

Note:

192.168.46.173 represents the IP address from the library.

pg_wal_lsn_diff(lsn pg_lsn, lsn pg_lsn): Calculate the difference between the locations of two pre-write logs.

pg_current_wal_lsn(): Get the current write-pre-log write location

Less than version 10.0:

SELECT 
  pg_xlog_location_diff(A .c1, replay_lsn) /(1024 * 1024) AS slave_latency_MB,
  pg_xlog_location_diff(A .c1, sent_lsn) /(1024 * 1024) AS send_latency_MB,
  pg_xlog_location_diff(A .c1, flush_lsn) /(1024 * 1024) AS flush_latency_MB,
  state,
  backend_start,
  now()::timestamp with time zone
 FROM pg_stat_replication, pg_current_xlog_location AS A(c1)
 WHERE client_addr='192.168.46.173' and application_name = 'standby1'
 ORDER BY slave_latency_MB, send_latency_MB DESC
 LIMIT 1;

Note:

192.168.46.173 represents the IP address from the library.

pg_xlog_location_diff(lsn pg_lsn, lsn pg_lsn): Calculate the difference between the locations of two pre-write logs.

pg_current_xlog_location (): Obtain the current write-pre-logged log writing location

The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.