Re: Inquiry on Setting Up Automatic Failover with Stream Replication

> *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 AM Zaid Shabbir <zaidshabbir(at)gmail(dot)com> 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’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,
>>
>