Re: Problem with PITR recovery

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.

>
> Let's say you have your WAL's FTP'd to a remote server
> off-site. Friday at 4:50 PM Postgres starts a new
> WAL, and everyone goes home for the weekend at 5pm.
> No activity occurs on the database all weekend long,
> so the new WAL never fills and is never closed. If
> something should happen during the weekend, and the
> disks are ruined on the PG DB server, the last WAL is
> never sent to the remote off-site server. The last
> transactions of the day are lost, even though they
> could have taken place days ago. With feature 3, you
> can guarantee that the oldest WAL is XX minutes old,
> so at least you have all the transactions within the
> last XX minutes.
>
> Of course feature #3 also needs to have some smarts to
> it, so it doesn't create a bunch of completely empty
> WAL's everytime the timer runs out. It should only
> write and close the WAL if there is actually some new
> data in it.
>
> Later
> Rob
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83