Thread

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

   Zeugswetter Andreas ADI SD <zeugswettera@spardat.at> — 2002-07-08T12:51:04Z

   > 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
   
   
   
   

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

   Patrick Macdonald <patrickm@redhat.com> — 2002-07-08T13:10:30Z

   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
   
   
   
   

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

   Barry Lind <barry@xythos.com> — 2002-07-08T16:53:10Z

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

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

   Hannu Krosing <hannu@tm.ee> — 2002-07-09T06:19:42Z

   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
   
   
   
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2002-07-09T15:26:55Z

   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
   
   

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

   Hannu Krosing <hannu@tm.ee> — 2002-07-09T17:27:37Z

   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