pg_basebackup + incremental base backups

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

pg_basebackup + incremental base backups

Christopher Pereira
Our stream replication slave server got out of sync so we need to base
backup again.

In case of big databases, can we do incremental backups with pg_basebackup?

Is there any alternative?

Here was a proposal: https://wiki.postgresql.org/wiki/Incremental_backup




Reply | Threaded
Open this post in threaded view
|

Re: pg_basebackup + incremental base backups

Michael Paquier-2
On Fri, Nov 29, 2019 at 04:57:11PM -0300, Christopher Pereira wrote:
> Our stream replication slave server got out of sync so we need to base
> backup again.
>
> In case of big databases, can we do incremental backups with pg_basebackup?

I know of two ways to define such backups, one being actually
incremental and the other differential:
- In the shape of a range of WAL segments, which is incremental.  A
full base backup is taken, and then by backing up periodically a range
of WAL segments to make sure that you are able to recover up to the
point you are looking for.  Postgres core can help to do that by
itself.
- Using a combination of full backups and differential backups
(differential), the latter containing only (well, mostly) relation
pages which have changed since the last full backup or differential
backup.  When restoring, you then need to merge the last full backup
and one or more differential backups, followed by a replay of WAL
segments up to the point you are willing to recover to.  In this case
some external tools offer solutions to that problem: pgBackRest and
pg_rman are two I know of.

Other backup solutions on top of the two cited above, like barman, all
have implementations to handle the first type of incremental backup.

> Is there any alternative?
>
> Here was a proposal: https://wiki.postgresql.org/wiki/Incremental_backup

Yeah, nothing has been done in uptream though in this area.  That's
more about differential backups.
--
Michael

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: pg_basebackup + incremental base backups

Stephen Frost
In reply to this post by Christopher Pereira
Greetings,

* Christopher Pereira ([hidden email]) wrote:
> Our stream replication slave server got out of sync so we need to base
> backup again.

If you do WAL archiving instead of depending on the WAL to exist on the
primary then a replica can catch up using WAL.  Having a WAL archive
also means you can do point-in-time-recovery for any point.

> In case of big databases, can we do incremental backups with pg_basebackup?

pg_basebackup doesn't support incremental backups, though there's been
discussion of adding some kind of support for it, check -hackers if
you're curious.

> Is there any alternative?

There's a few different alternatives for PG backup software, some of
which support incremental backups and restores.  I'm personally involved
with pgbackrest- https://pgbackrest.org though there's other options out
there also (wal-g, barman, and more).

Thanks,

Stephen

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: pg_basebackup + incremental base backups

Christopher Pereira

>> In case of big databases, can we do incremental backups with pg_basebackup?
> pg_basebackup doesn't support incremental backups, though there's been
> discussion of adding some kind of support for it, check -hackers if
> you're curious.
>
>> Is there any alternative?
> There's a few different alternatives for PG backup software, some of
> which support incremental backups and restores.  I'm personally involved
> with pgbackrest- https://pgbackrest.org though there's other options out
> there also (wal-g, barman, and more).
>
> Thanks,
>
> Stephen

Hi Stephen,

We tried pgbackrest as a solution to rebuild the standby cluster, by
making a differential backup of the primary cluster.

But it seems that pgbackrest is only able to rebuild the standby cluster
by restoring an existing backup from a repository cluster, and not
directly from the primary cluster.

We are afraid that creating a backup on the repository cluster is an
additional step that could be avoided.

Is there some way to rebuild the standby cluster by doing a differential
backup of the primary cluster directly?




Reply | Threaded
Open this post in threaded view
|

Re: pg_basebackup + incremental base backups

Stephen Frost
Greetings,

* Christopher Pereira ([hidden email]) wrote:

