Postgresql AutoFailover

Question

I am having Java Application with Two nods in cluster however I have Postgresql 1 Master Database server and 3 nodes in Hot-Standby Mode.

Problem: Since DB is a single point of failure, every time patching the servers I need to bring down the entire Application which increases my Application downtime.

Looking for a Solution to implement Postgres DB Autofailover to Stand By node. My Environment: Ubuntu 18 OS with Postgres v10

Questions:

1. Postgres 10 Supports Auto Failover?
2. is it possible to implement AutoFailover of DB using Postgres native tools?
3. I am planning to configure my application to point to single LB IP which is configured to connect to an active DB server out of 4 Postgres DB serves( Out of 4 one is master). Any recommendation on this is welcome.
4. How to configure Auto failover for Postgresql DB with minimal failover time?

Answer 1

Np, PostgreSQL doesn't have any built-in facilities for automatic failover.

You have to use third-party software like Patroni.

Client failover can be managed by using a connection string that looks like

host=host1,host2 port=5432,5432 target_session_attrs=read-write

• rgh: Thanks, Laurenz, is support available for Patroni :) since I see in the document they have mentioned "It will have its own caveats. Use wisely." since i am a beginner on this topic, i am not sure what are the caveats and we have an Enterprise application to support. Is there commercial support available for this tool or any other equivalent tool.
• Laurenz Albe: Yes, ask a PostgreSQL company of your choice.