Re: Postgres Replication on a different network interface

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.
>
>
> image.png
>
>
>
>
>
>
>
>
>
>
>
>
> A
>
> On Tue, Sep 1, 2020 at 4:43 PM Achilleas Mantzios
> <achill(at)matrix(dot)gatewaynet(dot)com <mailto:achill(at)matrix(dot)gatewaynet(dot)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
>>
>>
>>
>>
>>
>>
>>
>>