Обсуждение: [BDR] vs pgpool-II v3
Hello everyone!
The context of this is using BDR to implement a HA solution where we have one node getting all connections at a time, if the node fails we move all connections to another node. (eg. only one node gets all connections at any given time).
I am looking for help / advice on how to answer the following question I was presented with:
I appreciate any input as to pros/cons of BDR vs PGPool-II v3 in this context.
~Wayne
On 08/13/2015 08:52 AM, Wayne E. Seguin wrote: > "BDR is only one in Beta compare to stable options: > https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling. > Not sure why this solution would be chosen. Experience with pgpool is > that you only needed to change a port and no SQL changes required on an > application side." > > I appreciate any input as to pros/cons of BDR vs PGPool-II v3 in this BDR is async multi master. PGPool is largely just a connection pooler with load balancing (although it has other features). They play different sports. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing "I'm offended" is basically telling the world you can't control your own emotions, so everyone else should do it for you.
The question is specifically about the replication feature mentioned here http://www.pgpool.net/mediawiki/index.php/Main_Page for the purposes of failing over minimizing downtime.
On Thu, Aug 13, 2015 at 12:28 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
On 08/13/2015 08:52 AM, Wayne E. Seguin wrote:"BDR is only one in Beta compare to stable options:
https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling.
Not sure why this solution would be chosen. Experience with pgpool is
that you only needed to change a port and no SQL changes required on an
application side."
I appreciate any input as to pros/cons of BDR vs PGPool-II v3 in this
BDR is async multi master. PGPool is largely just a connection pooler with load balancing (although it has other features). They play different sports.
JD
--
Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.
(But thank you for the response!!!)
On Thu, Aug 13, 2015 at 1:37 PM, Wayne E. Seguin <wayneeseguin@gmail.com> wrote:
The question is specifically about the replication feature mentioned here http://www.pgpool.net/mediawiki/index.php/Main_Page for the purposes of failing over minimizing downtime.--On Thu, Aug 13, 2015 at 12:28 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
On 08/13/2015 08:52 AM, Wayne E. Seguin wrote:"BDR is only one in Beta compare to stable options:
https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling.
Not sure why this solution would be chosen. Experience with pgpool is
that you only needed to change a port and no SQL changes required on an
application side."
I appreciate any input as to pros/cons of BDR vs PGPool-II v3 in this
BDR is async multi master. PGPool is largely just a connection pooler with load balancing (although it has other features). They play different sports.
JD
--
Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.
El 13/08/15 a las 14:37, Wayne E. Seguin escribió: > The question is specifically about the replication feature mentioned here > http://www.pgpool.net/mediawiki/index.php/Main_Page for the purposes of > failing over minimizing downtime. They aim a completely different problems. The thing Joshua mentioned about BDR being *async* means that data will be *eventually* consistent. This tackles the problem with nodes which are distant (one node in London, another in NY, one in Tokio, ...) PgPools replication_mode is for nodes on a local network. It would never scale like BDR with geographically distant nodes. Conclusion, they don't solve the same problem, so it's hard to compare. Cheers, -- Martín Marqués http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
For your information, I would like to explain a little bit more about pgpool-II own replication (we call it "native replication mode" to separate from streaming replication or slony replication). In the pgpool-II's native replication mode, for a start pgpool-II sends the query to "master" PostgreSQL node, which is the first one in the configuration file. After the PostgreSQL returns response, pgpool-II sends the query to the second PostgreSQL node. This is to avoid deadlock between DB nodes. If you have third or fourth DB node, pgpool-II sends the query concurrently with the second node. So in theory pgpool-II's performance in DML/DDL is no less than 50% comparing with single PostgreSQL regardless number of DB nodes. There's one optimization however. If there's only one concurrent sessions (i.e. the number pgpool-II preforked child process is 1), then pgpool-II sends DML/DDL to all the DB nodes at once because there's no possibilty of deadlock. In this case the DML/DDL performance is equivalent to single PostgreSQL in theory. I remember a user deployed inter continental pgpool-II configuration. If my memory serves, the continentals were Australia and North America. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp > The question is specifically about the replication feature mentioned here > http://www.pgpool.net/mediawiki/index.php/Main_Page for the purposes of > failing over minimizing downtime. > > On Thu, Aug 13, 2015 at 12:28 PM, Joshua D. Drake <jd@commandprompt.com> > wrote: > >> >> On 08/13/2015 08:52 AM, Wayne E. Seguin wrote: >> >> "BDR is only one in Beta compare to stable options: >>> >>> https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling >>> . >>> Not sure why this solution would be chosen. Experience with pgpool is >>> that you only needed to change a port and no SQL changes required on an >>> application side." >>> >>> I appreciate any input as to pros/cons of BDR vs PGPool-II v3 in this >>> >> >> BDR is async multi master. PGPool is largely just a connection pooler with >> load balancing (although it has other features). They play different sports. >> >> JD >> >> >> -- >> Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 >> PostgreSQL Centered full stack support, consulting and development. >> Announcing "I'm offended" is basically telling the world you can't >> control your own emotions, so everyone else should do it for you. >> > > > > -- > ~Wayne > > Wayne E. Seguin > wayneeseguin@gmail.com > wayneeseguin on irc.freenode.net > http://twitter.com/wayneeseguin/ > https://github.com/wayneeseguin/
On 13 August 2015 at 23:52, Wayne E. Seguin <wayneeseguin@gmail.com> wrote: > The context of this is using BDR to implement a HA solution where we have > one node getting all connections at a time, if the node fails we move all > connections to another node. (eg. only one node gets all connections at any > given time). This sounds like a job better suited to a normal active/standby configuration with regular built-in streaming replication. Use a tool like repmgr to manage failover and a proxy like pgbouncer to redirect traffic. There's no reason to use async multi-master replication when simple single-master replication will do just as well. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Thank you everyone for your replies, the thoughts and context are greatly appreciated!
On Sun, Aug 16, 2015 at 9:35 PM, Craig Ringer <craig@2ndquadrant.com> wrote:
On 13 August 2015 at 23:52, Wayne E. Seguin <wayneeseguin@gmail.com> wrote:
> The context of this is using BDR to implement a HA solution where we have
> one node getting all connections at a time, if the node fails we move all
> connections to another node. (eg. only one node gets all connections at any
> given time).
This sounds like a job better suited to a normal active/standby
configuration with regular built-in streaming replication. Use a tool
like repmgr to manage failover and a proxy like pgbouncer to redirect
traffic.
There's no reason to use async multi-master replication when simple
single-master replication will do just as well.
--
Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services