Обсуждение: pgpool + BDR, is it possible?

Поиск
Список
Период
Сортировка

pgpool + BDR, is it possible?

От
"Ruth Melendo"
Дата:

Hi all,

 

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

I´m working with Postgre 9.4 and have logical replication configured.

 

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

 

Thanks in advance!

 

Ruth Patricia Melendo Ventura

Software Engineer

TELTRONIC, S.A.U.

T: +34 976 465656   Ext. 179

F: +34 976 465722

www.teltronic.es

 

Logo40

 

Before printing this e-mail please consider your environmental responsibility.

*****  DISCLAIMER  *****

This message is intended exclusively for the named person. It may contain confidential, propietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. Your must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorised to state them to be the views of TELTRONIC. If the addressee of this message does not consent to the use of internet e-mail, please communicate it to us immediately.

 

Вложения

Re: pgpool + BDR, is it possible?

От
Craig Ringer
Дата:


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

Re: pgpool + BDR, is it possible?

От
"Ruth Melendo"
Дата:

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

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

Вложения

Re: pgpool + BDR, is it possible?

От
Craig Ringer
Дата:
(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.

Re: pgpool + BDR, is it possible?

От
"Ruth Melendo"
Дата:

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

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.

Вложения

Re: pgpool + BDR, is it possible?

От
Craig Ringer
Дата:


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

Re: pgpool + BDR, is it possible?

От
"Ruth Melendo"
Дата:

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

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

Вложения

Re: pgpool + BDR, is it possible?

От
Craig Ringer
Дата:

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

Re: pgpool + BDR, is it possible?

От
jaime soler
Дата:
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
>

Re: pgpool + BDR, is it possible?

От
"Ruth Melendo"
Дата:

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

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

Вложения

Re: pgpool + BDR, is it possible?

От
"Ruth Melendo"
Дата:
Yes, I don´t find the way to recovery and start working again so after some attempts  I have to delete data directory
andstarts 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

Re: pgpool + BDR, is it possible?

От
Craig Ringer
Дата:


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

Re: pgpool + BDR, is it possible?

От
"Ruth Melendo"
Дата:

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

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

Вложения

Re: pgpool + BDR, is it possible?

От
Craig Ringer
Дата:


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?


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