Use Consul dns for pgbouncer failover

What does this MR do?

The MR will render a resolv.conf file using the Consul DNS service.

It introduces a consul['configure_pgbouncer_dns'] setting, which will set pgbouncer to use Consul DNS via resolv_conf option.

To use Consul DNS for failover, the postgresql watcher needs to be disabled, and populate the pgbouncer database setting with the database primary node's consul domain form.

One caveat is, when the pgbouncer is configured to use Consul DNS, pgbouncer will use it only, which means other non-Consul domains will no longer work. Users should be encouraged to use Consul's recursors option to fallback to system DNS.

Draft: Create pgbouncer user and function for r... (!8712 - closed) is a demo for registry metadata db HA support.

Related issues

Closes Support PgBouncer failover by Consul DNS (#9288)

Related to:

• Add HA support for other databases (Praefect, Geo) (#7292)
• Support PostgreSQL HA for registry database (L... (#9116 - closed)

Checklist

See Definition of done.

For anything in this list which will not be completed, please provide a reason in the MR discussion.

Required

• MR title and description are up to date, accurate, and descriptive.
• MR targeting the appropriate branch.
• Latest Merge Result pipeline is green.
• When ready for review, MR is labeled workflowready for review per the Distribution MR workflow.

For GitLab team members

If you don't have access to this, the reviewer should trigger these jobs for you during the review process.

• The manual Trigger:ee-package jobs have a green pipeline running against latest commit.
  • To debug QA failures, refer to the Investigate QA failures section.
• If config/software or config/patches directories are changed, make sure the build-package-on-all-os job within the Trigger:ee-package downstream pipeline succeeded.
• If you are changing anything SSL related, then the Trigger:package:fips manual job within the Trigger:ee-package downstream pipeline must succeed.
• If CI configuration is changed, the branch must be pushed to dev.gitlab.org to confirm regular branch builds aren't broken.

Expected (please provide an explanation if not completing)

• Test plan indicating conditions for success has been posted and passes.
• Documentation created/updated.
• Tests added.
• Integration tests added to GitLab QA.
• Equivalent MR/issue for the GitLab Chart opened.
• Validate potential values for new configuration settings. Formats such as integer 10, duration 10s, URI scheme://user:passwd@host:port may require quotation or other special handling when rendered in a template and written to a configuration file.