Re: Add shutdown_at_recovery_target option to recovery.conf

On 10/09/14 13:13, Fujii Masao wrote:
> On Wed, Sep 10, 2014 at 1:45 AM, Petr Jelinek <petr(at)2ndquadrant(dot)com> wrote:
>> Hi,
>>
>> I recently wanted several times to have slave server prepared at certain
>> point in time to reduce the time it takes for it to replay remaining WALs
>> (say I have pg_basebackup -x on busy db for example).
>
> In your example, you're thinking to perform the recovery after taking
> the backup and stop it at the consistent point (i.e., end of backup) by
> using the proposed feature? Then you're expecting that the future recovery
> will start from that consistent point and which will reduce the recovery time?
>
> This is true if checkpoint is executed at the end of backup. But there might
> be no occurrence of checkpoint during backup. In this case, even future
> recovery would need to start from very start of backup. That is, we cannot
> reduce the recovery time. So, for your purpose, for example, you might also
> need to add new option to pg_basebackup so that checkpoint is executed
> at the end of backup if the option is set.
>

For my use-case it does not matter much as I am talking here of huge
volumes where it would normally take hours to replay so being behind one
checkpoint is not too bad, but obviously I am not sure that it's good
enough for project in general. Adding checkpoint for pg_basebackup might
be useful addition, yes.

Also I forgot to write another use-case which making sure that I
actually do have all the WAL present to get to certain point in time
(this one could be done via patch to pg_receivexlog I guess, but I see
advantage in having the changes already applied compared to just having
the wal files).

>> So I wrote simple patch that adds option to shut down the cluster once
>> recovery_target is reached. The server will still be able to continue WAL
>> replay if needed later or can be configured to start standalone.
>
> What about adding something like action_at_recovery_target=pause|shutdown
> instead of increasing the number of parameters?
>

That will also increase number of parameters as we can't remove the
current pause one if we want to be backwards compatible. Also there
would have to be something like action_at_recovery_target=none or off or
something since the default is that pause is on and we need to be able
to turn off pause without having to have shutdown on. What more, I am
not sure I see any other actions that could be added in the future as
promote action already works and listen (for RO queries) also already
works independently of this.

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services