Обсуждение: [GENERAL] NOTIFY command impact
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
I am working on a project which needs me to implement the direct “wired-protocol” to talk to pgsql. One side, I talk to pgsql,the other side, I need to make a listener which accepts the protocol from pgsql’s and mysql’s ODBC (talking wired toboth). [sorry if Wired is not the correct term, that is what I was assigned to do… so I am asking here]. URL? I have an example of coding this in python, but only does like 5 commands. w/o documentation of where they found the specs.
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@aklaver.com
Ozz Nixon <ozznixon@gmail.com> writes: > I am working on a project which needs me to implement the direct “wired-protocol” to talk to pgsql. One side, I talk topgsql, the other side, I need to make a listener which accepts the protocol from pgsql’s and mysql’s ODBC (talking wiredto both). [sorry if Wired is not the correct term, that is what I was assigned to do… so I am asking here]. > URL? https://www.postgresql.org/docs/current/static/protocol.html regards, tom lane
On 2/21/2017 1:12 PM, Ozz Nixon wrote: > I am working on a project which needs me to implement the direct “wired-protocol” to talk to pgsql. One side, I talk topgsql, the other side, I need to make a listener which accepts the protocol from pgsql’s and mysql’s ODBC (talking wiredto both). [sorry if Wired is not the correct term, that is what I was assigned to do… so I am asking here]. > > URL? > > I have an example of coding this in python, but only does like 5 commands. w/o documentation of where they found the specs. dare I ask why you can't use libpq or another existing language binding ? writing a custom binding ties you down to a lifetime of tracking version updates... if you really must, start here, https://www.postgresql.org/docs/current/static/protocol.html then look at the source to libpq, and/or pg-jdbc, and/or PyGreSQL and/or Psycopg to work out implementation details. -- john r pierce, recycling bits in santa cruz
Depending on how long 'long term' is:On 02/21/2017 01:07 PM, Rob Brucks wrote:
Do you see any long-term problems with constantly issuing "NOTIFY"
commands every 30 seconds without an associated "LISTEN" command?
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
In my case the monitoring user will be connecting, issuing the notify, then immediately disconnecting.
And we don't have any systems using listen/notify.
So I'm hoping there won't be a problem.
That's why I'm asking ☺
--Rob
On 2/21/17, 3:17 PM, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote:
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@aklaver.com
I did find a post a while back saying they were discarded, but I wanted to double-check.
I performed some tests to see if listens worked AFTER the notify was issued, they were not. This leads me to believe that the messages are discarded when a listen does not yet exist.
--Rob
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Tuesday, February 21, 2017 at 3:38 PM
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Rob Brucks <rob.brucks@rackspace.com>, "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] NOTIFY command impact
On Tue, Feb 21, 2017 at 2:17 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 02/21/2017 01:07 PM, Rob Brucks wrote:
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
Its not clear in the OP that this is the case (it seems to be) but the documentation is non-specific as to what happens when "# of listeners" = 0; I suspect that said messages are created and then immediately discarded - though apparently they do make it over to the standby server too - and likely also immediately discarded there as well.
David J.
On 02/21/2017 02:19 PM, Rob Brucks wrote: > I did find a post a while back saying they were discarded, but I wanted > to double-check. > > > > I performed some tests to see if listens worked AFTER the notify was > issued, they were not. This leads me to believe that the messages are > discarded when a listen does not yet exist. Seems the thing to do would be to monitor the size of : $PG_DATA/pg_notify/ > > > > --Rob > > > > *From: *"David G. Johnston" <david.g.johnston@gmail.com> > *Date: *Tuesday, February 21, 2017 at 3:38 PM > *To: *Adrian Klaver <adrian.klaver@aklaver.com> > *Cc: *Rob Brucks <rob.brucks@rackspace.com>, > "pgsql-general@postgresql.org" <pgsql-general@postgresql.org> > *Subject: *Re: [GENERAL] NOTIFY command impact > > > > On Tue, Feb 21, 2017 at 2:17 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > On 02/21/2017 01:07 PM, Rob Brucks wrote: > > > 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 > > > > Its not clear in the OP that this is the case (it seems to be) but the > documentation is non-specific as to what happens when "# of listeners" = > 0; I suspect that said messages are created and then immediately > discarded - though apparently they do make it over to the standby server > too - and likely also immediately discarded there as well. > > > > David J. > > > -- Adrian Klaver adrian.klaver@aklaver.com
That's a great thought!
Comparing between systems (one where I don't issue "notify" and one where I do every minute) yields the same thing:
one8k file named "0000".
If that's truly where notifications are stored, then it looks like I should be good, at least for storage. But I
wonderif that file is only used to store notify commands during shutdown/startup?
Or if there are any considerations for memory usage…
--Rob
On 2/21/17, 4:38 PM, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote:
On 02/21/2017 02:19 PM, Rob Brucks wrote:
> I did find a post a while back saying they were discarded, but I wanted
> to double-check.
>
>
>
> I performed some tests to see if listens worked AFTER the notify was
> issued, they were not. This leads me to believe that the messages are
> discarded when a listen does not yet exist.
Seems the thing to do would be to monitor the size of :
$PG_DATA/pg_notify/
>
>
>
> --Rob
>
>
>
> *From: *"David G. Johnston" <david.g.johnston@gmail.com>
> *Date: *Tuesday, February 21, 2017 at 3:38 PM
> *To: *Adrian Klaver <adrian.klaver@aklaver.com>
> *Cc: *Rob Brucks <rob.brucks@rackspace.com>,
> "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
> *Subject: *Re: [GENERAL] NOTIFY command impact
>
>
>
> On Tue, Feb 21, 2017 at 2:17 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
> On 02/21/2017 01:07 PM, Rob Brucks wrote:
>
>
> 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
>
>
>
> Its not clear in the OP that this is the case (it seems to be) but the
> documentation is non-specific as to what happens when "# of listeners" =
> 0; I suspect that said messages are created and then immediately
> discarded - though apparently they do make it over to the standby server
> too - and likely also immediately discarded there as well.
>
>
>
> David J.
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
But I wonder if that file is only used to store notify commands during shutdown/startup?
If a notify is sent and then PG is immediately shut down, wouldn't PG want to save that message for processing after startup?
Or is the message just discarded?
--Rob
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Tuesday, February 21, 2017 at 4:45 PM
To: Rob Brucks <rob.brucks@rackspace.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] NOTIFY command impact
On Tue, Feb 21, 2017 at 3:43 PM, Rob Brucks <rob.brucks@rackspace.com> wrote:
But I wonder if that file is only used to store notify commands during shutdown/startup?
huh?
David J.
On 02/21/2017 02:43 PM, Rob Brucks wrote: > That's a great thought! > > Comparing between systems (one where I don't issue "notify" and one where I do every minute) yields the same thing: one8k file named "0000". > > If that's truly where notifications are stored, then it looks like I should be good, at least for storage. But I wonderif that file is only used to store notify commands during shutdown/startup? From the source of async.c: During start or reboot, clean out the pg_notify directory. I would guess the 0000 file is like a WAL file it is a pre-initialized file filled with 0(?)'s > > Or if there are any considerations for memory usage… > > --Rob > > On 2/21/17, 4:38 PM, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote: > > On 02/21/2017 02:19 PM, Rob Brucks wrote: > > I did find a post a while back saying they were discarded, but I wanted > > to double-check. > > > > > > > > I performed some tests to see if listens worked AFTER the notify was > > issued, they were not. This leads me to believe that the messages are > > discarded when a listen does not yet exist. > > Seems the thing to do would be to monitor the size of : > > $PG_DATA/pg_notify/ > > > > > > > > > --Rob > > > > > > > > *From: *"David G. Johnston" <david.g.johnston@gmail.com> > > *Date: *Tuesday, February 21, 2017 at 3:38 PM > > *To: *Adrian Klaver <adrian.klaver@aklaver.com> > > *Cc: *Rob Brucks <rob.brucks@rackspace.com>, > > "pgsql-general@postgresql.org" <pgsql-general@postgresql.org> > > *Subject: *Re: [GENERAL] NOTIFY command impact > > > > > > > > On Tue, Feb 21, 2017 at 2:17 PM, Adrian Klaver > > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > > > On 02/21/2017 01:07 PM, Rob Brucks wrote: > > > > > > 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 > > > > > > > > Its not clear in the OP that this is the case (it seems to be) but the > > documentation is non-specific as to what happens when "# of listeners" = > > 0; I suspect that said messages are created and then immediately > > discarded - though apparently they do make it over to the standby server > > too - and likely also immediately discarded there as well. > > > > > > > > David J. > > > > > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > -- Adrian Klaver adrian.klaver@aklaver.com
Rob Brucks <rob.brucks@rackspace.com> writes:
> If a notify is sent and then PG is immediately shut down, wouldn't PG want to save that message for processing after
startup?
> Or is the message just discarded?
NOTIFY data is not saved across a shutdown or crash.
(The reason it goes into the WAL stream is so that you can have listeners
on replication slaves, not for recovery purposes.)
regards, tom lane
If a notify is sent and then PG is immediately shut down, wouldn't PG want to save that message for processing after startup?
Or is the message just discarded?
> On Feb 21, 2017, at 6:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Rob Brucks <rob.brucks@rackspace.com> writes: >> If a notify is sent and then PG is immediately shut down, wouldn't PG want to save that message for processing after startup? >> Or is the message just discarded? > > NOTIFY data is not saved across a shutdown or crash. > > (The reason it goes into the WAL stream is so that you can have listeners > on replication slaves, not for recovery purposes.) > > regards, tom lane > Are we sure that replication slaves can have listeners? When I tried it on 9.4.10, I got the following message: "ERROR: cannot execute LISTEN during recovery" -=Frank
> On Feb 21, 2017, at 6:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Rob Brucks <rob.brucks@rackspace.com> writes: >> If a notify is sent and then PG is immediately shut down, wouldn't PG want to save that message for processing after startup? >> Or is the message just discarded? > > NOTIFY data is not saved across a shutdown or crash. > > (The reason it goes into the WAL stream is so that you can have listeners > on replication slaves, not for recovery purposes.) > > regards, tom lane > Are we sure that replication slaves can have listeners? When I tried it on 9.4.10, I got the following message: "ERROR: cannot execute LISTEN during recovery" -=Frank
Thanks Tom,
Can you answer my original question too:
If I am issuing NOTIFY commands every 30 seconds (and immediately committing) and there are no listeners, will that
havea negative impact on the cluster?
I'm using the NOTIFY to force streaming replication to update the "pg_last_xact_replay_timestamp" on the slaves so we
canmore accurately monitor replication lag on quiet systems.
Thanks,
Rob
On 2/21/17, 5:03 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
Rob Brucks <rob.brucks@rackspace.com> writes:
> If a notify is sent and then PG is immediately shut down, wouldn't PG want to save that message for processing
afterstartup?
> Or is the message just discarded?
NOTIFY data is not saved across a shutdown or crash.
(The reason it goes into the WAL stream is so that you can have listeners
on replication slaves, not for recovery purposes.)
regards, tom lane
=?utf-8?Q?Fran=C3=A7ois_Beaulieu?= <frank@TZoNE.ORG> writes:
>> On Feb 21, 2017, at 6:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> (The reason it goes into the WAL stream is so that you can have listeners
>> on replication slaves, not for recovery purposes.)
> Are we sure that replication slaves can have listeners? When I tried it on 9.4.10, I got the following message:
> "ERROR: cannot execute LISTEN during recovery"
Hmm ... you know what, my remark above is full of it. NOTIFY traffic
*doesn't* go into the WAL stream. I think I was remembering some
discussions about how that would be a good idea so that you could put
listeners on slaves; but that hasn't actually happened yet, as a look
through async.c will show.
After thinking a bit more, I believe what the OP is seeing is that
NOTIFY does result in an XID being assigned to the transaction (so
that the message it sticks into the pg_notify queue can be correctly
labeled). That therefore results in a transaction commit message
being sent to WAL, even though this transaction did nothing that would
actually change any persistent database state.
There are other ways of forcing XID assignment without doing much
real work, but this one is probably about as cheap as any.
regards, tom lane
Thank you Tom.
So, long-term, you don't see any negative impacts to the master cluster?
I just don't want to implement this as a streaming "push" mechanism and then have my cluster crash in 12 months because
ithit some obscure problem with notifications.
This turned out to be a really simple/elegant way to force WAL stream movement without having to modify the database in
anyway, since I am heavily restricting the permissions granted to my monitoring role.
And, on a side note, it would be EXTREMELY useful to include a default monitoring role in PG that has permission to
queryall columns of all pg_stat* views on any DB, yet has no ability to modify *anything* (not even create any objects
ofits own). I won't grant super-user to my monitoring role since it is administered remotely, it is too much of a
securityrisk.
Thanks!
Rob
On 2/22/17, 3:19 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
=?utf-8?Q?Fran=C3=A7ois_Beaulieu?= <frank@TZoNE.ORG> writes:
>> On Feb 21, 2017, at 6:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> (The reason it goes into the WAL stream is so that you can have listeners
>> on replication slaves, not for recovery purposes.)
> Are we sure that replication slaves can have listeners? When I tried it on 9.4.10, I got the following message:
> "ERROR: cannot execute LISTEN during recovery"
Hmm ... you know what, my remark above is full of it. NOTIFY traffic
*doesn't* go into the WAL stream. I think I was remembering some
discussions about how that would be a good idea so that you could put
listeners on slaves; but that hasn't actually happened yet, as a look
through async.c will show.
After thinking a bit more, I believe what the OP is seeing is that
NOTIFY does result in an XID being assigned to the transaction (so
that the message it sticks into the pg_notify queue can be correctly
labeled). That therefore results in a transaction commit message
being sent to WAL, even though this transaction did nothing that would
actually change any persistent database state.
There are other ways of forcing XID assignment without doing much
real work, but this one is probably about as cheap as any.
regards, tom lane
So, long-term, you don't see any negative impacts to the master cluster?
I just don't want to implement this as a streaming "push" mechanism and then have my cluster crash in 12 months because it hit some obscure problem with notifications.
Hi Benoit,
Your SQL works fine as long as there are never any communication problems between the master and slave. But if your slave loses communication with the master, then the SQL you provided will not report any lag despite the potential that the slave could be very far behind the master.
This is because that SQL is asking the slave how far it is behind the master based on logs it has received from the master. However if the slave has not received logs from the master it cannot tell if the master has simply gone idle or if it has lost communication and is unable to send updates.
By introducing a mechanism to manually force the replication stream to be sent periodically, which is what I use the NOTIFY command to do on the master, I can ensure that the slave is updating pg_last_xact_replay_timestamp at least as often as the NOTIFY is executed on the master (say every 30 seconds) and is successfully replayed on the slave. This enables an accurate measurement of replication lag whatever the circumstances.
If the master has simply gone idle because there is no update activity, the NOTIFY command will still force the replication stream to replay on the slave every 30 seconds and the replay timestamp will be updated.
But if communication to the master is lost, for whatever reason, the replay timestamp will not be updated on the slave and then lag can be accurately measured and alerted.
I hope this helps!
--Rob
From: Benoit Lobréau <benoit.lobreau@gmail.com>
Date: Thursday, February 23, 2017 at 9:23 AM
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Rob Brucks <rob.brucks@rackspace.com>, Tom Lane <tgl@sss.pgh.pa.us>, François Beaulieu <frank@tzone.org>, Adrian Klaver <adrian.klaver@aklaver.com>, "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] NOTIFY command impact
Hi,
I might have missed something.
This should be enough to solve the problem no ?
SELECT
CASE
WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0
ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())::INTEGER
END
AS replication_lag;
Benoit.