Re: Issues Outstanding for Point In Time Recovery (PITR)

On Tue, 2002-07-09 at 17:26, Tom Lane wrote:
> Hannu Krosing <hannu(at)tm(dot)ee> writes:
> > 1) record the lowest uncommitted transaction number (LUTN) , this may
> > have problems with wraparound, but I guess they are solvable. Disllow
> > VACUUM. Do a CHECKPOINT ('alter database begin backup')
> > 3) make a file-level (.tar) backup of data directory.
> > 4) Allow VACUUM. ('alter database end backup')
>
> Transactions don't necessarily commit in sequence number order, so the
> concept of LUTN seems meaningless.

Not quite. It is the most simple way to be sure that if we invalidate
all transactions >= than it we get back to a fairly recent
Point-In-Time.

The real solution would of course be to remember all committed
transactions at this PIT, which can probably be done by remembering LUTN
and all individual committed transactions > LUTN

> Why is it necessary (or even good) to disallow VACUUM?

So that it would be possible to resurrect these tuples that have been
deleted/updated during disk-level backup.

I would like better the ability to tell VACUUM not to touch tuples where
deleting transaction number >= LUTN . IIRC the original postgres was
able to do that.

> I really dislike
> a design that allows the DBA to cripple the database by forgetting the
> last step in a (long) process.

There are several ways around it.

1. do it in a script, that will not forget.

2. Closing the session that did 'alter database begin backup' session
could do it automatically, but this would make the backup script
trickier.

3. VACUUM should not block but report a warning about being restricted
from running.

4. database can be instructed to send a message to DBA's pager if it has
been in 'begin backup' state too long ;)

----------------
Hannu