Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tAaHU-00GZAJ-27 for pgsql-admin@arkaria.postgresql.org; Mon, 11 Nov 2024 19:40:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tAaHR-00HTJu-7L for pgsql-admin@arkaria.postgresql.org; Mon, 11 Nov 2024 19:40:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tAaHQ-00HTJl-QJ for pgsql-admin@lists.postgresql.org; Mon, 11 Nov 2024 19:40:57 +0000 Received: from mail-ej1-x62f.google.com ([2a00:1450:4864:20::62f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tAaHN-001Pfp-BJ for pgsql-admin@postgresql.org; Mon, 11 Nov 2024 19:40:56 +0000 Received: by mail-ej1-x62f.google.com with SMTP id a640c23a62f3a-a9a6b4ca29bso712043066b.3 for ; Mon, 11 Nov 2024 11:40:54 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1731354053; x=1731958853; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=HaqUciwNOG/c9XcAquHuhAHWlTG/a1MldfUDh5b6CY4=; b=DchlBPzkaOv+bZ6Oo0IYsoPnNRLZ/FrUx/ECa0GDAi8oUVUi49xyBBrbqMNZ2q/n3O oW8AHWPM1LEixbVPmlafseVAervHZ5HqhGYfxO1ZmKHPWd8hr8a3c9y8WSOq/2j0/lWT HypDHj2vsdtbyunWRVvGfV3Sn4q4bkPdGILdD+6RgaquvV28DYoW26asd8GuzJGM8OJU MT55PZanc4LE1xVS4CFPXlXylhgS1g0qtGzVSG9zft4w9FvoX3Sdk8YavdWKGLtZ1zY6 mP/b1HhINt/cmZaUJB9T8hJKvCM3K1SW2xv07CEgjI7T9oQNeh73kZgyBsxkkz8k6wFS LFCw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731354053; x=1731958853; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=HaqUciwNOG/c9XcAquHuhAHWlTG/a1MldfUDh5b6CY4=; b=vaqAvt4iByd10cq0DlpZvmghN+oSgx9XAV15hDunZ8ZukxrWKp7jzpaPq4PiAllhvK QqNLWV4KMDPJJDQDTNFoEvuHQeHQnaONcyCIKdHjCpwOEU27jl659HCzlnMTzYo9z5RO 0frmEaOEhODFDDQqBvr7jf/r9frV+L3WWiZUfUUUOclwOQmEoo1NwURSOXJwgqTLDO0R 0HjMmdum9JEbLEudG30Bzh86qyHQNpyIk9PZEq4ZSoMtQ6IHmtzbRru/d8Sn+j6F9LvY Tm2gMjG/URDGI3xQDgn6mPqFhdKDfZdrOI1RbnIyKHUrkGGg9SsUL8MoqSnHAoJnGHoT wObQ== X-Forwarded-Encrypted: i=1; AJvYcCVA8r4J1lKGHWgvAEUzG1QmgnlTSyg6rx2l5ggw2F4tVI47Z5pvwXPZF8DqxYfS56ir6/oSSNv0zj4n5g==@postgresql.org X-Gm-Message-State: AOJu0YzGZI6Q7OV0aXAMhR0j6/nmcfGLAU8mAQFzY7wWOCq3eOxHz0Cq QX9s5MpWTMHfRBppxt+lBwbPuIlFI08Zf+Fg7rU6Z4jigE5iZNRNFZsSVJvIYEFsF3Nj6PO8vRw lY+7sQLkpyEfB9hQD6w4Qo+bej2Q= X-Google-Smtp-Source: AGHT+IHHjZQt9QNmW+oYHp6/heSY/PJjKLlIffAuOZ2gIgWLgtA/w54yRJY1vKB5t/AZk7uid2cJbCiA4Vm2irCf1Qg= X-Received: by 2002:a17:907:9487:b0:a99:5cd5:5b9c with SMTP id a640c23a62f3a-a9eeff44ab6mr1403730466b.36.1731354052868; Mon, 11 Nov 2024 11:40:52 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Koen De Groote Date: Mon, 11 Nov 2024 20:40:40 +0100 Message-ID: Subject: Re: Inquiry on Setting Up Automatic Failover with Stream Replication To: Zaid Shabbir Cc: p sn , pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary="0000000000004d3f2b0626a8475e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004d3f2b0626a8475e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable > *Apply Configuration*: After making these changes, restart both servers to apply them. As far as I'm aware, it is not required to restart the server process, in order to reload the HBA file, you can just use the postgres user and use this: "select pg_reload_conf();" Some settings require an explicit restart, many do not and can be picked up by running that command. See the documentation: https://www.postgresql.org/docs/14/sql-altersystem.html " A server configuration reload can be commanded by calling the SQL function pg_reload_conf(), running pg_ctl reload, or sending a SIGHUP signal to the main server process. " > By default failover is not available but a couple of reliable open source products available like Failover is a standard part of Postgres. Now, **automatic** failover, that's something else, and goes beyond the scope of only postgres itself. This part of the documentation speaks on HA and Failover: https://www.postgresql.org/docs/14/high-availability.html Fully automating failover is not simple task, as you have to account for potential reasons it has to happen, like memory limitations or a failing machine. And on top of that: how do you handle split brain, the IP address where your leader node is located at, etc... If your leader node is located at 192.168.1.1 and at some point it has to switch to 192.168.1.2, that's not the end of the story. What about your code that is querying postgres? It's probably configured to go to 192.168.1.1. Whatever procedure you end up doing must also account for the fact that your code must be reconfigured to use the new IP. Or you could use a virtual IP address, and remove it from the old host and add it to the new host. That way the IP doesn't have to be updated in your application. But that also requires a few steps and has the potential that your application hangs for a while. And certainly in-flight queries will be lost. And don't forget to take a basebackup after you're done switching. You probably want to set up a new follower, you'll need to unpack a new basebackup for that. And those are just a few considerations. Maybe other people know more and better options Regards, Koen De Groote On Mon, Nov 11, 2024 at 7:34=E2=80=AFAM Zaid Shabbir wrote: > 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 fr= om > 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] > 2. petroni [Link] > 3. pg_auto_failover [Link] > > > Hope this helps you configure your environment. > > > > On Mon, Nov 11, 2024 at 11:03=E2=80=AFAM p sn 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 wit= h >> 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 o= n >> the specific configuration steps needed? I would greatly appreciate any >> guidance from those with experience in this area. Thank you. >> >> >> Best regards, >> > --0000000000004d3f2b0626a8475e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
>=20 Apply Configuration:=C2=A0After making these cha= nges, restart both servers to apply them.

As = far as I'm aware, it is not required to restart the server process, in = order to reload the HBA file, you can just use the postgres user and use th= is: "select pg_reload_conf();"

Some sett= ings require an explicit restart, many do not and can be picked up by runni= ng that command.


" A server configuration reload can be commanded by calling the SQL function = pg_reload_conf(), running pg_ctl reload, or sending a SIGHUP signal to the main server process. "

>=20 By default failove= r is not available but a couple of reliable open source products available = like

Failover is a standard part of Postgres.= Now, **automatic** failover, that's something else, and goes beyond th= e scope of only postgres itself.

This part of = the documentation speaks on HA and Failover: https://www.postgresql.org/docs/14/= high-availability.html


Fully au= tomating failover is not simple task, as you have to account for potential = reasons it has to happen, like memory limitations or a failing machine. And= on top of that: how do you handle split brain, the IP address where your l= eader node is located at, etc...

If your leader no= de is located at 192.168.1.1 and at some point it has to switch to 192.168.= 1.2, that's not the end of the story. What about your code that is quer= ying postgres? It's probably configured to go to 192.168.1.1. Whatever = procedure you end up doing must also account for the fact that your code mu= st be reconfigured to use the new IP.

Or you could= use a virtual IP address, and remove it from the old host and add it to th= e new host. That way the IP doesn't have to be updated in your applicat= ion. But that also requires a few steps and has the potential that your app= lication hangs for a while. And certainly in-flight queries will be lost.

And don't forget to take a basebackup after= you're done switching. You probably want to set up a new follower, you= 'll need to unpack a new basebackup for that.

=
And those are just a few considerations.

Maybe other people know more and better options

<= div>Regards,
Koen De Groote





On Mon, Nov 11, 2024 at 7:34=E2=80=AFAM Zai= d Shabbir <zaidshabbir@gmail.co= m> wrote:
Hello,

Authentication Options:=C2=A0You 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.

Replication User Configuration:=C2=A0For the replication user make sure both n= odes replication users set in pg_hba.conf like

  • On=C2=A0Node 1=C2=A0(<= code>192.168.1.1):
    host replication =
replicator 192.168.1.2/=
32 scram-sha-256
  • On=C2=A0Node 2=C2=A0(= 192.168.1.2):
    <=
div dir=3D"ltr">host replication replicato=
r 192.168.1.1/32 sc=
ram-sha-256
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; to allow all IPs).


Apply Configuration:=C2=A0After making these changes, restart= both servers to apply them.

Failover:=C2=A0By default failover is not avai= lable but a couple of reliable open source products available like
=C2=A0=C2=A01. r= epmgr [Link]
=
=C2=A0 2. petroni [Link]
=C2=A0 3. pg_auto_failover=C2=A0[Link]

Hope this he= lps you configure your environment.



On Mon, Nov 11, 2024 at 11:03=E2=80=AFAM p sn <tkdsud01@gmail.com> wrote:
=
= =C2=A0 Hello, I am currently setting up PostgreSQL Stream Replication for a = replication configuration.=C2=A0

=C2=A0I am using<= /span> PostgreSQL version 14.12 on a RedHat=C2=A0= 8.9 environment,=C2=A0
and my setup consists = of a Primary-Standby replication configuration with two nodes.
=C2=A0Due to certain constraints, I ca= nnot add more nodes or use external nodes.=C2=A0
=C2=A0 Questions:=C2=A0
=C2=A0 =C2=A0 1-1. How= should I configure postgresql.conf and pg_hba.conf for<= /span> this setup? I would like to implement automatic failover with only these two nodes.=C2=A0
=C2=A0 =C2=A0 1-2= . Is it possible to achieve this setup? If= so, could you advise on the specific configuration steps need= ed? I would greatly appreciate any guidance from those w= ith experience in this area. Thank you.=C2=A0


=C2=A0Best regards,=C2=A0=C2=A0
<= /div>
--0000000000004d3f2b0626a8475e--