> >>In case of big databases, can we do incremental backups with pg_basebackup?
> >pg_basebackup doesn't support incremental backups, though there's been
> >discussion of adding some kind of support for it, check -hackers if
> >you're curious.
> >
> >>Is there any alternative?
> >There's a few different alternatives for PG backup software, some of
> >which support incremental backups and restores.  I'm personally involved
> >with pgbackrest- https://pgbackrest.org though there's other options out
> >there also (wal-g, barman, and more).
>
> We tried pgbackrest as a solution to rebuild the standby cluster, by making
> a differential backup of the primary cluster.
>
> But it seems that pgbackrest is only able to rebuild the standby cluster by
> restoring an existing backup from a repository cluster, and not directly
> from the primary cluster.
Yes, pgbackrest is a full solution for backups, not just a clone sync
tool.

> We are afraid that creating a backup on the repository cluster is an
> additional step that could be avoided.

Ideally you'd be taking regular backups and therefore would have one to
use to perform the restore (or delta restore if you're trying to catch
up a replica that's fallen behind).  Note that with pgbackrest, you
don't need to take a *new* backup, if you're already using it for your
regular backups- just do a delta restore and pgbackrest will
automatically pick the latest backup (of whatever type) and will restore
using it.

> Is there some way to rebuild the standby cluster by doing a differential
> backup of the primary cluster directly?

We've contemplated adding support for something like this to pgbackrest,
since all the pieces are there, but there hasn't been a lot of demand
for it and it kind of goes against the idea of having a proper backup
solution, really..  It'd also create quite a bit of load on the primary
to checksum all the files to do the comparison against what's on the
replica that you're trying to update, so not something you'd probably
want to do a lot more than necessary.

Thanks,

Stephen

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: pg_basebackup + incremental base backups

Christopher Pereira

On 21-May-20 08:43, Stephen Frost wrote:

> * Christopher Pereira ([hidden email]) wrote:
>
> [...]
>> Is there some way to rebuild the standby cluster by doing a differential
>> backup of the primary cluster directly?
> We've contemplated adding support for something like this to pgbackrest,
> since all the pieces are there, but there hasn't been a lot of demand
> for it and it kind of goes against the idea of having a proper backup
> solution, really..  It'd also create quite a bit of load on the primary
> to checksum all the files to do the comparison against what's on the
> replica that you're trying to update, so not something you'd probably
> want to do a lot more than necessary.

We have backups of the whole server and only need a efficient way to
rebuild the hot-standby cluster when pg_rewind is not able to do so.

I agree with your concerns about the increased load on the primary
server, but this rebuilding process would only be done in case of
emergency or during low load hours.

pg_basebackup works fine but does not support differential/incremental
backups which is a blocker.

Do you know any alternative software that is able to rebuild the standby
PG data dir using rsync or similar while the primary is still online?

It seems a simple pg_start_backup + rsync + pg_stop_backup (maybe
combined with a LVM snapshot) would do, but we would prefer to use some
existing tool.

We just tried barman, but it also seems to require a restore from the
backup before being able to start the standby server (?), and we are
afraid this would require double storage, IO and time for rebuilding the
standby cluster.

Thanks.



Reply | Threaded
Open this post in threaded view
|

Re: pg_basebackup + incremental base backups

Stephen Frost
Greetings,

* Christopher Pereira ([hidden email]) wrote:

> On 21-May-20 08:43, Stephen Frost wrote:
> >* Christopher Pereira ([hidden email]) wrote:
> >>Is there some way to rebuild the standby cluster by doing a differential
> >>backup of the primary cluster directly?
> >We've contemplated adding support for something like this to pgbackrest,
> >since all the pieces are there, but there hasn't been a lot of demand
> >for it and it kind of goes against the idea of having a proper backup
> >solution, really..  It'd also create quite a bit of load on the primary
> >to checksum all the files to do the comparison against what's on the
> >replica that you're trying to update, so not something you'd probably
> >want to do a lot more than necessary.
>
> We have backups of the whole server and only need a efficient way to rebuild
> the hot-standby cluster when pg_rewind is not able to do so.
Personally, I find myself more confident in what pgbackrest does to
remaster a former primary (using a delta restore), but a lot of that
really comes down to the question of: why did the primary fail?  If you
don't know that, I really wouldn't recommend using pg_rewind.

> I agree with your concerns about the increased load on the primary server,
> but this rebuilding process would only be done in case of emergency or
> during low load hours.
>
> pg_basebackup works fine but does not support differential/incremental
> backups which is a blocker.

pg_basebackup is missing an awful lot of other things- managing of
backup rotation, WAL expiration, the ability to parallelize, encryption
support, ability to push backups/fetch backups to/from cloud storage
solutions, ability to resume from failed backups, delta restore (which
is more-or-less what you're asking for), parallel archiving/fetching of
WAL..

> Do you know any alternative software that is able to rebuild the standby PG
> data dir using rsync or similar while the primary is still online?

pgbackrest can certainly rebuild the standby, if you're using it for
backups, and do so very quickly thanks to delta restore and it's
parallelism.  I'm not aware of anything that does exactly what you're
looking for.

> It seems a simple pg_start_backup + rsync + pg_stop_backup (maybe combined
> with a LVM snapshot) would do, but we would prefer to use some existing
> tool.

I'd strongly recommend that you use an existing tool, there's an awful
lot of complications and you absolutely can *not* use rsync for that
unless you are doing it with checksums enabled, and even then it's
complicated- you probably don't want to sync across unlogged tables but
it's not easy to exclude those, or temp files/tables, you have to make
sure to manage the WAL properly, ensure that the appropriate information
makes it into the backup_label (you shouldn't be using exclusive backup
because a reboot of the primary at the wrong time will result in PG not
starting up on the primary...), etc, etc.

> We just tried barman, but it also seems to require a restore from the backup
> before being able to start the standby server (?), and we are afraid this
> would require double storage, IO and time for rebuilding the standby
> cluster.

I really think you should reconsider whatever backup solution you're
using today and rather than keeping it independent, make it part of the
solution to rebuilding replicas.

Maybe it isn't clear, so I'll try to explain- pgbackrest, if you use it
for your backups, will be able to restore over top of an existing PG
cluster, updating only those files which are different from what's in
the backup (based on checksums that it calculates), and is able to do so
in parallel, and then you can replay WAL from your pgbackrest repo,
right up until the replica is able to reconnect to the primary and
resume replaying WAL.  It's a pretty common approach and is supported by
HA solutions like patroni.

Thanks,

Stephen

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: pg_basebackup + incremental base backups

Christopher Pereira
In reply to this post by Stephen Frost

We've contemplated adding support for something like this to pgbackrest,
since all the pieces are there, but there hasn't been a lot of demand
for it and it kind of goes against the idea of having a proper backup
solution, really..  It'd also create quite a bit of load on the primary
to checksum all the files to do the comparison against what's on the
replica that you're trying to update, so not something you'd probably
want to do a lot more than necessary.

Ok, we want to use pgbackrest to rebuild a standby that has fallen behind (where pg_rewind won't work). After reading the docs, we believe we should use this setup:

a) Primary host: primary cluster

b) Repository host: needed for rebuilding the standby (and having PITR as bonus).

c) Standby host: standby cluster

