Re: Problems with postgres online backup - restore - Mailing list pgsql-general

From Richard Huxton
Subject Re: Problems with postgres online backup - restore
Date
Msg-id 4A445FA2.1030103@archonet.com
Whole thread Raw
In response to Re: Problems with postgres online backup - restore  (Gerhard Wiesinger <lists@wiesinger.com>)
Responses Re: Problems with postgres online backup - restore
List pgsql-general
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

pgsql-general by date:

Previous
From: CM J
Date:
Subject: Re: [JDBC] Postgres 8.3.7 -- EOF on client connection
Next
From: Sam Wun
Date:
Subject: Can't start postgresql 8.3.7