On Wed, Jun 4, 2014 at 12:54 AM, Amit Kapila <amit.kapil...@gmail.com> wrote: > On Tue, Jun 3, 2014 at 5:43 PM, Gurjeet Singh <gurj...@singh.im> wrote: >> >> 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 feel the need to enumerate the recovery scenarios we're talking about so that we're all on the same page. 1) Hot backup (cp/rsync/pg_basebackup/.. while the master was running) followed by 1a) recovery using archives or streaming replication. 1a.i) database in hot-standby mode 1a.ii) database not in hot-standby mode, i.e. it's in warm-standby mode. 1b) minimal recovery, that is, recover only the WAL available in pg_xlog, then come online. 2) Cold backup of a crashed master, followed by startup of the copy (causing crash recovery; IMHO same as case 1b above.). 3) Cold backup of clean-shutdown master, followed by startup of the copy (no recovery). In cases 1.x there won't be any save-files (*), because the BlockReader processes remove their respective save-file when they are done restoring the buffers, So the hot/warm-standby created thus will not inherit the save-files, and hence post-recovery will not cause any buffer restores. Case 2 also won't cause any buffer restores because the save-files are created only on clean shutdowons; not on a crash or immediate shutdown. Case 3, is the sweet spot of pg_hibernator. It will save buffer-list on shutdown, and restore them when the backup-copy is started (provided pg_hibernator is installed there). (*) If a hot-backup is taken immediately after database comes online, since BlockReaders may still be running and not have deleted the save-files, the save-files may end up in backup, and hence cause the recovery-time conflicts we're talking about. This should be rare in practice, and even when it does happen, at worst it will affect the initial performance of the cluster. >> 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. Given the above description, I don't think crash/archive recovery is a concern anymore. But if that corner case is still a concern, I wouldn't favour making recovery slow by default, and make users of pg_hibernator pay for choosing to use the extension. I'd prefer the user explicitly ask for a behaviour that makes startups slow. > 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"? There's no user interface to this extension except for the 3 GUC parameters; not even CREATE EXTENSION. The DBA is expected to append this extension's name in shared_preload_libraries. Since this extension declares one of its parameters PGC_POSTMASTER, it can't be loaded via the SQL 'LOAD ' command. postgres=# load 'pg_hibernator'; FATAL: cannot create PGC_POSTMASTER variables after startup FATAL: cannot create PGC_POSTMASTER variables after startup The connection to the server was lost. Attempting reset: Succeeded. Best regards, PS: I was out sick yesterday, so couldn't respond promptly. -- Gurjeet Singh http://gurjeet.singh.im/ EDB www.EnterpriseDB.com -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers