Re: Backup solution over unreliable network - Mailing list pgsql-admin

On 30/11/18 6:50 μ.μ., David Steele wrote:
> 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.


Thank you a lot, will start with non async !


>
>>> 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,