Обсуждение: Re: [ADMIN] How do you manage cluster replication and failover ?
You can use repmgr because it performs automatic failover, promotes a master mores closer to replica and follows other slaves to new master.
Then pgpool detects the new master promoted by repmgr.
Regards.
De: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] En nombre de Robin LUCBERNET
Enviado el: jueves, 6 de abril de 2017 05:18 a. m.
Para: pgsql-admin@postgresql.org
Asunto: [ADMIN] How do you manage cluster replication and failover ?
Hello,
We are currently trying to setup a multi hosts databases cluster with goals:
* replication (no data-loss is "required", replication timing do not needs to be instant)
* failover
* load-balancing (bonus)
We tried:
* synchronious replication (pgpool replication mode) + load-balancing (pgpool) : very interesing as we can theorically failover on any node at any moment. But even after several configuraation tweeks, we never succeed getting good write performance.
* asynchronious replication (postgres 9.6 streaming replication) : good write performance, good replication timings (< 1 second for small transactions). We could loadbalance select requests using pgpool.
How do you manage your postgresql clusters ? Do you use pgpool ? pgbouncer ? other ?
How do you manage to single access point ? usign pgpool ? pgbouncer ?
Do you use streaming replication ? WAL archiving ? How do you handle to automatic failover ?
Robin
Hey, thanks for your feedbacks.
I do not know about Patroni. I will have a look at it.
Could someone confirm me that all features of pgpool (connection pooling + automatic failover) can be achieve using pgbouncer (connection pooling) + repmgr (automatic failover) ? Is it not overkill to user repmgr + pgpool ? Am I missing something ?
Robin.
Envoyé : vendredi 7 avril 2017 15:26:41
À : Robin LUCBERNET; pgsql-admin@postgresql.org
Objet : RE: [ADMIN] How do you manage cluster replication and failover ?
You can use repmgr because it performs automatic failover, promotes a master mores closer to replica and follows other slaves to new master.
Then pgpool detects the new master promoted by repmgr.
Regards.
De: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] En nombre de Robin LUCBERNET
Enviado el: jueves, 6 de abril de 2017 05:18 a. m.
Para: pgsql-admin@postgresql.org
Asunto: [ADMIN] How do you manage cluster replication and failover ?
Hello,
We are currently trying to setup a multi hosts databases cluster with goals:
* replication (no data-loss is "required", replication timing do not needs to be instant)
* failover
* load-balancing (bonus)
We tried:
* synchronious replication (pgpool replication mode) + load-balancing (pgpool) : very interesing as we can theorically failover on any node at any moment. But even after several configuraation tweeks, we never succeed getting good write performance.
* asynchronious replication (postgres 9.6 streaming replication) : good write performance, good replication timings (< 1 second for small transactions). We could loadbalance select requests using pgpool.
How do you manage your postgresql clusters ? Do you use pgpool ? pgbouncer ? other ?
How do you manage to single access point ? usign pgpool ? pgbouncer ?
Do you use streaming replication ? WAL archiving ? How do you handle to automatic failover ?
Robin
You can user pgbouncer + repmgr but pgbouncer is not a load balancer.
When repmgr performs failover you can configure the property promote_command in repmgr.conf and build an script for updating the pgbouncer configuration with the new master.
Repmgr + pgpool would be very usefull when you have more than 2 nodes because pgpool does not knows how is the best candidate node for promoting to master.
Regards.
De: Robin LUCBERNET [mailto:rlucbernet@maltem.com]
Enviado el: viernes, 7 de abril de 2017 11:34 a. m.
Para: Lazaro Garcia; pgsql-admin@postgresql.org
Asunto: RE: [ADMIN] How do you manage cluster replication and failover ?
Hey, thanks for your feedbacks.
I do not know about Patroni. I will have a look at it.
Could someone confirm me that all features of pgpool (connection pooling + automatic failover) can be achieve using pgbouncer (connection pooling) + repmgr (automatic failover) ? Is it not overkill to user repmgr + pgpool ? Am I missing something ?
Robin.
De : Lazaro Garcia <lazaro3487@gmail.com>
Envoyé : vendredi 7 avril 2017 15:26:41
À : Robin LUCBERNET; pgsql-admin@postgresql.org
Objet : RE: [ADMIN] How do you manage cluster replication and failover ?
You can use repmgr because it performs automatic failover, promotes a master mores closer to replica and follows other slaves to new master.
Then pgpool detects the new master promoted by repmgr.
Regards.
De: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] En nombre de Robin LUCBERNET
Enviado el: jueves, 6 de abril de 2017 05:18 a. m.
Para: pgsql-admin@postgresql.org
Asunto: [ADMIN] How do you manage cluster replication and failover ?
Hello,
We are currently trying to setup a multi hosts databases cluster with goals:
* replication (no data-loss is "required", replication timing do not needs to be instant)
* failover
* load-balancing (bonus)
We tried:
* synchronious replication (pgpool replication mode) + load-balancing (pgpool) : very interesing as we can theorically failover on any node at any moment. But even after several configuraation tweeks, we never succeed getting good write performance.
* asynchronious replication (postgres 9.6 streaming replication) : good write performance, good replication timings (< 1 second for small transactions). We could loadbalance select requests using pgpool.
How do you manage your postgresql clusters ? Do you use pgpool ? pgbouncer ? other ?
How do you manage to single access point ? usign pgpool ? pgbouncer ?
Do you use streaming replication ? WAL archiving ? How do you handle to automatic failover ?
Robin
With pgpool2 and load balancing, care must be taken with application backends that use queuing like rabbitq. Logical units of work from a client application server endpoint may encompass multiple transactions across multiple connections. This could break load balancing if you are not using PG 9.6 where synchronous_commit can be set to remote_apply. Got stabbed in the foot by this in the past.
Regards,
Michael Vitale
Friday, April 7, 2017 11:33 AM<!-- /* Font Definitions */ @font-face{font-family:"Cambria Math";panose-1:2 4 5 3 5 4 6 3 2 4;} @font-face{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal{margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;font-family:"Times New Roman","serif";} a:link, span.MsoHyperlink{mso-style-priority:99;color:#0563C1;text-decoration:underline;} a:visited, span.MsoHyperlinkFollowed{mso-style-priority:99;color:#954F72;text-decoration:underline;} p{mso-style-priority:99;margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;font-family:"Times New Roman","serif";} span.messagebody{mso-style-name:message_body;} span.parabreak{mso-style-name:para_break;} span.EstiloCorreo20{mso-style-type:personal-reply;font-family:"Calibri","sans-serif";color:#1F497D;} .MsoChpDefault{mso-style-type:export-only;font-size:10.0pt;} @page WordSection1{size:612.0pt 792.0pt;margin:70.85pt 3.0cm 70.85pt 3.0cm;} div.WordSection1{page:WordSection1;} --> <!-- P {margin-top:0;margin-bottom:0;} --> Hey, thanks for your feedbacks.
I do not know about Patroni. I will have a look at it.
Could someone confirm me that all features of pgpool (connection pooling + automatic failover) can be achieve using pgbouncer (connection pooling) + repmgr (automatic failover) ? Is it not overkill to user repmgr + pgpool ? Am I missing something ?
Robin.
De : Lazaro Garcia <lazaro3487@gmail.com>
Envoyé : vendredi 7 avril 2017 15:26:41
À : Robin LUCBERNET; pgsql-admin@postgresql.org
Objet : RE: [ADMIN] How do you manage cluster replication and failover ?You can use repmgr because it performs automatic failover, promotes a master mores closer to replica and follows other slaves to new master.
Then pgpool detects the new master promoted by repmgr.
Regards.
De: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] En nombre de Robin LUCBERNET
Enviado el: jueves, 6 de abril de 2017 05:18 a. m.
Para: pgsql-admin@postgresql.org
Asunto: [ADMIN] How do you manage cluster replication and failover ?
Hello,
We are currently trying to setup a multi hosts databases cluster with goals:
* replication (no data-loss is "required", replication timing do not needs to be instant)
* failover
* load-balancing (bonus)
We tried:
* synchronious replication (pgpool replication mode) + load-balancing (pgpool) : very interesing as we can theorically failover on any node at any moment. But even after several configuraation tweeks, we never succeed getting good write performance.
* asynchronious replication (postgres 9.6 streaming replication) : good write performance, good replication timings (< 1 second for small transactions). We could loadbalance select requests using pgpool.
How do you manage your postgresql clusters ? Do you use pgpool ? pgbouncer ? other ?
How do you manage to single access point ? usign pgpool ? pgbouncer ?
Do you use streaming replication ? WAL archiving ? How do you handle to automatic failover ?
Robin
Re: [ADMIN] How do you manage cluster replication and failover ?
Hello Robin, Sorry to jump in this thread so lately, I was on holidays :) Considering the failover subject, did you check the PAF project as well? It relies on Pacemaker, the industry standard for high availability under Linux.