pg_basebackup + incremental base backups

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

Greetings,

* Christopher Pereira (kripper@imatronix.cl) 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

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?

Greetings,

* Christopher Pereira (kripper@imatronix.cl) 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

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

* Christopher Pereira (kripper@imatronix.cl) 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.

Greetings,

* Christopher Pereira (kripper@imatronix.cl) wrote:

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

* Christopher Pereira (kripper@imatronix.cl) 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

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.

Greetings,

* Christopher Pereira (kripper@imatronix.cl) 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

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.

Greetings,

* Christopher Pereira (kripper@imatronix.cl) 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

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

Greetings,

* Christopher Pereira (kripper@imatronix.cl) wrote:

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.

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.

... This is exactly what pg_rewind is for, so I'm not really sure what
you're asking about here.

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

That isn't really a meaningful or useful thing to consider.

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)?

Yes.

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

Yes, if you use a repo then you can use pgbackrest's incremental and
delta features.

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?

If you want to minimize network traffic then probably using either
incremental backups or delta restore would make the most sense.

Thanks,

Stephen