On 02/21/2017 01:07 PM, Rob Brucks wrote:
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?
Depending on how long 'long term' is:
https://www.postgresql.org/docs/9.6/static/sql-notify.html
"There is a queue that holds notifications that have been sent but not
yet processed by all listening sessions. If this queue becomes full,
transactions calling NOTIFY will fail at commit. The queue is quite
large (8GB in a standard installation) and should be sufficiently sized
for almost every use case. However, no cleanup can take place if a
session executes LISTEN and then enters a transaction for a very long
time. Once the queue is half full you will see warnings in the log file
pointing you to the session that is preventing cleanup. In this case you
should make sure that this session ends its current transaction so that
cleanup can proceed."
Thank you,
Rob Brucks
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
