Re: sync_standbys_defined and pg_stat_replication

On Wed, 8 Oct 2025 20:38:53 +0300
Ants Aasma <ants(dot)aasma(at)cybertec(dot)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