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

Err....  PG_DUMP nightly on a 38,000,000+row table that takes forever to 
dump/unload, and gets updated every 5 minutes with 256KChar worth of 
updates? 

Give me a FAST pg_dump, and I'll think about it, until then, no....

LER
(PS: this is also a reason for making a pg_upgrade work IN PLACE on a 
table). 

LER
>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

On 7/18/01, 11:35:04 AM, Bruce Momjian <pgman@candle.pha.pa.us> wrote 
regarding Re: [HACKERS] 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, Pennsylvania 19026

> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?

> http://www.postgresql.org/search.mpl