Configure read replica pgbouncer for GitLab

[jacobbmay]

https://docs.gitlab.com/ee/administration/postgresql/database_load_balancing.html

GitLab recommends configuring pgbouncer for reads with a single pgbouncer instance per replica and configuring the gitlab read replica loadbalancing to balance between the hosts of each node.

The current implementation of the pgbouncer uds package doesn't support this in a way that is scalable at deploy time since each replica in the package gets the same configuration and shares the same service. Configuring it in the recommended way with this package would require instantiating the package once per replica in the bundle at build time which means the bundle would expect a specific number of postgres replicas that couldn't be adjusted at deploy time. It also means a lot of copying and pasting since there is no way to tell UDS to create multiple copies of a package based on an input.

The temporary solution for configuring pgbouncer for reads is to create a pgbouncer cluster with the pgbouncer uds package and add a single hostname (the kubernetes service for the read cluster) to the gitlab load balancing configuration. It is unclear how GitLab will handle this since its built in load balancer expects to be able to evaluate stale reads and switch to a different replica if needed.

We should either find a scalable way to support configuring a single pgbouncer pod per read replica at deploy time, or find a way to configure pgbouncer on each postgres VM via NDB.

If we go the in cluster route, ideally we would have a UDS package that would support passing in a list of read replica hostnames, and then it would create a service and pod per hostname. I don't think there is a good way to do this with UDS features and would likely require creating a custom helm chart to support this. I am not sure if this is a generic enough usecase to warrant adding this functionality to the uds-package-pgbouncer and would likely end up being something specifically to support NDB HA postgres.

If we go the route of installing the read pgbouncer instances on the DB VMs, it will require baking pgbouncer executables and configuration in the postgres profile images, and configuring the post create script to start pgbouncer after NDB is finished configuring postgres. It would need to use a different port than postgres and GitLab and the RW pgbouncer cluster would need to be configured in a way that GitLab can use the same port for both writes and reads (it expects the read replica hostnames to be accessible on the same port as the primary).

This issue got wordy, but I needed to brain dump it all while it was fresh so I didn't forget anything