Re: failover vs. read only queries

On Thu, Jun 10, 2010 at 9:58 AM, Takahiro Itagaki
<itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> wrote:
>
> Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>
>> > 1. Reset max_standby_delay = 0 in postgresql.conf
>> > 2. pg_ctl reload
>> > 3. Create a trigger file
>>
>> As far as I read the HS code, SIGHUP is not checked while a recovery
>> is waiting for queries :(  So pg_ctl reload would have no effect on
>> the conflicting queries.
>>
>> Independently from the problem I raised, I think that we should call
>> HandleStartupProcInterrupts() in that sleep loop.
>
> Hmmm, if reload doesn't work, can we write a query like below?
>
>  SELECT pg_terminate_backend(pid)
>    FROM pg_locks
>   WHERE conflicted-with-recovery-process;

I'm not sure that, but as you suggested, we can minimize the failover
time by using the following operation even in 9.0.

1. Reset max_standby_delay = 0 in postgresql.conf
2. pg_ctl reload
3. Cancel all the queries or all the conflicting ones
4. Create a trigger file

For now, I'll use the above when building the HA system using 9.0
and a clusterware.

Regards,

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