Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YXoBP-0005dZ-S1 for pgsql-admin@arkaria.postgresql.org; Tue, 17 Mar 2015 09:53:08 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YXoBP-0005tT-6m for pgsql-admin@arkaria.postgresql.org; Tue, 17 Mar 2015 09:53:07 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YXoBN-0005tF-Oy for pgsql-admin@postgresql.org; Tue, 17 Mar 2015 09:53:06 +0000 Received: from correo.teltronic.es ([93.92.233.233] helo=teltronic.es) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YXoBJ-0003RN-8E for pgsql-admin@postgresql.org; Tue, 17 Mar 2015 09:53:03 +0000 Received: from RMELENDOW7 (unknown [192.168.8.40]) (using TLSv1 with cipher AES256-SHA (256/256 bits)) (No client certificate requested) by correo.teltronic.es (Postfix) with ESMTP id EAC7344C981; Tue, 17 Mar 2015 10:52:58 +0100 (CET) From: "Ruth Melendo" To: "'Craig Ringer'" Cc: "'pgsql-admin'" References: <001801d05fef$e8dca0a0$ba95e1e0$@teltronic.es> <005701d06081$c8acf450$5a06dcf0$@teltronic.es> <007801d06093$5abd0360$10370a20$@teltronic.es> In-Reply-To: Subject: Re: pgpool + BDR, is it possible? Date: Tue, 17 Mar 2015 10:52:33 +0100 Message-ID: <008d01d06098$2d706320$88512960$@teltronic.es> MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_NextPart_000_008E_01D060A0.8F3651C0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQKit4q4wfTCuiNBr4Gl+K58GKyStAJgjQb9AWRyCJEBvy3o8AJr3CtVAX6S9DGbMEpBAA== Content-Language: es X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_008E_01D060A0.8F3651C0 Content-Type: multipart/alternative; boundary="----=_NextPart_001_008F_01D060A0.8F3651C0" ------=_NextPart_001_008F_01D060A0.8F3651C0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Ok, Thanks.=20 =20 I have tuned my db to work with global sequences in every table. I think = that will solve most of the problems. Cartography will be updated just = from time to time. That shouldn=C2=B4t cause problems.=20 =20 But now, my dude is about pgpool. Do you think It can work well just as = a balancer in this environment? =20 =20 My main troubles from the beginning have been in recovery and I = dind=C2=B4t find detailed doc in that way. Most of times, I need to = delete data directory in node 2 and starts init replication again. =20 1.- Stop both nodes and comment BDR entries in postgresql.conf=20 2.- Delete data directory in node 2. 3.- Start node 1 without BDR so that BDR schema drops. 4.- Restart node 1 with BDR active 5.- Start node 2 with BDR active so that init replica happens and BDR = gets well configured from here in both nodes. =20 Is that the best way to recovery? There is something I can do to recover = database without all these steps? =20 Ruth Patricia Melendo Ventura Software Engineer TELTRONIC, S.A.U. T: +34 976 465656 Ext. 179 F: +34 976 465722=20 www.teltronic.es Logo40 =20 De: Craig Ringer [mailto:craig@2ndquadrant.com]=20 Enviado el: martes, 17 de marzo de 2015 10:30 Para: Ruth Melendo CC: pgsql-admin Asunto: Re: [ADMIN] pgpool + BDR, is it possible? =20 =20 =20 On 17 March 2015 at 17:18, Ruth Melendo wrote: Thanks for your help. =20 My app is a GIS Server and we strongly need high availability. The = reason to be master/master is to share load because we have a lot of = users and as, this is an app for security sector, each node must be able = to work alone to ensure availability all the time.=20 =20 If that's a requirement, then you're going to need to write the = application to cope with the consequences. What if, while there's replication lag due to network issues, someone = creates a row with id "42" in one node. Someone else creates a row with = id "42" in the same table in another node? The app has to be able to deal with that, or use things (like BDR's = global sequences) to prevent it. And you can't always prevent it unless = writeable tables are basically append-only, since two UPDATEs that = affect the same row can also conflict. So ... *if* you can review your application and change it where = necessary to cope with the anomalies that can arise in asynchronous = multi-master replication, BDR will be absolutely ideal for your needs. = That's pretty much what it's for. But you need to be able to do that = review and adjustment rather than just pointing your app at a couple of = BDR nodes and hoping for the best. BDR has conflict logging and conflict statistics features that will help = you during testing, too; see the docs for details on them. --=20 Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services ------=_NextPart_001_008F_01D060A0.8F3651C0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Ok, Thanks.

 

