Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kD4E6-0003a4-Sl for pgsql-admin@arkaria.postgresql.org; Tue, 01 Sep 2020 11:13:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kD4E5-0004Xj-S3 for pgsql-admin@arkaria.postgresql.org; Tue, 01 Sep 2020 11:13:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kD4E5-0004Xb-G1 for pgsql-admin@lists.postgresql.org; Tue, 01 Sep 2020 11:13:21 +0000 Received: from hermes.gatewaynet.com ([193.92.121.98]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kD4Dz-0006yt-DF for pgsql-admin@lists.postgresql.org; Tue, 01 Sep 2020 11:13:20 +0000 Received: from hercules.internal.net (hercules.internal.net [10.9.200.8]) by hermes.gatewaynet.com (8.15.2/8.15.2/Debian-8) with ESMTP id 081BDBpw006570 for ; Tue, 1 Sep 2020 14:13:11 +0300 Received: from [10.9.251.249] (dcmchgw.internal.net [10.9.0.1]) by hercules.internal.net (Postfix) with ESMTPS id 3741D1350AD8 for ; Tue, 1 Sep 2020 14:13:09 +0300 (EEST) Subject: Re: Postgres Replication on a different network interface To: pgsql-admin@lists.postgresql.org References: From: Achilleas Mantzios Message-ID: <9b5b88a0-83b5-711f-d70c-11f4b25ed5ea@matrix.gatewaynet.com> Date: Tue, 1 Sep 2020 14:13:07 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.10.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------7F2AE02F2B56779F8C207176" Content-Language: en-US X-Virus-Scanned: clamav-milter 0.101.4 at hermes X-Virus-Status: Clean X-Spam-Status: No, score=1.0 required=5.0 tests=ALL_TRUSTED,DYN_HAM_SIGNAT1, DYN_SPAM_WATCHES8,HTML_MESSAGE autolearn=no autolearn_force=no version=3.4.2 X-Spam-Level: * X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on hermes.gatewaynet.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------7F2AE02F2B56779F8C207176 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 > > > > > > > > --------------7F2AE02F2B56779F8C207176 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

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



 


 


--------------7F2AE02F2B56779F8C207176--