Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ojt4S-0008N4-N9 for pgsql-admin@arkaria.postgresql.org; Sun, 16 Oct 2022 02:08:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ojt4P-0002o7-U6 for pgsql-admin@arkaria.postgresql.org; Sun, 16 Oct 2022 02:08:05 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ojt4P-0002nx-9Q for pgsql-admin@lists.postgresql.org; Sun, 16 Oct 2022 02:08:05 +0000 Received: from mail-oo1-xc2c.google.com ([2607:f8b0:4864:20::c2c]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ojt4I-0000fz-Gr for pgsql-admin@lists.postgresql.org; Sun, 16 Oct 2022 02:08:04 +0000 Received: by mail-oo1-xc2c.google.com with SMTP id k11-20020a4ab28b000000b0047659ccfc28so2207848ooo.8 for ; Sat, 15 Oct 2022 19:07:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=MtQWr2hWCC4/cWg2iAjIeDC3i7gOF1fPFo+Jgr5BvPU=; b=C+lxo0VtdBYoeI/L1hjcepeya9bBiG94bYpUhXiyj6Sqdzifq9rS9IJspu+dJENUEO etzOItnh9KNhgh6H+MaXcOWauv8ke3AzxeAAA+SnFBVujW9zBejt7I6AFlPvWavjjQXN V1yT5cQsTanzBIqikb20Hb+nNppRjmbv+8mzmjQMt5XmiVt4SVCHNTOO4nRb/qIgBaiG jrQDy/T6MmLeBesVxfB720RxA+3aCjLbJZGEC3oaW598EBRS9lvyAz1VqUYzjMT3D3Mw UIMUgXdWis0Axexgr1bEFjoFRCLtnmocyYkxFevSl9gagbmc4Oos184fpceHvbaI+0iv Od6A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=MtQWr2hWCC4/cWg2iAjIeDC3i7gOF1fPFo+Jgr5BvPU=; b=qjMdeaf/d4/gEudeFhfM1XUkSBZrroYmshBd1FHjAyc31Ijx378TeMztG9vmEDF61o +dOD4E4yyB71AR2RPbMJKsq7u5kJuZ9m+GrsHF49vMGqP65HnpaKAVCsCa4EM8Vh/aZV +qQCVfW9tdcrORhoKWBwsWEhndwmyUnAc1pWf13Cysqtzq4cSqeTe3sNP9URDF54stp8 KDBgy++oOR2TjZ4PUsDRIcPYOngCVVxU8Dz+NMCUnzVK2XHWA0TzOhX+qD6qmlenxEV4 yP81GidVQrQyRoTU1DKWhfahr155XNHJiF55G2ky0nl9pQvGcDU2tSiyoKOzTHyvTpRd /AFw== X-Gm-Message-State: ACrzQf0YKNvkgRV9Rxl9GSbobpwqD2biJ+kR/nsE0o4tAUOI2KrwOog0 dAAphsp1QQES46nuQ6hDikGgs8xzST4= X-Google-Smtp-Source: AMsMyM7PjeAfe2NpS4H/n48++sB0UYsvVbb5buloHObPKRb0lbKMp9HY0SH4Q0B6gLZNq0wIl9ynqQ== X-Received: by 2002:a4a:d4c1:0:b0:476:42e3:b45e with SMTP id r1-20020a4ad4c1000000b0047642e3b45emr1835759oos.89.1665886077265; Sat, 15 Oct 2022 19:07:57 -0700 (PDT) Received: from [192.168.88.10] (ip68-11-68-85.no.no.cox.net. [68.11.68.85]) by smtp.googlemail.com with ESMTPSA id q30-20020a4a88e1000000b004805e9e9f3dsm2776228ooh.1.2022.10.15.19.07.56 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 15 Oct 2022 19:07:56 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------IMIymZ6kR3mSFJ0V1jJ9MKZ6" Message-ID: <49c76fab-950c-33a9-ae8d-3af93c6c7051@gmail.com> Date: Sat, 15 Oct 2022 21:07:55 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: Open source HA setup for postgres Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------IMIymZ6kR3mSFJ0V1jJ9MKZ6 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 10/14/22 11:33, Tharmarajah, Sam wrote: > > Hi, > > We are wondering what is open-source tools being used to set up the HA. We > are also looking for a way to pass the reads to all the HA nodes and > writes to the primary.  One of the pressing criteria is the HA also should > be able to handle the node being taken down during patching and should be > able to fail back or recover seamlessly without any manual intervention. > > This is the first time we are embarking on HA, so would like some advice > and caveats to avoid. > What I'm currently looking at is this combo: https://repmgr.org/ (for quorum voting, fail over *and seamless fail back*) https://pgpool.net/mediawiki/index.php/Main_Page (for a common IP address) On a switchover (whether planned or unplanned), repgmr reconfigures PgPool to use the New-primary's IP address. -- Angular momentum makes the world go 'round. --------------IMIymZ6kR3mSFJ0V1jJ9MKZ6 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 10/14/22 11:33, Tharmarajah, Sam wrote:

Hi,

 

We are wondering what is open-source tools being used to set up the HA. We are also looking for a way to pass the reads to all the HA nodes and writes to the primary.  One of the pressing criteria is the HA also should be able to handle the node being taken down during patching and should be able to fail back or recover seamlessly without any manual intervention.

 

This is the first time we are embarking on HA, so would like some advice and caveats to avoid.


What I'm currently looking at is this combo:
https://repmgr.org/ (for quorum voting, fail over and seamless fail back)
https://pgpool.net/mediawiki/index.php/Main_Page (for a common IP address)

On a switchover (whether planned or unplanned), repgmr reconfigures PgPool to use the New-primary's IP address.

--
Angular momentum makes the world go 'round.
--------------IMIymZ6kR3mSFJ0V1jJ9MKZ6--