Re: Backup solution over unreliable network

On 16/1/19 7:18 μ.μ., David Steele wrote:

> On 1/16/19 10:52 AM, Achilleas Mantzios wrote:
>> Hello David, Stephen, All and HPNY
>> On 30/11/18 6:50 μ.μ., David Steele wrote:
>>>
>>> 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.
>
>> Just tested finished backing up our 1.2T logical subscriber test node 
>> DB ! With a deliberate interrupt and with --resume --process-max=4 
>> and it worked just great!
>> On our production primary/physical standby cluster I want to retain 
>> our (primitive) local backup/archive functionality, which we do via :
>> archive_command = /usr/bin/rsync -a --delay-updates %p 
>> sma:/smadb/pgsql/pitr/%f
>> and instead of using a second local pgbackrest repo, just combine 
>> archive_command asis with pgbackrest to the remote repo with 
>> something like :
>> archive_command = /usr/bin/rsync -a --delay-updates %p 
>> sma:/smadb/pgsql/pitr/%f && pgbackrest --stanza=dynacom 
>> --archive-push-queue-max=50G archive-push %p
>
>> I read the code and saw that --archive-push-queue-max works even when 
>> not in async mode (default push). We are not planning for async at 
>> this early stage. Do you see and potential problem with the above?
>
> This seems reasonable since there is only one pgBackRest archive command.
Thanks!
>
> If you do eventually decide you need async then the rsync command will 
> become a major bottleneck -- pgBackRest is simply much faster than rsync.
>
>>> 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.
>>
>> We are planning to backup from the physical standby, but as you said 
>> the archive_command would be running from the primary.
>
> We haven't seen any issue with this configuration.  If WAL rates are 
> high then replication will likely lag whereas pgBackRest can keep up 
> with higher WAL rates using parallel async archiving on the primary. 
> This certainly consumes valuable primary resources but is the best way 
> to keep up-to-date.
My intention was just to verify I am inline with the docs and your prior 
emails!
>
> Regards,