Re: where should I stick that backup?

From: Jose Luis Tallon <jltallon(at)adv-solutions(dot)net>
To: Robert Haas <robertmhaas(at)gmail(dot)com>, Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Stephen Frost <sfrost(at)snowman(dot)net>, Magnus Hagander <magnus(at)hagander(dot)net>, Noah Misch <noah(at)leadboat(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: where should I stick that backup?
Date: 2020-04-11 16:24:13
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 10/4/20 15:49, Robert Haas wrote:
> On Thu, Apr 9, 2020 at 6:44 PM Bruce Momjian <bruce(at)momjian(dot)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.
> 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
> 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(at)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.


    / J.L.

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Mark Dilger 2020-04-11 16:28:03 Re: cleaning perl code
Previous Message Andrew Dunstan 2020-04-11 16:13:08 Re: cleaning perl code