Re: Duplicate history file?

Hi Horiguchi-san,

>> Regarding "test ! -f",
>> I am wondering how many people are using the test command for
>> archive_command. If I remember correctly, the guide provided by
>> NTT OSS Center that we are using does not recommend using the test
>> command.
> 
> I think, as the PG-REX documentation says, the simple cp works well as
> far as the assumption of PG-REX - no double failure happenes, and
> following the instruction - holds.


I believe that this assumption started to be wrong after
archive_mode=always was introduced. As far as I can tell, it doesn't
happen when it's archive_mode=on.


> On the other hand, I found that the behavior happens more generally.
> 
> If a standby with archive_mode=always craches, it starts recovery from
> the last checkpoint. If the checkpoint were in a archived segment, the
> restarted standby will fetch the already-archived segment from archive
> then fails to archive it. (The attached).
> 
> So, your fear stated upthread is applicable for wider situations. The
> following suggestion is rather harmful for the archive_mode=always
> setting.
> 
> https://www.postgresql.org/docs/14/continuous-archiving.html
>> The archive command should generally be designed to refuse to
>> overwrite any pre-existing archive file. This is an important safety
>> feature to preserve the integrity of your archive in case of
>> administrator error (such as sending the output of two different
>> servers to the same archive directory).
> 
> I'm not sure how we should treat this..  Since archive must store
> files actually applied to the server data, just being already archived
> cannot be the reason for omitting archiving.  We need to make sure the
> new file is byte-identical to the already-archived version. We could
> compare just *restored* file to the same file in pg_wal but it might
> be too much of penalty for for the benefit. (Attached second file.)


Thanks for creating the patch!

  
> Otherwise the documentation would need someting like the following if
> we assume the current behavior.
> 
>> The archive command should generally be designed to refuse to
>> overwrite any pre-existing archive file. This is an important safety
>> feature to preserve the integrity of your archive in case of
>> administrator error (such as sending the output of two different
>> servers to the same archive directory).
> + For standby with the setting archive_mode=always, there's a case where
> + the same file is archived more than once.  For safety, it is
> + recommended that when the destination file exists, the archive_command
> + returns zero if it is byte-identical to the source file.


Agreed.
That is same solution as I mentioned earlier.
If possible, it also would better to write it postgresql.conf (that might
be overkill?!).


Regards,
Tatsuro Yamada