Add alerting on postgresql replication
There is no feedback on the postgresql replication status.
swh@gloin001:~$ sudo crm status
Status of pacemakerd: 'Pacemaker is running' (last updated 2024-03-04 10:55:59 +01:00)
Cluster Summary:
* Stack: corosync
* Current DC: gloin001 (version 2.1.5-a3f44794f94) - MIXED-VERSION partition with quorum
* Last updated: Mon Mar 4 10:55:59 2024
* Last change: Mon Mar 4 04:04:27 2024 by root via crm_attribute on gloin001
* 2 nodes configured
* 4 resource instances configured
Node List:
* Online: [ gloin001 gloin002 ]
Full List of Resources:
* r_vip_pub (ocf:heartbeat:IPaddr2): Started gloin001
* r_vip_ha (ocf:heartbeat:IPaddr2): Started gloin001
* Clone Set: ha_postgresql [r_postgresql] (promotable):
* Promoted: [ gloin001 ]
* Unpromoted: [ gloin002 ]
Failed Resource Actions:
* r_postgresql probe on gloin002 returned 'error' (Instance "r_postgresql" controldata indicates a running secondary instance, the instance has probably crashed) at Tue Feb 27 04:35:42 2024 after 168ms
We must be notified in case of a replication issue.
postgres=# select * from pg_stat_wal_receiver;
pid | status | receive_start_lsn | receive_start_tli | written_lsn | flushed_lsn | received_tli | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_time | slot_name | sender_host | sender_port | conninfo
-----+--------+-------------------+-------------------+-------------+-------------+--------------+--------------------+-----------------------+----------------+-----------------+-----------+-------------+-------------+----------
(0 rows)
postgres=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+------------
(0 ligne)