Re: Idea: recycle WAL segments, don't delete/recreate 'em

> > > Of course not.  As mentioned, you'd probably archive them with your
> > > backup(s).
> > 
> > You mean the nigthly backup?  Why not do a pg_dump and be done with it.
> 
> But the purpose of point-in-time recovery is to restore your backup 
> and then use the WAL to bring the backed up image up to a more current
> version.   

My point was that the WAL logs are going to be archived after the backup
occurs, right?  From the text below, I see you are addressing that.

> > > > > A possible solution (as I mentioned before)) is to have 2 methods
> > > > > of logging available: circular and forward-recoverable.  When a
> > > > > database is created, the creator selects which type of logging to
> > > > > perform.  The log segments are exactly the same, only the recycling
> > > > > method is different.
> > > >
> > > > Will not fly.  We need a solution that is flexible.
> > >
> > > Could you expand on that a little (ie. flexible in which way).
> > > Offering the user a choice of two is more flexible than offering no
> > > choice.
> > 
> > We normally don't give users choices unless we can't come up with a
> > win-win solution to the problem.  In this case, we could just query to
> > see if the WAL PIT archiver is running and handle tune reuse of log
> > segments on the fly.  In fact, my guess is that the PIT archiver will
> > have to tell the system when it is done with WAL logs anyway.
> 
> But this could be a win-win situation.  If a user doesn't not care 
> about point-in-time recovery, circular logs can be used.  When a
> database is created, a configurable number of log segments are
> allocated.  The database uses those logs in a cyclic manner.  No
> new log segments need to be created under normal use.  Automatic
> reuse.
> 
> A database requiring point-in-time functionality will log very
> similar to the method in place today.  New log segments will be
> created when needed.  

Basically, when the user asks for point-in-time, we can then control how
we recycle the logs, right? 

> > > > > Hmmm... the more I look at this, the more interested I become.
> > > >
> > > > My assumption is that once a log is full the point-in-time recovery
> > > > daemon will copy that off somewhere, either to a different disk, tape,
> > > > or over the network to another machine.  Once it is done making a copy,
> > > > the WAL log can be recycled, right?  Am I missing something here?
> > >
> > > Ok... I wasn't thinking of having a point-in-time daemon.  Some other
> > > databases provide, for lack of a better term, user exits to allow
> > > user defined scripts or programs to be called to perform log segment
> > > archiving.  This archiving is somewhat orthogonal to point-in-time
> > > recovery proper.
> > >
> > > Yep, once the archiving is complete, you can do whatever you want
> > > with the local log segment.
> > 
> > We will clearly need something to transfer these WAL logs somewhere
> > else, and it would be nice if it could be easily configured.  I think a
> > PIT logger daemon is the only solution, especially since tape/network
> > transfer could take a long time.  It would be forked by the postmaster
> > so would cover all users and databases.
> 
> Actually, it would be better if the entire logger was split out into
> it's own process like the large commercial databases.  Archiving the
> log segments would just be one of the many functions of the logger
> process.  Just a thought.

I think we already have a daemon that does checkpoints.

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