From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Postgres Replication on a different network interface
Date: Fri, 4 Sep 2020 12:17:37 +0300
Message-ID: <f545bd03-bf17-72a5-4147-7cc89eb03a62@matrix.gatewaynet.com> (raw)
In-Reply-To: <CAAUegELPiPqs+4cu=Rk8Hyj5=bYn-uwU=zLmMHms9ZFFqrOWVw@mail.gmail.com>
References: <CAAUegEKQL5FdxR_St-B2J7A-D7fpvJRRSVVKf9iHxhmmTxhyaw@mail.gmail.com>
<9b5b88a0-83b5-711f-d70c-11f4b25ed5ea@matrix.gatewaynet.com>
<CAAUegEL-GfG7XEZWQfdpTw8NZEmLKQ0w0Rtu__f_nPv9+44-zw@mail.gmail.com>
<0824b016-3920-aada-8d42-8315af5b1d7e@matrix.gatewaynet.com>
<CAAUegELPiPqs+4cu=Rk8Hyj5=bYn-uwU=zLmMHms9ZFFqrOWVw@mail.gmail.com>
On 3/9/20 1:24 μ.μ., Sanjib Mohanty wrote:
> 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 <mailto: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.
>>
>>
>> image.png
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> A
>>
>> On Tue, Sep 1, 2020 at 4:43 PM Achilleas Mantzios
>> <achill@matrix.gatewaynet.com
>> <mailto: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
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
Attachments:image.png (image/png, 13.4K) downloadview image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: achill@matrix.gatewaynet.com, pgsql-admin@lists.postgresql.org
Subject: Re: Postgres Replication on a different network interface
In-Reply-To: <f545bd03-bf17-72a5-4147-7cc89eb03a62@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox