Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YXlgK-0000cX-Cd for pgsql-general@arkaria.postgresql.org; Tue, 17 Mar 2015 07:12:52 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YXlgJ-0002Dt-27 for pgsql-general@arkaria.postgresql.org; Tue, 17 Mar 2015 07:12:51 +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 1YXlgH-0002Dg-BJ; Tue, 17 Mar 2015 07:12:49 +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 1YXlgC-0000TC-Tg; Tue, 17 Mar 2015 07:12:47 +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 0B39026F67F; Tue, 17 Mar 2015 08:12:41 +0100 (CET) From: "Ruth Melendo" To: "'Craig Ringer'" Cc: "'pgsql-admin'" , References: <001801d05fef$e8dca0a0$ba95e1e0$@teltronic.es> In-Reply-To: Subject: Re: [ADMIN] pgpool + BDR, is it possible? Date: Tue, 17 Mar 2015 08:12:52 +0100 Message-ID: <005701d06081$c8acf450$5a06dcf0$@teltronic.es> MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_NextPart_000_0058_01D0608A.2A72E2F0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQKit4q4wfTCuiNBr4Gl+K58GKyStAJgjQb9m2iM62A= 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-general Precedence: bulk Sender: pgsql-general-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_0058_01D0608A.2A72E2F0 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0059_01D0608A.2A72E2F0" ------=_NextPart_001_0059_01D0608A.2A72E2F0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Ok, I will try to explain my environment. =20 I want to have a high availability environment in active/active mode. = I=C2=B4m building a cluster with 2 or more nodes, with Apache and JBOSS. = Apache works as proxy and balancer and JBOSS serves my application. I = have high availability in JBOSS thanks to mod_proxy. But, from = application layer, each node needs to connect to database (Postgresql = and PostGIS).=20 =20 NODE (1-N) =C2=B7 Apache (active/passive) =C2=B7 Mod_cluster =C2=B7 JBOSS (active/active) =C2=B7 PostgreSQL + PostGIS (active/active) =20 The way I=C2=B4m working now is that each node connects to its own = database. I have solved the high availability problem in JBOSS but not = in PostgreSQL because in my node JBOSS may be down but PostgreSQL may be = up and the other way. I was thinking about connecting from application = to the pgpool IP from all nodes and this problem would be solved. =20 And about Barman, tried to use it just for backups but BDR just work = fine for me (based on the tests I=C2=B4ve done) in logical replication = and Barman need physical one. About Barman, to restore database =20 1.- Stop nodes=20 2.- Disable BDR in node 1 3.- Backup node 1 4.- Start node 1 5.- Delete data directory in node 2 6.- Start node 2 with BDR active. =20 That would be the recovery process? =20 Thank you very much for your help. =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: pgsql-admin-owner@postgresql.org = [mailto:pgsql-admin-owner@postgresql.org] En nombre de Craig Ringer Enviado el: martes, 17 de marzo de 2015 4:57 Para: Ruth Melendo CC: pgsql-admin; psql-general@postgresql.org Asunto: Re: [ADMIN] pgpool + BDR, is it possible? =20 =20 =20 On 16 March 2015 at 21:48, Ruth Melendo wrote: Hi all, =20 =C2=BFDoes anybody know if I can use pgpool as a balancer between 2 = nodes that have BDR master/master configured for replication? =20 It's possible. In most cases I think it's probably going to be a very = bad idea, though. Most applications cannot just be pointed at multiple nodes and expected = to work properly. BDR doesn't have a global lock manager or global = transaction manager, so anomalies can occur that cannot on a single = node. Applications must take extra steps to avoid them or be tolerant of = them. If you take an app that expects ACID semantics and READ COMMITTED = isolation then, without it knowing, you send some of its transactions to = each of two semi-independent nodes, you're going to get fireworks. If your goal is write scaling, then it's not likely to do you a great = deal of good anyway, because each node still has to apply the writes = from the other node. So the writes still have to get done, just on = another channel. I'm speaking of the most general case, where I have no idea of what = problem you're trying to solve or what your application is. If you can = be more specific about the problem you're trying to solve by doing this = and what the app does perhaps I can tell you more about whether/how BDR = can help you, and if not point you to other solutions that might. =20 =20 I have tried also Barman but does not work in logical replication and = repmgr that does not work master/master. =20 repmgr doesn't understand multi-master and BDR yet, correct. PgBarman works fine with BDR. It doesn't care that the database(s) it's = backing up are BDR nodes. However you can't just restore a single node - = to do a restore you have to shut the lot down, restore one node, then = re-init new nodes from the one you restored. --=20 Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services ------=_NextPart_001_0059_01D0608A.2A72E2F0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Ok, I will try to explain my environment.

 

