MIME-Version: 1.0
References: 
 <CA+Z6K-U_hSkoCeBu5WLrxyz4kHNnj_orFP3cghBHj_di3OW9+g@mail.gmail.com>
In-Reply-To: 
 <CA+Z6K-U_hSkoCeBu5WLrxyz4kHNnj_orFP3cghBHj_di3OW9+g@mail.gmail.com>
From: Zaid Shabbir <zaidshabbir@gmail.com>
Date: Mon, 11 Nov 2024 11:34:11 +0500
Message-ID: 
 <CABCJe_WDsxz27yZRABDHCEWLWn_fcdHxMbJcB=M2HZzV2kdqvg@mail.gmail.com>
Subject: Re: Inquiry on Setting Up Automatic Failover with Stream Replication
To: p sn <tkdsud01@gmail.com>
Cc: pgsql-admin@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000008dcc1806269d4a2c"
Archived-At: 
 <https://www.postgresql.org/message-id/CABCJe_WDsxz27yZRABDHCEWLWn_fcdHxMbJcB%3DM2HZzV2kdqvg%40mail.gmail.com>
Precedence: bulk

--0000000000008dcc1806269d4a2c
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hello,

*Authentication Options*: You may use any authentication method you prefer,
such as scram-sha-256, md5 or cert for certificate-based authentication, to
ensure secure connections.

*Replication User Configuration*: For the replication user make sure both
nodes replication users set in pg_hba.conf like

   - On *Node 1* (192.168.1.1):

   host replication replicator 192.168.1.2/32 scram-sha-256

   - On *Node 2* (192.168.1.2):

   host replication replicator 192.168.1.1/32 scram-sha-256


Ensure pg_hba.conf on each node allows connections from the other node=E2=
=80=99s
IP, and postgresql.conf has listen_addresses set to accept connections from
the other node=E2=80=99s IP (or '*' to allow all IPs).


*Apply Configuration*: After making these changes, restart both servers to
apply them.

*Failover: *By default failover is not available but a couple of reliable
open source products available like
  1. repmgr [Link] <https://www.repmgr.org>
  2. petroni [Link] <https://patroni.readthedocs.io/en/latest/>
  3. pg_auto_failover [Link]
<https://opensource.microsoft.com/blog/2019/05/06/introducing-pg_auto_failo=
ver-postgresql-open-source-extension-automated-failover-high-availability/>

Hope this helps you configure your environment.


On Mon, Nov 11, 2024 at 11:03=E2=80=AFAM p sn <tkdsud01@gmail.com> wrote:

>   Hello, I am currently setting up PostgreSQL Stream Replication for a
> replication configuration.
>
>  I am using PostgreSQL version 14.12 on a RedHat 8.9 environment,
> and my setup consists of a Primary-Standby replication configuration with
> two nodes.
>  Due to certain constraints, I cannot add more nodes or use external
> nodes.
>
>   Questions:
>     1-1. How should I configure postgresql.conf and pg_hba.conf for this
> setup? I would like to implement automatic failover with only these two
> nodes.
>     1-2. Is it possible to achieve this setup? If so, could you advise on
> the specific configuration steps needed? I would greatly appreciate any
> guidance from those with experience in this area. Thank you.
>
>
>  Best regards,
>

--0000000000008dcc1806269d4a2c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Hello,<div><br></div><div><strong style=
=3D"color:rgb(0,0,0)">Authentication Options</strong><span style=3D"color:r=
gb(0,0,0);font-size:medium">:=C2=A0</span>You may use any authentication me=
thod you prefer, such as scram-sha-256, md5 or cert for certificate-based a=
uthentication, to ensure secure connections.<br></div><div><span style=3D"c=
olor:rgb(0,0,0);font-size:medium"><br></span></div><div><p><strong style=3D=
"color:rgb(0,0,0)">Replication User Configuration</strong><font color=3D"#0=
00000"><span style=3D"caret-color: rgb(0, 0, 0);">:=C2=A0</span></font>For =
the replication user make sure both nodes replication users set in pg_hba.c=
onf like</p><ul><li style=3D"color:rgb(0,0,0)">On<span class=3D"gmail-Apple=
-converted-space">=C2=A0</span><strong>Node 1</strong><span class=3D"gmail-=
Apple-converted-space">=C2=A0</span>(<code>192.168.1.1</code>):<pre class=
=3D"gmail-!overflow-visible"><div class=3D"gmail-contain-inline-size gmail-=
rounded-md gmail-border-[0.5px] gmail-border-token-border-medium gmail-rela=
tive gmail-bg-token-sidebar-surface-primary gmail-dark:bg-gray-950"><div cl=
ass=3D"gmail-overflow-y-auto gmail-p-4" dir=3D"ltr"><code class=3D"gmail-!w=
hitespace-pre gmail-hljs gmail-language-plaintext">host replication replica=
tor <a href=3D"http://192.168.1.2/32">192.168.1.2/32</a> scram-sha-256
</code></div></div></pre></li><li><font color=3D"#000000"><span style=3D"ca=
ret-color: rgb(0, 0, 0);">On</span></font><span class=3D"gmail-Apple-conver=
ted-space" style=3D"color:rgb(0,0,0)">=C2=A0</span><strong style=3D"color:r=
gb(0,0,0)">Node 2</strong><span class=3D"gmail-Apple-converted-space" style=
=3D"color:rgb(0,0,0)">=C2=A0</span><font color=3D"#000000"><span style=3D"c=
aret-color: rgb(0, 0, 0);">(</span></font><code style=3D"color:rgb(0,0,0)">=
192.168.1.2</code><font color=3D"#000000"><span style=3D"caret-color: rgb(0=
, 0, 0);">):</span></font><pre class=3D"gmail-!overflow-visible"><div class=
=3D"gmail-contain-inline-size gmail-rounded-md gmail-border-[0.5px] gmail-b=
order-token-border-medium gmail-relative gmail-bg-token-sidebar-surface-pri=
mary gmail-dark:bg-gray-950"><div class=3D"gmail-overflow-y-auto gmail-p-4"=
 dir=3D"ltr"><code class=3D"gmail-!whitespace-pre gmail-hljs gmail-language=
