Thread: [ADMIN] Postgres - Replication

[ADMIN] Postgres - Replication

From
jasoninmel
Date:
Hi there, I have used Postgres with Master/Slave setup and I've used streaming replication to keep the slave instance up to date with Master. Now I want to have a DR site and ensure the secondary site also has up to date data. What is the best replication method recommended for this purpose? Thanks, Jason

View this message in context: Postgres - Replication
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

Re: [ADMIN] Postgres - Replication

From
Samed YILDIRIM
Date:
Hi Jason,
 
How much data loss toleration do you have? Which version of Postgres are you using?
 
If no data loss is required and Postgres version is 9.6, you can use synchronous replication.
If you have data loss tolerance you can use asynchronous replication or delayed replication.
 
Best regards.
 
İyi çalışmalar.
Samed YILDIRIM
 
 
 
15.03.2017, 08:01, "jasoninmel" <jasoninmel+1@gmail.com>:
Hi there, I have used Postgres with Master/Slave setup and I've used streaming replication to keep the slave instance up to date with Master. Now I want to have a DR site and ensure the secondary site also has up to date data. What is the best replication method recommended for this purpose? Thanks, Jason
View this message in context: Postgres - Replication
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

Re: [ADMIN] Postgres - Replication

From
jasoninmel
Date:
Thanks Samed.

Perhaps the question may not be clear and sorry about that.

What I'm looking for is an option to scale the solution to multi site
configuration where two different sites located in different geographical
locations to be able to maintain data in sync.

Currently I'm using synchronous streaming replication and one option I've
evaluated is cascading replication.



--
View this message in context: http://www.postgresql-archive.org/Postgres-Replication-tp5949678p5950172.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


Re: [ADMIN] Postgres - Replication

From
jasoninmel
Date:
Here is bit more background of the work I've done and issues around them.

What I'm looking for is an option to scale the solution to multi site
configuration where two different sites located in different geographical
locations to be able to maintain data in sync.

Currently I'm using synchronous streaming replication and one option I've
evaluated is cascading replication. Issues or concerns so far I have with
this setup are;

1. Only supports async replication
2. Automatic failover script I have built (have been using for several years
now without an issue) would 3. have to be enhanced to be intelligent to
switch to new master in primary site in the event primary fails.
4. All the standby servers including cascading standby would not allow
writes.


Happy to understand if there are implementations done by an expert in this
forum with solutions for these concerns.

Please note that I'm open for any other replication method that would allow
better scalability. One of the best option would be to have Multi-master
setup though I haven't seen any documentation around the support for such
capability by Postgres.

Thanks,
Jason



--
View this message in context: http://www.postgresql-archive.org/Postgres-Replication-tp5949678p5950175.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


Re: [ADMIN] Postgres - Replication

From
Rosser Schwarz
Date:
On Thu, Mar 16, 2017 at 20:35 jasoninmel <jasoninmel+1@gmail.com> wrote:
Please note that I'm open for any other replication method that would allow
better scalability. One of the best option would be to have Multi-master
setup though I haven't seen any documentation around the support for such
capability by Postgres.

Have you considered third-party tools like Bucardo? It, particularly, was designed for replicating across unreliable WAN links, and is currently in active use replicating large volumes of data between sites separated by oceans and continents.

It has overhead, and is asynchronous, but supports multi-master (where N > 2), and will simply queue changes for replication in the event of link failure. 

It may not suit your needs, but it's probably worth considering

rls

--
:wq

Re: [ADMIN] Postgres - Replication

From
bricklen
Date:

On Thu, Mar 16, 2017 at 8:34 PM, jasoninmel <jasoninmel+1@gmail.com> wrote:
Please note that I'm open for any other replication method that would allow
better scalability. One of the best option would be to have Multi-master
setup though I haven't seen any documentation around the support for such
capability by Postgres.

Postgresql doesn't support multi-master in the core project but there are options available. Have you come across BiDirectional Replication (BDR) yet?  https://2ndquadrant.com/en/resources/bdr/

Re: [ADMIN] Postgres - Replication

From
Alvaro Aguayo Garcia-Rada
Date:
I have some experience with BDR. Works fine.

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 954183248
Website: www.ocs.pe

----- Original Message -----
From: "bricklen" <bricklen@gmail.com>
To: "jasoninmel" <jasoninmel+1@gmail.com>
Cc: pgsql-admin@postgresql.org
Sent: Saturday, 18 March, 2017 11:41:48 AM
Subject: Re: [ADMIN] Postgres - Replication





On Thu, Mar 16, 2017 at 8:34 PM, jasoninmel < jasoninmel+1@gmail.com > wrote:



Please note that I'm open for any other replication method that would allow
better scalability. One of the best option would be to have Multi-master
setup though I haven't seen any documentation around the support for such
capability by Postgres.


Postgresql doesn't support multi-master in the core project but there are options available. Have you come across
BiDirectionalReplication (BDR) yet? https://2ndquadrant.com/en/resources/bdr/  


Re: [ADMIN] Postgres - Replication

From
jasoninmel
Date:
Thank you very much bricklen for the recommending BDR. It looks like a
potential candidate and will surely give it a try.



--
View this message in context: http://www.postgresql-archive.org/Postgres-Replication-tp5949678p5950815.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


Re: [ADMIN] Postgres - Replication

From
Om Prakash Jaiswal
Date:
I am using Bucardo.
It is working fine. It has been runinning for 5 years across 50 replicated nodes.
I strongly recommend Bucardo for master-master replication.
It is trigger based asynchronous replication system.

Regards
Om Prakash



On Saturday, 18 March 2017 10:24 PM, Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com> wrote:


I have some experience with BDR. Works fine.

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 954183248
Website: www.ocs.pe

----- Original Message -----
From: "bricklen" <bricklen@gmail.com>
To: "jasoninmel" <jasoninmel+1@gmail.com>
Cc: pgsql-admin@postgresql.org
Sent: Saturday, 18 March, 2017 11:41:48 AM
Subject: Re: [ADMIN] Postgres - Replication





On Thu, Mar 16, 2017 at 8:34 PM, jasoninmel < jasoninmel+1@gmail.com > wrote:



Please note that I'm open for any other replication method that would allow
better scalability. One of the best option would be to have Multi-master
setup though I haven't seen any documentation around the support for such
capability by Postgres.


Postgresql doesn't support multi-master in the core project but there are options available. Have you come across BiDirectional Replication (BDR) yet? https://2ndquadrant.com/en/resources/bdr/



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



Re: [ADMIN] Postgres - Replication

From
"Michael@sqlexec.com"
Date:
BDR works fine for writeloads that are geographically separate.  True multi-master is ACID compliant when you have a global transaction manager like Postgres-xc or Postgres-xl.

Regards


Sent from my iPad

On Mar 21, 2017, at 4:07 AM, Om Prakash Jaiswal <op12om@yahoo.co.in> wrote:

I am using Bucardo.
It is working fine. It has been runinning for 5 years across 50 replicated nodes.
I strongly recommend Bucardo for master-master replication.
It is trigger based asynchronous replication system.

Regards
Om Prakash



On Saturday, 18 March 2017 10:24 PM, Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com> wrote:


I have some experience with BDR. Works fine.

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 954183248
Website: www.ocs.pe

----- Original Message -----
From: "bricklen" <bricklen@gmail.com>
To: "jasoninmel" <jasoninmel+1@gmail.com>
Cc: pgsql-admin@postgresql.org
Sent: Saturday, 18 March, 2017 11:41:48 AM
Subject: Re: [ADMIN] Postgres - Replication





On Thu, Mar 16, 2017 at 8:34 PM, jasoninmel < jasoninmel+1@gmail.com > wrote:



Please note that I'm open for any other replication method that would allow
better scalability. One of the best option would be to have Multi-master
setup though I haven't seen any documentation around the support for such
capability by Postgres.


Postgresql doesn't support multi-master in the core project but there are options available. Have you come across BiDirectional Replication (BDR) yet? https://2ndquadrant.com/en/resources/bdr/



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



Re: [ADMIN] Postgres - Replication

