Re: block-level incremental backup

Greetings,

* Bruce Momjian (bruce@momjian.us) wrote:
> On Mon, Apr 15, 2019 at 09:01:11AM -0400, Stephen Frost wrote:
> > * Robert Haas (robertmhaas@gmail.com) wrote:
> > > Several companies, including EnterpriseDB, NTT, and Postgres Pro, have
> > > developed technology that permits a block-level incremental backup to
> > > be taken from a PostgreSQL server.  I believe the idea in all of those
> > > cases is that non-relation files should be backed up in their
> > > entirety, but for relation files, only those blocks that have been
> > > changed need to be backed up.
> >
> > I love the general idea of having additional facilities in core to
> > support block-level incremental backups.  I've long been unhappy that
> > any such approach ends up being limited to a subset of the files which
> > need to be included in the backup, meaning the rest of the files have to
> > be backed up in their entirety.  I don't think we have to solve for that
> > as part of this, but I'd like to see a discussion for how to deal with
> > the other files which are being backed up to avoid needing to just
> > wholesale copy them.
>
> I assume you are talking about non-heap/index files.  Which of those are
> large enough to benefit from incremental backup?

Based on discussions I had with Andrey, specifically the visibility map
is an issue for them with WAL-G.  I haven't spent a lot of time thinking
about it, but I can understand how that could be an issue.

> > I'm quite concerned that trying to graft this on to pg_basebackup
> > (which, as you note later, is missing an awful lot of what users expect
> > from a real backup solution already- retention handling, parallel
> > capabilities, WAL archive management, and many more... but also is just
> > not nearly as developed a tool as the external solutions) is going to
> > make things unnecessairly difficult when what we really want here is
> > better support from core for block-level incremental backup for the
> > existing external tools to leverage.
>
> I think there is some interesting complexity brought up in this thread.
> Which options are going to minimize storage I/O, network I/O, have only
> background overhead, allow parallel operation, integrate with
> pg_basebackup.  Eventually we will need to evaluate the incremental
> backup options against these criteria.

This presumes that we're going to have multiple competeing incremental
backup options presented, doesn't it?  Are you aware of another effort
going on which aims for inclusion in core?  There's been past attempts
made, but I don't believe there's anyone else currently planning to or
working on something for inclusion in core.

Just to be clear- we're not currently working on one, but I'd really
like to see core provide good support for incremental block-level backup
so that we can leverage when it is there.

Thanks!

Stephen