
> As noted, one of the main problems is knowing where to begin
> in the log.  This can be handled by having backup processing 
> update the control file with the first lsn and log file 
> required.  At the time of the backup, this information is or 
> can be made available.  The control file can be the last file
> added to the tar and can contain information spanning the entire
> backup process.

lsn and logfile number (of latest checkpoints) is already in the control 
file, thus you need control file at start of backup. (To reduce the number 
of logs needed for restore of an online backup you could force a checkpoint
before starting file backup)

You will also need lsn and logfile number after file backup, to know how much 
log needs to at least be replayed to regain a consistent state. 

Andreas



Mon, 8 Jul 2002 14:51:04 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Issues Outstanding for Point In Time Recovery (PITR)
Zeugswetter Andreas SB SD wrote:
> 
> > As noted, one of the main problems is knowing where to begin
> > in the log.  This can be handled by having backup processing
> > update the control file with the first lsn and log file
> > required.  At the time of the backup, this information is or
> > can be made available.  The control file can be the last file
> > added to the tar and can contain information spanning the entire
> > backup process.
> 
> lsn and logfile number (of latest checkpoints) is already in the control
> file, thus you need control file at start of backup. (To reduce the number
> of logs needed for restore of an online backup you could force a checkpoint
> before starting file backup)

Maybe I should have been more clear.  The control file snapshot must 
be taken at backup start (as you mention) but can be stored in cache.
The fields can then be modified as we see fit.  At the end of backup,
we can write this to a temp file and add it to the tar.  Therefore,
as mentioned, the snapshot spans the entire backup process.
 
> You will also need lsn and logfile number after file backup, to know how much
> log needs to at least be replayed to regain a consistent state.

This is a nicety but not a necessity. If you have a backup end log 
record, you just have to enforce that the PIT recovery encounters 
that particular log record on forward recovery.  Once encountered,
you know that you at passed the point of back up end.

Cheers,
Patrick



Mon, 08 Jul 2002 09:10:30 -0400
Patrick Macdonald <patrickm@redhat.com>
Re: Issues Outstanding for Point In Time Recovery (PITR)
I know that in Oracle there are 'alter database begin backup' and 'alter 
database end backup' commands that allow you to script your hot backups 
through a cron job by calling the begin backup command first, then using 
disk backup method of choice and then finally call the end backup command.

--Barry

Patrick Macdonald wrote:

>Zeugswetter Andreas SB SD wrote:
>  
>
>>>As noted, one of the main problems is knowing where to begin
>>>in the log.  This can be handled by having backup processing
>>>update the control file with the first lsn and log file
>>>required.  At the time of the backup, this information is or
>>>can be made available.  The control file can be the last file
>>>added to the tar and can contain information spanning the entire
>>>backup process.
>>>      
>>>
>>lsn and logfile number (of latest checkpoints) is already in the control
>>file, thus you need control file at start of backup. (To reduce the number
>>of logs needed for restore of an online backup you could force a checkpoint
>>before starting file backup)
>>    
>>
>
>Maybe I should have been more clear.  The control file snapshot must 
>be taken at backup start (as you mention) but can be stored in cache.
>The fields can then be modified as we see fit.  At the end of backup,
>we can write this to a temp file and add it to the tar.  Therefore,
>as mentioned, the snapshot spans the entire backup process.
> 
>  
>
>>You will also need lsn and logfile number after file backup, to know how much
>>log needs to at least be replayed to regain a consistent state.
>>    
>>
>
>This is a nicety but not a necessity. If you have a backup end log 
>record, you just have to enforce that the PIT recovery encounters 
>that particular log record on forward recovery.  Once encountered,
>you know that you at passed the point of back up end.
>
>Cheers,
>Patrick
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>
>
>
>  
>





Mon, 08 Jul 2002 09:53:10 -0700
Barry Lind <barry@xythos.com>
Re: Issues Outstanding for Point In Time Recovery (PITR)
On Mon, 2002-07-08 at 21:53, Barry Lind wrote:
> I know that in Oracle there are 'alter database begin backup' and 'alter 
> database end backup' commands that allow you to script your hot backups 
> through a cron job by calling the begin backup command first, then using 
> disk backup method of choice and then finally call the end backup command.

This gave me an idea of a not-too-difficult-to-implement way of doing
consistent online backups (thanks to MVCC it is probably much easier
than Oracle's):


Backup:

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')



Restore:

1) restore the data directory from file-level backup

2) mark all transactions committed after LUTN as aborted, effectively
deleting all tuples inserted and resurrecting those deleted/updated
after start of backups. 

3) make sure that new transaction number is large enough.



PS. It would be nice if our OID-based filenames had some type indicator
in their names - it is usually waste of time and space to backup indexes
and temp tables. The names could be of form
pg_class.relkind:pg_class.relfilenode instead of just
pg_class.relfilenode they are now.


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




09 Jul 2002 11:19:42 +0500
Hannu Krosing <hannu@tm.ee>
Re: Issues Outstanding for Point In Time Recovery (PITR)
Hannu Krosing <hannu@tm.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.

Why is it necessary (or even good) to disallow VACUUM?  I really dislike
a design that allows the DBA to cripple the database by forgetting the
last step in a (long) process.

			regards, tom lane

Tue, 09 Jul 2002 11:26:55 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Issues Outstanding for Point In Time Recovery (PITR) 
On Tue, 2002-07-09 at 17:26, Tom Lane wrote:
> Hannu Krosing <hannu@tm.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



09 Jul 2002 19:27:37 +0200
Hannu Krosing <hannu@tm.ee>
Re: Issues Outstanding for Point In Time Recovery (PITR)