Thread: PostgreSQL Streaming replication on two diff networks

PostgreSQL Streaming replication on two diff networks

From
Pierre Ochsenbein
Date:

Dear securtiy support,


I want to deploy a PostgreSQL Cluster with streaming replication, but I have a problem..


We have two VMs in two different data-centers with two different network.


It's possible to made a streaming replication with two different network?


My output :


vm01:10.1.24.13

vm02:10.0.2.67


On my slave (vm02) :

I can't make a pg_basebackup..


pg_basebackup: could not connect to server: could not connect to server: No route to host
    Is the server running on host "10.1.24.13" and accepting
    TCP/IP connections on port 5432?
pg_basebackup: removing data directory "/var/lib/pgsql/10/data"


[root@vm02 ~]# tcptraceroute 10.1.24.13 5432
traceroute to 10.1.24.13 (10.1.24.13), 30 hops max, 60 byte packets
 1  gateway (192.168.1.1)  0.375 ms  0.304 ms  0.282 ms
 2  169.254.95.213 (169.254.95.213)  0.261 ms  0.241 ms  0.198 ms
 3  10.0.2.1 (10.0.2.1)  1.761 ms  2.725 ms  3.449 ms
 4  172.16.9.41 (172.16.9.41)  1.141 ms 172.16.9.33 (172.16.9.33)  1.190 ms 172.16.9.41 (172.16.9.41)  1.642 ms
 5  172.16.9.50 (172.16.9.50)  0.388 ms 172.16.9.54 (172.16.9.54)  0.367 ms 172.16.9.62 (172.16.9.62)  0.430 ms
 6  10.1.23.4 (10.1.23.4)  0.227 ms  0.388 ms  0.345 ms
 7  10.1.23.4 (10.1.23.4)  0.298 ms !X  0.384 ms !X  0.326 ms !X



pg_hba.conf file on my master :

# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident
host    replication     replicate       10.0.2.67/32      scram-sha-256



Thank you so much for your help


Best regards, Pierre



Acceleris

Acceleris AG | cloudifying IT
Pierre Ochsenbein | Technical Consultant
Schosshaldenstrasse 85 | CH-3006 Bern
T : +41 31 911 33 22 | F : +41 31 911 62 63
pierre.ochsenbein@acceleris.ch | www.acceleris.ch  

Acceleris Offices are in :
Bern | Zurich | Renens | Bucharest
 
Acceleris
 

Disclaimer

This message possibly contains confidential data or items of information. It is intended solely for the rightful recipient. If you should have received the message wrongfully, it is not permitted to disclose the e-mail message or its contents to third parties, to copy or use it. We would respectfully request you to destroy the message with the exclusion of any reproduction and to notify this to the originator by e-mail. Thank you very much.



Re: PostgreSQL Streaming replication on two diff networks

From
Scott Whitney
Date:

Sure, but you need to be able to reach it.


You need to be able to ping/ssh between the 2 hosts first.


From: Pierre Ochsenbein <pierre.ochsenbein@acceleris.ch>
Sent: Tuesday, June 5, 2018 8:14 AM
To: pgsql-admin@lists.postgresql.org
Subject: PostgreSQL Streaming replication on two diff networks
 

Dear securtiy support,


I want to deploy a PostgreSQL Cluster with streaming replication, but I have a problem..


We have two VMs in two different data-centers with two different network.


It's possible to made a streaming replication with two different network?


My output :


vm01:10.1.24.13

vm02:10.0.2.67


On my slave (vm02) :

I can't make a pg_basebackup..


pg_basebackup: could not connect to server: could not connect to server: No route to host
    Is the server running on host "10.1.24.13" and accepting
    TCP/IP connections on port 5432?
pg_basebackup: removing data directory "/var/lib/pgsql/10/data"


[root@vm02 ~]# tcptraceroute 10.1.24.13 5432
traceroute to 10.1.24.13 (10.1.24.13), 30 hops max, 60 byte packets
 1  gateway (192.168.1.1)  0.375 ms  0.304 ms  0.282 ms
 2  169.254.95.213 (169.254.95.213)  0.261 ms  0.241 ms  0.198 ms
 3  10.0.2.1 (10.0.2.1)  1.761 ms  2.725 ms  3.449 ms
 4  172.16.9.41 (172.16.9.41)  1.141 ms 172.16.9.33 (172.16.9.33)  1.190 ms 172.16.9.41 (172.16.9.41)  1.642 ms
 5  172.16.9.50 (172.16.9.50)  0.388 ms 172.16.9.54 (172.16.9.54)  0.367 ms 172.16.9.62 (172.16.9.62)  0.430 ms
 6  10.1.23.4 (10.1.23.4)  0.227 ms  0.388 ms  0.345 ms
 7  10.1.23.4 (10.1.23.4)  0.298 ms !X  0.384 ms !X  0.326 ms !X



