pg_basebackup + incremental base backups

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 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