Thread: (replication) Detecting if server a slave, or a master in recovery
Hi, What is the correct way to tell what mode a replicated Pg server is currently in, via a database connection? I can take a guess if it's the master or a slave by using the pg_current_xlog_location() and pg_last_xlog_replay_location() functions. However it occurs to me that a master server, which has been roughly rebooted and is replaying its WAL files will probably give the same response as a slave, when I call the current_xlog_location(). What is the best method for determining whether you're connected to the master or slave database? Thanks, Toby
On 12/09/11 16:54, Toby Corkindale wrote: > Hi, > What is the correct way to tell what mode a replicated Pg server is > currently in, via a database connection? > > I can take a guess if it's the master or a slave by using the > pg_current_xlog_location() and pg_last_xlog_replay_location() functions. > > However it occurs to me that a master server, which has been roughly > rebooted and is replaying its WAL files will probably give the same > response as a slave, when I call the current_xlog_location(). > > > What is the best method for determining whether you're connected to the > master or slave database? It's disappointing that I can't query standby_mode in psql. ie. SHOW standby_mode; Toby
On Mon, Sep 12, 2011 at 7:54 AM, Toby Corkindale <toby.corkindale@strategicdata.com.au> wrote: > What is the correct way to tell what mode a replicated Pg server is > currently in, via a database connection? > > I can take a guess if it's the master or a slave by using the > pg_current_xlog_location() and pg_last_xlog_replay_location() functions. > > However it occurs to me that a master server, which has been roughly > rebooted and is replaying its WAL files will probably give the same response > as a slave, when I call the current_xlog_location(). > > > What is the best method for determining whether you're connected to the > master or slave database? SELECT pg_is_in_recovery(); -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 12/09/11 17:13, Simon Riggs wrote: > On Mon, Sep 12, 2011 at 7:54 AM, Toby Corkindale > <toby.corkindale@strategicdata.com.au> wrote: > >> What is the correct way to tell what mode a replicated Pg server is >> currently in, via a database connection? >> >> I can take a guess if it's the master or a slave by using the >> pg_current_xlog_location() and pg_last_xlog_replay_location() functions. >> >> However it occurs to me that a master server, which has been roughly >> rebooted and is replaying its WAL files will probably give the same response >> as a slave, when I call the current_xlog_location(). >> >> >> What is the best method for determining whether you're connected to the >> master or slave database? > > SELECT pg_is_in_recovery(); If I'm on a master database, which had previously crashed and is now in the process of recovery, won't that also return "true" there?
On Mon, Sep 12, 2011 at 8:19 AM, Toby Corkindale <toby.corkindale@strategicdata.com.au> wrote: > On 12/09/11 17:13, Simon Riggs wrote: >> >> On Mon, Sep 12, 2011 at 7:54 AM, Toby Corkindale >>> What is the best method for determining whether you're connected to the >>> master or slave database? >> >> SELECT pg_is_in_recovery(); > > > If I'm on a master database, which had previously crashed and is now in the > process of recovery, won't that also return "true" there? No, because you can't connect to the database during crash recovery, so the first time you can connect to a has-crashed master it will return false. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 12/09/11 17:27, Simon Riggs wrote: > On Mon, Sep 12, 2011 at 8:19 AM, Toby Corkindale > <toby.corkindale@strategicdata.com.au> wrote: >> On 12/09/11 17:13, Simon Riggs wrote: >>> >>> On Mon, Sep 12, 2011 at 7:54 AM, Toby Corkindale >>>> What is the best method for determining whether you're connected to the >>>> master or slave database? >>> >>> SELECT pg_is_in_recovery(); >> >> >> If I'm on a master database, which had previously crashed and is now in the >> process of recovery, won't that also return "true" there? > > > No, because you can't connect to the database during crash recovery, > so the first time you can connect to a has-crashed master it will > return false. aaah.. Thanks! I didn't realise that. Thanks for your help. -Toby
On Mon, 2011-09-12 at 17:09 +1000, Toby Corkindale wrote: > On 12/09/11 16:54, Toby Corkindale wrote: > > Hi, > > What is the correct way to tell what mode a replicated Pg server is > > currently in, via a database connection? > > > > I can take a guess if it's the master or a slave by using the > > pg_current_xlog_location() and pg_last_xlog_replay_location() functions. > > > > However it occurs to me that a master server, which has been roughly > > rebooted and is replaying its WAL files will probably give the same > > response as a slave, when I call the current_xlog_location(). > > > > > > What is the best method for determining whether you're connected to the > > master or slave database? > > > It's disappointing that I can't query standby_mode in psql. > ie. > SHOW standby_mode; > I agree. Actually, you can't get the value of any parameter set in recovery.conf. But Fuji Masao seems to work on it: he posted a patch to unite recovery.conf and postgresql.conf (see "unite recovery.conf and postgresql.conf" thread on pgsql-hackers). And I guess it'll help us querying these parameters' values. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com