pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBackup solution over unreliable network
15+ messages / 4 participants
[nested] [flat]
* Backup solution over unreliable network
@ 2018-11-30 10:17 Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 15+ messages in thread
From: Achilleas Mantzios @ 2018-11-30 10:17 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello,
we've been running our backup solution for the last 5 months to a second site which has an unreliable network connection. We had problems with barman, since it doesn't support backup resume, also no
option to disable the replication slot, in the sense, that it is better to sacrifice the backup rather than fill up the primary with WALs and bring the primary down. Another issue is now supporting
entirely backing up from the secondary. With barman this is not possible, streaming (or archiving) must originate from the primary.So I want to ask two things here :
- Backing up to a remote site over an unreliable channel is a limited use case by itself, it is useful for local PITR restores on specific tables/data, or in case the whole primary suffers a disaster.
Is there any other benefit that would justify building a solution for it?
- I have only read the best reviews about PgBackRest, can PgBackRest address those issues?
Thank you!
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Backup solution over unreliable network
@ 2018-11-30 12:06 Stephen Frost <sfrost@snowman.net>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 1 reply; 15+ messages in thread
From: Stephen Frost @ 2018-11-30 12:06 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin@lists.postgresql.org
Greetings,
* Achilleas Mantzios (achill@matrix.gatewaynet.com) wrote:
> we've been running our backup solution for the last 5 months to a second
> site which has an unreliable network connection. We had problems with
> barman, since it doesn't support backup resume, also no option to disable
> the replication slot, in the sense, that it is better to sacrifice the
> backup rather than fill up the primary with WALs and bring the primary down.
> Another issue is now supporting entirely backing up from the secondary. With
> barman this is not possible, streaming (or archiving) must originate from
> the primary.So I want to ask two things here :
> - Backing up to a remote site over an unreliable channel is a limited use
> case by itself, it is useful for local PITR restores on specific
> tables/data, or in case the whole primary suffers a disaster. Is there any
> other benefit that would justify building a solution for it?
Please don't build your own solution, it's really quite difficult to get
backups done correctly.
> - I have only read the best reviews about PgBackRest, can PgBackRest address those issues?
Glad to hear you've read good reviews about pgbackrest. As for
addressing these issues, pgbackrest has:
- Backup resume
- Max WAL lag (in other words, you can have it simply start throwing WAL
away if it can't archive it, rather than allowing the primary to run
out of disk space)
- Backup using the replica, primairly (note that this, currently,
requires access to the primary, but the bulk of the data comes from
the replica)
- Incremental/differential backup
- Parallel backup/resume and parallel archiving/fetching
- Backup verification- we checksum every file backed up and verify those
checksums on a resume, and we make sure that every WAL file needed to
restore the backup has made it into the archive.
- Delta restore
Which I believe covers most of the use-cases you brought up.
When we first implemented backup using the replica we had concerns
regarding doing a 'full' replica-based backup, and we didn't really see
there being a lot of demand for such a use-case (the replica has access
to the primary in general if it's a streaming replica, after all...),
but we might be open to revisiting that.
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Backup solution over unreliable network
@ 2018-11-30 13:46 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 15+ messages in thread
From: Achilleas Mantzios @ 2018-11-30 13:46 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 30/11/18 2:06 μ.μ., Stephen Frost wrote:
> Greetings,
>
> * Achilleas Mantzios (achill@matrix.gatewaynet.com) wrote:
>> we've been running our backup solution for the last 5 months to a second
>> site which has an unreliable network connection. We had problems with
>> barman, since it doesn't support backup resume, also no option to disable
>> the replication slot, in the sense, that it is better to sacrifice the
>> backup rather than fill up the primary with WALs and bring the primary down.
>> Another issue is now supporting entirely backing up from the secondary. With
>> barman this is not possible, streaming (or archiving) must originate from
>> the primary.So I want to ask two things here :
>> - Backing up to a remote site over an unreliable channel is a limited use
>> case by itself, it is useful for local PITR restores on specific
>> tables/data, or in case the whole primary suffers a disaster. Is there any
>> other benefit that would justify building a solution for it?
> Please don't build your own solution, it's really quite difficult to get
> backups done correctly.
By "building" I meant setting up, nothing fancier :)
>
>> - I have only read the best reviews about PgBackRest, can PgBackRest address those issues?
> Glad to hear you've read good reviews about pgbackrest. As for
> addressing these issues, pgbackrest has:
>
> - Backup resume
> - Max WAL lag (in other words, you can have it simply start throwing WAL
> away if it can't archive it, rather than allowing the primary to run
> out of disk space)
This is just superb! In our case we had the following architecture (now barman is defunct) :
Primary (consistent snapshots with pg_start/stop_backup)+ --> reliable net (archive_command via rsync) --> WAL repository
| (async streaming replication)
| (reliable net)
V
Standby --> unreliable net (barman via method rsync + barman streaming from standby ***) --> remote cloud provider site(barman)
So Primary and Standby are in the same cloud provider over consistent (mostly) network, whereas the barman (remote recovery) site communicates over internet. We would like to keep the old
functionality (or even add a new PgBackRest node in the main cloud provider, so the question is : is there a way for archive-push to two different stanzas? Or delegate the archive-push to work from
the Standby ?
*** newer barman docs (2.5) say this is not supported (wasn't so clear in 2.4)
> - Backup using the replica, primairly (note that this, currently,
> requires access to the primary, but the bulk of the data comes from
> the replica)
> - Incremental/differential backup
> - Parallel backup/resume and parallel archiving/fetching
> - Backup verification- we checksum every file backed up and verify those
> checksums on a resume, and we make sure that every WAL file needed to
> restore the backup has made it into the archive.
> - Delta restore
>
> Which I believe covers most of the use-cases you brought up.
>
> When we first implemented backup using the replica we had concerns
> regarding doing a 'full' replica-based backup, and we didn't really see
> there being a lot of demand for such a use-case (the replica has access
> to the primary in general if it's a streaming replica, after all...),
> but we might be open to revisiting that.
Thank you a lot! We'll definitely consider PgBackRest.
> Thanks!
>
> Stephen
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Backup solution over unreliable network
@ 2018-11-30 15:29 Stephen Frost <sfrost@snowman.net>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 15+ messages in thread
From: Stephen Frost @ 2018-11-30 15:29 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin@lists.postgresql.org
Greetings,
* Achilleas Mantzios (achill@matrix.gatewaynet.com) wrote:
> On 30/11/18 2:06 μ.μ., Stephen Frost wrote:
> >>- I have only read the best reviews about PgBackRest, can PgBackRest address those issues?
> >Glad to hear you've read good reviews about pgbackrest. As for
> >addressing these issues, pgbackrest has:
> >
> >- Backup resume
> >- Max WAL lag (in other words, you can have it simply start throwing WAL
> > away if it can't archive it, rather than allowing the primary to run
> > out of disk space)
>
> This is just superb! In our case we had the following architecture (now barman is defunct) :
>
> Primary (consistent snapshots with pg_start/stop_backup)+ --> reliable net (archive_command via rsync) --> WAL repository
> | (async streaming replication)
> | (reliable net)
> V
> Standby --> unreliable net (barman via method rsync + barman streaming from standby ***) --> remote cloud provider site(barman)
>
> So Primary and Standby are in the same cloud provider over consistent
> (mostly) network, whereas the barman (remote recovery) site communicates
> over internet. We would like to keep the old functionality (or even add a
> new PgBackRest node in the main cloud provider, so the question is : is
> there a way for archive-push to two different stanzas? Or delegate the
> archive-push to work from the Standby ?
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.
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.
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.
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Backup solution over unreliable network
@ 2018-11-30 16:24 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Stephen Frost <sfrost@snowman.net>
1 sibling, 1 reply; 15+ messages in thread
From: Achilleas Mantzios @ 2018-11-30 16:24 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello Stephen!
On 30/11/18 5:29 μ.μ., Stephen Frost wrote:
> Greetings,
>
> * Achilleas Mantzios (achill@matrix.gatewaynet.com) wrote:
>> On 30/11/18 2:06 μ.μ., Stephen Frost wrote:
>>>> - I have only read the best reviews about PgBackRest, can PgBackRest address those issues?
>>> Glad to hear you've read good reviews about pgbackrest. As for
>>> addressing these issues, pgbackrest has:
>>>
>>> - Backup resume
>>> - Max WAL lag (in other words, you can have it simply start throwing WAL
>>> away if it can't archive it, rather than allowing the primary to run
>>> out of disk space)
>> This is just superb! In our case we had the following architecture (now barman is defunct) :
>>
>> Primary (consistent snapshots with pg_start/stop_backup)+ --> reliable net (archive_command via rsync) --> WAL repository
>> | (async streaming replication)
>> | (reliable net)
>> V
>> Standby --> unreliable net (barman via method rsync + barman streaming from standby ***) --> remote cloud provider site(barman)
>>
>> So Primary and Standby are in the same cloud provider over consistent
>> (mostly) network, whereas the barman (remote recovery) site communicates
>> over internet. We would like to keep the old functionality (or even add a
>> new PgBackRest node in the main cloud provider, so the question is : is
>> there a way for archive-push to two different stanzas? Or delegate the
>> archive-push to work from the Standby ?
> 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?
>
> 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).
> 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.
Yes the idea is to protect the primary and do all stuff to the secondary.
> Thanks!
Thank you!!
>
> Stephen
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Backup solution over unreliable network
@ 2018-11-30 16:41 David Steele <david@pgmasters.net>
parent: Stephen Frost <sfrost@snowman.net>
1 sibling, 0 replies; 15+ messages in thread
From: David Steele @ 2018-11-30 16:41 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin@lists.postgresql.org
On 11/30/18 10:29 AM, Stephen Frost wrote:
> * Achilleas Mantzios (achill@matrix.gatewaynet.com) wrote:
>> On 30/11/18 2:06 μ.μ., Stephen Frost wrote:
>>>> - I have only read the best reviews about PgBackRest, can PgBackRest address those issues?
>>> Glad to hear you've read good reviews about pgbackrest. As for
<...>
> 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.
> 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.
The issue here is ensuring that only one system is writing to the
repository at a time. This is easy enough if there is a dedicated
repo-host but is much harder if the repo is on something like S3 or NFS.
Using independent repos might work, but we'd need a way to ensure the
configuration doesn't get broken.
Regards,
--
-David
david@pgmasters.net
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Backup solution over unreliable network
@ 2018-11-30 16:50 David Steele <david@pgmasters.net>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 15+ messages in thread
From: David Steele @ 2018-11-30 16:50 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; pgsql-admin@lists.postgresql.org
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.
>> 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,
--
-David
david@pgmasters.net
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Backup solution over unreliable network
@ 2018-11-30 18:22 Evan Bauer <evanbauer@mac.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 1 reply; 15+ messages in thread
From: Evan Bauer @ 2018-11-30 18:22 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin@lists.postgresql.org
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.
Cheers,
Evan
Sent from my iPhone
> On Nov 30, 2018, at 05:17, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
>
> Hello,
> we've been running our backup solution for the last 5 months to a second site which has an unreliable network connection. We had problems with barman, since it doesn't support backup resume, also no option to disable the replication slot, in the sense, that it is better to sacrifice the backup rather than fill up the primary with WALs and bring the primary down. Another issue is now supporting entirely backing up from the secondary. With barman this is not possible, streaming (or archiving) must originate from the primary.So I want to ask two things here :
> - Backing up to a remote site over an unreliable channel is a limited use case by itself, it is useful for local PITR restores on specific tables/data, or in case the whole primary suffers a disaster. Is there any other benefit that would justify building a solution for it?
> - I have only read the best reviews about PgBackRest, can PgBackRest address those issues?
>
> Thank you!
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Backup solution over unreliable network
@ 2018-11-30 18:49 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Evan Bauer <evanbauer@mac.com>
0 siblings, 1 reply; 15+ messages in thread
From: Achilleas Mantzios @ 2018-11-30 18:49 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
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.
Am I missing something or exaggerating?
>
> Cheers,
>
> Evan
>
> Sent from my iPhone
>
>> On Nov 30, 2018, at 05:17, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
>>
>> Hello,
>> we've been running our backup solution for the last 5 months to a second site which has an unreliable network connection. We had problems with barman, since it doesn't support backup resume, also no option to disable the replication slot, in the sense, that it is better to sacrifice the backup rather than fill up the primary with WALs and bring the primary down. Another issue is now supporting entirely backing up from the secondary. With barman this is not possible, streaming (or archiving) must originate from the primary.So I want to ask two things here :
>> - Backing up to a remote site over an unreliable channel is a limited use case by itself, it is useful for local PITR restores on specific tables/data, or in case the whole primary suffers a disaster. Is there any other benefit that would justify building a solution for it?
>> - I have only read the best reviews about PgBackRest, can PgBackRest address those issues?
>>
>> Thank you!
>>
>> --
>> Achilleas Mantzios
>> IT DEV Lead
>> IT DEPT
>> Dynacom Tankers Mgmt
>>
>>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Backup solution over unreliable network
@ 2018-11-30 18:59 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: David Steele <david@pgmasters.net>
1 sibling, 0 replies; 15+ messages in thread
From: Achilleas Mantzios @ 2018-11-30 18:59 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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,
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Backup solution over unreliable network
@ 2018-11-30 23:12 David Steele <david@pgmasters.net>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 15+ messages in thread
From: David Steele @ 2018-11-30 23:12 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; pgsql-admin@lists.postgresql.org; +Cc: evanbauer@mac.com, Stephen Frost <sfrost@snowman.net>
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Backup solution over unreliable network
@ 2018-12-01 05:47 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: David Steele <david@pgmasters.net>
0 siblings, 0 replies; 15+ messages in thread
From: Achilleas Mantzios @ 2018-12-01 05:47 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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,
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Backup solution over unreliable network
@ 2019-01-16 08:52 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: David Steele <david@pgmasters.net>
1 sibling, 1 reply; 15+ messages in thread
From: Achilleas Mantzios @ 2019-01-16 08:52 UTC (permalink / raw)
To: David Steele <david@pgmasters.net>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Backup solution over unreliable network
@ 2019-01-16 17:18 David Steele <david@pgmasters.net>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 15+ messages in thread
From: David Steele @ 2019-01-16 17:18 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; pgsql-admin@lists.postgresql.org, Stephen Frost <sfrost@snowman.net>
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Backup solution over unreliable network
@ 2019-01-16 19:26 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: David Steele <david@pgmasters.net>
0 siblings, 0 replies; 15+ messages in thread
From: Achilleas Mantzios @ 2019-01-16 19:26 UTC (permalink / raw)
To: David Steele <david@pgmasters.net>; pgsql-admin@lists.postgresql.org, Stephen Frost <sfrost@snowman.net>
On 16/1/19 7:18 μ.μ., David Steele wrote:
> 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.
Thanks!
>
> 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.
My intention was just to verify I am inline with the docs and your prior
emails!
>
> Regards,
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2019-01-16 19:26 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-11-30 10:17 Backup solution over unreliable network Achilleas Mantzios <achill@matrix.gatewaynet.com>
2018-11-30 12:06 ` Stephen Frost <sfrost@snowman.net>
2018-11-30 13:46 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2018-11-30 15:29 ` Stephen Frost <sfrost@snowman.net>
2018-11-30 16:24 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2018-11-30 16:50 ` David Steele <david@pgmasters.net>
2018-11-30 18:59 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2019-01-16 08:52 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2019-01-16 17:18 ` David Steele <david@pgmasters.net>
2019-01-16 19:26 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2018-11-30 16:41 ` David Steele <david@pgmasters.net>
2018-11-30 18:22 ` Evan Bauer <evanbauer@mac.com>
2018-11-30 18:49 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2018-11-30 23:12 ` David Steele <david@pgmasters.net>
2018-12-01 05:47 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
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