Re: How to reliably detect if it's a promoting standby

Hi,

I'm looking for a way to reliably detect if it's a promoting
standby. This is neccessary for pgpool-II manage streaming replication
clusters. When primary goes down, standby *could* start promoting to
primary. The only way to find it is calling
pg_is_in_recovery(). Problem is, it returns true even if standby is
promoting. If pgpool-II waits for cetain period, it may finishes
promotion, thus it returns false. But how long should we wait?

Any idea?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

Ok, it seems impossible to do that by using any public interfaces
currently available in PostgreSQL 9.0. I will create a custom C
function to be distributed along with pgpool-II.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Hi,
>
> I'm looking for a way to reliably detect if it's a promoting
> standby. This is neccessary for pgpool-II manage streaming replication
> clusters. When primary goes down, standby *could* start promoting to
> primary. The only way to find it is calling
> pg_is_in_recovery(). Problem is, it returns true even if standby is
> promoting. If pgpool-II waits for cetain period, it may finishes
> promotion, thus it returns false. But how long should we wait?
>
> Any idea?
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Oct 14, 2010 at 11:10 AM, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:
> Ok, it seems impossible to do that by using any public interfaces
> currently available in PostgreSQL 9.0. I will create a custom C
> function to be distributed along with pgpool-II.

Could you submit the function for 9.1? Thanks :-)

--
Itagaki Takahiro

> On Thu, Oct 14, 2010 at 11:10 AM, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:
>> Ok, it seems impossible to do that by using any public interfaces
>> currently available in PostgreSQL 9.0. I will create a custom C
>> function to be distributed along with pgpool-II.
>
> Could you submit the function for 9.1? Thanks :-)

I can't wait till 9.1 since pgpool-II needs it now. Of course I could
propose a patch against HEAD when I have enough time...

BTW, I have checked xlog.c etc. and probably the only way to know if
the standby is promoting is, to see if ShutdownWalRcv() is called. If
it's called, global variable WalRcvData->walRcvsState is set to other
than WALRCV_RUNNING, I think.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

What new public interfaces do you think are needed for 9.1 in this
regard?

Cheers,
David.
On Thu, Oct 14, 2010 at 11:10:14AM +0900, Tatsuo Ishii wrote:
> Ok, it seems impossible to do that by using any public interfaces
> currently available in PostgreSQL 9.0. I will create a custom C
> function to be distributed along with pgpool-II.
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
>
> > Hi,
> >
> > I'm looking for a way to reliably detect if it's a promoting
> > standby. This is neccessary for pgpool-II manage streaming replication
> > clusters. When primary goes down, standby *could* start promoting to
> > primary. The only way to find it is calling
> > pg_is_in_recovery(). Problem is, it returns true even if standby is
> > promoting. If pgpool-II waits for cetain period, it may finishes
> > promotion, thus it returns false. But how long should we wait?
> >
> > Any idea?
> > --
> > Tatsuo Ishii
> > SRA OSS, Inc. Japan
> > English: http://www.sraoss.co.jp/index_en.php
> > Japanese: http://www.sraoss.co.jp
> >
> > --
> > Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-hackers
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

> What new public interfaces do you think are needed for 9.1 in this
> regard?

At this point I'm thinking of modifying existing pg_is_in_recovery(),
thus 0 new public interface. The heart of the function is
RecoveryInProgress(). It simply returns LocalRecoveryInProgress. In
addition to that, checking WalRcvData->walRcvsState being set to other
than WALRCV_RUNNING seems to solve the problem, though I have not
tested yet. Let me see if it's working...
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Cheers,
> David.
> On Thu, Oct 14, 2010 at 11:10:14AM +0900, Tatsuo Ishii wrote:
>> Ok, it seems impossible to do that by using any public interfaces
>> currently available in PostgreSQL 9.0. I will create a custom C
>> function to be distributed along with pgpool-II.
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
>>
>> > Hi,
>> >
>> > I'm looking for a way to reliably detect if it's a promoting
>> > standby. This is neccessary for pgpool-II manage streaming replication
>> > clusters. When primary goes down, standby *could* start promoting to
>> > primary. The only way to find it is calling
>> > pg_is_in_recovery(). Problem is, it returns true even if standby is
>> > promoting. If pgpool-II waits for cetain period, it may finishes
>> > promotion, thus it returns false. But how long should we wait?
>> >
>> > Any idea?
>> > --
>> > Tatsuo Ishii
>> > SRA OSS, Inc. Japan
>> > English: http://www.sraoss.co.jp/index_en.php
>> > Japanese: http://www.sraoss.co.jp
>> >
>> > --
>> > Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-hackers
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>
> --
> David Fetter <david(at)fetter(dot)org> http://fetter.org/
> Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
> Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
> iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
>
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate

On Thu, Oct 14, 2010 at 7:35 PM, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:
>> What new public interfaces do you think are needed for 9.1 in this
>> regard?
>
> At this point I'm thinking of modifying existing pg_is_in_recovery(),
> thus 0 new public interface.

pg_is_in_recovery() returns a bool, are you proposing to change that?

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte y capacitación de PostgreSQL

> On Thu, Oct 14, 2010 at 7:35 PM, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:
>>> What new public interfaces do you think are needed for 9.1 in this
>>> regard?
>>
>> At this point I'm thinking of modifying existing pg_is_in_recovery(),
>> thus 0 new public interface.
>
> pg_is_in_recovery() returns a bool, are you proposing to change that?

No. I just thought about adding more condition when it returns true.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

>> pg_is_in_recovery() returns a bool, are you proposing to change that?
>
> No. I just thought about adding more condition when it returns true.

Here is the patch. Comments are welcome!

*** a/src/backend/access/transam/xlog.c
--- b/src/backend/access/transam/xlog.c
***************
*** 5604,5610 **** GetLatestXTime(void)
Datum
pg_is_in_recovery(PG_FUNCTION_ARGS)
{
! PG_RETURN_BOOL(RecoveryInProgress());
}

/*
--- 5604,5613 ----
Datum
pg_is_in_recovery(PG_FUNCTION_ARGS)
{
! /* use volatile pointer to prevent code rearrangement */
! volatile WalRcvData *walrcv = WalRcv;
!
! PG_RETURN_BOOL(RecoveryInProgress() && walrcv->walRcvState == WALRCV_RUNNING);
}

/*

On 20.10.2010 17:32, Tatsuo Ishii wrote:
>>> pg_is_in_recovery() returns a bool, are you proposing to change that?
>>
>> No. I just thought about adding more condition when it returns true.
>
> Here is the patch. Comments are welcome!
>...
> Datum
> pg_is_in_recovery(PG_FUNCTION_ARGS)
> {
> ! /* use volatile pointer to prevent code rearrangement */
> ! volatile WalRcvData *walrcv = WalRcv;
> !
> ! PG_RETURN_BOOL(RecoveryInProgress()&& walrcv->walRcvState == WALRCV_RUNNING);
> }
>

This returns 'false' if you're in hot standby mode running against an
archive. That seems wrong, I don't think the walreceiver state should
play any role in this.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Wed, Oct 20, 2010 at 10:35 AM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> On 20.10.2010 17:32, Tatsuo Ishii wrote:
>>>>
>>>> pg_is_in_recovery() returns a bool, are you proposing to change that?
>>>
>>> No. I just thought about adding more condition when it returns true.
>>
>> Here is the patch. Comments are welcome!
>> ...
>>   Datum
>>   pg_is_in_recovery(PG_FUNCTION_ARGS)
>>   {
>> !       /* use volatile pointer to prevent code rearrangement */
>> !       volatile WalRcvData *walrcv = WalRcv;
>> !
>> !       PG_RETURN_BOOL(RecoveryInProgress()&&  walrcv->walRcvState ==
>> WALRCV_RUNNING);
>>   }
>>
>
> This returns 'false' if you're in hot standby mode running against an
> archive. That seems wrong, I don't think the walreceiver state should play
> any role in this.

I think what we need is a second function, not a change to the
definition of this one.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

> This returns 'false' if you're in hot standby mode running against an
> archive. That seems wrong, I don't think the walreceiver state should
> play any role in this.

Apart this, I wonder why walsender/walreceiver do not transfer archive
logs as well.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

On 20.10.2010 18:06, Tatsuo Ishii wrote:
>> This returns 'false' if you're in hot standby mode running against an
>> archive. That seems wrong, I don't think the walreceiver state should
>> play any role in this.
>
> Apart this, I wonder why walsender/walreceiver do not transfer archive
> logs as well.

What do you mean?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> On 20.10.2010 18:06, Tatsuo Ishii wrote:
>> Apart this, I wonder why walsender/walreceiver do not transfer archive
>> logs as well.
>
> What do you mean?

I'd be pleased if Tatsuo idea have anything to do with this mail:

http://archives.postgresql.org/message-id/m239s1f2oi.fsf@2ndQuadrant.fr

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support