From: David Steele <david@pgmasters.net>
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-admin@lists.postgresql.org, Stephen Frost <sfrost@snowman.net>
Subject: Re: Backup solution over unreliable network
Date: Wed, 16 Jan 2019 19:18:15 +0200
Message-ID: <4f415a70-2cd8-2c8f-bcb0-6a6edd666f61@pgmasters.net> (raw)
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>
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.
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.
Regards,
--
-David
david@pgmasters.net
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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: david@pgmasters.net, achill@matrix.gatewaynet.com, sfrost@snowman.net
Subject: Re: Backup solution over unreliable network
In-Reply-To: <4f415a70-2cd8-2c8f-bcb0-6a6edd666f61@pgmasters.net>
* 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