I have tuned my db to work with global sequences in every table. I = think that will solve most of the problems. =C2=A0Cartography will be = updated just from time to time. That shouldn=C2=B4t cause problems. =

 

But now, my dude is about pgpool. Do you think It can work well just = as a balancer in this environment? =C2=A0=C2=A0

 

My main troubles from the beginning have been in recovery and I = dind=C2=B4t find detailed doc in that way. Most of times, I need to = delete data directory in node 2 and starts init replication = again.

 

1.- Stop both nodes and comment BDR entries in postgresql.conf =

2.- Delete data directory in node 2.

3.- Start node 1 without BDR so that BDR schema = drops.

4.- Restart node 1 with BDR active

5.- Start node 2 with BDR active so that init replica happens and BDR = gets well configured from here in both nodes.

 

Is that the best way to recovery? There is something I can do to = recover database without all these steps?

 

Ruth Patricia = Melendo Ventura

Software Engineer

TELTRONIC, S.A.U.=

T: +34 976 465656=C2=A0=C2=A0 Ext. 179

F: +34 976 465722

www.teltronic.es

=

3DLogo40

 

De: = Craig Ringer [mailto:craig@2ndquadrant.com]
Enviado el: = martes, 17 de marzo de 2015 10:30
Para: Ruth = Melendo
CC: pgsql-admin
Asunto: Re: [ADMIN] pgpool + = BDR, is it possible?

 

 

 

On 17 = March 2015 at 17:18, Ruth Melendo <rmelendo@teltronic.es> = wrote:

Thanks for your help.

 

My app is a GIS Server and we strongly need high availability. The = reason to be master/master is to share load because we have a lot of = users and as, this is an app for security sector, each node must be able = to work alone to ensure availability all the time. =

 

If that's a requirement, then you're = going to need to write the application to cope with the = consequences.

What if, while there's replication lag = due to network issues, someone creates a row with id "42" in = one node. Someone else creates a row with id "42" in the same = table in another node?

