Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1jnM6K-0005II-1F
	for pgsql-hackers@arkaria.postgresql.org; Mon, 22 Jun 2020 13:03:04 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1jnM6I-00007i-U3
	for pgsql-hackers@arkaria.postgresql.org; Mon, 22 Jun 2020 13:03:02 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <masao.fujii@oss.nttdata.com>)
	id 1jnM6I-00007F-Nf
	for pgsql-hackers@lists.postgresql.org; Mon, 22 Jun 2020 13:03:02 +0000
Received: from oss.nttdata.com ([49.212.34.109])
	by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <masao.fujii@oss.nttdata.com>)
	id 1jnM6C-0007XT-23
	for pgsql-hackers@postgresql.org; Mon, 22 Jun 2020 13:03:01 +0000
Received: from hnk.local (p2604028-ipngn12401funabasi.chiba.ocn.ne.jp
 [180.1.110.28])
	by oss.nttdata.com (Postfix) with ESMTPSA id E9822600FA;
	Mon, 22 Jun 2020 22:02:51 +0900 (JST)
X-Virus-Status: Clean
X-Virus-Scanned: clamav-milter 0.102.3 at oss.nttdata.com
Subject: Re: min_safe_lsn column in pg_replication_slots view
To: Amit Kapila <amit.kapila16@gmail.com>,
 Michael Paquier <michael@paquier.xyz>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>,
 Kyotaro Horiguchi <horikyota.ntt@gmail.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
References: <20200619121552.GH453547@paquier.xyz>
 <20200620014223.GA7119@alvherre.pgsql>
 <CAA4eK1+O2yHQ6O4PYa=zjyazaZcD10SoAtR5DUbMTnC3_b1BxA@mail.gmail.com>
 <20200620065354.GA5367@paquier.xyz> <20200622054950.GC50978@paquier.xyz>
 <CAA4eK1JBGLnSxaY4xEjDSDZBtTgWmbxA3YfknEZj3cfE5zu51w@mail.gmail.com>
From: Fujii Masao <masao.fujii@oss.nttdata.com>
Message-ID: <4700d202-a420-e8e0-becd-aac6ac52dce6@oss.nttdata.com>
Date: Mon, 22 Jun 2020 22:02:51 +0900
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:68.0)
 Gecko/20100101 Thunderbird/68.9.0
MIME-Version: 1.0
In-Reply-To: 
 <CAA4eK1JBGLnSxaY4xEjDSDZBtTgWmbxA3YfknEZj3cfE5zu51w@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Precedence: bulk



On 2020/06/22 21:01, Amit Kapila wrote:
> On Mon, Jun 22, 2020 at 11:19 AM Michael Paquier <michael@paquier.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?

Regards,


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