Re: [ADMIN] pgpool + BDR, is it possible?

["Ruth Melendo" <rmelendo@xxxxxxxxxxxx>]

Ok, I will try to explain my environment.

 

I want to have a high availability environment in active/active mode. I´m 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)

·         Apache (active/passive)

·         Mod_cluster

·         JBOSS (active/active)

·         PostgreSQL + PostGIS (active/active)

 

The way I´m 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´ve 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   Ext. 179

F: +34 976 465722

www.teltronic.es

 

De: pgsql-admin-owner@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] En nombre de Craig Ringer
Enviado el: martes, 17 de marzo de 2015 4:57
Para: Ruth Melendo
CC: pgsql-admin; psql-general@xxxxxxxxxxxxxx
Asunto: Re: [ADMIN] pgpool + BDR, is it possible?

 

 

 

On 16 March 2015 at 21:48, Ruth Melendo <rmelendo@xxxxxxxxxxxx> wrote:

Hi all,

 

¿Does 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                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services