Thread: 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

You could use pgpool2 for connection pooling, load balancing and HA failover or use pgbouncer for connection pooling (lesser footprint than pgpool), pgpool2 for load balancing and failover (using watchdog).  But I don't see the point of combining repmgr with pgpool2.

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

Robin LUCBERNET

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

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.