Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cwWKJ-0000e0-IP for pgsql-admin@arkaria.postgresql.org; Fri, 07 Apr 2017 16:01:31 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cwWKI-0003EB-8F for pgsql-admin@arkaria.postgresql.org; Fri, 07 Apr 2017 16:01:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cwWKH-0003E1-Lh for pgsql-admin@postgresql.org; Fri, 07 Apr 2017 16:01:29 +0000 Received: from mail-qk0-x22e.google.com ([2607:f8b0:400d:c09::22e]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cwWKE-0000PE-Bx for pgsql-admin@postgresql.org; Fri, 07 Apr 2017 16:01:28 +0000 Received: by mail-qk0-x22e.google.com with SMTP id p22so66456059qka.3 for ; Fri, 07 Apr 2017 09:01:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:references:in-reply-to:subject:date:message-id:mime-version :thread-index:content-language; bh=KN46TSrtosFrGSFjisBEdGyeX82zTkg0jn0JuKZshwA=; b=jmO+Jr9/eGJCC6YOtYxjbpcTcHt6Wt4zFHjlHRjLzPu15yj6KOhWzvABjutVUxpfye NTw5p0QPZYqTqUUg3+4Oyl1QowyjpxK+hrGqqbu5R48lVIb8w6YNMlN6W3oaoKys8Fc/ elruYSlpxUQqO/XJM7sEoDLNNPyeR8jsbY9YuuMhQSkhTIyFLG4cLAcmr3HONNfILgWE JnZzi5ySRCvyfnDRkSnysgmhddi22a0tNwpUE955THI2uLAN0vfA1qe/ZEEukuQuGo9Y TrLv8Yk9llACguQvfvJCW8WR97oRlB8c0Ym6Lxz39McYX4j91214QWbnfVmDckXKJMw5 /maw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:references:in-reply-to:subject:date :message-id:mime-version:thread-index:content-language; bh=KN46TSrtosFrGSFjisBEdGyeX82zTkg0jn0JuKZshwA=; b=KoqN0sr2m1aYVAiu6Mml5P0ljXixmvZiS8LF5Iz9IsokP8JA968M470uGt82fiS1+h Z/qp7n0ZtakueioctlK/8QN/vHlPvdW54s4Q5rol/X9flHAlcCbrH0cpqZkDM+Wza7Q0 W0T9BBV/4fqgwVR5NKwy/hWV8WI1I2oV5EY7e8PgO0Ov2CMY/T8Ziwg34Bfov/KqYYfD ltjVAhcgJQ1ok7hb27DfXGUOQgFjcAwFWPF6z0beKGwZkH65TZlWSsVWPVusjMtn4FPe HRrP8XuEEDfpmQDKMa10eRALUQgMLbsAHicqviYc8OBfcRpZHBwIWVSNjzc0q0V96vEu zWHg== X-Gm-Message-State: AFeK/H259vnOnrUMJIVvFRzoatmvp+ZLBmY/JRIKPt5oHsRoLIYPWc94QsUkMhNgmN7EQQ== X-Received: by 10.55.201.202 with SMTP id m71mr30210828qkl.31.1491580884121; Fri, 07 Apr 2017 09:01:24 -0700 (PDT) Received: from irylachi ([200.55.161.75]) by smtp.gmail.com with ESMTPSA id 1sm3299818qtb.40.2017.04.07.09.01.19 (version=TLS1_2 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Fri, 07 Apr 2017 09:01:23 -0700 (PDT) From: "Lazaro Garcia" To: "'Robin LUCBERNET'" , References: ,<000e01d2afa2$98f71ca0$cae555e0$@gmail.com> In-Reply-To: Subject: Re: How do you manage cluster replication and failover ? Date: Fri, 7 Apr 2017 12:01:15 -0400 Message-ID: <002101d2afb8$31f579a0$95e06ce0$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0022_01D2AF96.AAE6BFD0" X-Mailer: Microsoft Outlook 15.0 Thread-Index: AQC/2qvGCdRNhr/IaSflC9Igk5IMLAGg6EaaAYiVkTujxmwkcA== Content-Language: es-mx X-Pg-Spam-Score: -2.4 (--) 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 multipart message in MIME format. ------=_NextPart_000_0022_01D2AF96.AAE6BFD0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable You can user pgbouncer + repmgr but pgbouncer is not a load balancer. =20 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. =20 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. =20 Regards. =20 De: Robin LUCBERNET [mailto:rlucbernet@maltem.com]=20 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 ? =20 Hey, thanks for your feedbacks. =20 I do not know about Patroni. I will have a look at it. =20 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 ? =20 Robin. _____ =20 De : Lazaro Garcia < lazaro3487@gmail.com> Envoy=E9 : vendredi 7 avril 2017 15:26:41 =C0 : Robin LUCBERNET; pgsql-admin@postgresql.org Objet : RE: [ADMIN] How do you manage cluster replication and failover ? = =20 You can use repmgr because it performs automatic failover, promotes a = master mores closer to replica and follows other slaves to new master. =20 =20 Then pgpool detects the new master promoted by repmgr. =20 Regards. =20 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 =20 Asunto: [ADMIN] How do you manage cluster replication and failover ? =20 Hello, =20 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) =20 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. =20 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 ? =20 Robin ------=_NextPart_000_0022_01D2AF96.AAE6BFD0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

You can user pgbouncer=A0 + 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:<= /b> = 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=E9 : vendredi 7 avril 2017 15:26:41
=C0 = : 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:<= /b> = pgsql-admin-owner@postgr= esql.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

------=_NextPart_000_0022_01D2AF96.AAE6BFD0--