Re: Incremental / Level -1 backup in PG

On Wed, Mar 22, 2017 at 9:40 AM, rakeshkumar464
<rakeshkumar464(at)outlook(dot)com> wrote:
> basebackup + WAL archive lets you do just exactly this.
.....
> Yes John I do know about using WAL archive. IMO that will not be as fast as
> restoring using the incremental backup.

That's an opinion, have you tried measuring? Because normally I've found that

1.- Incremental backups are slow and impose a greater runtime penalty
on the system than log-change-archiving methods.

2.- Incremental restores are not that fast.

> Eg:
> It is common to take a full backup on weekends and incremental on
> weeknights. If we have to restore
> upto Thu afternoon, which one do you think will be faster :-
>
> 1 - Restore from basebackup.
> 2 - Restore from wed night backup
> 3 - Apply WAL logs after wed night backup until the time we want to restore.

You are assuming your backup product does direct-diff to base. Those
are gonna be costly when friday arrives.

> vs
> 1 - Restore from basebackup
> 2 - Apply WAL logs from weekend until the time we want to restore.

> If first choice is lot faster in Oracle,DB2,

Is it really testable / a lot faster ? ( bear in mind if a product
just supports one strategy there is a huge interest in telling it is
the faster one )

> I have reasons to believe that
> the same should be true for PG also. But as someone explained, the PG
> technology can not support this.

I fear incremental backup capabilities will make postgres slower.

Anyway, with base backup + wal archive you always have the option of
making incremental. Just start a recovery on the backup each time you
receive a wal segment wal and you are done. In fact, you can treat a
replication slave as a very low lag backup.

Francisco Olarte.