Re: Proposing pg_hibernate

On Thu, Jun 5, 2014 at 8:32 AM, Gurjeet Singh <gurjeet(at)singh(dot)im> wrote:
> On Wed, Jun 4, 2014 at 2:50 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> The thing I was concerned about is that the system might have been in
>> recovery for months. What was hot at the time the base backup was
>> taken seems like a poor guide to what will be hot at the time of
>> promotion. Consider a history table, for example: the pages at the
>> end, which have just been written, are much more likely to be useful
>> than anything earlier.
>
> I think you are specifically talking about a warm-standby that runs
> recovery for months before being brought online. As described in my
> response to Amit, if the base backup used to create that standby was
> taken after the BlockReaders had restored the buffers (which should
> complete within few minutes of startup, even for large databases),
> then there's no concern since the base backup wouldn't contain the
> save-files.
>
> If it's a hot-standby, the restore process would start as soon as the
> database starts accepting connections, finish soon after, and get
> completely out of the way of the normal recovery process. At which
> point the buffers populated by the recovery would compete only with
> the buffers being requested by backends, which is the normal
> behaviour.

I guess I don't see what warm-standby vs. hot-standby has to do with
it. If recovery has been running for a long time, then restoring
buffers from some save file created before that is probably a bad
idea, regardless of whether the buffers already loaded were read in by
recovery itself or by queries running on the system. But if you're
saying that doesn't happen, then there's no problem there.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company