Обсуждение: 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@postgresql.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@postgresql.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@postgresql.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@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-hackers > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.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@postgresql.org) >> > To make changes to your subscription: >> > http://www.postgresql.org/mailpref/pgsql-hackers >> >> -- >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-hackers > > -- > David Fetter <david@fetter.org> http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter > Skype: davidfetter XMPP: david.fetter@gmail.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@postgresql.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@postgresql.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.linnakangas@enterprisedb.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.linnakangas@enterprisedb.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