Re: where should I stick that backup?

On 10/4/20 21:38, Andres Freund wrote:
> Hi,
>
> On 2020-04-10 12:20:01 -0400, Robert Haas wrote:
>> - We're only talking about writing a handful of tar files, and that's
>> in the context of a full-database backup, which is a much
>> heavier-weight operation than a query.
>> - There is not really any state that needs to be maintained across calls.
>> - The expected result is that a file gets written someplace, which is
>> not an in-memory data structure but something that gets written to a
>> place outside of PostgreSQL.
> Wouldn't there be state like a S3/ssh/https/... connection?
...to try and save opening a new connection in the context of a 
(potentially) multi-TB backup? :S
> And perhaps
> a 'backup_id' in the backup metadata DB that'd one would want to update
> at the end?

This is, indeed, material for external tools. Each cater for a 
particular set of end-user requirements.

We got many examples already, with most even co-authored by this list's 
regulars... and IMHO none is suitable for ALL use-cases.


BUT I agree that providing better tools with Postgres itself, ready to 
use --- that is, uncomment the default "archive_command" and get going 
for a very basic starting point --- is a huge advancement in the right 
direction. More importantly (IMO): including the call to "pgfile" or 
equivalent quite clearly signals any inadvertent user that there is more 
to safely archiving WAL segments than just doing "cp -a" blindly and 
hoping that the tool magically does all required steps [needed to ensure 
data safety in this case, rather than the usual behaviour]. It's 
probably more effective than just ammending the existing comments to 
point users to a (new?) section within the documentation.


This comment is from experience: I've lost count of how many times I 
have had to "fix" the default command for WAL archiving --- precisely 
because it had been blindly copied from the default without further 
thinking of the implications should there happen any 
(deviation-from-expected-behaviour) during WAL archiving .... only to be 
noticed at (attempted) recovery time :\


HTH.

Thanks,

     J.L.