PostgreSQL master-slave diff view

1. Size difference

Executing on the main library

Select Application_name, Pg_size_pretty (Pg_xlog_location_diff (Pg_current_xlog_location (), replay_location)) As Difffrom pg_stat_replication;

Or:

selectapplication_name,client_addr,cur_xlog | |   '/'  | |  cur_offset as cur_xlog,  sent_xlog | |   '/'  | |  sent_offset as sent_xlog,          replay_ xlog | |   '/'  | |  replay_offset as replay_xlog,pg_size_pretty (  ((cur_xlog * 255 * 16  ^ 6)  + cur_offset)  -  ((sent_xlog * 255 * 16 ^ 6)  + sent_offset)  ):: Numeric)  as master_lag,          pg_size_pretty ((  (sent_xlog * 255 * 16 ^ 6)  + sent_ Offset)  -  ((replay_xlog * 255 * 16 ^ 6)  + replay_offset)  ):: Numeric)  as slave_lag,                                                                     pg_size_pretty (  ((cur_xlog * 255 *  16 ^ 6) ( + cur_offset)  -  (replay_xlog * 255 * 16  ^ 6)  + replay_offset)  ):: Numeric)  as total_lagfrom  (Selectapplication_name, Client_addr, (' X '  | |  lpad (Split_part (Sent_location::text, '/',  1), 8,  ' 0 ')):: Bit (+):: Bigint as sent_ Xlog, (' X '  | |  lpad (Split_part (replay_location::text,  '/',  1), 8,  ' 0 ')):: Bit (+):: Bigint as  replay_xlog, (' X '  | |  lpad (Split_part (sent_location::text,  '/',  2), 8,  ' 0 ')):: Bit (+):: Bigint as  sent_offset, (' X '  | |  lpad (Split_part (replay_location::text,  '/',  2), 8,  ' 0 '):: Bit (+):: Bigint as replay_offset, (' X '  | |  lpad (Split_part (pg_current_xlog_location ()::text,  '/',  1), 8,  ' 0 '):: Bit (+):: bigint  as cur_xlog, (' X '  | |  lpad (Split_part (pg_current_xlog_location ()::text,  '/',  2), 8,  ' 0 '):: Bit (+):: bigint  as cur_offsetfrompg_stat_replication)  as s;

2. Time difference

On the run from the library:

Select Now ()-Pg_last_xact_replay_timestamp () as Replication_delay;

PostgreSQL master-slave diff view