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

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

> > You have to copy them off
> > somewhere, right, and once you have copied them, why not reuse them?
> 
> I'm not arguing that point.  I stated "recycling of the only available
> log segments".  Once the log segment is archived (copied) elsewhere
> you have two available images of the same segment.  You can rename
> the local copy. 

Yes, OK, I see now.  As Tom mentioned, there would have to be some delay
where we allow the WAL log to be archived before reusing it.

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

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

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