
> I imagine a daemon extracting redo log entries from WAL segments, 
> asynchronously.  Mixing redo log entries into the WAL allows the WAL 
> to be the only synchronous disk writer in the system, a Good Thing.

This comes up periodically now. WAL currently already has all the info
that would be needed for redo (it actually has to). All that is missing 
is a program, that can take a consistent physical snapshot (as it was after 
a particular checkpoint) and would replay the WAL after a restore of such a 
snapshot. This replay after a consistent snapshot is probably as simple
as making the WAL files available to the standard startup rollforward (redo) 
mechanism, that is already implemented.

Actually it might even be possible to do the WAL redo based on
an inconsistent backup (e.g. done with tar/cpio), but that probably needs 
more thought and testing than above. At the least, the restore would need to 
generate a valid pg_control before starting redo.

Andreas

Wed, 4 Jul 2001 10:37:57 +0200 
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
AW: Re: Backup and Recovery
On Wed, Jul 04, 2001 at 10:37:57AM +0200, Zeugswetter Andreas SB wrote:
> 
> > I imagine a daemon extracting redo log entries from WAL segments, 
> > asynchronously.  Mixing redo log entries into the WAL allows the WAL 
> > to be the only synchronous disk writer in the system, a Good Thing.
> 
> This comes up periodically now. WAL currently already has all the info
> that would be needed for redo (it actually has to). 

The WAL has the information needed to take a binary table image
from the checkpoint state to the last committed transaction.
IIUC, it is meaningless in relation to a pg_dump image.

> All that is missing is a program, that can take a consistent physical
> snapshot (as it was after a particular checkpoint) and would replay
> the WAL after a restore of such a snapshot. This replay after a
> consistent snapshot is probably as simple as making the WAL files
> available to the standard startup rollforward (redo) mechanism, that
> is already implemented.

How would you take a physical snapshot without interrupting database 
operation?  Is a physical/binary snapshot a desirable backup format?  
People seem to want to be able to restore from ASCII dumps.

Also, isn't the WAL format rather bulky to archive hours and hours of?
I would expect high-level transaction redo records to be much more compact;
mixed into the WAL, such records shouldn't make the WAL grow much faster.

Nathan Myers
ncm@zembu.com

Wed, 4 Jul 2001 04:46:20 -0700
ncm@zembu.com (Nathan Myers)
Re: Re: Backup and Recovery
> > All that is missing is a program, that can take a consistent physical
> > snapshot (as it was after a particular checkpoint) and would replay
> > the WAL after a restore of such a snapshot. This replay after a
> > consistent snapshot is probably as simple as making the WAL files
> > available to the standard startup rollforward (redo) mechanism, that
> > is already implemented.
> 
> How would you take a physical snapshot without interrupting database 
> operation?  Is a physical/binary snapshot a desirable backup format?  
> People seem to want to be able to restore from ASCII dumps.
> 
> Also, isn't the WAL format rather bulky to archive hours and hours of?
> I would expect high-level transaction redo records to be much more compact;
> mixed into the WAL, such records shouldn't make the WAL grow much faster.

The page images are not needed and can be thrown away once the page is
completely sync'ed to disk or a checkpoint happens.  

The row images aren't that large.  I think any solution would have to
handle page images and row images differently.


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 4 Jul 2001 11:04:13 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Re: Backup and Recovery
> On Wed, Jul 04, 2001 at 10:37:57AM +0200, Zeugswetter Andreas SB wrote:
> > 
> > > I imagine a daemon extracting redo log entries from WAL segments, 
> > > asynchronously.  Mixing redo log entries into the WAL allows the WAL 
> > > to be the only synchronous disk writer in the system, a Good Thing.
> > 
> > This comes up periodically now. WAL currently already has all the info
> > that would be needed for redo (it actually has to). 
> 
> The WAL has the information needed to take a binary table image
> from the checkpoint state to the last committed transaction.
> IIUC, it is meaningless in relation to a pg_dump image.
> 

Also, I expect this will be completed for 7.2.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 4 Jul 2001 11:04:56 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Re: Backup and Recovery
At 11:04 AM 7/4/01 -0400, Bruce Momjian wrote:
> > > All that is missing is a program, that can take a consistent physical
> > > snapshot (as it was after a particular checkpoint) and would replay
> > > the WAL after a restore of such a snapshot. This replay after a
> > > consistent snapshot is probably as simple as making the WAL files
> > > available to the standard startup rollforward (redo) mechanism, that
> > > is already implemented.
> >
You would also have to have some triggering method that would not allow 
WAL's to be overwritten, and make sure they are sent to some device before 
that happen.  In addition, you'd have to anchor the LOGS against the dumped 
database, so it would know exactly which log to start with when replaying 
against any given archive.
--
Naomi Walker
Chief Information Officer
Eldorado Computing, Inc.
602-604-3100  ext 242 


Thu, 05 Jul 2001 09:13:40 -0700
Naomi Walker <nwalker@eldocomp.com>
Re: Re: Backup and Recovery