Provide support for loadBalanceHosts connection property

[caipre]

PgJDBC supports connection load balancing and fail-over by accepting a comma-separated list of hosts in the connection string. These hosts are then chosen from serially, or randomly if the loadBalanceHosts property is included in the connection string. New connections can be established if any single host is unavailable, giving the appearance of high-availability to the application.

With this library, the host is just "google" and instances are passed through the non-standard cloudSqlInstance property. How can I ensure load balancing and high-availability using this library?

[jcristol]

Please I need this.

[aarnone]

Is there any update on this ticket? It seems quite a common case, especially if using Cloud SQL read replicas. 🙏

[Shpota]

I assumed this library allows querying Cloud SQL Read Replicas, but it seems like all queries hit only the primary instance. Is there a way to configure it to access Read Replicas as well?

[kurtisvg]

No update to report - when we last investigated supporting this it turned out to be somewhat complicated as different drivers have different mechanisms for interacting with read replicas, and there didn't seem to be a one fit all approach that made sense. We haven't prioritized work on it because this issue has had relatively little traction (it's been open >2 years, only has 1 thumbs up and 3 comments).

The workaround would be to use the Cloud SQL proxy with multiple instances instead, and pass in the different sockets to whatever mechanism your driver currently supports.

[kurtisvg]

looks like mysql 8.0 added a different mechanism for doing this: https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-usagenotes-j2ee-concepts-managing-load-balanced-connections.html

[enocom]

This seems like a very useful feature to support.

We'd need to pass a list of instance connection names and somehow have the driver pull from that list when attempting to create a socket.

[enocom]

For Postgres: https://jdbc.postgresql.org/documentation/use/#connection-fail-over

[gaurav517]

While we are doing that.. can we please also add the support to get the list of instances from srv records? I see its prioritised as P1 about 4 months ago.. does this mean its under development? Also any expected release month for this? Thanks.

[enocom]

We don't have a timeline presently for this, but see disaster recovery generally as an important item to address.

Meanwhile, have you explored connecting directly to leverage this existing feature in the Postgres driver?

[gaurav517]

We use jdbc:mysql+srv:loadbalance (https://dev.mysql.com/doc/connector-j/8.1/en/connector-j-reference-dns-srv.html) for mysql client side loadbalancing. And it works great. Haven't explored any equivalent for Postgres.

[michaelkanis-dmtech]

We would also like to use that feature with Postgres. @enocom What do you mean by "direct connection"?

It would be really cool if the socket factory could provide the hostnames/ports for the read replica and primary instance to the pgJDBC driver. Setting up the Cloud SQL Auth proxy as sidecar container might be an option but sounds like a lot of mainenance overhead compared to using the connection factory.

Thank you for considering this feature request.

Best regards, Michael

[enocom]

It would be really cool if the socket factory could provide the hostnames/ports for the read replica and primary instance to the pgJDBC driver.

We're actually actively working on that, although it will require some backend changes which takes longer.

What do you mean by "direct connection"?

The primary value of the Java Connector is that it makes connecting to a Public IP instance a breeze. If you're running with a Private IP instance, then with the recent improved SSL support (you can now require SSL without requiring client certs), it's just as easy to connect directly to your Cloud SQL instance using the vanilla pgJDBC driver and skip using the Java Connector enitrely. IAM Authentication is also possible in this approach, but requires a bit of code in the client app. Happy to elaborate if there's interest.

[tsawada]

Hi! Thanks for working on the issue.

We use MySQL with private IP and IAM Auth, and are wondering if there's a way to configure fallback instances.
Currently we use Auth proxy with the following connection string:jdbc:mysql:replication://localhost:auth-proxy-port1,localhost:auth-proxy-port2/....
Is there a way to do this with the regular com.mysql.cj.protocol.StandardSocketFactory, like pgJDBC for Postgres?

[enocom]

There's not a supported way to do that today with the Java Connector here.

However, there is a way to use IAM authentication without the Java Connector -- that's probably your best option for failover without using the Auth Proxy.

If there's interest, I can show you how.

[GregoireW]

The primary value of the Java Connector is that it makes connecting to a Public IP instance a breeze. If you're running with a Private IP instance, then with the recent improved SSL support (you can now require SSL without requiring client certs), it's just as easy to connect directly to your Cloud SQL instance using the vanilla pgJDBC driver and skip using the Java Connector enitrely. IAM Authentication is also possible in this approach, but requires a bit of code in the client app. Happy to elaborate if there's interest.

You mean instead of jdbc:postgresql:///test?cloudSqlInstance=project:region:name&socketFactory=com.google.cloud.sql.postgres.SocketFactory&enableIamAuth=true&ipTypes=PRIVATE we could use the default jdbc:postgresql://the.internal.ip.value/text with an override on the pool to provide the password from gcloud auth library instead of a static password. the good news, no need for a specific additional library, the thing not so nice ... I don't speak ip, so "10.24.0.2" is a little bit obscur, I prefer the cloudSqlInstance name to understand quickly which db it is. Or is there a way to use an internal dns name ?

[enocom]

@GregoireW Yes, exactly. As for DNS, you certainly could configure your own DNS for your instance, but beware that the highest level of connection validation won't pass (since the DNS wouldn't appear in the server certificate).

[GregoireW]

@enocom At this level, I think I will stay with this factory, but in the future if we need such failover, as we would need to write our custom pool, I wonder if I will not modify the pool in such way that "postgresql://project:region:name,project:region2:name2/thedb" will be processed at startup to resolve the ip and that all.

[enocom]

The team is actively working on disaster recovery support in the Connectors. So that proposal might happen, but @hessjcg is the owner of this work and will know the details.

[hessjcg]

Hi all. We are implementing automatic failover for Cloud SQL designated replicas, see #2043. Would this satisfy your needs?

[GregoireW]

How would dns SRV entry be created ? From GCP itself ?

[hessjcg]

@GregoireW: initially, no. However, in the next 6 months, we will add features to Cloud SQL to create and update the DNS records automatically for some configurations.

(I made a correction to the issue. The connector will use a TXT record, not a SRV record.)