Обсуждение: Postgres Replication on a different network interface
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?
Regards,
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) S1Site2(Disaster) Passive : One Primary (read write) M2 2nd one is Slave (read only) S2Note: 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
![image.png](/media/maillist_attaches/pgsql-admin/2020/09/2/CAAUegEL-GfG7XEZWQfdpTw8NZEmLKQ0w0Rtu__f_nPv9+44-zw@mail.gmail.com/image.png)
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) S1Site2(Disaster) Passive : One Primary (read write) M2 2nd one is Slave (read only) S2Note: 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
Вложения
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.AOn 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) S1Site2(Disaster) Passive : One Primary (read write) M2 2nd one is Slave (read only) S2Note: 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
Вложения
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 |
|
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.AOn 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) S1Site2(Disaster) Passive : One Primary (read write) M2 2nd one is Slave (read only) S2Note: 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
Вложения
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.AOn 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) S1Site2(Disaster) Passive : One Primary (read write) M2 2nd one is Slave (read only) S2Note: 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