Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cwTue-0001K4-93 for pgsql-admin@arkaria.postgresql.org; Fri, 07 Apr 2017 13:26:52 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cwTud-0003CL-6L for pgsql-admin@arkaria.postgresql.org; Fri, 07 Apr 2017 13:26:51 +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 1cwTuc-0003CB-IT for pgsql-admin@postgresql.org; Fri, 07 Apr 2017 13:26:50 +0000 Received: from mail-qt0-x231.google.com ([2607:f8b0:400d:c0d::231]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cwTuZ-0001NI-JE for pgsql-admin@postgresql.org; Fri, 07 Apr 2017 13:26:49 +0000 Received: by mail-qt0-x231.google.com with SMTP id x35so60415282qtc.2 for ; Fri, 07 Apr 2017 06:26:47 -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=vUTfo1mlGFVHBogQMA2bbMx11ggqegNEjGBUuvke5+Q=; b=LnlOi/bYIHUQf4fQRwCRP7jsl28V03i1zgXp3ABB1MjZ1CC0a9F1dpBE9IfWBMGn15 sZOy+KcZc81tcY5sfiJMQlZ/vrTqm2T7AJH2WVpYroZ8HbYojmxRHJKdwirnCRv5A4gZ Xhwf3ZMvMOz7Yx+3E/HJTSI+RGM6vbeVij5Av+DXkl+wKHaZwWK6HWnTULUAjXwu2wq5 gr8z+jUSWDwiuGPN0XUzef45LsmK3NQBRYDWs7PkZObLy6qS/QZKLU1L60qMXxhBnWch VTunaIp6weKgNOhIFrgFJcwCREWrxvA9IExzoblRp4Yw2CDgV2DSsYT8+gJXnHp/uBjS t+6g== 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=vUTfo1mlGFVHBogQMA2bbMx11ggqegNEjGBUuvke5+Q=; b=MAbFIXsAWWOBnXGs0jITNf9ZzmjNkzDnJksYXhq2OGcGKyVNw5PJQXXI0NXdpCNzvn 5BgenH3tUdQVvgl5dM0MHgvMTHEYbX/UWAF0GrUC3dy2YPK79ykJIEeYC+i+pzfPwT5e XjBOY6EnamCOSssljeUPSwBoyagNZqgZEusmZXTMCWO6+KuQEjOfFY7S8ffoPg4UnNY1 sn6lBbNDKLQhwoJLI3NX0VC212FXaEmrJThfn5CSWPwtb1jtIkpShkjGTxunxnf7Eqj0 5WyO0FHyyn2Wgh5DMz2YkPW2L1zVNMA/xwmsRgBu1eXmmfB2uCQabo2thrLUyoHuYmCW nsaQ== X-Gm-Message-State: AFeK/H1aOKvl0cruuNnHewWBf01RqIjnOC9YkHU5NFAoPvrumCmZpJb6GlC1hcR4QzeAeQ== X-Received: by 10.200.45.194 with SMTP id q2mr42790980qta.130.1491571606468; Fri, 07 Apr 2017 06:26:46 -0700 (PDT) Received: from irylachi ([200.55.161.75]) by smtp.gmail.com with ESMTPSA id x139sm3040119qkb.21.2017.04.07.06.26.44 (version=TLS1_2 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Fri, 07 Apr 2017 06:26:45 -0700 (PDT) From: "Lazaro Garcia" To: "'Robin LUCBERNET'" , References: In-Reply-To: Subject: Re: How do you manage cluster replication and failover ? Date: Fri, 7 Apr 2017 09:26:41 -0400 Message-ID: <000e01d2afa2$98f71ca0$cae555e0$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000F_01D2AF81.11E72A50" X-Mailer: Microsoft Outlook 15.0 Thread-Index: AQC/2qvGCdRNhr/IaSflC9Igk5IMLKPfjwgQ Content-Language: es-mx X-Pg-Spam-Score: -1.7 (-) 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_000F_01D2AF81.11E72A50 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_000F_01D2AF81.11E72A50 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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

------=_NextPart_000_000F_01D2AF81.11E72A50--