Re: Postgres Replication on a different network interface

Thanks for the response again. Please find my answers below.

Hi Achilleas, Thanks for quick response.  Why do you want M2 to be read/write?  M2 can be just read only as a passive hot backup.  Please see the pic. embedded.   I have 2 clusters  ( 1- active in one site , 2- passive in contingency site). Within the  cluster in active  site I have already configured replication and its working fine. I would like to do the same  for one of the server in  contingency site. Please help. The replication traffic, I  would like to configure on a 2nd  ethernet interface i.e. connected  site to site as  mentioned in red color.  In active cluster , the replication between the primary and  slave is done using WAL replication and using interface (eth1-M1 to eth1-S1.   I  would like t o configure either (M1-eth2 to M2-eth2 or another option S1-eth2 to S2 eth2 or may be  M1-eth2 to M2 eth2). Do you see any issue if I use eth2 in each box for  WAL streaming ?  I believe this should be configured correctly on all the  pg_hba.conf file.   I don't want to put them in listen i.e.  listen_addresses = 'localhost,eth1'  .   Please advise.

please read the manual regarding listen_addresses and pg_hba.conf

By WAL replication you mean WAL file based or streaming? I guess you mean streaming. PostgreSQL supports cascading replication. If you dont trust the link between your main site's cluster (1) and the second site (2) you could setup your topology in this manner : M1 -> S1 -> M2 -> S2  ( I think this is the best approach) if you trust the link and the infrastructure on the (2) site :      M1 -> S1 |-> M2 -> S2

Hi Achilleas, Thanks for quick response.  Why do you want M2 to be read/write?  M2 can be just read only as a passive hot backup.

On Wed, Sep 2, 2020 at 10:37 PM Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

On 2/9/20 2:46 μ.μ., Sanjib Mohanty wrote:

Hi Achilleas,

Thanks for quick response. 

Why do you want M2 to be read/write?  M2 can be just read only as a passive hot backup. 

Please see the pic. embedded.   I have 2 clusters  ( 1- active in one site , 2- passive in contingency site). Within the  cluster in active  site I have already configured replication and its working fine. I would like to do the same  for one of the server in  contingency site.

Please help. The replication traffic, I  would like to configure on a 2nd  ethernet interface i.e. connected  site to site as  mentioned in red color. 

In active cluster , the replication between the primary and  slave is done using WAL replication and using interface (eth1-M1 to eth1-S1.  

I  would like t o configure either (M1-eth2 to M2-eth2 or another option S1-eth2 to S2 eth2 or may be  M1-eth2 to M2 eth2).

By WAL replication you mean WAL file based or streaming? I guess you mean streaming. PostgreSQL supports cascading replication. If you dont trust the link between your main site's cluster (1) and the second site (2) you could setup your topology in this manner :

M1 -> S1 -> M2 -> S2

if you trust the link and the infrastructure on the (2) site :

M1 -> S1

|-> M2 -> S2

Please suggest.     

A

On Tue, Sep 1, 2020 at 4:43 PM Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

On 1/9/20 1:43 μ.μ., Sanjib Mohanty wrote:

Hi Experts,

I had done replication earlier in EDB 9.6 version using simple WAl replication.

My master server is listening on localhost.

Example: 

listen_addresses = 'localhost,158.245.240.209'  

I have another additional IP configured  on my both master and slave servers which is a dedicated network  for my replication traffic example,

Primary : 172.176.43.112 ,   Salve: 172.198.47.101 ,  

Question:

Can I configure this Ip only for replication ? 

Yes via pg_hba.conf

My understanding:

postgresql.conf:

listen_addresses = 'localhost, < what all ips we should configure here>'   /// Do I need to add these replication traffic ip address here?? Please help


On Master
wal_level = logical or Hot_Standby

max_wal_senders = 4

max_replication_slots = 4
 



On Replica
In addition to the above, the following need to be configured on the replica:

hot_standby = on

hot_standby_feedback = on

pg_hba.conf:
host replication repuser 172.198.47.101 /32 md5


Salve:

standby_mode = 'on'

primary_conninfo = 'host=172.198.47.101  port=5432 user=foo password=foopass'

restore_command = 'cp /path/to/archive/%f %p'

archive_cleanup_command = 'pg_archivecleanup /path/to/archive %r'
 

One more question?

If I have to replicate the replication traffic from master to 2 different sites. 

Site1 ( Production) Active:  One Primary (read write) M1 , 2nd one is Slave (read only) S1

Site2(Disaster) Passive :   One Primary (read write) M2  2nd one is Slave (read only)  S2

Note: I have already configured replication within the site1 , where slave get the wal file using wal replication. 

Question?

Do I need to add another server as slave the Site 2 and replicate from the  Production Primary ? what is the best practise. ?

The sync I have plan is : M1->S1 & M2  , and then M2->S2  // please advise.

The are a lot of things to consider and lots of choices as well. Availability/reliability of the master is one thing VS things that can go wrong in the remote network.

What you have in mind (site2 : One Primary (read write)) is doable with logical replication which takes a different approach to physical. More flexible for sure but also needs more work and planing to be done.

Why do you want M2 to be read/write?

Regards,

Sanjib