From
Poul Kristensen
Date:
If you redirect you syslog to another server, the "pg_log" will be written in
/var/log/... and pg_log will just write that it has been redirected.

/Poul



2017-03-21 12:07 GMT+01:00 Michael@sqlexec.com <michael@sqlexec.com>:
BDR works fine for writeloads that are geographically separate.  True multi-master is ACID compliant when you have a global transaction manager like Postgres-xc or Postgres-xl.

Regards


Sent from my iPad

On Mar 21, 2017, at 4:07 AM, Om Prakash Jaiswal <op12om@yahoo.co.in> wrote:

I am using Bucardo.
It is working fine. It has been runinning for 5 years across 50 replicated nodes.
I strongly recommend Bucardo for master-master replication.
It is trigger based asynchronous replication system.

Regards
Om Prakash



On Saturday, 18 March 2017 10:24 PM, Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com> wrote:


I have some experience with BDR. Works fine.

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 954183248
Website: www.ocs.pe

----- Original Message -----
From: "bricklen" <bricklen@gmail.com>
To: "jasoninmel" <jasoninmel+1@gmail.com>
Cc: pgsql-admin@postgresql.org
Sent: Saturday, 18 March, 2017 11:41:48 AM
Subject: Re: [ADMIN] Postgres - Replication





On Thu, Mar 16, 2017 at 8:34 PM, jasoninmel < jasoninmel+1@gmail.com > wrote:



Please note that I'm open for any other replication method that would allow
better scalability. One of the best option would be to have Multi-master
setup though I haven't seen any documentation around the support for such
capability by Postgres.


Postgresql doesn't support multi-master in the core project but there are options available. Have you come across BiDirectional Replication (BDR) yet? https://2ndquadrant.com/en/resources/bdr/



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin






--
Med venlig hilsen / Best regards
Poul Kristensen
Linux-OS/Virtualizationexpert and Oracle DBA

Re: [ADMIN] Postgres - Replication

From
Om Prakash Jaiswal
Date:
Bacardo asynchronous tool is very good for master master as well as geographical distributed location.

Regards
Om Prakash.


On Tue, 21 Mar 2017 at 19:16, Poul Kristensen
<bcc5226@gmail.com> wrote:
If you redirect you syslog to another server, the "pg_log" will be written in
/var/log/... and pg_log will just write that it has been redirected.

/Poul



2017-03-21 12:07 GMT+01:00 Michael@sqlexec.com <michael@sqlexec.com>:
BDR works fine for writeloads that are geographically separate.  True multi-master is ACID compliant when you have a global transaction manager like Postgres-xc or Postgres-xl.

Regards


Sent from my iPad

On Mar 21, 2017, at 4:07 AM, Om Prakash Jaiswal <op12om@yahoo.co.in> wrote:

I am using Bucardo.
It is working fine. It has been runinning for 5 years across 50 replicated nodes.
I strongly recommend Bucardo for master-master replication.
It is trigger based asynchronous replication system.

Regards
Om Prakash



On Saturday, 18 March 2017 10:24 PM, Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com> wrote:


I have some experience with BDR. Works fine.

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 954183248
Website: www.ocs.pe

----- Original Message -----
From: "bricklen" <bricklen@gmail.com>
To: "jasoninmel" <jasoninmel+1@gmail.com>
Cc: pgsql-admin@postgresql.org
Sent: Saturday, 18 March, 2017 11:41:48 AM
Subject: Re: [ADMIN] Postgres - Replication





On Thu, Mar 16, 2017 at 8:34 PM, jasoninmel < jasoninmel+1@gmail.com > wrote:



Please note that I'm open for any other replication method that would allow
better scalability. One of the best option would be to have Multi-master
setup though I haven't seen any documentation around the support for such
capability by Postgres.


Postgresql doesn't support multi-master in the core project but there are options available. Have you come across BiDirectional Replication (BDR) yet? https://2ndquadrant.com/en/ resources/bdr/



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/ mailpref/pgsql-admin






--
Med venlig hilsen / Best regards
Poul Kristensen
Linux-OS/Virtualizationexpert and Oracle DBA