On Sat, Oct 10, 2020 at 3:41 AM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
On 2020-10-05 11:29:04 -0600, Michael Lewis wrote: > > I suggest that in PG12 you can monitor the > "lag" of a standby server more directly by looking at columns > write_lag, > flush_lag, replay_lag in the pg_stat_replication view. > > > And are those things updated when there are no changes to the master > database?
Probably not, as there is nothing to replicate, so no new data it provided.
> If you setup a scripted process to update a single row with a timestamptz on > the source/primary every minute, then you have a very simple consistent change > and also a way to check on the replica what is current time vs > last_scripted_update_time if you will and know the approx lag. It would seem > like a simple albeit hacky solution to you wanting a file every X minutes > regardless of server activity.
It also has the advantage that you don't have to wait for the WAL file to be written. You can just check whether the change appears on the replicas. About 2 years ago I wrote a Nagios/Icinga check that does that: Update a timestamp in a table on the master, then connect to all the replicas and wait for the change to show up on them. It then reports the lag for each replica and a final status (OK, WARNING, CRITICAL) based on the maximal lag.
I think I wrote it because the PostgreSQL version we were using at the time didn't have the lag columns yet, but it does have the advantage of providing an end to end check (do I really get the correct value?), not the database's idea of whether replication is working.
(The check is written in Go and buried in a svn repo at work, but I could publish it if there is interest)
hp
-- _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!"
I would be interested in the Nagios/Icinga check you wrote.
