Thread: 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

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

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
• 
  

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.

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​