Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cwWLu-0000lo-Ru for pgsql-admin@arkaria.postgresql.org; Fri, 07 Apr 2017 16:03:11 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cwWLu-00075X-DY for pgsql-admin@arkaria.postgresql.org; Fri, 07 Apr 2017 16:03:10 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cwWLt-0006xL-6S for pgsql-admin@postgresql.org; Fri, 07 Apr 2017 16:03:09 +0000 Received: from mout.perfora.net ([74.208.4.196]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cwWLq-0004Pt-6x for pgsql-admin@postgresql.org; Fri, 07 Apr 2017 16:03:07 +0000 Received: from [192.168.1.101] ([98.169.212.183]) by mrelay.perfora.net (mreueus002 [74.208.5.2]) with ESMTPSA (Nemesis) id 0McFtJ-1cd4cd3VKB-00JdOx; Fri, 07 Apr 2017 18:03:01 +0200 Message-ID: <58E7B832.7020105@sqlexec.com> Date: Fri, 07 Apr 2017 12:02:58 -0400 From: "michael@sqlexec.com" User-Agent: Postbox 5.0.12 (Windows/20170323) MIME-Version: 1.0 To: Robin LUCBERNET CC: Lazaro Garcia , "pgsql-admin@postgresql.org" Subject: Re: How do you manage cluster replication and failover ? References: ,<000e01d2afa2$98f71ca0$cae555e0$@gmail.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------010608000509050701060005" X-Provags-ID: V03:K0:DeyhkAtzD79q872Cpl8lfXlBRLb2C3hCbKjZ8yzcIVYp/W0Jf/S l/nH5BHC2vCzgUDOYNqk7vDqtINRcEHvcmyQylOFBlS1M5eFKpWMGbGl3Qu9dsWeNHP4LaO 0l1OCXLmRdvgKdQZ5y2+lEW7agxNaMjhpcccvbQKDEJYQI987OAXsOOTIguQKdFAqIPfVLy rsF+MwjdSppeE7kU0zT0Q== X-UI-Out-Filterresults: notjunk:1;V01:K0:Ox5yX7vxXcE=:t+ea8t/S6LCfsLXchAqXQ/ 9JATX45s9FGxJXR1JwjEbq+WUt3eKewppIEu0GEmDPDxhyjJEyiyU6L8N6w6zpe0ycvd9V8qM g2bWnil0CezCG8f+4m0N1357vb2lInU6o9cwr8it1xSuPoL8oLcStAQQeMgFXTRPZMCZ4Vfnx M4rWnmjIbF6kRocGQu7/XtMKM+m7iBmtNj4WBYic25xFO2lr0Q7U4BWhksUpETQ92gKIXF6Up 30p5pgA71RWBNeWaKNM16/paIB3YlOnsmbcpzGYL0ijuV6M/S8l3UjNl23ckX9gKAhAwEnuca np6dKnIuUH6vckdNznW3Y87KqWoqxBcO51gzzu//4Me24mNam0sBnjax2UVRJ8zcHk+Ye/r7q C0ImSV+sLLTwDm081/FEiWO/kOESAdRWiXgt9TjZgseOnTena+9zeEcT42Snu4cBbEfIusRsB kX6WBlv8YSO8eqzMafyO9LCHNwsM2JF+QhQRlv6Jhh/LSac3G/oTu3iClWbGLLVegp2+O5Pqf FIU7z0Mkw9Q2QwgyWkmcLXI/CmgmlzL05xJ8gyyyhKljr7IOBUD1FLJld9Jsfjn2FNDlcQdTe zh/6tnJuLX4Ozv3MCpi6nM6s590USqX+WXLscbJ21aEcAama1arYejqdVc6GFevVC2dFINQqH F0fCpaEb1HkzVbc3n//eNS9Ld8IHOUooXeduhbhwnu1P94oSuQ144eD7E7fuJhSfu9AuJSRoo Z2Sy06RddYkyuc8Z X-Pg-Spam-Score: -4.2 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------010608000509050701060005 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 > > 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 > *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 > --------------010608000509050701060005 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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

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@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


--------------010608000509050701060005--