Support for automated failovers

[gitstashpop]

Hi folks,

I would like to propose a feature[0] to PgBouncer to allow connecting to a new node if a database in the databases section is detected as down. This is especially useful in a single writer / multiple reader cluster when the writer is down during a manual or unexpected failover.

In my implementation, PgBouncer first attempts to connect to the configured writer. If the writer is detected as down, a connection is opened to the configured reader (reader_hostname in the pgbouncer config section). A new query introduced called topology_query is used to get the hostname of the promoted writer from the reader_hostname. If the node we connect to has been promoted to a writer, then the client connection resumes. Otherwise, we disconnect from the reader, and connect to the promoted writer we received as a response to the topology_query. Once that connection completes, the connection resumes.

With the solution proposed in my PR, I was seeing the following failover downtime assuming promotion of readers was instant and there was no replication lag:

Connection Type	Time to connect in seconds
Normal Connection to Writer	0.982
Connection to promoted RR after failover	1.08
Connection to read replica after failover	1.86

Another possible solution I was considering is to use polling (select is_pg_in_recovery();) to a list of configured nodes to detect who is the new writer. The downside of this solution is you could be connecting to N nodes until you detect the promoted writer.

I am also toying with the idea of pre-connecting to the reader nodes from a configuration and reusing those open connections once downtime to the writer node is detected. This should reduce failover time to hundreds of milliseconds since we will not need to set up SCRAM/TLS before getting the topology.

[0] gitstashpop@e8fd31d

Configuration changes for testing

[databases]
writes= host=foo.baz.com port=5432 user=master password=testpassword dbname=postgres pool_size=1 topology_query='select hostname from topology where is_writer = \'t\';'

[databases]
reader_hostname = reader.baz.com
server_connect_timeout = 1
# helps reduce downtime
server_login_retry = 0

[JelteF]

I do think PgBouncer should be having a failover solution, but I'm not sure the one proposed here is the direction we should go. I think @dpirotte has a follow up PR to #736 in mind, which would implement target_session_attrs just like libpq does. This would have a few benefits over the solution suggested here IMHO:

1. it's consistent with libpq
2. no need to configure a separate view for readers and writers (it uses the hot_standby setting to determine if a node is reader or writer)
3. It can be configured per database, instead of having the reader_hostname in the pgbouncer section of the config (I guess the proposal here could be changed though to have the reader_hostname in the writes database line.

[gitstashpop]

Thanks for taking a look. I looked over #736 and it looks promising.

I have a few questions about that implementation, and if this isn't the right place, I can take them to that PR:

• It looks like target_session_attrs can only be used in PG 14+ to determine reader/writer state. This seems problematic for users of older engine versions.
• The round robin approach seems slow to me, especially if you have multiple nodes. Between TLS and SCRAM for connections to new nodes, discovery could take N * 1 seconds depending on network latency. I think a good alternative would be to use a topology query like I proposed to limit the discovery to 2 * 1 seconds in the worst case (connect to a reader and get the hostname for the new writer)

Thanks!

[davecramer]

target_session_attrs works fine if you know in advance the ip addresses, however there are situations where this isn't practical. In patroni for instance the ip addresses can be found by querying the topology from patroni. It's rather trivial to write an extension to provide topology from patroni to pgbouncer. Instead of providing this information ahead of time, what about a plugin or hook to get the topology? That way you could easily accommodate target_session_attrs and whatever the future might bring.