pg_hba.conf file on my master :

# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident
host    replication     replicate       10.0.2.67/32      scram-sha-256



Thank you so much for your help


Best regards, Pierre



Acceleris

Acceleris AG | cloudifying IT
Pierre Ochsenbein | Technical Consultant
Schosshaldenstrasse 85 | CH-3006 Bern
T : +41 31 911 33 22 | F : +41 31 911 62 63
pierre.ochsenbein@acceleris.ch | www.acceleris.ch  

Acceleris Offices are in :
Bern | Zurich | Renens | Bucharest
 
Acceleris
 

Disclaimer

This message possibly contains confidential data or items of information. It is intended solely for the rightful recipient. If you should have received the message wrongfully, it is not permitted to disclose the e-mail message or its contents to third parties, to copy or use it. We would respectfully request you to destroy the message with the exclusion of any reproduction and to notify this to the originator by e-mail. Thank you very much.





Journyx, Inc.
7600 Burnet Road #300
Austin, TX 78757
www.journyx.com

p 512.834.8888 
f 512-834-8858 

Do you receive our promotional emails? Click here or visit http://journyx.com/communication-preferences to unsubscribe.

Re: PostgreSQL Streaming replication on two diff networks

From
Shreeyansh Dba
Date:
Hi Pierre,

Yes,You can setup streaming replication between different network but must have the network connections between them.



On Tue, Jun 5, 2018 at 6:44 PM, Pierre Ochsenbein <pierre.ochsenbein@acceleris.ch> wrote:

Dear securtiy support,


I want to deploy a PostgreSQL Cluster with streaming replication, but I have a problem..


We have two VMs in two different data-centers with two different network.


It's possible to made a streaming replication with two different network?


My output :


vm01:10.1.24.13

vm02:10.0.2.67


On my slave (vm02) :

I can't make a pg_basebackup..


pg_basebackup: could not connect to server: could not connect to server: No route to host
    Is the server running on host "10.1.24.13" and accepting
    TCP/IP connections on port 5432?
pg_basebackup: removing data directory "/var/lib/pgsql/10/data"


[root@vm02 ~]# tcptraceroute 10.1.24.13 5432
traceroute to 10.1.24.13 (10.1.24.13), 30 hops max, 60 byte packets
 1  gateway (192.168.1.1)  0.375 ms  0.304 ms  0.282 ms
 2  169.254.95.213 (169.254.95.213)  0.261 ms  0.241 ms  0.198 ms
 3  10.0.2.1 (10.0.2.1)  1.761 ms  2.725 ms  3.449 ms
 4  172.16.9.41 (172.16.9.41)  1.141 ms 172.16.9.33 (172.16.9.33)  1.190 ms 172.16.9.41 (172.16.9.41)  1.642 ms
 5  172.16.9.50 (172.16.9.50)  0.388 ms 172.16.9.54 (172.16.9.54)  0.367 ms 172.16.9.62 (172.16.9.62)  0.430 ms
 6  10.1.23.4 (10.1.23.4)  0.227 ms  0.388 ms  0.345 ms
 7  10.1.23.4 (10.1.23.4)  0.298 ms !X  0.384 ms !X  0.326 ms !X



pg_hba.conf file on my master :

# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident
host    replication     replicate       10.0.2.67/32      scram-sha-256



Thank you so much for your help


Best regards, Pierre



Acceleris

Acceleris AG | cloudifying IT
Pierre Ochsenbein | Technical Consultant
Schosshaldenstrasse 85 | CH-3006 Bern
T : +41 31 911 33 22 | F : +41 31 911 62 63
pierre.ochsenbein@acceleris.ch | www.acceleris.ch  

Acceleris Offices are in :
Bern | Zurich | Renens | Bucharest
 
Acceleris
 

Disclaimer