Some questions:

1) The standby will use streaming replication and will be in sync until someday something funny happens and both standby and repository get out of sync with the primary.
Now, to rebuild the standby first we will have to create a new backup transferring the data from primary -> repository, right?
Wouldn't this also have a load impact on the primary cluster?

2) In the user guide section 17.3 is explained how to create a "pg-standby host" to replicate the data from the repository host.
And in section 17.4 is explained how to setup Streaming Replication to replicate the data from the primary host.
Do 17.3 and 17.4 work together so that the data is replicated from the repository and then streamed from the primary?

3) Before being able to rebuild the standby cluster, would we first need to update the backup on the repository (backup from primary -> repository) in order for streaming replication to work (from primary -> standby)?

4) Once the backup on the repository is ready, what are the chances that streaming replication from primary to standby won't work because they got out of sync again?

5) Could we just work with 2 hosts (primary and standby) instead of 3?
FAQ section 8 says the repository shouldn't be on the same host as the standby and having it on the primary doesn't make much sense because if the primary host is down we won't have access to the backup.

It would be ideal to have the repository on the standby host and taking good care of the configurations. What exactly should be cared of for this setup to be safe?

I'm afraid I'm not understanding very well the pgbackrest design or how to use it efficiently to rebuild a standby cluster that got out of sync.

