PostgreSQL – Metrics for Monitoring Master-Slave Replication

master-slave-replicationmonitoringpostgresqlpostgresql-9.4replication

Is there some kind of query/view/table available in PostgreSQL that can reflect the actual status of a master-slave replication process? Perhaps the "degree" of sync between the nodes?

The above is to periodically monitor our servers to ensure they are actually "in sync" and the slave hasn't gone adrift.

Best Answer

In the primary side, you have to use the view pg_stat_replication and compare differents fields to see the drift. The function pg_xlog_location_diff give the drift in size : 

select   pid, client_addr, state, sync_state,  
         pg_xlog_location_diff(sent_location, write_location) as write_lag,  
         pg_xlog_location_diff(sent_location, flush_location) as flush_lag,  
         pg_xlog_location_diff(sent_location, replay_location) as replay_lag
from pg_stat_replication ;

You can use the lag in monitoring tools, as gauge, to monitor your replication.

If a standby is missing, you have to check in the log's standby why.

If you use replication slots, you have to check the pg_replication_slots view.

Related Question