I have a master and hot standby setup with PostgreSQL 9.3, and I'm attempting to monitor the state of replication on the standby using the check_postgres tool and the "hot_standby_delay" action. This seems to work by calculating the difference in bytes between the xlog position on the master and the standby.

In numerous online examples I have seen warning and critical thresholds for this in the < 1MB range. The exact command we are using in Nagios is:

/usr/local/bin/check_postgres.pl --action=hot_standby_delay --host=$HOSTNOTES$,$HOSTADDRESS$ --port=5432 --dbname=monitoring --dbuser=monitoring --dbpass=monitoring --warning=1000000 --critical=5000000

Which should set a warning at roughly 1MB and an outage at roughly 5MB. However, on our servers we routinely see it spike to a high level, like this:

[1417719713] SERVICE ALERT: host;PostgreSQL: Replication Delay;CRITICAL;SOFT;1;POSTGRES_HOT_STANDBY_DELAY CRITICAL: DB "monitoring" (host:host.example.com) 121175880
[1417719773] SERVICE ALERT: host;PostgreSQL: Replication Delay;CRITICAL;SOFT;2;POSTGRES_HOT_STANDBY_DELAY CRITICAL: DB "monitoring" (host:host.example.com) 132780968
[1417719833] SERVICE ALERT: host;PostgreSQL: Replication Delay;CRITICAL;SOFT;3;POSTGRES_HOT_STANDBY_DELAY CRITICAL: DB "monitoring" (host:host.example.com) 21412936

Followed up on the next Nagios check with:

[1417719893] SERVICE ALERT: host;PostgreSQL: Replication Delay;OK;SOFT;4;POSTGRES_HOT_STANDBY_DELAY OK: DB "monitoring" (host:host.example.com) 0

So in the general sense it seems that replication is working (and indeed, performing a data update on the master sees immediate results on the standby).

Unfortunately this scenario makes the monitoring useless since it is triggering a false positive many times a day. From what I've found between the documentation and other examples of using this, this result is not typical, and most people are able to set a threshold of 1MB or less and only see errors when there are in fact errors.

Does anybody have any idea of what I could try with the configuration to remedy this? On this particular install we have changed only a few parameters, and of those, only wal_keep_segments seems even remotely related (and we have that set to 128).

Both master and standby are hosted in EC2 in the same availability zone and there don't seem to be any communication delays between them. This is also a very low-traffic database so I am uncertain as to how the xlog delta could be that far off to begin with, unless I am missing some very critical fact.

  • I suspect (but this i just gut feeling) that there might be a problem with the monitoring setup rather than the replication. Flipping between 0 replication delay and a high number suggests that the script might occasionally be failing to grab the info it needs from one of the databases. I suggest (1) looking at Nagios logs to see if there's any indication of the script having problems talking to the databases (2) trying to verify independently of the script whether repliction is working or not. – Paul Haldane Dec 4 '14 at 23:11

A check that returns SOFT CRITICAL does not trigger notifications, as it has not reached the max_check_attempts threshold. This is not a false positive; it is Nagios working as designed. This is quite normal (for many services, not just for your case). It's exactly why max_check_attempts exists.

In your case, it's returning to normal within 3 minutes of the initial non-OK check result. For some services, that amount of time out-of-sync is acceptable, but it might not be for your use case. I don't know enough about Postgres replication to say definitively if it's indicative of an underlying problem or not.

  • That makes sense -- I tried upping the max_check_attempts but I am still wondering if this is indicative of a problem with the replication or not. With a value of 8-10 it seems to be stable, but the logs indicate that the byte offset difference still spikes heavily. That definitely seems to be a PostgreSQL-centric issue. Perhaps for now I'll have to live with it... – futureal Dec 6 '14 at 9:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.