The app has to be able to deal with that, = or use things (like BDR's global sequences) to prevent it. And you can't = always prevent it unless writeable tables are basically append-only, = since two UPDATEs that affect the same row can also = conflict.

So ... *if* you can review your = application and change it where necessary to cope with the anomalies = that can arise in asynchronous multi-master replication, BDR will be = absolutely ideal for your needs. That's pretty much what it's for. But = you need to be able to do that review and adjustment rather than just = pointing your app at a couple of BDR nodes and hoping for the = best.

BDR has conflict = logging and conflict statistics features that will help you during = testing, too; see the docs for details on = them.



--

 Craig = Ringer           &= nbsp;       http://www.2ndQuadrant.com/
 PostgreSQL = Development, 24x7 Support, Training & = Services

<= /html> ------=_NextPart_001_008F_01D060A0.8F3651C0-- ------=_NextPart_000_008E_01D060A0.8F3651C0 Content-Type: image/jpeg; name="image001.jpg" Content-Transfer-Encoding: base64 Content-ID: /9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAoHBwkHBgoJCAkLCwoMDxkQDw4ODx4WFxIZJCAmJSMg IyIoLTkwKCo2KyIjMkQyNjs9QEBAJjBGS0U+Sjk/QD3/2wBDAQsLCw8NDx0QEB09KSMpPT09PT09 PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT3/wAARCAA0AD8DASIA AhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAAAgEDAwIEAwUFBAQA AAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkKFhcYGRolJicoKSo0NTY3 ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWGh4iJipKTlJWWl5iZmqKjpKWm p6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl5ufo6erx8vP09fb3+Pn6/8QAHwEA AwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREAAgECBAQDBAcFBAQAAQJ3AAECAxEEBSEx BhJBUQdhcRMiMoEIFEKRobHBCSMzUvAVYnLRChYkNOEl8RcYGRomJygpKjU2Nzg5OkNERUZHSElK U1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6goOEhYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba3 uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk5ebn6Onq8vP09fb3+Pn6/9oADAMBAAIRAxEAPwD2as3W NdstEg8y6k+cj5I15Zvw/rVXxJ4hXRoVjgCy3knCR+g9T/h3qhonhYmb+0tcYzXbHcEc5VPr7+3Q VvCklHnqbfmc06zc/Z0tX1fRFC5vta121kvJLn+x9IUZ3Z/eOPboTnt0/GtTwHMW8LhpCwjWWTY8 jcsuc5Pp3/KsTV7mbxl4gTSLNtunwNukde+OC39B+dXfF+pCzt4tFsAFUoA4Uchf4VH1rqlBzSpJ Wb19F5+ZyKoqblWbulp6vy8iDxD41mkla00clcNtMwGS/so/rWSvh3xIxfUt7pNbjzA0kx3nvgf1 H4V1vhXwzHpUC3Vyub2ReQekY9B7+tXvE+oDTPD15OSA3llE92PA/nS9tGElTor59yvYSqRdWu/O 3Yd4c1Vta0K1vXXbJIuHA6bgcHH5VqVyXhWefR/DtjFc27eS671cdRuOf611MM8dwgeJwyn0rzZV qUqsqcXqm9D0acZ+yjKXVI8+tNVtIvEdzqGrJK0iufLRVDbSDjnnsBiruu+OYLnTZbbT45lklXaZ HAG0d+/pWxe+DdOvruS4Zp42kO5ljYAZ79q4zXdKtbDxFFp8Bk8rMYdpG5O488/Q168HRrST1ul8 tDxpRxGHi1pZvfq7kGg+IrnRQ1vp9nDLPcMBufJY+gABrd8MWj6z4hl1C6wfKbzG9N56Y+n+Fdja 6TYWQ/0W0hiONu5EAbH161yGl6onhK7vrS+gmO5wyFAOQM4644qfaqqp+zj7xU6Loyp+1l7qb9F2 O6JCqSxwBySa871S9PjXxNa6fa7jYQtlmxjcP4m/LgVHquv6l4omFjYQOkJP+rQ5LD1Y+ldh4b8O xaBaMu4SXEpBkkxj8B7CsVBYZc0vi6LsdLqfWpcsPhW77+RqG3iNuINi+UFChewHasCCZtOvZFU5 VSVI9fStW/1SO1BSMiSY8BR2+tVtM01y/wBouRyckKe+e5r5vG3r14Ro/Et329f8j2aPuQbnszYr Km8P6fPrH9ozx+ZcALgM3yjHQ4rVrHEM7XbRkhnJLueoP93P9BXsxk47M45QjL4lc0HuJFkZVh3k dlcZx64NZt/Fb6suLnTPPEZ27jKgKn0yDU0VrcNbrvjxITh8vy4+o7Z59TQtjK9pIjxJuDkoucA9 vw46VnaSd4ya+7/IpqMlaSuRW0MmnQrHYafbWqM2Pnk5Y/hnP51YNle3GRc3gVD/AAwrjP4mg2cz TIjRxiGMqyEN93HbFaNZyo87vOTfz/ysVGXKrRSRVtdNtrQ5ij+b+83Jq1RRVwhGC5YqyE5OTuwp oRVLEAAsck+tFFWIdRRRQAUUUUAFFFFAH//Z ------=_NextPart_000_008E_01D060A0.8F3651C0--