Re: Proposing pg_hibernate

On Tue, Jun 3, 2014 at 5:43 PM, Gurjeet Singh <gurjeet(at)singh(dot)im> wrote:
> On Tue, Jun 3, 2014 at 7:57 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > It seems like it would be best to try to do this at cluster startup
> > time, rather than once recovery has reached consistency. Of course,
> > that might mean doing it with a single process, which could have its
> > own share of problems. But I'm somewhat inclined to think that if
> > recovery has already run for a significant period of time, the blocks
> > that recovery has brought into shared_buffers are more likely to be
> > useful than whatever pg_hibernate would load.
>
> I am not absolutely sure of the order of execution between recovery
> process and the BGWorker, but ...
>
> For sizeable shared_buffers size, the restoration of the shared
> buffers can take several seconds.

Incase of recovery, the shared buffers saved by this utility are
from previous shutdown which doesn't seem to be of more use
than buffers loaded by recovery.

> I have a feeling the users wouldn't
> like their master database take up to a few minutes to start accepting
> connections.

I think this is fair point and to address this we can have an option to
decide when to load buffer's and have default value as load before
recovery.

> Currently pg_hibernator uses ReadBufferExtended() API, and AIUI, that
> API requires a database connection//shared-memory attachment, and that
> a backend process cannot switch between databases after the initial
> connection.

If recovery can load the buffer's to apply WAL, why can't it be done with
pg_hibernator. Can't we use ReadBufferWithoutRelcache() to achieve
the purpose of pg_hibernator?

One other point:
> Note that the BuffersSaver process exists at all times, even when this
> parameter is set to `false`. This is to allow the DBA to enable/disable
the
> extension without having to restart the server. The BufferSaver process
> checks this parameter during server startup and right before shutdown, and
> honors this parameter's value at that time.

Why can't it be done when user register's the extension by using dynamic
background facility "RegisterDynamicBackgroundWorker"?

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com