This message possibly contains confidential data or items of information. It is intended solely for the rightful recipient. If you should have received the message wrongfully, it is not permitted to disclose the e-mail message or its contents to third parties, to copy or use it. We would respectfully request you to destroy the message with the exclusion of any reproduction and to notify this to the originator by e-mail. Thank you very much.




Re: PostgreSQL Streaming replication on two diff networks

From
Ray Stell
Date:



On 6/5/18 9:19 AM, Scott Whitney wrote:

Sure, but you need to be able to reach it.


You need to be able to ping/ssh between the 2 hosts first.


From: Pierre Ochsenbein <pierre.ochsenbein@acceleris.ch>
Sent: Tuesday, June 5, 2018 8:14 AM
To: pgsql-admin@lists.postgresql.org
Subject: PostgreSQL Streaming replication on two diff networks
 

Dear securtiy support,


I want to deploy a PostgreSQL Cluster with streaming replication, but I have a problem..


We have two VMs in two different data-centers with two different network.


It's possible to made a streaming replication with two different network?


My output :


vm01:10.1.24.13

vm02:10.0.2.67


On my slave (vm02) :

I can't make a pg_basebackup..


pg_basebackup: could not connect to server: could not connect to server: No route to host

need to get with your sysadmin, "No route to host" is a good thing, really, 10. is not routable.  You need to be sending to an interface that can be reached:
https://www.arin.net/knowledge/address_filters.html

According to standards set forth in Internet Engineering Task Force (IETF) document RFC-1918, the following IPv4 address ranges have been reserved by the IANA for private internets, and are not publicly routable on the global internet:

  • 10.0.0.0/8 IP addresses: 10.0.0.0 -- 10.255.255.255


Re: PostgreSQL Streaming replication on two diff networks

From
Jorge Torralba
Date:
It's all about the pg_hba.conf :)

On Tue, Jun 5, 2018, 06:14 Pierre Ochsenbein <pierre.ochsenbein@acceleris.ch> wrote:

Dear securtiy support,


I want to deploy a PostgreSQL Cluster with streaming replication, but I have a problem..


We have two VMs in two different data-centers with two different network.


It's possible to made a streaming replication with two different network?


My output :


vm01:10.1.24.13

vm02:10.0.2.67


On my slave (vm02) :

I can't make a pg_basebackup..


pg_basebackup: could not connect to server: could not connect to server: No route to host
    Is the server running on host "10.1.24.13" and accepting
    TCP/IP connections on port 5432?
pg_basebackup: removing data directory "/var/lib/pgsql/10/data"


[root@vm02 ~]# tcptraceroute 10.1.24.13 5432
traceroute to 10.1.24.13 (10.1.24.13), 30 hops max, 60 byte packets
 1  gateway (192.168.1.1)  0.375 ms  0.304 ms  0.282 ms
 2  169.254.95.213 (169.254.95.213)  0.261 ms  0.241 ms  0.198 ms
 3  10.0.2.1 (10.0.2.1)  1.761 ms  2.725 ms  3.449 ms
 4  172.16.9.41 (172.16.9.41)  1.141 ms 172.16.9.33 (172.16.9.33)  1.190 ms 172.16.9.41 (172.16.9.41)  1.642 ms
 5  172.16.9.50 (172.16.9.50)  0.388 ms 172.16.9.54 (172.16.9.54)  0.367 ms 172.16.9.62 (172.16.9.62)  0.430 ms
 6  10.1.23.4 (10.1.23.4)  0.227 ms  0.388 ms  0.345 ms
 7  10.1.23.4 (10.1.23.4)  0.298 ms !X  0.384 ms !X  0.326 ms !X



pg_hba.conf file on my master :

# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident
host    replication     replicate       10.0.2.67/32      scram-sha-256



Thank you so much for your help


Best regards, Pierre



Acceleris

Acceleris AG | cloudifying IT
Pierre Ochsenbein | Technical Consultant
Schosshaldenstrasse 85 | CH-3006 Bern
T : +41 31 911 33 22 | F : +41 31 911 62 63
pierre.ochsenbein@acceleris.ch | www.acceleris.ch  

Acceleris Offices are in :
Bern | Zurich | Renens | Bucharest
 
Acceleris
 

Disclaimer

This message possibly contains confidential data or items of information. It is intended solely for the rightful recipient. If you should have received the message wrongfully, it is not permitted to disclose the e-mail message or its contents to third parties, to copy or use it. We would respectfully request you to destroy the message with the exclusion of any reproduction and to notify this to the originator by e-mail. Thank you very much.



