Re: min_safe_lsn column in pg_replication_slots view

On Tue, Jun 23, 2020 at 7:47 AM Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote:
>
> On 2020/06/23 10:10, Kyotaro Horiguchi wrote:
> > At Mon, 22 Jun 2020 22:02:51 +0900, Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote in
> >>
> >>> I feel such a function is good to have but I am not sure if there is a
> >>> need to tie it with the removal of min_safe_lsn column.
> >>
> >> We should expose the LSN calculated from
> >> "the current WAL LSN - max(wal_keep_segments * 16MB,
> >> max_slot_wal_keep_size)"?
> >> This indicates the minimum LSN of WAL files that are guaraneed to be
> >> currently retained by wal_keep_segments and max_slot_wal_keep_size.
> >> That is, if checkpoint occurs when restart_lsn of replication slot is
> >> smaller than that minimum LSN, some required WAL files may be removed.
> >> So DBAs can periodically monitor and compare restart_lsn and that
> >> minimum
> >> LSN. If they see frequently that difference of those LSN is very
> >> small,
> >> they can decide to increase wal_keep_segments or
> >> max_slot_wal_keep_size,
> >> to prevent required WAL files from being removed. Thought?
> >
> > I'm not sure about the consensus here about showing that number in the
> > view. It is similar to "remain" in the earlier versions of this patch
> > but a bit simpler. It would be usable in a similar way. I can live
> > with either numbers.
>
> It's useless to display this value in each replication slot in the view.
> I'm thinking to expose it as a function.
>

Having a separate function for this seems like a good idea but can we
consider displaying it in a view like pg_stat_replication_slots as we
are discussing a nearby thread to have such a view for other things.
I think ultimately this information is required to check whether some
slot can be invalidated or not, so having it displayed along with
other slot information might not be a bad idea.

[1] - https://www.postgresql.org/message-id/CAA4eK1Jyh4qgdnxzV4fYuk9GiXLb%3DUz-6o19E2RfiN8MPmUu3A%40mail.gmail.com

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com