(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