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

Bruce Momjian wrote:
> 
> > > > Yes, but in a very roundabout way (or so it seems).  The main point
> > > > that I was trying to illustrate was that if a database supports
> > > > point-in-time recovery, recycling of the only available log segments
> > > > is a bad thing.  And, yes, in practice if you have point-in-time
> > > > recovery enabled you better archive your logs with your backup to
> > > > ensure that you can roll forward as expected.
> > >
> > > I assume you are not going to do point-in-time recovery by keeping all
> > > the WAL segments around on the same disk.
> >
> > 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.   

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

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

Cheers,
Patrick