Subject: Re: [BUG] non archived WAL removed during production crash recovery
To: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>,
 pgsql-bugs@lists.postgresql.org, michael@paquier.xyz
References: <20200402.130443.1791362864704140391.horikyota.ntt@gmail.com>
 <20200402.130734.613319908534820379.horikyota.ntt@gmail.com>
 <03900fc5-fff4-109a-fe69-83de2c167929@oss.nttdata.com>
 <20200402.162323.770257425358491539.horikyota.ntt@gmail.com>
 <ca964b3a-61a0-902e-c7b3-3abbc01a921f@oss.nttdata.com>
 <20200402154915.4984cff2@firost>
From: Fujii Masao <masao.fujii@oss.nttdata.com>
Message-ID: <3d543ef2-ae02-5e59-3bfe-b79d00dab360@oss.nttdata.com>
Date: Thu, 2 Apr 2020 23:55:46 +0900
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:68.0)
 Gecko/20100101 Thunderbird/68.6.0
MIME-Version: 1.0
In-Reply-To: <20200402154915.4984cff2@firost>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk


On 2020/04/02 22:49, Jehan-Guillaume de Rorthais wrote:
> On Thu, 2 Apr 2020 19:38:59 +0900
> Fujii Masao <masao.fujii@oss.nttdata.com> wrote:
> 
>> On 2020/04/02 16:23, Kyotaro Horiguchi wrote:
>>> At Thu, 2 Apr 2020 14:19:15 +0900, Fujii Masao
>>> <masao.fujii@oss.nttdata.com> wrote in
> [...]
>>>> is whether to remove such WAL files in archive recovery case with
>>>> archive_mode=on. Those WAL files would be required when recovering
>>>> from the backup taken before that archive recovery happens.
>>>> So it seems unsafe to remove them in that case.
>>>
>>> I'm not sure I'm getting the intention correctly, but I think it
>>> responsibility of the operator to provide a complete set of archived
>>> WAL files for a backup.  Could you elaborate what operation steps are
>>> you assuming of?
>>
>> Please imagine the case where you need to do archive recovery
>> from the database snapshot taken while there are many WAL files
>> with .ready files. Those WAL files have not been archived yet.
>> In this case, ISTM those WAL files should not be removed until
>> they are archived, when archive_mode = on.
> 
> If you rely on snapshot without pg_start/stop_backup, I agree. Theses WAL
> should be archived if:
> 
> * archive_mode >= on for primary
> * archive_mode = always for standby
> 
>>>> Therefore, IMO that the patch should change the code so that
>>>> no unarchived WAL files are removed not only in crash recovery
>>>> but also archive recovery. Thought?
>>>
>>> Agreed if "an unarchived WAL" means "a WAL file that is marked .ready"
>>> and it should be archived immediately.  My previous mail is written
>>> based on the same thought.
>>
>> Ok, so our *current* consensus seems the followings. Right?
>>
>> - If archive_mode=off, any WAL files with .ready files are removed in
>>      crash recovery, archive recoery and standby mode.
> 
> yes
> 
>> - If archive_mode=on, WAL files with .ready files are removed only in
>>      standby mode. In crash recovery and archive recovery cases, they keep
>>      remaining and would be archived after recovery finishes (i.e., during
>>      normal processing).
> 
> yes
> 
>> - If archive_mode=always, in crash recovery, archive recovery and
>>      standby mode, WAL files with .ready files are archived if WAL archiver
>>      is running.
> 
> yes
> 
>> That is, WAL files with .ready files are removed when either
>> archive_mode!=always in standby mode or archive_mode=off.
> 
> sounds fine to me.
> 
> [...]
>>>>>>>> Another is to make the startup process remove .ready file if
>>>>>>>> necessary.
>>>>>>>
>>>>>>> I'm not sure to understand this one.
>>>>
>>>> I was thinking to make the startup process remove such unarchived WAL
>>>> files
>>>> if archive_mode=on and StandbyModeRequested/ArchiveRecoveryRequested
>>>> is true.
> 
> Ok, understood.
> 
>>> As mentioned above, I don't understand the point of preserving WAL
>>> files that are either marked as .ready or not marked at all on a
>>> standby with archive_mode=on.
>>
>> Maybe yes. But I'm not confident about that there is no such case.
> 
> Well, it seems to me that this is what you suggested few paragraph away:
> 
>    «.ready files are removed when either archive_mode!=always in standby mode»

Yes, so I'm fine with that as the first consensus because the behavior
is obviously better than the current one. *If* the case where no WAL files
should be removed is found, I'd just like to propose the additional patch.

Regards,

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