Usage with RDS or other HA Solutions

[MaerF0x0]

When one uses RDS they connect to a given fqdn(host) and port . In the event of a failover scenario, amazon changes the IP that the fqdn points to to a hot spare pg box.

I tested the failover and it seems that pgx did not detect anything occurred. Is it possible to have pgx testOnAcquire or similar? Basically I want some kind of hook where I can have the pool detect it's no longer working and make it connect to the new IP address.

Thoughts?

[MaerF0x0]

What if we test the connections before saying they're available here: https://github.com/jackc/pgx/blob/master/conn_pool.go#L280 ?

[jackc]

I'd like to support or at least provide hooks to support this use case. But what would the hook do? If Amazon has changed the IP the FQDN resolves to shouldn't creating a connection normally connect to the new server? Calling ConnPool.Reset in this case should cause all existing connections to be invalidated. All uses of the pool from that point should be on new connections to the new server.

Regardless of that, the TestOnBorrow function in the redis adapter looks like a good idea. I'm :+1: for that.

[jackc]

As far as Stat testing all connections, I'm not sure I like the performance implications of that. It changes a predictably fast call into an call with an unknown time cost.

[riaan53]

Im currently also looking into failover scenarios. I see that the jdbc driver supports multiple hosts in the connection url: https://jdbc.postgresql.org/documentation/head/connect.html . Can also extend that for load balancing.

[bpicolo]

@jackc libpq supports these scenarios now through target_session_attrs in v10:

https://paquier.xyz/postgresql-2/postgres-10-libpq-read-write/

It essentially just does a round-robin check for whether the connection is read-only (then rewrites the string so later connection attempts try in the last-known-good order). Is that something you think could get into the roadmap? Would be great to have built-in HA here.

I might be able to sneak in a first pass at it during the holiday weekend

[jackc]

I agree it would be good to have compatibility with libpq on this feature -- a PR would be welcomed.

[jackc]

I believe this issue is resolved now that pgx has support for target_session_attrs.

[gfodor]

I don't think target_session_attrs fixes this with RDS domain name updates. RDS does not expose multiple hostnames, they rely upon the client re-resolving the DNS record when there is a connection failure.