Thread: Pause streaming replication

Rita wrote on 11/10/21 1:25 PM:
> Hello.
>
> I am testing alerting on my primary and standby setup. I have async 
> replication working but I would like to temporarily pause it so the 
> value of 'state' isn't streaming. (select * from pg_stat_replication).
>
> How can I do that?

By reading the fine manual: 
https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL-TABLE

The function you are looking for is pg_wal_replay_pause().

--

Rita wrote on 11/10/21 5:36 PM:

Yes, I have read the manual and seen this. It pauses the replication (select pg_is_wal_replay_paused()). But on the primary, when I look at pg_stat_replication, it still says 'streaming' in the state column. My question was how do I get it from 'streaming'  to anything else?  (https://www.postgresql.org/docs/11/monitoring-stats.html#PG-STAT-REPLICATION-VIEW) ?

I want to trigger an alert when 'streaming' isn't there. Or is there a better way to do it?

Oh yes, I'm sorry. I see now that was your original question, and my suggestion doesn't help you there.

But I would say that, in my experience with monitoring streaming replication, I haven't cared so much about if the replica is streaming vs. rebuilding, but rather how far behind it has fallen when it is supposedly streaming. Pausing replication lets you see what it will look like when a disk failure blocks your replica's writes, or client activity blocks wal replay, which are absolutely situations you want to monitor for.

On Wed, Nov 10, 2021 at 08:36:45PM -0500, Rita wrote:
> Yes, I have read the manual and seen this. It pauses the replication
> (select pg_is_wal_replay_paused()). But on the primary, when I look at
> pg_stat_replication, it still says 'streaming' in the state column. My
> question was how do I get it from 'streaming'  to anything else?  (
> https://www.postgresql.org/docs/11/monitoring-stats.html#PG-STAT-REPLICATION-VIEW)
> ?

pg_is_wal_replay_paused() pauses WAL *replay* at recovery, but it does
not stop the stream of WAL from the primary to its standbys.

> I want to trigger an alert when 'streaming' isn't there. Or is there a
> better way to do it?

If you want to have some automated test to check a given state of the
replication, you could use a combination of a SIGSTOP on the WAL
receiver of the standby and/or the WAL sender of the primary, with
some pg_terminate_backend() calls, just to throw one idea in the
bucket.
--
Michael

On Wed, 2021-11-10 at 20:36 -0500, Rita wrote:
> On Wed, Nov 10, 2021 at 7:24 PM Ben Chobot <bench@silentmedia.com> wrote:
> >Rita wrote on 11/10/21 1:25 PM:
> > > Hello.
> > > 
> > > I am testing alerting on my primary and standby setup. I have async 
> > > replication working but I would like to temporarily pause it so the 
> > > value of 'state' isn't streaming. (select * from pg_stat_replication).
> > > 
> > > How can I do that?
> > 
> > By reading the fine manual: 
> > https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL-TABLE
> > 
> > The function you are looking for is pg_wal_replay_pause().
> 
> Yes, I have read the manual and seen this. It pauses the replication (select pg_is_wal_replay_paused()).
> But on the primary, when I look at pg_stat_replication, it still says 'streaming' in the
> state column. My question was how do I get it from 'streaming'  to anything else?
> (https://www.postgresql.org/docs/11/monitoring-stats.html#PG-STAT-REPLICATION-VIEW) ?
> 
> I want to trigger an alert when 'streaming' isn't there. Or is there a better way to do it?

If the standby is no longer streaming, that certainly should trigger an alert.

But if you want to monitor replication delay, you should instead use a query like

   SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)
   FROM pg_stat_replication;

That will measure the replay delay in bytes, and you can alert if the value exceeds a certain amount.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com