Re: Shutting down a warm standby database in 8.2beta3

On Fri, 2006-11-24 at 07:20 -0500, Stephen Harris wrote:
> On Thu, Nov 23, 2006 at 02:47:27PM -0500, Tom Lane wrote:
> 
> > It would back up to whereever the last recovery checkpoint was.  I think
> > we've got it hardwired at one recovery checkpoint per 100 source
> > checkpoints ... maybe that's too far apart.
> 
> I'm talking out of my arse now, since I have no ideas of the internals
> of postgres recovery, but maybe a recovery checkpoint can be added each
> time it asks for a new archive file, or 100 source checkpoints, whatever
> comes first.

We can take a restorepoint during replay only when we come across a
checkpoint WAL record. We take the first checkpoint WAL record we come
across after checkpoint_timeout seconds have expired, not 100 as
described.

History: The 100 checkpoints per restorepoint idea was the way it was
originally following the discussions at Anniversary. That was replaced
prior to code commit with the concept of a minimum delay between
restorepoints equal to the checkpoint_timeout.

> A database that is mostly idle (eg overnight) but has 1 or 2 critical
> transactions will have received lots of archive logs (from the 5 minute
> checkpoint timeout) but not necessarily enough to cause recovery to
> checkpoint.  This could mean needing to keep a large number of shipped
> logfiles available on the standby system "just in case".

It doesn't work that way. There is only ever one WAL file being worked
upon at a time, so there is no build up of log files in the case you
mention.

The case you mention would be captured by the current code, since after
5 minutes worth of WAL replay we would attempt a restorepoint at the
next checkpoint record. If replay goes at exactly the same speed as the
original WAL writes, then there would be one restorepoint per original
checkpoint. If replay goes 10 times faster then there would be one
restorepoint for each 10 checkpoints, etc.. So the restorepoint is taken
every 5 minutes in wall-clock time during replay, whereas that might be
anywhere from 5 minutes upwards in original-WAL-write-time, possibly
hours or days if you aren't writing any WAL at all for long periods. But
note that will not translate into many WAL files.

If the de-archive time is significant then the average rate of WAL apply
will go down and so the number of restorepoints per checkpoint will
fall. So overall, I can't see any need to change the code, but I'm
listening for different opinions and Stephen's especially has been very
good to have, thanks.

Humble apologies to all for my late arrival on this thread. I missed the
start of it during a busy period last week and didn't notice subsequent
postings. I'll try to filter things better in future since I'm clearly
not managing to read all postings. Please feel free to include me into
any threads.

If there's anything that people think I should be working on in support
of this, please say and I'll prioritise that now ahead of my docs and
advocacy activities around the release.

--  Simon Riggs              EnterpriseDB   http://www.enterprisedb.com