Re: Inquiry on Setting Up Automatic Failover with Stream Replication

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’s
IP, and postgresql.conf has listen_addresses set to accept connections from
the other node’s 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_failover-postgresql-open-source-extension-automated-failover-high-availability/>

Hope this helps you configure your environment.

On Mon, Nov 11, 2024 at 11:03 AM p sn <tkdsud01(at)gmail(dot)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,
>