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

Bruce Momjian wrote:
>
> > Hmmm... my prior appends to this newsgroup are stalled. Hopefully,
> > they'll be available soon.
> >
> > Tom Lane wrote:
> > >
> > > What you may really be saying is that the existing scheme for management
> > > of log segments is inappropriate for PIT usage; if so feel free to
> > > propose a better one. But I don't see how recycling of no-longer-wanted
> > > segments can break anything.
> >
> > 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 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.

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

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

Cheers,
Patrick