Improve database resiliency with new JDBC Connectors and EDB Failover Manager

This blog was written by Ahsan Hadi and co-written by Jason Davis.

EnterpriseDB is about to release an update to the Connectors for Postgres and EDB Postgres. The update will include some new capabilities in the JDBC driver to support connection failover.

Connection failover provides automatic and robust failover for Java applications connecting to Postgres via the JDBC driver. The idea is to provide multiple host and ports to the JDBC connection URL. When a client instantiates a connection (i.e Connection conn = DriverManager.getConnection(url);), the JDBC driver will try to connect to the first host/port in the connection URL. Failing to connect to the first host, it will continue trying to establish connections with the hosts in the connection string until a successful one is established or it has tried all the hosts in the connection string. If the connection can’t be established with any host in the connection string then a normal exception would be returned.

The basic syntax for the connection url is:

jdbc:postgresql://host1:port1,host2:port2,host3:port3/database

The developer using the JDBC Connector can also specify three useful parameters:

• targetServerType – allows you to configure incoming requests to a server with a specific state (master, replica or preferReplica). Server type is determined by observing if the server allows writes. This capability allows for flexible configurations of the connections, which we discuss in more depth below.
• hostRecheckSeconds – specifies how long the knowledge of a host state is stored in the JVM. The default is 10 seconds.  
• loadBalanceHosts – allows you to configure if the first host is always tried (when set to false) or if connections are randomly chosen (when set to true)

There are a number of interesting use cases where this new JDBC Connector feature can be deployed. In this blog, we are going to discuss how the targetServerType=master setting can be used with EDB Failover Manager (EFM) from EnterpriseDB (EDB) to enable a high availability solution without requiring a VIP or load balancer. Another solution could include setting up two different connections – one for reads and one for writes – so the application developer can effectively load balance read requests amongst replicas while keeping write requests routed to a proper master node. A third solution could use different connections to provide preference to specific nodes in an xDB Multi-Master environment, but also provide connection failover to another master node in the event of failure.

For those not familiar, EFM is EDB’s solution for managing failover between multiple instances of EDB Postgres Advanced Server or PostgreSQL databases configured with streaming replication. EDB Failover Manager adds fault tolerance to these database clusters to minimize downtime when a master database fails. EDB Failover Manager provides the cluster health monitoring, node/database failure detection, and automatic failover mechanisms needed for integration into a variety of stringent 9’s high availability solutions. EFM solves this problem by providing a lightweight product with no single point of failure that is easy to install, configure and use.

The JDBC connection failover feature would be useful when it is working with a high availability database cluster. Prior to this release of the Connectors, EFM would require the use or a VIP or Load Balancer in front of the database server to route connections to the appropriate master database. With this new Connector feature, a Java application would try to connect to each master listed in the connection URL until it found a database server willing to service the connection; at the same time, EFM can ensure that only a single database server is acting as the master and willing to accept the connection. The other nodes would act as a replica so the JDBC driver would not use a connection to them. The JDBC would try and connect to each host in the connection string and upon confirming that the server is a master and not a replica, the driver would be connected to the host and it would only connect to the master and not the replica. Those databases would still accept connections; it's just that the driver wouldn’t use such a connection when the "targetServerType" prop is used.

In order to further describe this functionality, I will use a common use case of creating a HA Postgres database cluster in an AWS environment. In this example, we have a database cluster with one master node and two replica nodes deployed using AWS instances. The database nodes can be in the same availability zone or they can reside in separate availability zones. The EFM agents will be performing a health check of all the nodes in the cluster so if the master node goes down, EFM will promote one of the replicas as master and new clients will be routed to the new master.

As you can see from the diagram below, we have the IP address of all database nodes in the connection string of the JDBC driver. The JDBC driver will try to connect to each of them and check whether the server to which it’s being connected is a master or a replica. It only establishes connection with the master and doesn’t establish connection with the replica nodes.

The following diagram describes an architecture where the client will always connects to a master node via the JDBC driver. EFM ensures that only one node in the cluster is the master and other nodes are configured as replicas. The JDBC driver performs the master verification check upon establishing a connection with the host. If the host is not a master it will refuse to establish a connection with that host and move to the next host in the connection string. The driver will only establish and return a successful connection to the client when it gets connected to the master node.

Now let’s discuss the possible failure scenarios and how each one will be handled using JDBC connection failover and EDB Failover Manager:

1. The EFM agent goes down.
2. The database server on the master node goes down.
3. The AWS instance running the master node dies.
4. The Master node is isolated from other nodes in the cluster.

In order to handle the first failure scenario of the EFM agent going down, as in the agent crashes for some reason (as opposed to someone shutting it down), the other agents will detect that it has left and they'll all try to ping that agent's database. If any agent can ping the database, then there's no failover. In this case, email notifications will be sent saying that there's a problem.

In case of the second failure scenario of the master database server going down, EFM will promote one of the replica databases to master. The clients that were connected to the old master database that went down will get a connection error, the client program will then attempt to reconnect and will now establish connection with the new master because the old master database is no longer available and one of the replicas was promoted as a master by EFM.

The third case will be handled similar to the second case, the replica node won’t be able to connect to the master node because the AWS instance that was running the master database went down. EFM will promote the replica node to master and the clients that were connected to the old master will get a connection error. The client will reconnect and now the JDBC driver will establish connection with the new master.

The fourth scenario is when the master node becomes isolated from the replica nodes, due to let’s say a networking failure. In this case, EFM will promote the replica node as master and will also ensure that clients don’t connect to the old master. The EFM currently achieves this by using a fencing script that will block the IP on the old master so clients cannot connect to it directly. In the next update to EFM (2.0.2), we will provide an option to terminate an old master so clients cannot connect to it directly.

I hope the above has been helpful in describing how the JDBC multiple IP connection failover feature will be able to be used together with EDB Failover Manager to provide a robust HA Postgres database cluster. In other scenarios when the clients are connecting using a virtual IP, EFM provides the failover by using the virtual IP. The VIP is assigned to the master node and in case of failover the replica is promoted to master and VIP is assigned to the new master node. 

For information on how EDB can help you with high availability and Postgres, please contact us.