Re: Incremental / Level -1 backup in PG

>> 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.

I assume you are talking about other RDBMS where we can do PITR restore either:

Restore base backup
Apply incremental backup
Rollfoward WAL logs to the point you want to recover
vs
Restore base backup
Rollfoward WAL logs to the point you want to recover

When the PITR is far apart from the time of base backup (far apart as in, let us
say 4 to 5 days), the first approach beats the second approach hands down. This
coming from experience. Reason is simple. In the second approach every transaction
(from the time of base backup) has to applied to roll-foward to PIT. In incremental backup,
a block is only applied once, regardless of how many times it changed after the base backup.

The diff may not be much if PITR is close to the time of base backup.

Note: I have never tried option (1) for PG.

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

You mean costly as in finding more and more blocks changed since weekend. that is
correct. However Oracle keeps track of location of all changed blocks since last base
backup and it helps in quick backup. It does not scan entire tablespace to figure which
blocks changed.

>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 )

Nope. Incremental backup is not the only way to reach PITR until Thu afternoon
from a base backup on Sunday. You can always apply redo logs after restoring
from the base backup.
And yes, it is much faster. We do it to prove to our clients why incremental backup
will benefit them more.