Error alarm of Postgres master-slave replication delay monitoring

​ In the use of Prometheus Monitored Postgres Database time , An alarm will be generated due to the delay of master-slave replication , But in fact, the problem of normal database , What we use exporter by https://github.com/prometheus-community/postgres_exporter, The alarm expression is ：

pg_replication_lag > 300

The description of this indicator is as follows ：

# HELP pg_replication_lag Replication lag behind master in seconds
# TYPE pg_replication_lag gauge
pg_replication_lag{server=""}

But actually , This indicator indicates how long there has been no replication between master and slave , from https://github.com/prometheus-community/postgres_exporter/blob/master/queries.yaml We can find out the use of this indicator SQL by ：

SELECT
CASE 
  WHEN NOT pg_is_in_recovery() THEN 0 
  ELSE GREATEST(0, EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_TIMESTAMP()))) 
END 
AS LAG

The official documents describe the two functions as follows ：

this SQL As the result of the ： The main warehouse is 0, From the library is the difference between the current time and the last recovery transaction time .

So a special case is , If there is no transaction commit in the main database , that pg_last_xact_replay_TIMESTAMP() The value of remains unchanged , Corresponding pg_replication_lag The value will continue to increase , However, it does not mean that the master-slave replication fails .

therefore , If you want to avoid false alarms , We can create a test table in the main library , Update the data in the table every minute , Keep the database active , In this way, if an alarm occurs, it really indicates that there is a serious fault in the data . The specific methods are as follows ：

psql (11.7)
Type "help" for help.

postgres=# CREATE DATABASE test;
postgres=# \c test
postgres=# CREATE TABLE test(id INT PRIMARY KEY NOT NULL DEFAULT 1, time varchar(255));
postgres=# INSERT INTO "public"."test"("time") VALUES ('123');

Configure scheduled tasks ：

# crontab -e
* * * * * time=`date`;/usr/pgsql-11/bin/psql -h localhost -p 18083 -d test -c "UPDATE public.test SET time = '${time}'"