I want to have a high availability environment in active/active mode. = I=C2=B4m building a cluster with 2 or more nodes, with Apache and JBOSS. = Apache works as proxy and balancer and JBOSS serves my application. I = have high availability in JBOSS thanks to mod_proxy. But, from = application layer, each node needs to connect to database (Postgresql = and PostGIS).

 

NODE (1-N)

=C2=B7         = Apache (active/passive)

=C2=B7         = Mod_cluster

=C2=B7         = JBOSS (active/active)

=C2=B7         = PostgreSQL + PostGIS (active/active)

 

The way I=C2=B4m working now is that each node connects to its own = database. I have solved the high availability problem in JBOSS but not = in PostgreSQL because in my node JBOSS may be down but PostgreSQL may be = up and the other way. I was thinking about connecting from application = to the pgpool IP from all nodes and this problem would be = solved.

 

And about Barman, tried to use it just for backups but BDR just work = fine for me (based on the tests I=C2=B4ve done) in logical replication = and Barman need physical one.

About Barman, to restore database

 

1.- Stop nodes

2.- Disable BDR in node 1

3.- Backup node 1

4.- Start node 1

5.- Delete data directory in node 2

6.- Start node 2 with BDR active.

 

That would be the recovery process?

 

Thank you very much for your help.

 

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: = pgsql-admin-owner@postgresql.org = [mailto:pgsql-admin-owner@postgresql.org] En nombre de Craig = Ringer
Enviado el: martes, 17 de marzo de 2015 = 4:57
Para: Ruth Melendo
CC: pgsql-admin; = psql-general@postgresql.org
Asunto: Re: [ADMIN] pgpool + BDR, = is it possible?

 

 

 

On 16 = March 2015 at 21:48, Ruth Melendo <rmelendo@teltronic.es> = wrote:

Hi = all,

 <= /o:p>

=C2=BFDoes anybody know if I can use pgpool as a balancer = between 2 nodes that have BDR  master/master configured for = replication?

 

It's possible. In most cases I think it's = probably going to be a very bad idea, = though.

Most applications cannot just be pointed = at multiple nodes and expected to work properly. BDR doesn't have a = global lock manager or global transaction manager, so anomalies can = occur that cannot on a single node. Applications must take extra steps = to avoid them or be tolerant of them.

If you take an app that = expects ACID semantics and READ COMMITTED isolation then, without it = knowing, you send some of its transactions to each of two = semi-independent nodes, you're going to get = fireworks.

If your goal is write scaling, then it's = not likely to do you a great deal of good anyway, because each node = still has to apply the writes from the other node. So the writes still = have to get done, just on another channel.

I'm speaking of the most general case, where I have no = idea of what problem you're trying to solve or what your application is. = If you can be more specific about the problem you're trying to solve by = doing this and what the app does perhaps I can tell you more about = whether/how BDR can help you, and if not point you to other solutions = that might.


 

 

I have tried also Barman but does not work in logical = replication and repmgr that does not work = master/master.

 

repmgr doesn't understand multi-master = and BDR yet, correct.

PgBarman works fine with BDR. It doesn't care that the = database(s) it's backing up are BDR nodes. However you can't just = restore a single node - to do a restore you have to shut the lot down, = restore one node, then re-init new nodes from the one you restored.


-- =

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

<= /html> ------=_NextPart_001_0059_01D0608A.2A72E2F0-- ------=_NextPart_000_0058_01D0608A.2A72E2F0 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+ZcALgM3yjHQ4rVrHEM7XbRkhnJLueoP93P9BXsxk47M45QjL4lc0HuJFkZVh8w jsrjOPXBrNv4rfVlxc6Z54jO3cZUBU+mQamitbhrdd8eJCcPl+XH1HbPPqaFsZXtJEeJNwclFzgH t+HHSs7STvGTX3f5FNRkrSVyK2hk06FY7DT7a1Rmx88nLH8M5/OrBsr24yLm8Cof4YVxn8TQbOZp kRo4xDGVZCG+7jtitGs5Ued3nJv5/wCVioy5VaKSKtrpttaHMUfzf3m5NWqKKuEIwXLFWQnJyd2F NCKpYgAFjkn1ooqxDqKKKACiiigAooooA//Z ------=_NextPart_000_0058_01D0608A.2A72E2F0--