Reply | Threaded
Open this post in threaded view
|

Re: pg_basebackup + incremental base backups

Stephen Frost
Greetings,

* Christopher Pereira ([hidden email]) wrote:

> Ok, we want to use pgbackrest to *rebuild a standby that has fallen behind*
> (where pg_rewind won't work). After reading the docs, we believe we should
> use this setup:
>
> a) Primary host: primary cluster
>
> b) Repository host: needed for rebuilding the standby (and having PITR as
> bonus).
>
> c) Standby host: standby cluster
That would work.

> 1) The standby will use streaming replication and will be in sync until
> someday something funny happens and both standby and repository get out of
> sync with the primary.

Having failures in both the standby and repo would have to be something
very 'funny' indeed..

> Now, to rebuild the standby first we will have to create a new backup
> transferring the data from *primary -> repository*, right?

If the repo is entirely gone *and* the standby server is broken, then,
yes, you'd have to do a new backup into the repo and then restore that.

> Wouldn't this also have a load impact on the primary cluster?

Yes.

The distinction I was trying to make earlier is that if the standby
server gets messed up in any shape, and the repo/repo-server is still
operating correctly, then you can perform a pgbackrest delta restore
whereby the standby is rebuilt from the last backup that was performed.
That process only involves the repo server and the standby server, there
wouldn't be any load on the primary, and further, only those files which
are different on the standby server vs. the last backup in the repo
would be copied, minimizing bandwidth between the two.

> 2) In the user guide section 17.3 is explained how to create a "pg-standby
> host" to replicate the data *from the repository host*.
> And in section 17.4 is explained how to setup Streaming Replication to
> replicate the data *from the primary host*.
> Do 17.3 and 17.4 work together so that the data is *replicated from the
> repository* and then *streamed from the primary*?

It's more of an either-or.  That is, if you have a pgbackrest repo, and
you configure PostgreSQL to have a restore_command which will fetch WAL
from the repo *and* you configure PostgreSQL to have a primary_conninfo
line to connect to an upstream primary and stream WAL, then you give PG
two ways to get the WAL and it'll try one and then the other.  If either
are able to provide the necessary WAL, then the replica will replay the
WAL until it gets to the end of all available WAL, at which point it
should be able to connect to the primary and stream WAL.

Should the replica ever fall behind and the primary no longer has the
WAL that the replica needs, it'll automatically go to the repo to fetch
that WAL.  Only if the WAL is no longer available from either the
primary or the WAL repo is the replica so far behind that it would need
a delta restore from a backup to be caught back up.

> 3) Before being able to rebuild the standby cluster, would we first need to
> update the backup on the repository (backup from primary -> repository) in
> order for streaming replication to work (from primary -> standby)?

This isn't required, no.  Doing so, however, perhaps using a pgbackrest
incremental backup, would reduce the amount of WAL the replica would
need to replay to get caught back up with the primary though.

> 4) Once the backup on the repository is ready, what are the chances that
> streaming replication from primary to standby won't work because they got
> out of sync again?

That really shouldn't be possible.  I'm very curious as to exactly what
happened that resulted in your primary/replica being 'out of sync', as
you say.

> 5) Could we just work with 2 hosts (primary and standby) instead of 3?
> FAQ section 8 says the repository shouldn't be on the same host as the
> standby and having it on the primary doesn't make much sense because if the
> primary host is down we won't have access to the backup.

