Обсуждение: Replication
Hi,
On an hot standby streaming server, is there any way to know, in SQL, to know the ip of current master ?
The solution I have is to read the recovery.conf file to find primary_conninfo, but, it can be false.
Regards,
Bertrand
It's been a few years since I worked with slony, and you did not state which version of slony or PostgreSQL you are working with, nor did you indicate the O/S.
That being said, you should be able to formulate a query with a join between sl_path & sl_node that gives you the information you need.On Thu, Jun 2, 2016 at 9:32 AM, Bertrand Paquet <bertrand.paquet@doctolib.fr> wrote:
Hi,On an hot standby streaming server, is there any way to know, in SQL, to know the ip of current master ?The solution I have is to read the recovery.conf file to find primary_conninfo, but, it can be false.Regards,Bertrand
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
On Thu, Jun 2, 2016 at 10:16 AM, Melvin Davidson <melvin6925@gmail.com> wrote:
It's been a few years since I worked with slony, and you did not state which version of slony or PostgreSQL you are working with, nor did you indicate the O/S.
I think OP had pointed to using streaming....
That being said, you should be able to formulate a query with a join between sl_path & sl_node that gives you the information you need.On Thu, Jun 2, 2016 at 9:32 AM, Bertrand Paquet <bertrand.paquet@doctolib.fr> wrote:Hi,On an hot standby streaming server, is there any way to know, in SQL, to know the ip of current master ?The solution I have is to read the recovery.conf file to find primary_conninfo, but, it can be false.
I've run into this as well. Only way is recovery.conf.
--Scott
--Regards,BertrandMelvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On Thu, 2 Jun 2016, 10:34 p.m. Scott Mead, <scottm@openscg.com> wrote:
On Thu, Jun 2, 2016 at 10:16 AM, Melvin Davidson <melvin6925@gmail.com> wrote:It's been a few years since I worked with slony, and you did not state which version of slony or PostgreSQL you are working with, nor did you indicate the O/S.I think OP had pointed to using streaming....That being said, you should be able to formulate a query with a join between sl_path & sl_node that gives you the information you need.On Thu, Jun 2, 2016 at 9:32 AM, Bertrand Paquet <bertrand.paquet@doctolib.fr> wrote:Hi,On an hot standby streaming server, is there any way to know, in SQL, to know the ip of current master ?The solution I have is to read the recovery.conf file to find primary_conninfo, but, it can be false.I've run into this as well. Only way is recovery.conf.
9.6 onward you will have a new view which will facilitate you to query the replication details on standby.
I have not tried but probably you can check the pid of wal receiver and find out what host it is connected to (should be possible from network stats).
--Scott--Regards,BertrandMelvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
--
--
Best Regards
Sameer Kumar | DB Solution Architect
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
On 6/2/2016 6:32 AM, Bertrand Paquet wrote: > On an hot standby streaming server, is there any way to know, in SQL, > to know the ip of current master ? > The solution I have is to read the recovery.conf file to find > primary_conninfo, but, it can be false. "The IP" assumes there is only one... hosts can be multihomed, postgres can be listening on numerous interfaces, there is no 'the IP' -- john r pierce, recycling bits in santa cruz
On 02/06/16 15:32, Bertrand Paquet wrote: > Hi, > > On an hot standby streaming server, is there any way to know, in SQL, to > know the ip of current master ? No. > The solution I have is to read the recovery.conf file to find > primary_conninfo, That is currently the only solution. There are plans to allow SQL access to the parameters in recovery.conf (or to merge them into postgresql.conf) but that's not currently possible. > but, it can be false. It would only be wrong if recovery.conf has been edited since the stanby was last restarted. There are hooks for connections and disconnections of the walreceiver, so it should be possible and fairly simple to write an extension that remembers and exposes the primary_conninfo in effect. -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On 02/06/16 18:39, John R Pierce wrote: > On 6/2/2016 6:32 AM, Bertrand Paquet wrote: >> On an hot standby streaming server, is there any way to know, in SQL, >> to know the ip of current master ? >> The solution I have is to read the recovery.conf file to find >> primary_conninfo, but, it can be false. > > "The IP" assumes there is only one... hosts can be multihomed, > postgres can be listening on numerous interfaces, there is no 'the IP' That's nice, but a standby is only connecting to one. -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On Sat, Jun 4, 2016 at 10:58 PM, Vik Fearing <vik@2ndquadrant.fr> wrote: > On 02/06/16 15:32, Bertrand Paquet wrote: >> Hi, >> >> On an hot standby streaming server, is there any way to know, in SQL, to >> know the ip of current master ? > > No. > >> The solution I have is to read the recovery.conf file to find >> primary_conninfo, > > That is currently the only solution. There are plans to allow SQL > access to the parameters in recovery.conf (or to merge them into > postgresql.conf) but that's not currently possible. > It might not be a right way but how about using pg_read_file()? postgres(1)=# select regexp_replace(pg_read_file('recovery.conf'), '.*primary_conninfo = (.*)', '\1'); regexp_replace --------------------------------------------------- 'host=localhost port=5550 application_name=node1'+ (1 row) You can get the master server information via SQL from standby server. Regards, -- Masahiko Sawada
On 06/06/16 09:54, Masahiko Sawada wrote: > On Sat, Jun 4, 2016 at 10:58 PM, Vik Fearing <vik@2ndquadrant.fr> wrote: >> On 02/06/16 15:32, Bertrand Paquet wrote: >>> Hi, >>> >>> On an hot standby streaming server, is there any way to know, in SQL, to >>> know the ip of current master ? >> >> No. >> >>> The solution I have is to read the recovery.conf file to find >>> primary_conninfo, >> >> That is currently the only solution. There are plans to allow SQL >> access to the parameters in recovery.conf (or to merge them into >> postgresql.conf) but that's not currently possible. > > It might not be a right way but how about using pg_read_file()? > postgres(1)=# select regexp_replace(pg_read_file('recovery.conf'), > '.*primary_conninfo = (.*)', '\1'); > regexp_replace > --------------------------------------------------- > 'host=localhost port=5550 application_name=node1'+ > > (1 row) > > You can get the master server information via SQL from standby server. This is a good idea, but suffers the same problem that Bertrand has with looking at the file a different way: if the file was changed but the standby server has not been restarted, it's (potentially) not going to be the correct information. -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Hi,
Thx you for answering.
Regards,
Bertrand
2016-06-06 10:22 GMT+02:00 Vik Fearing <vik@2ndquadrant.fr>:
On 06/06/16 09:54, Masahiko Sawada wrote:
> On Sat, Jun 4, 2016 at 10:58 PM, Vik Fearing <vik@2ndquadrant.fr> wrote:
>> On 02/06/16 15:32, Bertrand Paquet wrote:
>>> Hi,
>>>
>>> On an hot standby streaming server, is there any way to know, in SQL, to
>>> know the ip of current master ?
>>
>> No.
>>
>>> The solution I have is to read the recovery.conf file to find
>>> primary_conninfo,
>>
>> That is currently the only solution. There are plans to allow SQL
>> access to the parameters in recovery.conf (or to merge them into
>> postgresql.conf) but that's not currently possible.
>
> It might not be a right way but how about using pg_read_file()?
> postgres(1)=# select regexp_replace(pg_read_file('recovery.conf'),
> '.*primary_conninfo = (.*)', '\1');
> regexp_replace
> ---------------------------------------------------
> 'host=localhost port=5550 application_name=node1'+
>
> (1 row)
>
> You can get the master server information via SQL from standby server.
This is a good idea, but suffers the same problem that Bertrand has with
looking at the file a different way: if the file was changed but the
standby server has not been restarted, it's (potentially) not going to
be the correct information.