Consider switch to jackc/pgx from lib/pq

[daa]

Is your feature request related to a problem? Please describe. I have a HA PostgreSQL cluster consisting of 1 master and 2 replicas with quorum commit and I'd like to use client-side failover like libpq has (https://www.postgresql.org/docs/13/libpq-connect.html, 31.3.3). But currently I can't because Go's lib/pq module is in maintenance mode (https://github.com/lib/pq#status) and lacks required features (https://github.com/lib/pq/issues/683).

Describe the solution you'd like I think that the best and easiest way would be to use pgx as a PostgreSQL driver instead of pq because it's actively maintained, has necessary features and is compatible with sqlx used in SQL plugins.

Describe alternatives you've considered Alternative solution may be to deploy and use proxy to handle failover and load balancing however this makes scheme more complex which I'd like to avoid.

Additional context Authors of lib/pq recommend using pgx on package front page.

[wilsoniya]

I'd also like to see support for multiple hosts for HA Postgres.

Absent this, is there documentation for alternative ways to use multi-host (i.e., HA) postgres with temporal?

[yiminc]

We don't have resource to work on this at the moment. Contribution on this would be awesome.

[wilsoniya]

A bug in lib/pq appears to be a direct cause of this https://github.com/temporalio/temporal/issues/4184