Re: sync_standbys_defined and pg_stat_replication

On Wed, 8 Oct 2025 20:38:53 +0300
Ants Aasma <ants.aasma@cybertec.at> wrote:

> 
> For the other problem, my thinking is to provide a new function that
> allows a user to check if synchronous replication is active.
> 
> Ideally this function would give other information also needed by
> cluster managers. Specifically when a replica is removed from
> synchronous standby names we would need still need to consider that
> replica as a potential synchronous replica until a quorum matching the
> current synchronous_standby_names setting overtakes the last LSN
> confirmed by a replica matching the removed name.
> 
> ...
> 
> My proposal is something like this:
> 
> postgres=# SELECT * FROM pg_sync_replication_status();
>  is_active | synchronous_standby_names | has_quorum
> -----------+---------------------------+------------
>  t         | ANY 1 (A B)               | f
> (1 row)

Would it make sense to also add a fourth column in this proposed new
function with the effective LSN from the last time
synchronous_standby_names was changed?

I'm also thinking about changes to the synchronous_commit setting; if we
wanted the highest level of correctness, should we also capture the LSN
of that change and reflect it in this view? I haven't thought this
through fully yet.

Should external tools use procs corresponding with synchronous_commit
setting for promotion decisions? (pg_last_wal_receive_lsn vs
pg_last_wal_replay_lsn) This also relates to Andrey Borodin's other
thread about adding direct access to the write LSN on replicas for
promotion decisions.

https://www.postgresql.org/message-id/flat/0B44E464-BA62-4056-9465-3320DD2D0302%40yandex-team.ru

-Jeremy