From: David Steele <david@pgmasters.net>
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-admin@lists.postgresql.org
Cc: evanbauer@mac.com, Stephen Frost <sfrost@snowman.net>
Subject: Re: Backup solution over unreliable network
Date: Fri, 30 Nov 2018 18:12:59 -0500
Message-ID: <f8f3833a-5bae-9bc8-14bb-1e4351395b3f@pgmasters.net> (raw)
In-Reply-To: <9956da38-bcb6-6768-d245-887df9e0fb93@matrix.gatewaynet.com>
References: <fb7e7296-c60e-c2cc-93d5-9c2451e9a2a5@matrix.gatewaynet.com>
<20801752-C311-4F70-9D03-08C5E711645C@mac.com>
<9956da38-bcb6-6768-d245-887df9e0fb93@matrix.gatewaynet.com>
On 11/30/18 1:49 PM, Achilleas Mantzios wrote:
>
> On 30/11/18 8:22 μ.μ., Evan Bauer wrote:
>> Achilleas,
>>
>> I may be over-simplifying your situation, but have you considered
>> breaking the problem into two pieces? First backing up to a local
>> drive and then using rsync to move those files over the unreliable
>> network to the remote site.
>>
>> Like other who have responded, I can heartily recommend pgbackrest.
>> But if the network stinks, then I’d break the problem in two and leave
>> PostgreSQL out of the network equation.
>
> Pretty good idea, but :
>
> 1) those rsync transfers have to be somehow db aware, otherwise lots of
> things might break, checksums, order of WALs, etc. There would be the
> need to write a whole solution and end up ... getting one of the
> established solutions
It's actually perfectly OK to rsync a pgBackRest repository. We've
already done the hard work of interacting with the database and gotten
the backups into a format that can be rsync'd, backed up to tape, with
standard enterprise backups tools, etc.
It is common to backup the pgBackRest repo or individual backups (with
--archive-copy enabled) and we have not seen any issues.
BTW, in this context I expect local means in the same data center, not
on the database host.
> 2) the rsync part would go basically unattended, meaning no smart
> software would be taking care of it, monitoring it, sending alerts, etc.
> Also we had our issues with rsync in the past with unreliable networks
> like getting error messages for which google returns one or no results
> (no pgsql stuff, just system scripts) . No wonder more and more PgSQL
> backup solutions move away from rsync.
I agree that this is a concern -- every process needs to be monitored
and if you can avoid the extra step that would be best. But if things
start getting complicated it might be the simpler option.
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, pgsql-admin@lists.postgresql.org, sfrost@snowman.net
Subject: Re: Backup solution over unreliable network
In-Reply-To: <f8f3833a-5bae-9bc8-14bb-1e4351395b3f@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