Re: Incrementally Updated Backups

On Sun, Sep 12, 2010 at 7:39 AM, J. Roeleveld <joost(at)antarean(dot)org> wrote:
> On Sunday 12 September 2010 13:32:00 Martijn van Oosterhout wrote:
>> On Sun, Sep 12, 2010 at 12:18:10PM +0200, J. Roeleveld wrote:
>> > How can you ensure the snapshot is in a consistent state if the server is
>> > running?
>> >
>> > If a snapshot is taken between 2 updates in a single transaction, only
>> > half of this transaction is included in the snapshot.
>> > I would never take an LVM (or similar) snapshot of an application that
>> > can't be paused in a way to provide a consistent filesystem.
>>
>> That's the trick, the filesystem is always in a consistant state,
>> otherwise how could a database survive a power failure?
>
> This is something you want to try to avoid.
> Recovery situations are not always reliable.

When hardware doesn't fsync properly, this can be a problem.

>> The trick is WAL, which ensure that changes are logged consistantly and
>> replays them if the database crashes.
>>
>> If you take a snapshot the database will simply startup and replay the
>> log as if the machine crashed at the point. All committed transactions
>> appears anything uncommitted vanishes.
>
> Nice in theory.
> Except backups can not be fully trusted if they rely on database recovery
> mechanics as part of the restore process.

Why?

> How certain can you be that the data you have in your backup will always
> result to being able to recover 100%?

100% certain if you test said restores often. It's not uncommon to
use this method to bring up a slave which you then run pg_dump on to
see if you get any errors.