pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: David Steele <david@pgmasters.net>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Backup solution over unreliable network
Date: Wed, 16 Jan 2019 10:52:52 +0200
Message-ID: <78d8f90a-7745-743f-53a2-547d6aa551c8@matrix.gatewaynet.com> (raw)
In-Reply-To: <57ee3c7c-284e-d847-49f3-1a1d28d3540d@pgmasters.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>

Hello David, Stephen, All and HPNY

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.
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?

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

>
> Regards,


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt





view thread (15+ messages)  latest in thread

Message-ID: <78d8f90a-7745-743f-53a2-547d6aa551c8@matrix.gatewaynet.com>
Permalink:  ../78d8f90a-7745-743f-53a2-547d6aa551c8@matrix.gatewaynet.com/
Also on:    postgresql.org/message-id/78d8f90a-7745-743f-53a2-547d6aa551c8@matrix.gatewaynet.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: achill@matrix.gatewaynet.com, david@pgmasters.net
  Subject: Re: Backup solution over unreliable network
  In-Reply-To: <78d8f90a-7745-743f-53a2-547d6aa551c8@matrix.gatewaynet.com>
  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>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox