Failover Manager with client connect failover v4

Most of the PostgreSQL connection libraries support client connection failover. These libraries support connection strings with more than one database server. On first connection attempt or when the connection is lost (which also occurs during a failover), the client driver connects to the supplied hosts one by one until it finds a read-write connection. The time for reconnecting to the new master depends on the connection timeouts as configured in the driver and TCP layer.

Failover Manager traffic routing diagram for client connect failover

Figure 2: Failover Manager's traffic routing using client connect failover

Using Failover Manager with Client Connection failover

Installing

Install and configure Advanced Server and Failover Manager on three servers as follows:

SystemsComponents
PG Primary, PG Standby1, and PG Standby2Primary or standby nodes running Advanced Server and Failover Manager

You don't need to configure the virtual IP configuration in efm.properties (virtual.ip, virtual.ip.interface, virtual.ip.prefix, and virtual.ip.single).

Configuring Client Connection failover

DriverClient Connection failover supportVersion supportedConfiguration
JDBCYesAll supported versionsSupply multiple hosts in the connection string, and set the targetServerType attribute as primary. Example: jdbc:postgresql://host1:5444,host2:5444/accounting?targetServerType=primary. More information: https://jdbc.postgresql.org/documentation/head/connect.html#connection-failover
libpqYes10 and aboveSupply multiple hosts in the connection string, and set the target_session_attrs attribute as read-write. Example: postgresql://host1:5444,host2:5444/edb?target_session_attrs=read-write. More information: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING
.NETYes6 and aboveSupply multiple hosts in the connection string, and set the Target Session Attributes attribute as primary. Example: Host=host1,host2;Username=test;Password=test;Target Session Attributes=primary. More information: https://www.npgsql.org/doc/failover-and-load-balancing.html
go - jackc/pgxYes5 and aboveUse libpq syntax as shown above. More information: https://pkg.go.dev/github.com/jackc/pgx#ConnConfig
psycopyg3YesAll supported versionsUse libpq syntax as shown above. More information: https://www.psycopg.org/psycopg3/docs/api/connections.html
OCLYes10 and aboveOCL is based on libpq, hence check libpq for details.
ODBCNo