Re: pg_basebackup + incremental base backups

Michael Paquier <[email protected]> Sat, 30 Nov 2019 11:34:30 +0900

Newsgroups	gmane.comp.db.postgresql.general
Message-ID	<[email protected]>

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 (application/pgp-signature, 833 B)