Subject: Re: Backup solution over unreliable network
To: pgsql-admin@lists.postgresql.org
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>
From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Message-ID: <3797baa0-427c-eb98-55a3-d3b4ecf4fbbc@matrix.gatewaynet.com>
Date: Fri, 30 Nov 2018 20:59:59 +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: <57ee3c7c-284e-d847-49f3-1a1d28d3540d@pgmasters.net>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk


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,