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.