Re: Backup solution over unreliable network

On 11/30/18 11:24 AM, Achilleas Mantzios wrote:
> On 30/11/18 5:29 μ.μ., Stephen Frost wrote:

>> We've had a few folks using pgbackrest to push to two stanzas by way of
>> basically doing 'pgbackrest --stanza=a archive-push && pgbackrest
>> --stanza=b archive-push' and with that it does work, and you could
>> combine that with the max WAL setting, potentially, but it's not a
>> solution that I'm really a fan of.  That's definitely a use-case we've
>> been thinking about though and have plans to support in the future,
>> but there are other things we're tackling now and so multi-repo hasn't
>> been a priority.
>
> If we called with e.g. --archive-push-queue-max=50G for the unreliable
> stanza and let to default for the reliable stanza that would be OK, I
> guess. Yes I understand you'd like a more systematic approach to the
> problem, but for the moment do you see any potential risk in doing what
> you described?

Multiple stanzas are tricky to configure if async archiving is in use,
otherwise it is relatively straightforward. You just need two
configuration files and each archive command will need one explicitly
configured (--config).

If async archiving is enabled then each stanza will also need a separate
spool directory. This configuration has never been tested and I
recommend against it.

>> We've also considered supporting archive-mode=always and being able to
>> have the standby also push WAL and while we may support that in the
>> future, I'd say it's farther down on the list than multi-repo support.
>> As I recall, David Steele also had some specific technical concerns
>> around how to handle two systems pushing into the same WAL archive.
>
> I recall with barman 2.4 and postgresql 10, I had absolutely no problem
> receiving wal stream from the secondary and WAL archives from the
> primary, AS LONG AS there was no exclusive pg_start/stop_backup
> happening on the primary. The moment pg_start_backup (exclusive) started
> on the primary, the very same barman started to complain about errors in
> duplicates IIRC, which means the WALs with same name were different. The
> WAL themselves were identical except some bytes on the header (which I
> guess have to do with exclusive backup).

I don't think this is because of the exclusive backup, but I'm not sure
what is happening. There are a few scenarios where WAL files may not be
binary equal between the primary and standby but this isn't one that I
know of.

>> Having archive-mode=always be allowed if it's going to an independent
>> repo is an interesting thought though and might be simpler to do.

pgBackRest currently requires some files and all WAL to be sent from the
primary even when doing backup from standby. We may improve this in the
future but it's not on the road map right now.

Regards,
--
-David
david(at)pgmasters(dot)net