It's possible but it's really not recommended, particularly if the idea
is to have the primary/replica providing high availability.  When you
are setting up such an HA solution, you *really* want to have your
configurations and such be symmetric between the two (and pgbackrest
makes this easy for you, when you have a repo host, by allowing you to
configure both in the pgbackrest config file and pgbackrest will figure
out which is primary and which is the replica, and perform the backup
from whichever one you'd prefer to).

In the 2 node configuration you're contemplating, if the replia is down,
the primary won't be able to ship WAL off of the system, resulting in a
WAL buildup on the primary which could lead to a database failure due to
running out of disk space, and further increases risk that the primary
then fails and you have lost both availability *and* whatever had been
still on the primary, potentially going back hours or more.

> It would be ideal to have the repository on the standby host and taking good
> care of the configurations. What exactly should be cared of for this setup
> to be safe?

No, that's really not an ideal configuration.  In such a situation,
there's no way to make the configurations symmetric, so what happens in
a failover?  Do you move the repo over to the old primary once you have
things back up?  That seems unlikely to make sense, so you're left
having to potentially rebuild the old primary and then flip back over to
it, while you're in a pretty rough state regarding your availability as
all of that will take time during which you're at a high risk of the old
standby/now primary going down.

> I'm afraid I'm not understanding very well the pgbackrest design or how to
> use it efficiently to rebuild a standby cluster that got out of sync.

Note that you don't actually have to have another server to be the repo
host- you can backup to s3 or anything which supports s3 (as long as
it's sufficiently compatible... not all are, but they're getting
better) with pgbackrest, and we're adding support for Azure and GCS
before too much longer (looks likely that we'll have Azure support in
the June release, GCS at some point after that).

Thanks,

Stephen

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: pg_basebackup + incremental base backups

Christopher Pereira

On 24-May-20 15:48, Stephen Frost wrote:
> That really shouldn't be possible.  I'm very curious as to exactly what
> happened that resulted in your primary/replica being 'out of sync', as
> you say.

Hi Stephen,

Actually this was more a hypothetical question to find a solution in
case some day one of our standby clusters goes out of sync and we have
to rebuild it having a very big database.
With proper WAL archiving this shouldn't happen but we wanted to be
prepared for this scenario just in case.

We did some tests measuring IO and traffic and are very happy with the
results. We will definitely be adding pgBackRest to our toolchain.

Regarding my initial question, I still believe that the world deserves a
simple direct pg_basebackup replacement even when putting an additional
"repo host" in the middle is a better idea in the long term.

As you said, all the pieces are there and it would be quite easy to
write a new "pg_basebackup_delta" script that could be executed on the
standby host to:

1) setup a pgBackRest repo on the primary host (via SSH)

2) create a backup on the primary host (via SSH)

3) do a delta restore on the standby

Even when the repository on the primary host is only created temporarily
(and require double storage, resources, etc), it may still be worth
considering the traffic that can be saved by doing a delta restore on a
standby host in a different region, right?

Thanks and congratulations for the good work.



Reply | Threaded
Open this post in threaded view
|

Re: pg_basebackup + incremental base backups

Stephen Frost
Greetings,

* Christopher Pereira ([hidden email]) wrote:
> On 24-May-20 15:48, Stephen Frost wrote:
> >That really shouldn't be possible.  I'm very curious as to exactly what
> >happened that resulted in your primary/replica being 'out of sync', as
> >you say.
>
> Actually this was more a hypothetical question to find a solution in case
> some day one of our standby clusters goes out of sync and we have to rebuild
> it having a very big database.

"out of sync" is a bit of an odd concept, but having a replica fall
behind a long way is certainly something that can happen and may require
a rebuild from a backup (or from a new sync off of the primary in some
other way, as you suggest below).  In a situation where there's async
replication happening and you promote a replica to take over, that's
definitely a case where you might also have to rebuild the former
primary.

> With proper WAL archiving this shouldn't happen but we wanted to be prepared
> for this scenario just in case.

Sure, having WAL archiving and good backups is definitely my strong
recommendation for how to address any cases where the replica falls
behind, or the replica is promoted and you want to remaster the former
primary.

> We did some tests measuring IO and traffic and are very happy with the
> results. We will definitely be adding pgBackRest to our toolchain.

Glad to hear that.

> Regarding my initial question, I still believe that the world deserves a
> simple direct pg_basebackup replacement even when putting an additional
> "repo host" in the middle is a better idea in the long term.

Perhaps..