-plaintext" style=3D"color:rgb(0,0,0)">host replication replicator <a href=
=3D"http://192.168.1.1/32">192.168.1.1/32</a> scram-sha-256</code>
</div></div></pre></li></ul>Ensure pg_hba.conf on each node allows connecti=
ons from the other node=E2=80=99s IP, and postgresql.conf has listen_addres=
ses set to accept connections from the other node=E2=80=99s IP (or &#39;*&#=
39; to allow all IPs).</div><div><br></div><div><br></div><div><strong styl=
e=3D"color:rgb(0,0,0)">Apply Configuration</strong><span style=3D"color:rgb=
(0,0,0);font-size:medium">:=C2=A0</span>After making these changes, restart=
 both servers to apply them.<br></div><div><span style=3D"color:rgb(0,0,0);=
font-size:medium"><br></span></div><div><span style=3D"color:rgb(0,0,0);fon=
t-size:medium"><b>Failover:=C2=A0</b></span>By default failover is not avai=
lable but a couple of reliable open source products available like</div><di=
v><span style=3D"color:rgb(0,0,0);font-size:medium">=C2=A0=C2=A0</span>1. r=
epmgr <a href=3D"https://www.repmgr.org">[Link]</a></div><div>=C2=A0 2. pet=
roni <a href=3D"https://patroni.readthedocs.io/en/latest/">[Link]</a></div>=
<div>=C2=A0 3. pg_auto_failover=C2=A0<a href=3D"https://opensource.microsof=
t.com/blog/2019/05/06/introducing-pg_auto_failover-postgresql-open-source-e=
xtension-automated-failover-high-availability/">[Link]</a></div><div><br></=
div><div>Hope this helps you configure your environment.</div><div><br></di=
v><div><br></div></div><div></div><br><div class=3D"gmail_quote"><div dir=
=3D"ltr" class=3D"gmail_attr">On Mon, Nov 11, 2024 at 11:03=E2=80=AFAM p sn=
 &lt;<a href=3D"mailto:tkdsud01@gmail.com">tkdsud01@gmail.com</a>&gt; wrote=
:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.=
8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204=
,204,204);padding-left:1ex"><div dir=3D"ltr">=C2=A0 Hello,

I am currently setting up PostgreSQL Stream Replication <span>for</span> a =
replication configuration.=C2=A0<div><br></div><div>=C2=A0I am <span>using<=
/span> PostgreSQL version <span>14.12</span> <span>on</span> a RedHat=C2=A0=
<span>8.9</span> environment,=C2=A0<div><span>and</span> my setup consists =
<span>of</span> a Primary-Standby replication configuration <span>with</spa=
n> two nodes.</div><div>=C2=A0Due <span>to</span> certain constraints, I ca=
nnot add more nodes <span>or</span> use external nodes.=C2=A0</div><div><br=
></div><div>=C2=A0
<span>Questions:</span>=C2=A0</div><div><span>=C2=A0 =C2=A0 1-1</span>. How=
 should I configure postgresql.conf <span>and</span> pg_hba.conf <span>for<=
/span> this setup?

I would <span>like</span> <span>to</span> implement automatic failover <spa=
n>with</span> only these two nodes.=C2=A0</div><div><span>=C2=A0 =C2=A0 1-2=
. Is</span> it possible <span>to</span> achieve this setup? <span>If</span>=
 so, could you advise <span>on</span> the specific configuration steps need=
ed? I would greatly appreciate any guidance <span>from</span> those <span>w=
ith</span> experience <span>in</span> this area. Thank you.=C2=A0</div><div=
><br></div><div><br></div><div>=C2=A0Best regards,=C2=A0=C2=A0</div></div><=
/div>
</blockquote></div></div>

--0000000000008dcc1806269d4a2c--