Re: where should I stick that backup? - Mailing list pgsql-hackers

Greetings,

* Bruce Momjian (bruce@momjian.us) wrote:
> I think we need to step back and look at the larger issue.  The real
> argument goes back to the Unix command-line API vs the VMS/Windows API.
> The former has discrete parts that can be stitched together, while the
> VMS/Windows API presents a more duplicative but more holistic API for
> every piece.  We have discussed using shell commands for
> archive_command, and even more recently, for the server pass phrase.

When it comes to something like the server pass phrase, it seems much
more reasonable to consider using a shell script (though still perhaps
not ideal) because it's not involved directly in ensuring that the data
is reliably stored and it's pretty clear that if it doesn't work the
worst thing that happens is that the database doesn't start up, but it
won't corrupt any data or destroy it or do other bad things.

> To get more specific, I think we have to understand how the
> _requirements_ of the job match the shell script API, with stdin,
> stdout, stderr, return code, and command-line arguments.  Looking at
> archive_command, the command-line arguments allow specification of file
> names, but quoting can be complex.  The error return code and stderr
> output seem to work fine.  There is no clean API for fsync and testing
> if the file exists, so that all that has to be hand done in one
> command-line.  This is why many users use pre-written archive_command
> shell scripts.

We aren't considering all of the use-cases really though, in specific,
things like pushing to s3 or gcs require, at least, good retry logic,
and that's without starting to think about things like high-rate systems
(spawning lots of new processes isn't free, particularly if they're
written in shell script but any interpreted language is expensive) and
wanting to parallelize.

> This brings up a few questions:
>
> *  Should we have split apart archive_command into file-exists, copy,
> fsync-file?  Should we add that now?

No..  The right approach to improving on archive command is to add a way
for an extension to take over that job, maybe with a complete background
worker of its own, or perhaps a shared library that can be loaded by the
archiver process, at least if we're talking about how to allow people to
extend it.

Potentially a better answer is to just build this stuff into PG- things
like "archive WAL to s3/GCS with these credentials" are what an awful
lot of users want.  There's then some who want "archive first to this
other server, and then archive to s3/GCS", or more complex options.

I'll also point out that there's not one "s3"..  there's quite a few
alternatives, including some which are open source, which talk the s3
protocol (sadly, they don't all do it perfectly, which is why we are
talking about building a GCS-specific driver for gcs rather than using
their s3 gateway, but still, s3 isn't just 'one thing').

> *  How well does this backup requirement match with the shell command
> API?

For my part, it's not just a question of an API, but it's a question of
who is going to implement a good and reliable solution- PG developers,
or some admin who is just trying to get PG up and running in their
environment..?  One aspect of that is being knowledgable about where all
the land mines are- like the whole fsync thing.  Sure, if you're a PG
developer or you've been around long enough, you're going to realize
that 'cp' isn't going to fsync() the file and therefore it's a pretty
high risk choice for archive_command, and you'll understand just how
important WAL is, but there's certainly an awful lot of folks out there
who don't realize that or at least don't think about it when they're
standing up a new system and instead they just are following our docs
with the expectation that those docs are providing good advice.

Thanks,

Stephen