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

On 10/4/20 15:49, Robert Haas wrote:
> On Thu, Apr 9, 2020 at 6:44 PM Bruce Momjian <bruce@momjian.us> wrote:
>> Good point, but if there are multiple APIs, it makes shell script
>> flexibility even more useful.
> [snip]
>
> One thing I do think would be realistic would be to invent a set of
> tools that are perform certain local filesystem operations in a
> "hardened" way.
+10
>   Maybe a single tool with subcommands and options. So
> you could say, e.g. 'pgfile cp SOURCE TARGET' and it would create a
> temporary file in the target directory, write the contents of the
> source into that file, fsync the file, rename it into place, and do
> more fsyncs to make sure it's all durable in case of a crash. You
> could have a variant of this that instead of using the temporary file
> and rename in place approach, does the thing where you open the target
> file with O_CREAT|O_EXCL, writes the bytes, and then closes and fsyncs
> it.
Behaviour might be decided in the same way as the default for 
'wal_sync_method' gets chosen, as the most appropriate for a particular 
system.
> And you could have other things too, like 'pgfile mkdir DIR' to
> create a directory and fsync it for durability. A toolset like this
> would probably help people write better archive commands

Definitely, "mkdir" and "create-exclusive" (along with cp) would be a 
great addition and simplify the kind of tasks properly (i.e. with 
risking data loss every time)
> [excerpted]
>
> pg_basebackup -Ft --pipe-output 'bzip | pgfile create-exclusive - %f.bz2'
>
> [....]
>
> pg_basebackup -Ft --pipe-output 'bzip | gpg -e | ssh someuser@somehost
> pgfile create-exclusive - /backups/tuesday/%f.bz2'
Yep. Would also fit the case for non-synchronous NFS mounts for backups...
> It is of course not impossible to teach pg_basebackup to do all of
> that stuff internally, but I have a really difficult time imagining us
> ever getting it done. There are just too many possibilities, and new
> ones arise all the time.

Indeed. The beauty of Unix-like OSs is precisely this.

> A 'pgfile' utility wouldn't help at all for people who are storing to
> S3 or whatever. They could use 'aws s3' as a target for --pipe-output,
> [snip]
> (Incidentally, pg_basebackup already has an option to output the
> entire backup as a tarfile on standard output, and a user can already
> pipe that into any tool they like. However, it doesn't work with
> tablespaces. So you could think of this proposal as extending the
> existing functionality to cover that case.)

Been there already :S  Having pg_basebackup output multiple tarballs 
(one per tablespace), ideally separated via something so that splitting 
can be trivially done on the receiving end.

...but that's probably matter for another thread.


Thanks,

     / J.L.