Re: min_safe_lsn column in pg_replication_slots view

At Tue, 23 Jun 2020 11:50:34 +0530, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote in
> On Mon, Jun 22, 2020 at 6:32 PM Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote:
> >
> > On 2020/06/22 21:01, Amit Kapila wrote:
> > > On Mon, Jun 22, 2020 at 11:19 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> > >>
> > >> On Sat, Jun 20, 2020 at 03:53:54PM +0900, Michael Paquier wrote:
> > >>> On Sat, Jun 20, 2020 at 09:45:52AM +0530, Amit Kapila wrote:
> > >>>> Isn't this information specific to checkpoints, so maybe better to
> > >>>> display in view pg_stat_bgwriter?
> > >>>
> > >>> Not sure that's a good match. If we decide to expose that, a separate
> > >>> function returning a LSN based on the segment number from
> > >>> XLogGetLastRemovedSegno() sounds fine to me, like
> > >>> pg_wal_last_recycled_lsn(). Perhaps somebody has a better name in
> > >>> mind?
> > >>
> > >> I was thinking on this one for the last couple of days, and came up
> > >> with the name pg_wal_oldest_lsn(), as per the attached, traking the
> > >> oldest WAL location still available.
> >
> > Thanks for the patch!
> >
> > + <literal>NULL</literal> if no WAL segments have been removed since
> > + startup.
> >
> > Isn't this confusing? I think that we should store the last removed
> > WAL segment to somewhere (e.g., pg_control) and restore it at
> > the startup, so that we can see the actual value even after the startup.
> > Or we should scan pg_wal directory and find the "minimal" WAL segment
> > and return its LSN.
> >
> >
> > > 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?
> >
>
> +1. This sounds like a good and useful stat for users.

+1 for showing a number that is not involving lastRemovedSegNo. It is
like returning to the initial version of this patch. It showed a
number like ((the suggested above) minus restart_lsn). The number is
different for each slot so they fit in the view.

The number is usable for the same purpose so I'm ok with it.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center