Re: PostgreSQL Streaming replication on two diff networks

From
Johannes Truschnigg
Date:
On Tue, Jun 05, 2018 at 06:39:42AM -0700, Jorge Torralba wrote:
> It's all about the pg_hba.conf :)

No; not if there's not even basic IP connectivity between the participating
hosts. Pierre will have to make sure he gets the routing between the two sites
fixed (which is probably going to need some kind of VPN solution).

--
with best regards:
- Johannes Truschnigg ( johannes@truschnigg.info )

www:   https://johannes.truschnigg.info/
phone: +43 650 2 133337
xmpp:  johannes@truschnigg.info

Please do not bother me with HTML-email or attachments. Thank you.

Attachment

Re: PostgreSQL Streaming replication on two diff networks

From
Jorge Torralba
Date:
>No; not if there's not even basic IP connectivity between the participating
hosts. Pierre will have to make sure he gets the routing between the two sites
fixed (which is probably going to need some kind of VPN solution).<

You are correct. But,  I am assuming all routing, vpn's, iptables etc are configured since it is a known network to the user.  He will still need entrie in pg_hba.conf.

JT

On Tue, Jun 5, 2018 at 6:43 AM, Johannes Truschnigg <johannes@truschnigg.info> wrote:
On Tue, Jun 05, 2018 at 06:39:42AM -0700, Jorge Torralba wrote:
> It's all about the pg_hba.conf :)

No; not if there's not even basic IP connectivity between the participating
hosts. Pierre will have to make sure he gets the routing between the two sites
fixed (which is probably going to need some kind of VPN solution).

--
with best regards:
- Johannes Truschnigg ( johannes@truschnigg.info )

www:   https://johannes.truschnigg.info/
phone: +43 650 2 133337
xmpp:  johannes@truschnigg.info

Please do not bother me with HTML-email or attachments. Thank you.



--
Thanks,

Jorge Torralba
----------------------------

Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.

RE: PostgreSQL Streaming replication on two diff networks

From
Pierre Ochsenbein
Date:

Everything is good for the ssh connectivity from the slave to the master but not from the master to the slave.


On the master :

-bash-4.2$ ssh postgres@10.0.2.67 (slaveIP)

Permission denied (publickey,gssapi-keyex,gssapi-with-mic).

-bash-4.2$​


-bash-4.2$ ssh-copy-id -i id_rsa.pub postgres@10.0.2.67
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "id_rsa.pub"
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
Permission denied (publickey,gssapi-keyex,gssapi-with-mic).
-bash-4.2$


--------


On the slave : 

-bash-4.2$ ssh postgres@10.1.24.13 (masterIP)

postgres@10.1.24.13's password: 

Last failed login: Tue Jun  5 10:10:19 EDT 2018 from slave on ssh:notty

There were 44 failed login attempts since the last successful login.

Last login: Tue Jun  5 09:48:42 2018 from slave

-bash-4.2$ exit

logout​




De : Jorge Torralba <jorge.torralba@gmail.com>
Envoyé : mardi 5 juin 2018 16:02
À : Johannes Truschnigg
Cc : Pierre Ochsenbein; pgsql-admin@lists.postgresql.org
Objet : Re: PostgreSQL Streaming replication on two diff networks
 
>No; not if there's not even basic IP connectivity between the participating
hosts. Pierre will have to make sure he gets the routing between the two sites
fixed (which is probably going to need some kind of VPN solution).<

You are correct. But,  I am assuming all routing, vpn's, iptables etc are configured since it is a known network to the user.  He will still need entrie in pg_hba.conf.

JT

On Tue, Jun 5, 2018 at 6:43 AM, Johannes Truschnigg <johannes@truschnigg.info> wrote:
On Tue, Jun 05, 2018 at 06:39:42AM -0700, Jorge Torralba wrote:
> It's all about the pg_hba.conf :)

No; not if there's not even basic IP connectivity between the participating
hosts. Pierre will have to make sure he gets the routing between the two sites
fixed (which is probably going to need some kind of VPN solution).

--
with best regards:
- Johannes Truschnigg ( johannes@truschnigg.info )

www:   https://johannes.truschnigg.info/
phone: +43 650 2 133337
xmpp:  johannes@truschnigg.info

