How to use Drogon framework to handle switch databases in case of Database Failover?

[requinham]

Hello,

I am using C++ Drogon framework for developing Restful microservice. In case to manage Database Failover mechanism for High Availability purpose, we have to configure Drogon to switch automatically from Primary DBMS to Secondary DBMS in case of Failover and to return to Primary when it is restored.

Setting both DBMS in configuration file, only allow us to create some DbClient exchanging with the targeted database. So, if we have to code manually the switching, we have to catch exception for each query and switch to secondary database client if the exception means that we lost the Primary DBMS.

This kind of management is really bad for quality of code and it is huge effort for nothing because our goal in this case is not writing intentionally one of them but write to the primary always until we lost it as failover works.

One solution was to put load balancer between Drogon and DBMSs but this involve adding new component and monitoring it and it is not our goal.

So, is there any ready mechanism to handle this obvious use case like in Java Spring or other (which ask you just to put the primary and secondary and handle the failover automatically)?

Thx!

[an-tao]

As of now, the Drogon framework itself does not provide a built-in mechanism to handle database failover automatically without manually handling the logic or relying on external tools (such as a load balancer). However, you can implement a custom solution by creating a FailoverDbClient class. This class manages two DbClient instances (primary and secondary), automatically retries queries on the secondary database if the primary fails, and periodically checks the primary to switch back when it becomes available. This encapsulates the failover logic, keeping your application code clean. Alternatively, you can use external tools like HAProxy or database-level replication solutions to handle failover.

[requinham]

Your method is good for small program with some SQL Query. I guess it is so simplistic to be integrated well with Mapper, Transaction, Binder and other Drogon ORM classes. These classes are tightly coupled to concrete implementation. So we tried to make it but it is not possible properly...
What I misunderstand is if you are using libpq for Postgresql connection so normally we can set multiple hosts in config file as supported in libpq: libpq-connect

It is possible to specify multiple host components, each with an optional port component, in a single URI. A URI of the form postgresql://host1:port1,host2:port2,host3:port3/ is equivalent to a connection string of the form host=host1,host2,host3 port=port1,port2,port3. As further described below, each host will be tried in turn until a connection is successfully established.

Now, when host1 becomes down, connection with host2 is established and queries are served from host2.

Reading your answer, I feel you changed this default running mode in Drogon. Is this way of running still preserved with Drogon?

Thanks!

[an-tao]

In Drogon development, there isn't specific support for multi-node setups. However, I read the relevant documentation of postgresql, and if the port number for each of your nodes is the same, you can achieve this functionality by setting the host configuration option to a comma-separated string. I'm not sure, but you can give it a try.