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.
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>*
http://openscg.com
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>*
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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi,
Thx you for answering.
Regards,
Bertrand
2016-06-06 10:22 GMT+02:00 Vik Fearing <vik@2ndquadrant.fr>:
Show quoted text
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