Re: Incomplete description of pg_start_backup?

On 24.05.2013 14:33, Dmitry Koterov wrote:
> I don't get still.
>
> Suppose we have a data file with blocks with important (non-empty) data:
>
> A B C D
>
> 1. I call pg_start_backup().
> 2. Tar starts to copy A block to the destination archive...
> 3. During this copying, somebody removes data from a table which is
> situated in B block. So this data is a subject for vacuuming, and the block
> is marked as a free space.
> 4. Somebody writes data to a table, and this data is placed to a free space
> - to B block. This is also added to the WAL log (so the data is stored at 2
> places: at B block and at WAL).
> 5. Tar (at last!) finishes copying of A block and begins to copy B block.
> 6. It finishes, then it copies C and D to the archive too.
> 7. Then we call pg_stop_backup() and also archive collected WAL (which
> contains the new data of B block as we saw above).
>
> The question is - *where is the OLD data of B block in this scheme?* Seems
> it is NOT in the backup!

Correct.

> So it cannot be restored.

Right, the old data on block B is gone. The backup is fine, the old data
on block B is is not needed to recover the backup.

> (And, in case when we never overwrite blocks between
> pg_start_backup...pg_stop_backup, but always append the new data, it
> is not a problem.) Seems to me this is not documented at all! That is
> what my initial e-mail about.

When you restore the backup, the database is restored to the state it
was when pg_stop_backup() was called. What did you expect?

> (I have one hypothesis on that, but I am not sure. Here is it: does vacuum
> saves ALL deleted data of B block to WAL on step 3 prior deletion? If yes,
> it is, of course, a part of the backup. But it wastes space a lot...)

Nope, it doesn't do that.

- Heikki