We are currently using the following script on our PostgreSQL 10 standby server to monitor streaming replication lag:
replication_lag=$(psql -U gateway postgres -tAc " SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp()) END; ")
This script appears to measure only the replay lag on the standby. However, I am concerned that it may not detect certain replication failure scenarios and could incorrectly report a healthy status.
Specifically, I would like to know how to monitor the following situations:
1. The standby is disconnected from the primary.
2. The WAL receiver process has stopped.
3. The standby cannot continue replication because the required WAL files have already been removed from the primary (for example, requested WAL segment ... has already been removed).
Could you suggest the best approach or a more comprehensive monitoring script for PostgreSQL 10 streaming replication that covers not only replication lag but also these failure scenarios? If some of these conditions cannot be detected using SQL alone.
