pgjdbc-ng driver and cluster failover - targetServerType=master

[vazquesaid]

trafficstars

i have a postgresql cluster with a master and 2 standby nodes, i need the failover to be managed by the impossibl drive the same as the standard postgresql driver using the targetServerType=master. will the impossibl driver implement a such feature ?

Thanks for your answer.

[kdubb]

https://impossibl.github.io/pgjdbc-ng/docs/current/user-guide/#connection-fallback

[kdubb]

☝️That is all that is supported with regard to "multiple" servers.

[dibwill]

I have the same issue, does targetServerType parameter supported by pgjdbc-ng ? thanks for feedback

[vazquesaid]

The pgjdbc-ng does not support the targetServerType. i have to create a proxy driver witch encapsulate the pgjdbc and added the targetType=master as URL property and ping for each connection the server using the (select pg_is_in_recovery()) to elect the master. if you use tomcat or hikari connection pool you can add the validationQuery to help you validate the master connection : select case when not pg_is_in_recovery() then 1 else random() / 0 end doing all above it works like a charm .

[bhemar]

I also have issues with failover when multiple addresses are specified in connection URL. If the first host is secondary (replica), then my service will successfully connect to it, but it will throw an error when LISTEN command is executed on replica.

Do you have any plans to support proper automatic failover within this driver, something similar like on official Postgres JDBC driver?

[vazquesaid]

To fix this failover issue, you need to write a small driver delegate, by writing or overloading the getConnection() method. The new getConnection method will test each native getConnection (from the url lists) against the (if(select pg_is_in_recovery() = false) then return connection; else go to the next url; and so until you find a master node). the target is to return the connection on the pool only if it is from master. Hope that helps.

[bhemar]

I can try something like this. thanks!