Re: Problem with PITR recovery

Oleg Bartunov wrote:
> On Mon, 18 Apr 2005, Rob Butler wrote:
>
> >
> >> I'd say it's very not cool :) It's not we all
> >> expected from PITR.
> >> I recall now Simon mentioned about that and have it
> >> in his TODO.
> >> Other thing I don't understand what's the problem to
> >> generate WAL file
> >> by demand ? Probably, TODO should says about this.
> >
> > This would definetly be a good feature to have. What
> > I would prefer is:
> >
> > 1) have the pitr stop command write out and close the
> > WAL that it is currently using.
> >
> > 2) have another stored proc which can be invoked at
> > any time that will write out and close the WAL that is
> > currently in use when that command is executed.
> >
> > 3) have a feature in postgres that will automatically
> > write out and close the WAL if the server hasn't had
> > any activity in XX minutes, or hasn't closed a WAL
> > file in XX minutes.
> >
> > The reason for this is "the Friday night" scenario.
>
> This is exactly what I'm worry about ! Very typical
> scenario. I hope PITR improvement could be done in
> 8.0.X development cycle.

Yes, I described this exact scenario during a talk I gave on Saturday.
I think the only way to do this for 8.0.X now is to run a cron job that
just copies pg_xlog off to another location every so often.

Of course, there is the risk that your cron copy will fail in the
middle, leaving the WAL file corrupt. You would have to copy to a
temporary directory, then once that succeeds, move the files to overlay
the previous copies.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073