Please do not bother me with HTML-email or attachments. Thank you.



--
Thanks,

Jorge Torralba
----------------------------

Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.

Acceleris

Acceleris AG | cloudifying IT
Pierre Ochsenbein | Technical Consultant
Schosshaldenstrasse 85 | CH-3006 Bern
T : +41 31 911 33 22 | F : +41 31 911 62 63
pierre.ochsenbein@acceleris.ch | www.acceleris.ch  

Acceleris Offices are in :
Bern | Zurich | Renens | Bucharest
 
Acceleris
 

Disclaimer

This message possibly contains confidential data or items of information. It is intended solely for the rightful recipient. If you should have received the message wrongfully, it is not permitted to disclose the e-mail message or its contents to third parties, to copy or use it. We would respectfully request you to destroy the message with the exclusion of any reproduction and to notify this to the originator by e-mail. Thank you very much.



Re: PostgreSQL Streaming replication on two diff networks

From
Shreeyansh Dba
Date:
Hi Pierre,

It looks you are not passing the  public-key of replica to your  master server  you need to pass or generate public-key between master-slave vice versa.

Hope this helps..



On Tue, Jun 5, 2018 at 7:46 PM, Pierre Ochsenbein <pierre.ochsenbein@acceleris.ch> wrote:

Everything is good for the ssh connectivity from the slave to the master but not from the master to the slave.


On the master :

-bash-4.2$ ssh postgres@10.0.2.67 (slaveIP)

Permission denied (publickey,gssapi-keyex,gssapi-with-mic).

-bash-4.2$​


-bash-4.2$ ssh-copy-id -i id_rsa.pub postgres@10.0.2.67
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "id_rsa.pub"
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
Permission denied (publickey,gssapi-keyex,gssapi-with-mic).
-bash-4.2$


--------


On the slave : 

-bash-4.2$ ssh postgres@10.1.24.13 (masterIP)

postgres@10.1.24.13's password: 

Last failed login: Tue Jun  5 10:10:19 EDT 2018 from slave on ssh:notty

There were 44 failed login attempts since the last successful login.

Last login: Tue Jun  5 09:48:42 2018 from slave

-bash-4.2$ exit

logout​




De : Jorge Torralba <jorge.torralba@gmail.com>
Envoyé : mardi 5 juin 2018 16:02
À : Johannes Truschnigg
Cc : Pierre Ochsenbein; pgsql-admin@lists.postgresql.org
Objet : Re: PostgreSQL Streaming replication on two diff networks
 
>No; not if there's not even basic IP connectivity between the participating
hosts. Pierre will have to make sure he gets the routing between the two sites
fixed (which is probably going to need some kind of VPN solution).<

You are correct. But,  I am assuming all routing, vpn's, iptables etc are configured since it is a known network to the user.  He will still need entrie in pg_hba.conf.

JT

On Tue, Jun 5, 2018 at 6:43 AM, Johannes Truschnigg <johannes@truschnigg.info> wrote:
On Tue, Jun 05, 2018 at 06:39:42AM -0700, Jorge Torralba wrote:
> It's all about the pg_hba.conf :)

No; not if there's not even basic IP connectivity between the participating
hosts. Pierre will have to make sure he gets the routing between the two sites
fixed (which is probably going to need some kind of VPN solution).

--
with best regards:
- Johannes Truschnigg ( johannes@truschnigg.info )

www:   https://johannes.truschnigg.info/
phone: +43 650 2 133337
xmpp:  johannes@truschnigg.info

Please do not bother me with HTML-email or attachments. Thank you.



--
Thanks,

Jorge Torralba
----------------------------

Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.

Acceleris

Acceleris AG | cloudifying IT
Pierre Ochsenbein | Technical Consultant
Schosshaldenstrasse 85 | CH-3006 Bern
T : +41 31 911 33 22 | F : +41 31 911 62 63
pierre.ochsenbein@acceleris.ch | www.acceleris.ch  

Acceleris Offices are in :
Bern | Zurich | Renens | Bucharest
 
Acceleris
 

Disclaimer

This message possibly contains confidential data or items of information. It is intended solely for the rightful recipient. If you should have received the message wrongfully, it is not permitted to disclose the e-mail message or its contents to third parties, to copy or use it. We would respectfully request you to destroy the message with the exclusion of any reproduction and to notify this to the originator by e-mail. Thank you very much.