Обсуждение: Replication

Поиск

Список

Период

Сортировка

Replication

От

Bertrand Paquet

Дата:

02 июня 2016 г., 16:32:43

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

Re: Replication

От

Melvin Davidson

Дата:

02 июня 2016 г., 17:19:38

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.

Re: Replication

От

Scott Mead

Дата:

02 июня 2016 г., 17:34:06

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,

Bertrand

--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

--
Scott Mead
Sr. Architect
OpenSCG

http://openscg.com

Re: Replication

От

Sameer Kumar

Дата:

02 июня 2016 г., 18:02:41

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,

Bertrand

--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

--
--
Scott Mead
Sr. Architect
OpenSCG
http://openscg.com

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

Re: Replication

От

John R Pierce

Дата:

02 июня 2016 г., 19:39:16

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

Re: Replication

От

Vik Fearing

Дата:

04 июня 2016 г., 16:58:17

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

Re: Replication

От

Vik Fearing

Дата:

04 июня 2016 г., 16:59:46

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

Re: Replication

От

Masahiko Sawada

Дата:

06 июня 2016 г., 10:55:21

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

Re: Replication

От

Vik Fearing

Дата:

06 июня 2016 г., 11:23:09

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

Re: Replication

От

Bertrand Paquet

Дата:

06 июня 2016 г., 11:53:39

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.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: Replication