Re: min_safe_lsn column in pg_replication_slots view

On 2020/06/23 15:27, Amit Kapila wrote:
> 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.

"the current WAL LSN - max(wal_keep_segments * 16MB, max_slot_wal_keep_size)"
is the same value between all the replication slots. But you think it's better
to display that same value for every slots in the view?

Or you're thinking to display the difference of that LSN value and
restart_lsn as Horiguchi-san suggested? That diff varies each replication slot,
so it seems ok to display it for every rows.

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION