Re: How do you manage cluster replication and failover ?

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 <mailto:rlucbernet(at)maltem(dot)com>
> Friday, April 7, 2017 11:33 AM
>
> 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(at)gmail(dot)com>
> *Envoyé :* vendredi 7 avril 2017 15:26:41
> *À :* Robin LUCBERNET; pgsql-admin(at)postgresql(dot)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(at)postgresql(dot)org
> [mailto:pgsql-admin-owner(at)postgresql(dot)org] *En nombre de *Robin LUCBERNET
> *Enviado el:* jueves, 6 de abril de 2017 05:18 a. m.
> *Para:* pgsql-admin(at)postgresql(dot)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
>