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,