Thread: Inquiry on Setting Up Automatic Failover with Stream Replication
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,
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
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@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,
> 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@gmail.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
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).
- 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-256Apply 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 like1. 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@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,