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.

[alexpoletaev]

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

[matthudsonatx]

I recommend against implementing round-robin connection logic because high availability is out of scope for an app-layer driver. Whether spreading read-only load to replicas or expecting the driver to hunt for the primary from a list of servers, it is all better managed by a proxy such as PgBouncer and a regular HA supervisor such as Patroni.