Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hEmHO-0006uD-BV for pgsql-admin@arkaria.postgresql.org; Fri, 12 Apr 2019 02:51:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hEmHM-0003L9-U2 for pgsql-admin@arkaria.postgresql.org; Fri, 12 Apr 2019 02:51:00 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hEmHM-0003Dv-2W for pgsql-admin@lists.postgresql.org; Fri, 12 Apr 2019 02:51:00 +0000 Received: from mail-oln040092254055.outbound.protection.outlook.com ([40.92.254.55] helo=APC01-PU1-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hEmHH-00035I-DU for pgsql-admin@lists.postgresql.org; Fri, 12 Apr 2019 02:50:58 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=hotmail.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=0vIJUMG9yAY6WfV7Bdw8oKPGzw8s9Z+YyfRIf9Ty8/g=; b=aMeE7GvTx6HqHd4khCpWM2QsUgi/cLT/UvCt+x35Uqv522Fmc3JYbdTfw4Jf//UiSaRbmwREozVO4NzaGpZn8rHGQOp+j86sA01QPl92wCw64c4dK6hDn6vg1jwuTlj2DjEP7jkADAyoDV7PfUZAoDVaIWHuidXw2ceYhEvB1fh50IJbFYaL6+W+X46ET6vSop//fhW2gYEHY8Xy4LVOoiLSRAE+1THcyngTMCm7I0bUHO5SONksg+pNjnrsIk7hRgx6bqIVqvAS3szH7PRcfTt9dbLoMNFJMMc5+8lXqofuTqbU1q9biBkEe9Fryw9fcsIPfLw/VCP0S8on5hIHKQ== Received: from SG2APC01FT112.eop-APC01.prod.protection.outlook.com (10.152.250.52) by SG2APC01HT149.eop-APC01.prod.protection.outlook.com (10.152.251.123) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384) id 15.20.1771.16; Fri, 12 Apr 2019 02:50:50 +0000 Received: from MA1PR01MB3548.INDPRD01.PROD.OUTLOOK.COM (10.152.250.51) by SG2APC01FT112.mail.protection.outlook.com (10.152.250.201) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.1771.16 via Frontend Transport; Fri, 12 Apr 2019 02:50:50 +0000 Received: from MA1PR01MB3548.INDPRD01.PROD.OUTLOOK.COM ([fe80::c1f8:460e:124a:283]) by MA1PR01MB3548.INDPRD01.PROD.OUTLOOK.COM ([fe80::c1f8:460e:124a:283%2]) with mapi id 15.20.1771.016; Fri, 12 Apr 2019 02:50:50 +0000 From: soumitra bhandary To: Rijo Roy , "pgsql-admin@lists.postgresql.org" , Prince Pathria , "bdr-list@2ndquadrant.com" Subject: Re: BDR OR MASTER SLAVE REPLICATION WITH REPMGR IS SUITABLE FOR LARGE ONLINE APPLICATION Thread-Topic: BDR OR MASTER SLAVE REPLICATION WITH REPMGR IS SUITABLE FOR LARGE ONLINE APPLICATION Thread-Index: AQHU8HN9YH+0uD6VV0KJ8jQM4DxcuaY3UeoAgABkYReAABMJAIAACfIE Date: Fri, 12 Apr 2019 02:50:50 +0000 Message-ID: References: , In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-incomingtopheadermarker: OriginalChecksum:1986F7BB3608A518FE2193110AD7B3509CF9B5B120F01AD2474C04F1461B4CFD;UpperCasedChecksum:D4F4B3FA9BBFEB16CA57A241E7A7216A354F500AABDEA4C6C1F1B32522D4F20B;SizeAsReceived:7351;Count:44 x-ms-exchange-messagesentrepresentingtype: 1 x-tmn: [JFIiKYPkZgGNb1gRGwaoXOOvb7AHX76+] x-ms-publictraffictype: Email x-incomingheadercount: 44 x-eopattributedmessage: 0 x-microsoft-antispam: BCL:0;PCL:0;RULEID:(2390118)(7020095)(20181119110)(201702061078)(5061506573)(5061507331)(1603103135)(2017031320274)(2017031323274)(2017031324274)(2017031322404)(1601125500)(1603101475)(1701031045);SRVR:SG2APC01HT149; x-ms-traffictypediagnostic: SG2APC01HT149: x-ms-exchange-purlcount: 3 x-microsoft-antispam-message-info: x+dzxzpTc0PH0remdR2emk8YMRYdVEQSdF8kr3rne6BC5gmbemVL28lSwBv/DStI8AuLrue2KDYDoFKO5cLQohG4q0ampach3zPTUOU0nJpUAL/Xm9npKl7PLEq6tdYB57oFvRUfWw96lctaqxtM4NgmOn3IUAykmTBpTXsTkdtAkjG0X1HrF+dVLTjw6tP3 Content-Type: multipart/alternative; boundary="_000_MA1PR01MB35481282A696D5EB16A19FCBE0280MA1PR01MB3548INDP_" MIME-Version: 1.0 X-OriginatorOrg: hotmail.com X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-CrossTenant-Network-Message-Id: ce41d16b-cc94-45df-07e1-08d6bef1ac56 X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-CrossTenant-originalarrivaltime: 12 Apr 2019 02:50:50.5998 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Internet X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa X-MS-Exchange-Transport-CrossTenantHeadersStamped: SG2APC01HT149 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_MA1PR01MB35481282A696D5EB16A19FCBE0280MA1PR01MB3548INDP_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Thanks for the response . Here is my observation / queries about BDR , correct me if I am missing a= nything . Any other issue with BDR if you guys faced let me know . 1. Due to any reason if replication is failing and replication log byte = size reaches more than 1GB . Then corresponding replication slot become unr= esponsive and need to set up BDR again. 2. Due to any issue (may be network failure or any key conflicts) if rep= lication is stopped or failing then apart from re initializing the cluster = (using bdr_init_copy ) any other approach is there to revive the replication ? As bdr.= bdr_group_join does not work well for large databases for more t= han 100 GB . 3. For a large OLTP application does BDR replicates the changes made to = procedure, function ,triggers , views definition, across the cluster if I= set the replication at DB level ? (I am not sure as not tested) 4. Though BDR tries to resolve conflicts using a simple last-update-wins= strategy, but still conflicts as mentioned below are very common in a prod= uction instance . Are these taken care in the licensed version ? ERROR: insert or update on table "AAAAA" violates foreign key constraint "= fk_traf444kk6qrkms7n56aiwq5y" Violating foreign key constraint ,whereas other node is having record. ERROR: multiple unique constraints violated by remotely Inserted tuple An INSERT/INSERT conflict can violate more than one UNIQUE constraint (of w= hich one might be the PRIMARY KEY). 1. Back up , recovery and PITR process . As per the documentation , Logi= cal replication cannot be used for point-in-time recovery . So what is the = extent of this statement ? Does it imply even we run the DBs in archival m= ode and use pg_basebackup to take file level back up in a running instance? Thanks , Soumitra ________________________________ From: Rijo Roy Sent: Friday, April 12, 2019 7:42 AM To: soumitra bhandary Cc: Prince Pathria; pgsql-admin@lists.postgresql.org Subject: Re: BDR OR MASTER SLAVE REPLICATION WITH REPMGR IS SUITABLE FOR LA= RGE ONLINE APPLICATION BDR is best when you have a need of multi-master environment like a distrib= uted database paradigm where you have a need of writing/reading to/from loc= al masters let's say masters in NY, London, China, India etc.. And it uses = pglogical to achieve this with a globally managed transaction XID's. It nee= ds to be properly tested how it handles conflicts in a heavy oltp workload = application. If you only need high availability for a heavy oltp application, a well set= active passive model of streaking replication backed by repmgr where write= s are only hitting primary and reads on standby will help. Otherwise you ca= n also consider citusdata.. Note: I am not an advocate of any vendors, my comments are purely based on = my experience and I love Postgresql the way it is! Regards, Rijo Roy On Fri, 12 Apr 2019, 6:34 am soumitra bhandary, > wrote: To ensure zero down time. Sent from my iPhone On 12-Apr-2019, at 12:35 AM, Prince Pathria > wrote: It very much depends upon your use case. Why you want a multi master implem= entation? Happy to help :) Prince Pathria Systems Engineer Evive +91 9478670472 goevive.com On Thu, Apr 11, 2019 at 8:03 PM soumitra bhandary > wrote: Hi , Can anyone suggest me for large OLTP application which sort of replication = is suitable BDR or master slave replication with synchronous and asynchrono= us node and REPMGR enabled. Can BDR handle real-time conflicts arises due to high network latency or Da= taintegrity conflicts?? Please suggest Sent from my iPhone --_000_MA1PR01MB35481282A696D5EB16A19FCBE0280MA1PR01MB3548INDP_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Thanks for the response . 

Here is my observation / queries  about BDR ,  correct me if I am= missing anything . Any other issue with BDR if you guys faced let me = know .  

  1. Due to = any reason if replication is failing and replication log byte size reaches = more than 1GB . Then corresponding replication slot become unresponsive and= need to set up BDR again.
  2. Due to = any issue (may be network failure or any key conflicts) if replication is s= topped or failing then apart from re initializing the cluster (using <= span style=3D"font-size:9.0pt;font-family:"Verdana",sans-serif;co= lor:#004E66">bdr_init_copy )  any other approach is there to re= vive the replication ? As bdr.bdr_group_join  does not work well for large databases for more than 100 GB .
  3. For a l= arge OLTP application does BDR replicates the changes made to procedure, fu= nction ,triggers  , views definition,  across the cluster if I se= t the replication at DB level ? (I am not sure as not tested)
  4. Though = BDR tries to resolve conflicts using a simple last-update-wins strategy, bu= t still conflicts as mentioned below are very common in a production instan= ce . Are these taken care in the licensed version ?

ERROR:  insert or update on table "AA= AAA" violates foreign key constraint "fk_traf444kk6qrkms7n56aiwq5= y"

Violating foreign key constraint ,whereas other= node is having record.

ERROR: multiple unique constraints violated by = remotely Inserted tuple

An INSERT/INSERT conflict can violate more than= one UNIQUE constraint (of which one might be the PRIMARY KEY).
  1. Back up= , recovery and PITR process . As per the documentation , Logical replication cannot be used for point-in-time reco= very . So what is the extent of this statement ?  Does it imply even we run the DBs in archival mode and use pg_basebackup to take file le= vel back up in a running instance?

Thanks , 
Soumitra 

From: Rijo Roy <royvk664= 4@gmail.com>
Sent: Friday, April 12, 2019 7:42 AM
To: soumitra bhandary
Cc: Prince Pathria; pgsql-admin@lists.postgresql.org
Subject: Re: BDR OR MASTER SLAVE REPLICATION WITH REPMGR IS SUITABLE= FOR LARGE ONLINE APPLICATION
 
BDR is best when you have a need of multi-master environment like a di= stributed database paradigm where you have a need of writing/reading to/fro= m local masters let's say masters in NY, London, China, India etc.. And it = uses pglogical to achieve this with a globally managed transaction XID's. It needs to be properly tested how i= t handles conflicts in a heavy oltp workload application.
If you only need high availability for a heavy oltp appli= cation, a well set active passive model of streaking replication backed by = repmgr where writes are only hitting primary and reads on standby will help= . Otherwise you can also consider citusdata.. 

Note: I am not an advocate of any vendors, my comments ar= e purely based on my experience and I love Postgresql the way it is!

Regards, 
Rijo Roy 


On Fri, 12 Apr 2019, 6:34 am soumit= ra bhandary, <soumitra.= bhandary@hotmail.com> wrote:
To ensure zero down time. 

Sent from= my iPhone

On 12-Apr-2019, at 12:35 AM, Prince Pathria <prince.pathria@goev= ive.com> wrote:

It very much depends upon your use case. Why you want a mu= lti master implementation? 
 
Happy to help :)
Prince Pathria Systems Engineer Evive +91 94= 78670472 goevive= .com


On Thu, Apr 11, 2019 at 8:03 PM sou= mitra bhandary <soumitra.bhandary@hotmail.com> wrote:=
Hi ,

Can anyone suggest me for large OLTP application which sort of replication = is suitable BDR or master slave replication with synchronous and asynchrono= us node and REPMGR enabled.
Can BDR handle real-time conflicts arises due to high network latency or Da= taintegrity conflicts?? Please suggest

Sent from my iPhone

--_000_MA1PR01MB35481282A696D5EB16A19FCBE0280MA1PR01MB3548INDP_--