SGShardedCluster: connect the citus nodes to each other through PgBouncer

Summary

The nodes of a citus SGShardedCluster connect to each other directly to the Postgres port that Patroni registers in pg_dist_node (the port of postgresql.connect_address). Every coordinator and query router backend that runs a distributed query opens its own connections to the workers, bypassing the PgBouncer that runs next to Postgres in each Pod.

The port registered in pg_dist_node can not be changed to the one of PgBouncer, since Patroni builds it from postgresql.connect_address, that is also used by the replicas for physical replication (PgBouncer can not proxy replication connections). Patroni writes nothing else to pg_dist_node (postgresql.proxy_address is only published in the DCS).

Proposed resolution

Add SGShardedCluster.spec.configurations.citus.connectToPooler (true by default). When true:

• The coordinator maintains pg_dist_poolinfo so that Citus connects to each node through PgBouncer (port 6432, or the Envoy entry port 7432 when Envoy is enabled) instead of the Postgres port. Only the port is set, so the host keeps following the one that Patroni updates in pg_dist_node on failover. pg_dist_poolinfo is not synced by Citus, so the rows are written on the coordinator and on every worker and query router with a scheduled coordinator SGScript entry.
• The disableConnectionPooling fields of the coordinator, shards and their overrides are ignored and PgBouncer is always created.

Citus ignores pg_dist_poolinfo for the connections that can not go through a pooler (like the ones of the shard rebalancer), that keep connecting directly.