Hi All,
I just wanted to check with you guys to make sure that constantly issuing "NOTIFY" commands without corresponding "LISTEN" commands will not cause any harm or excessive work for the PostgreSQL cluster. Nothing that would put my cluster at risk.
The reason I ask is because I was trying to implement a reliable method of monitoring replication lag for streaming replication on 9.2+ systems using the following SQL on slaves:
select extract(epoch from now() - pg_last_xact_replay_timestamp());
This SQL provides me with a time-based measure of replication lag instead of a byte-based measure. Time-based lag measurement is more meaningful for us in time-sensitive applications.
During my testing I noticed that if the database went "quiet" (no update activity on the master) for a period of time, then the last replay timestamp remained unchanged. Having little or no update activity after-hours is very common on our smaller systems.
This made the monitoring of replication lag inconsistent because, despite the slave being "caught up" with the master, it was reporting an increasing time lag. And I didn't want our DBAs to get false alerts from our monitoring.
So I went on the hunt for a method of forcing replay to occur without actually performing any database updates. I also did not want to grant any kind of update capability on the database to my monitoring role, for tighter security.
I discovered that the monitoring role, despite not having any update permissions, could successfully issue a "NOTIFY" command to a bogus channel and that this command actually forced the log to replay on the slave, updating the replay timestamp. This seems like a viable solution to my problem.
My plan is to have the monitoring role issue a "NOTIFY" every 30 seconds to ensure the timestamp is updated at least that frequently. But there will not be an associated "LISTEN" for these notifications.
However, I don't want to cause any problems for the PostgreSQL cluster itself by having messages inserted with nobody to listen for them, which is why I'm posting here.
Do you see any long-term problems with constantly issuing "NOTIFY" commands every 30 seconds without an associated "LISTEN" command?
Thank you,
Rob Brucks