Use pgbouncer from Omnibus and run it on separate servers
In #1448 (closed) @sfrost recommended running pgbouncer on the application servers, as this means each pgbouncer only has a small amount of incoming connections and you don't need additional servers.
pgbouncer maps incoming connections to a target database based on the given database name. For example, it can route connections using the databsae "foo" to the "bar" database. pgbouncer can not route based on a port as far as I am aware of, it always only listens on a single port. Because the application has to be aware of the secondaries (so it can check if they are all in sync), this means that for every secondary we need to configure a custom DB name in config/database.yml
. For example, you may end up with something like this:
production: &default
adapter: postgresql
encoding: unicode
pool: 1
database: gitlab_primary
host: localhost
username: gitlab
password: hunter2
load_balancing:
hosts:
- host: localhost
database: gitlab_secondary1
- host: localhost
database: gitlab_secondary2
pgbouncer in turn would forward connections to db1/db3/db4/etc based on the input database name. For the primary pgbouncer would connect to the Azure load balancer, which in turn connects to the current primary. pgbouncer in turn can also be configured to fetch its target connection details from a DNS record. This doesn't remove the need for service discovery (as outlined in https://gitlab.com/gitlab-org/gitlab-ee/issues/2042) as we still need that to figure out what db names to use, but it does remove the need for having to manually configure pgbouncer and update it during a failover.
I quite like the idea of running pgbouncer locally, it certainly feels more resilient compared to dumping it on the DB hosts. However, it does mean that high server load could impact pgbouncer; though only the process running on said host (meaning only said host is affected).
@gl-infra: any thoughts/comments on the matter? We definitely need omnibus support for pgbouncer before we embark on this journey though.