PgClient: support multi hosts connection

[mcanzerini]

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]

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]

@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]

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

[alex-pumpkin]

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.