Re: Problems with postgres online backup - restore

Gerhard Wiesinger wrote:
> Hello,
>
> OK, what's then the difference doing a pg_start_backup() or just doing
> the backup?

pg_start_backup() forces a checkpoint (and logs a label for your backup
too).

> Isn't that a problem that the datablock are very inconsistent, even so
> inconsistent that they are corrupt:
>
> E.g. A part of a datablock is written when e.g. the file is tarred. =>
> Datablock on backup is corrupt => An then even the WAL can't be applied.
>
> Why does it work correctly? Or is there some design problem?

It works because the WAL doesn't hold a list of row updates ("update row
12345 set field 4 = true") but block updates. Any update to a disk block
is recorded - table or index. The WAL doesn't really know anything about
tables, columns, primary keys etc - just disk blocks.

One small optimisation is that the first time a block is touched after a
checkpoint the value of the whole block is written to WAL and after that
only updates.

So - if you have a checkpointed system (all updates guaranteed written
to disk) and a complete set of WAL files from that point on you can
always recreate the writes to any point in time after that.

http://www.postgresql.org/docs/8.3/static/continuous-archiving.html

--
Richard Huxton
Archonet Ltd