search

eclipse-vertx / vertx-sql-client

High performance reactive SQL Client written in Java
Apache License 2.0
894 stars 200 forks source link

PgClient: support multi hosts connection #1250

Open mcanzerini opened 2 years ago

mcanzerini commented 2 years ago

Describe the feature

here I saw that we cannot connect to a multi hosts instance of postgres which is mandatory for us.

Can you plan supporting multi hosts connection ?

Use cases

Use case: Support high availbility postgresql instances

As an example, with the classic jdbc driver, we can define this property to connect to our HA postgres instance: 

quarkus.datasource.jdbc.url=jdbc:postgresql://my.host-1.com,my.host-2.com,my.host-3.com:5432/myDB?targetServerType=master&ssl=true&sslmode=verify-ca&sslcert=my-cert&sslkey=my-key&sslpassword=&sslrootcert=my-cert.crt

It would be great to be able to do the same with your PgClient by parsing the URI the same way

tsegismont commented 2 years ago

I think you can already do this using a list of PgConnectOptions

Vert.x will pick a different database for each new connection in a round-robin fashion.

alex-pumpkin commented 1 year ago

@tsegismont , round-robin does not help us with targetServerType=master. Please correct me if I am wrong, but as I understand PgClient doesn't care about the type of the server.

It would be great if PgClient could choose only preferred/required servers (primary, secondary etc. HostRequirement). We cannot correctly use HA Postgres clusters without it. Classic JDBC Driver example is here: MultiHostChooser

Thanks in advance

vietj commented 1 year ago

@alex-pumpkin in the next version you will be able to implement the policy you want with the new Supplier<Future<SqlConnectOptions>>

alex-pumpkin commented 1 year ago

Thank you, @vietj . Maybe one day I will start implementing it. But I believe HA support should work out of the box as part of the vertx-pg-client. It should help the widespread adoption of the PgClient in large projects using Vert.x, Quarkus etc.

alexpoletaev commented 3 weeks ago

@alex-pumpkin in the next version you will be able to implement the policy you want with the new Supplier<Future<SqlConnectOptions>>

@vietj, since HA support is not implemented yet, could you please provide an example how I could implement my own policy with the Supplier<Future<SqlConnectOptions>> instead of the round-robin strategy? I'm using Quarkus 3.9.2 with the vertx-pg-client 4.5.7 by the way and can update to latest version if it's needed.