Re: Incremental / Level -1 backup in PG

John,

* John R Pierce (pierce(at)hogranch(dot)com) wrote:
> On 3/21/2017 5:27 PM, Rakesh Kumar wrote:
> >PG does not have a concept of incremental backup. The way it works in Oracle and other RDBMS is that incremental backup only backups up changed blocks since the last full backup. So if only 10% of blocks changed since the last full backup, incremental backup will be only for 10%.
> >I am wondering whether it is technically feasible to implement it like this:
> >
> >1 - At the time of full backup, note the last modified time of each data file in a repository.
> >2 - Next time when incremental backup runs, for every data file it will check the last modified time of it with the one in the repository to determine whether it has changed since last full backup. If yes, back it up.
> >
> >Now on to restore:
> >
> >1 - First restore full backup.
> >2 - Restore incremental backup.
> >
> >My question: Will it work in PG?
>
> basebackup + WAL archive lets you do just exactly this. you can
> restore to any transaction between when that basebackup was taken,
> and the latest entry in the WAL archive, its referred in the
> documentation as PITR, Point in Time Recovery.

WAL must always be kept for file-level backups, of course, but it does
not allow the kind of incremental backup the OP was suggesting.

It's important to understand that you might start reading a file whose
timestamp is X, read half of it, and then PG starts writing to the first
half of the file, and you finish reading the file, all within the same
second.

A later incremental backup might assume that file hadn't been changed
from the version you have and therefore not back it up. The WAL for the
change which was written by PG would be in the first 'full' backup, but
would not be included in the WAL which is generated during the
incremental backup, leading to a situation where that write would be
lost and you have a corrupted backup.

Do not try to implement an incremental backup solution using
simple/naive tools like rsync with timestamp-based incrementals. It is
not safe.

Thanks!

Stephen