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
