Subject: Re: Backup solution over unreliable network
To: David Steele <david@pgmasters.net>, pgsql-admin@lists.postgresql.org,
        Stephen Frost <sfrost@snowman.net>
References: <fb7e7296-c60e-c2cc-93d5-9c2451e9a2a5@matrix.gatewaynet.com>
 <20181130120613.GW3415@tamriel.snowman.net>
 <6834c573-2a52-dd63-0264-5fc7258e7857@matrix.gatewaynet.com>
 <20181130152931.GY3415@tamriel.snowman.net>
 <88ba3c9e-eb96-8567-3c35-c3fdb85c1048@matrix.gatewaynet.com>
 <57ee3c7c-284e-d847-49f3-1a1d28d3540d@pgmasters.net>
 <78d8f90a-7745-743f-53a2-547d6aa551c8@matrix.gatewaynet.com>
 <4f415a70-2cd8-2c8f-bcb0-6a6edd666f61@pgmasters.net>
From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Message-ID: <1e4c2867-604d-3f19-dcba-d4b5bc9c6b04@matrix.gatewaynet.com>
Date: Wed, 16 Jan 2019 21:26:48 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.2.1
MIME-Version: 1.0
In-Reply-To: <4f415a70-2cd8-2c8f-bcb0-6a6edd666f61@pgmasters.net>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

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,