New trigger file in pg_standby to promote the standby to the primary

Hi,

In current pg_standby, the presence of the trigger file causes
recovery to end whether or not the next WAL file is available.
Thereby, some transactions in the available WAL files will be
lost. So, we cannot use this trigger file to promote the standby
to the primary.

I'd like to add new trigger file option into pg_standby, and end
recovery after redoing all the available WAL files. Concretely
speaking, I would always make pg_standby look for the trigger
file after trying to restore the WAL file once. If the trigger file
is present, pg_standby exits immediately without deleting the
trigger file. Subsequent pg_standby uses the existing trigger
file.

This option is also useful for warm-standby.

Any comments welcome!

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Wed, 2008-11-26 at 00:03 +0900, Fujii Masao wrote:

> In current pg_standby, the presence of the trigger file causes
> recovery to end whether or not the next WAL file is available.
> Thereby, some transactions in the available WAL files will be
> lost. So, we cannot use this trigger file to promote the standby
> to the primary.
>
> I'd like to add new trigger file option into pg_standby, and end
> recovery after redoing all the available WAL files. Concretely
> speaking, I would always make pg_standby look for the trigger
> file after trying to restore the WAL file once. If the trigger file
> is present, pg_standby exits immediately without deleting the
> trigger file. Subsequent pg_standby uses the existing trigger
> file.
>
> This option is also useful for warm-standby.
>
> Any comments welcome!

Yes, it was on my todo. Happy for you to do it if you have time.

Sometimes we want fast failover, sometimes we want complete catchup. So
my solution is to put a message in the trigger file to say which one we
want.

Happy for the default to change to "complete catchup".

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Hello, Simon.

On Fri, Nov 28, 2008 at 4:29 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> On Wed, 2008-11-26 at 00:03 +0900, Fujii Masao wrote:
>
>> In current pg_standby, the presence of the trigger file causes
>> recovery to end whether or not the next WAL file is available.
>> Thereby, some transactions in the available WAL files will be
>> lost. So, we cannot use this trigger file to promote the standby
>> to the primary.
>>
>> I'd like to add new trigger file option into pg_standby, and end
>> recovery after redoing all the available WAL files. Concretely
>> speaking, I would always make pg_standby look for the trigger
>> file after trying to restore the WAL file once. If the trigger file
>> is present, pg_standby exits immediately without deleting the
>> trigger file. Subsequent pg_standby uses the existing trigger
>> file.
>>
>> This option is also useful for warm-standby.
>>
>> Any comments welcome!
>
> Yes, it was on my todo. Happy for you to do it if you have time.
>
> Sometimes we want fast failover, sometimes we want complete catchup. So
> my solution is to put a message in the trigger file to say which one we
> want.
>
> Happy for the default to change to "complete catchup".

I have added the new trigger option into pg_standby in the latest patch,
before reading your comment. But I think that your idea is more flexible,
so I will change it by the next patch.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center