> As you said, all the pieces are there and it would be quite easy to write a
> new "pg_basebackup_delta" script that could be executed on the standby host
> to:
>
> 1) setup a pgBackRest repo on the primary host (via SSH)
>
> 2) create a backup on the primary host (via SSH)
>
> 3) do a delta restore on the standby
>
> Even when the repository on the primary host is only created temporarily
> (and require double storage, resources, etc), it may still be worth
> considering the traffic that can be saved by doing a delta restore on a
> standby host in a different region, right?
So...  There's actually a way to do this with pgbackrest, but it doesn't
support the delta capability.

We take care in pgbackrest to make the repo format for full backups
actually match exactly what a PG cluster would look like, specifically
because we wish to allow users to, if absolutely everything else fails
and pgbackrest is non-functional, rebuild from the repo.  To that point,
we even make sure that command-line tools like gzip, bzip2, openssl,
etc, work with the files we create.

For what you're asking about though, you would do something like:

- Set up a pgbackrest repo on the host you're rebuilding (the replica)
- Disable all repo compression, encryption, et al.
- Enable archive copy, so the WAL for the backup is put into the backup
- Enable parallel jobs
- Set up pgbackrest on the primary with the replica configured as the
  repo host, get WAL archiving working and such.
- Run create-stanza
- Perform a *full* backup, make sure everything works.

Once that's done, you go find the pg_data directory inside the full
backup that you made inside of the pgbackrest repo, copy/move/rsync
(with checksums enabled!) that to where your PG data directory should
be, set up a recovery.conf to point to the primary, maybe also set it up
with a restore_command pointing to that repo (may or may not be needed,
depending) and start up PG.  That should allow PG to start, replay all
of the necessary WAL, and then connect to the primary and start
streaming.

If you have any tablespaces, you'd need to deal with those too, of
course.  If you start moving things out of the pgbackrest repo, you're
corrupting it, naturally, so you wouldn't really want to continue using
it once you've gotten all of this done.

Again, this isn't an approach which I'd generally recommend...  We used
to do it when we were first writing pgbackrest with a ZFS filesystem and
after a backup we'd create a snapshot off of the repo and start PG right
up and then run pg_dump on it and do other checks to make sure it
worked, but have moved to using pgbackrest delta restores instead for
that kind of use-case.

Thanks,

Stephen

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: pg_basebackup + delta base backups

Christopher Pereira
On 26-May-20 10:20, Stephen Frost wrote:
> [...]
>
> "out of sync" is a bit of an odd concept, but having a replica fall
> behind a long way is certainly something that can happen and may require
> a rebuild from a backup (or from a new sync off of the primary in some
> other way, as you suggest below).  In a situation where there's async
> replication happening and you promote a replica to take over, that's
> definitely a case where you might also have to rebuild the former
> primary.

Hi Stepehen,

Yes, a common case with async streaming is when primary (A) goes down
and replica is promoted as a new master (B).
Then A comes back and has some data that was not streamed to B so
pg_rewind is useless.

I wonder if there is some option to just discard this branched data from
A in order to start as a new replica.

I noticed that pg_rewind is useless even when both DBs are identical
(according to pg_dumpall | md5sum).

> [...]
>> As you said, all the pieces are there and it would be quite easy to write a
>> new "pg_basebackup_delta" script that could be executed on the standby host
>> to:
>>
>> 1) setup a pgBackRest repo on the primary host (via SSH)
>>
>> 2) create a backup on the primary host (via SSH)
>>
>> 3) do a delta restore on the standby
>>
>> Even when the repository on the primary host is only created temporarily
>> (and require double storage, resources, etc), it may still be worth
>> considering the traffic that can be saved by doing a delta restore on a
>> standby host in a different region, right?
> So...  There's actually a way to do this with pgbackrest, but it doesn't
> support the delta capability.

If I understood correctly the method you described, you were basically
doing a "backup" between A (primary) and B (repo) and in such a way the
repo is then compatible with the pg_data structure, but without delta
support (ie. transfering the whole database)?

Delta support is critical for VLDBs, so I see two alternatives to
replace pg_basebackup with pgbackrest to rebuild a replica:

1) Create a temporary repo on the primary

2) Create a temporary repo on the replica

All configurations would be undone after the replica has been rebuilt
and both alternatives would be using delta over the wire.
In your opinion, which alternative is better considering network traffic?

Thanks,

Christopher