Re: Problem with PITR recovery

OK, I updated the two current TODO items:

* Allow point-in-time recovery to archive partially filled write-ahead
logs

Currently only full WAL files are archived. This means that the most
recent transactions aren't available for recovery in case of a disk
failure. This could be triggered by a user command or a timer.

* Automatically force archiving of partially-filled WAL files when
pg_stop_backup() is called or the server is stopped

Doing this will allow administrators to know more easily when the
archive contins all the files needed for point-in-time recovery.

Is this OK?

---------------------------------------------------------------------------

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

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