pgpool + BDR, is it possible?

On 16 March 2015 at 21:48, Ruth Melendo <rmelendo@teltronic.es> 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

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

<http://www.teltronic.es/&gt; www.teltronic.es

Logo40

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,

¿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

(I've dropped the Cc of pgsql-general to avoid cross-posting)

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

Ok, I will try to explain my environment.

I want to have a high availability environment in active/active mode.
[snip]

Right, so that's some of the architecture, but nothing on the application.

You need to understand that you'd be working with asynchronous replication
here. The app has to be aware of and written to cope with anomalies arising
from that, like insert/insert conflicts, etc. BDR provides some tools to
help, like global sequences and user-defined conflict handlers, but it's
*not* the same thing as just pointing the app at a standalone PostgreSQL
server. The nodes are *not* always consistent with each other (because it's
asynchronous) so the app can see older data on one node than on another.
That sort of thing.

If your goal is HA then I strongly recommend an active/passive setup with
failover. It will be simpler and not require app changes. BDR comes into
its own when you need latency tolerance, partition tolerance, etc, and it
doesn't sound like you do.

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?

Since there's no restore in that list, no, it wouldn't. I haven't written
step by step guidance there, but it's something that's worth adding to the
documentation, so I'll note that.

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.

The 2 databases I want to configure for replica master/master are the admin database which has no high amount of transactions and the cartography one. The data db does not need replication because each node has its own data cache coming from another control application.

With this description, do you still think that best way is active/passive? I´m not sure about that and it is taking me much time to take a decision…. :/

Ruth Patricia Melendo Ventura

Software Engineer

TELTRONIC, S.A.U.

T: +34 976 465656 Ext. 179

F: +34 976 465722

<http://www.teltronic.es/&gt; www.teltronic.es

Logo40

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 9:31
Para: Ruth Melendo
CC: pgsql-admin
Asunto: Re: [ADMIN] pgpool + BDR, is it possible?

(I've dropped the Cc of pgsql-general to avoid cross-posting)

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

Ok, I will try to explain my environment.

I want to have a high availability environment in active/active mode. [snip]

Right, so that's some of the architecture, but nothing on the application.

You need to understand that you'd be working with asynchronous replication here. The app has to be aware of and written to cope with anomalies arising from that, like insert/insert conflicts, etc. BDR provides some tools to help, like global sequences and user-defined conflict handlers, but it's *not* the same thing as just pointing the app at a standalone PostgreSQL server. The nodes are *not* always consistent with each other (because it's asynchronous) so the app can see older data on one node than on another. That sort of thing.

If your goal is HA then I strongly recommend an active/passive setup with failover. It will be simpler and not require app changes. BDR comes into its own when you need latency tolerance, partition tolerance, etc, and it doesn't sound like you do.

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?

Since there's no restore in that list, no, it wouldn't. I haven't written step by step guidance there, but it's something that's worth adding to the documentation, so I'll note that.

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

Ok, Thanks.

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´t cause problems.

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

My main troubles from the beginning have been in recovery and I dind´t 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 Ext. 179

F: +34 976 465722

<http://www.teltronic.es/&gt; www.teltronic.es

Logo40

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

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

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

I cannot tell you that with any confidence without knowing more about the
application than is going to be practical. The best I can do is "probably,
with the caveats already explained re async multi-master above".

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

"recovery" from what? What are you trying to do?

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

El mar, 17-03-2015 a las 21:03 +0800, Craig Ringer escribió:

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

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

I cannot tell you that with any confidence without knowing more about
the application than is going to be practical. The best I can do is
"probably, with the caveats already explained re async multi-master
above".

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

"recovery" from what? What are you trying to do?

I think, she means failback master-master cluster after any node have
had an error and postgresql was stopped in that node.

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

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

My problem is that sometimes database stops replying. All I can see is that I have registers in that table

SELECT * FROM pg_logical_slot_get_binary_changes('bdr_17911_6120567807158814813_1_16385__', NULL, 1, 'interactive', 'true')

When I get to this situation, I suppose that means that some transaction have not finish well. So, what can I do to recovery my replication?

Ruth Patricia Melendo Ventura

Software Engineer

TELTRONIC, S.A.U.

T: +34 976 465656 Ext. 179

F: +34 976 465722

<http://www.teltronic.es/&gt; www.teltronic.es

Logo40

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 14:03
Para: Ruth Melendo
CC: pgsql-admin
Asunto: Re: [ADMIN] pgpool + BDR, is it possible?

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

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

I cannot tell you that with any confidence without knowing more about the application than is going to be practical. The best I can do is "probably, with the caveats already explained re async multi-master above".

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

"recovery" from what? What are you trying to do?

--

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

Yes, I don´t find the way to recovery and start working again so after some attempts I have to delete data directory and starts again. That´s the point I need to solve.

Ruth Patricia Melendo Ventura
Software Engineer
TELTRONIC, S.A.U.
T: +34 976 465656 Ext. 179
F: +34 976 465722
www.teltronic.es

-----Mensaje original-----
De: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] En nombre de jaime soler
Enviado el: martes, 17 de marzo de 2015 14:38
Para: Craig Ringer
CC: Ruth Melendo; pgsql-admin
Asunto: Re: [ADMIN] pgpool + BDR, is it possible?

El mar, 17-03-2015 a las 21:03 +0800, Craig Ringer escribió:

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

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

I cannot tell you that with any confidence without knowing more about
the application than is going to be practical. The best I can do is
"probably, with the caveats already explained re async multi-master
above".

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

"recovery" from what? What are you trying to do?

I think, she means failback master-master cluster after any node have had an error and postgresql was stopped in that node.

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

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

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

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

My problem is that sometimes database stops replying. All I can see is
that I have registers in that table

SELECT * FROM
pg_logical_slot_get_binary_changes('bdr_17911_6120567807158814813_1_16385__',
NULL, 1, 'interactive', 'true')

When I get to this situation, I suppose that means that some transaction
have not finish well. So, what can I do to recovery my replication?

Check the PostgreSQL server error log to find out what's going on and go
from there. You need to find out what's going on, why replication apply has
stopped progressing. Assuming it has.

BTW, you should use the "peek" functions not the "get" functions on an
existing slot used by something else. Otherwise you might consume changes,
causing them not to get replayed and resulting in inconsistencies.

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

I don’t get this about the "peek" functions not the "get" functions .. What do you mean?

Thanks

Ruth Patricia Melendo Ventura

Software Engineer

TELTRONIC, S.A.U.

T: +34 976 465656 Ext. 179

F: +34 976 465722

<http://www.teltronic.es/&gt; www.teltronic.es

Logo40

De: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] En nombre de Craig Ringer
Enviado el: miércoles, 18 de marzo de 2015 3:12
Para: Ruth Melendo
CC: pgsql-admin
Asunto: Re: [ADMIN] pgpool + BDR, is it possible?

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

My problem is that sometimes database stops replying. All I can see is that I have registers in that table

SELECT * FROM pg_logical_slot_get_binary_changes('bdr_17911_6120567807158814813_1_16385__', NULL, 1, 'interactive', 'true')

When I get to this situation, I suppose that means that some transaction have not finish well. So, what can I do to recovery my replication?

Check the PostgreSQL server error log to find out what's going on and go from there. You need to find out what's going on, why replication apply has stopped progressing. Assuming it has.

BTW, you should use the "peek" functions not the "get" functions on an existing slot used by something else. Otherwise you might consume changes, causing them not to get replayed and resulting in inconsistencies.

--

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

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

I don’t get this about the "peek" functions not the "get" functions ..
What do you mean?

See:

http://www.postgresql.org/docs/current/static/logicaldecoding.html

http://www.postgresql.org/docs/current/static/logicaldecoding-sql.html

You used pg_logical_slot_get_binary_changes . This consumes changes from
the slot. Don't do that on a slot used by something else. Use
pg_logical_slot_peek_changes or pg_logical_slot_peek_binary_changes .

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