PostgreSQL Database Replication Monitoring

PostgreSQL Database Replication Monitoring

On Standby : To check whether standby is in recovery or not

select pg_is_in_recovery();

On Master: 

1) Select * from pg_stat_replication;

Query to find current wal lsn:

Select  *  from pg_current_wal_lsn();

On Standby :

Query to check what was the last wal_lsn received:

Select  *  from pg_last_wal_receive_lsn();

Query to find the difference between lsn:

select pg_wal_lsn_diff('0/23000073','0/3342343');

To find the difference in MB:

Select round(67213032/pow(1024,2.0) ,2) missing_in_mb;

On Master:

Query to find the physical name of wal file:

select pg_walfile_name('0/23000073');

Other useful queries:

Query to monitor incoming replication:

select * from pg_stat_wal_receiver;

Query to find last received lsn:

select pg_last_wal_receive_lsn();

Query to find last replayed lsn:

select pg_last_wal_replay_lsn();

Query to find replication slots:

select * from pg_replication_slots;

Query to find replay lag, receiving lag:

select pid,application_name,pg_wal_lsn_diff(pg_current_wal_lsn(),sent_lsn) sending_lag,pg_wal_lsn_diff(sent_lsn,flush_lsn) receiving_lag,pg_wal_lsn_diff(flush_lsn,replay_lsn) replaying_lag,pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) total_lag from pg_stat_replication;