Re: Inquiry on Setting Up Automatic Failover with Stream Replication

[Zaid Shabbir <zaidshabbir@xxxxxxxxx>]

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]

  2. petroni [Link]

  3. pg_auto_failover [Link]

Hope this helps you configure your environment.

On Mon, Nov 11, 2024 at 11:03 AM p sn <tkdsud01@xxxxxxxxx> 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,