Pause streaming replication
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?
--
--- Get your facts first, then you can distort them as you please.--
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?
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().
--
--- Get your facts first, then you can distort them as you please.--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-TABLEThe 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