From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Backup solution over unreliable network
Date: Sat, 1 Dec 2018 07:47:16 +0200
Message-ID: <7f4ab880-d449-19bb-a74e-30ace2f42a49@matrix.gatewaynet.com> (raw)
In-Reply-To: <f8f3833a-5bae-9bc8-14bb-1e4351395b3f@pgmasters.net>
References: <fb7e7296-c60e-c2cc-93d5-9c2451e9a2a5@matrix.gatewaynet.com>
<20801752-C311-4F70-9D03-08C5E711645C@mac.com>
<9956da38-bcb6-6768-d245-887df9e0fb93@matrix.gatewaynet.com>
<f8f3833a-5bae-9bc8-14bb-1e4351395b3f@pgmasters.net>
On 1/12/18 1:12 π.μ., David Steele wrote:
> 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.
Great info!
>
>> 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.
All well noted! Thank you!
>
> Regards,
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: achill@matrix.gatewaynet.com, pgsql-admin@lists.postgresql.org
Subject: Re: Backup solution over unreliable network
In-Reply-To: <7f4ab880-d449-19bb-a74e-30ace2f42a49@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