PostgreSQL addon: expose pgbouncer (pgbouncer.ini) tunables via Reconfiguring / cluster API

[weicao]

Problem

Today the PostgreSQL addon ships a per-pod pgbouncer sidecar (port 6432) using addons/postgresql/config/pgbouncer-ini.tpl mounted as pgbouncer-configuration. Operators often need to tune pool sizing (default_pool_size, related pool limits) and other pgbouncer.ini settings for workload shape and connection storms.

KubeBlocks Reconfiguring OpsRequest only targets postgresql.conf (see templates/paramsdef.yaml: fileName: postgresql.conf). The pgbouncer-configuration config entry in templates/cmpd.yaml has no reconfigure action (unlike postgresql-configuration, which includes postgresql.config.reconfigureAction).

So users cannot adjust pgbouncer via the cluster API after deployment; default_pool_size is not even set in the template, so PgBouncer default (20) applies. That is a real product gap (documented as such in kubeblocks-docs PostgreSQL FAQ §7).

Desired behavior

• First-class support to change pgbouncer parameters (at minimum: default_pool_size, ideally pool_mode, max_client_conn override, and other common pgbouncer.ini keys) without forking the chart for every cluster.
• Consistent UX with engine reconfiguration: e.g. OpsRequest type Reconfiguring (or a dedicated operation) with validated parameters and clear reload vs restart semantics for the pgbouncer container.

Suggested implementation sketch

1. Add a parameters definition / schema for pgbouncer.ini (properties format or dedicated constraint), or extend the component config model so pgbouncer keys are valid reconfigures[].parameters for component postgresql.
2. Attach reconfigure lifecycle to pgbouncer-configuration in ComponentDefinition (reload pgbouncer or restart sidecar as appropriate—Bitnami images often support SIGHUP reload for many settings).
3. Ensure generated pgbouncer.ini merges user overrides with templated values (memory-based max_client_conn, etc.).
4. Tests / examples: sample OpsRequest and kbcli path if applicable.

References

• addons/postgresql/templates/cmpd.yaml — configs for postgresql-configuration vs pgbouncer-configuration
• addons/postgresql/templates/paramsdef.yaml — postgresql.conf only
• addons/postgresql/config/pgbouncer-ini.tpl — current template

---

If this belongs in the main kubeblocks repo instead (core reconfiguring API), please move/relabel; the addon change still needs the pgbouncer config + reload wiring above.

[earayu]

Adding downstream validation from an ApeRAG/ApeMind deployment: this is blocking PgBouncer transaction-pooling rollout, not just pool-size tuning.

Current observed shape:

• PostgreSQL services expose both 5432 and 6432, and the bundled PgBouncer sidecar is present.
• Generated pgbouncer.ini is still fixed at pool_mode = session.
• The generated config also has no explicit default_pool_size; PgBouncer's built-in default applies.
• Attempting to change PgBouncer through a Reconfiguring OpsRequest fails because PgBouncer keys are not in the parameter schema. Example failure when trying to set only pool_mode:

reconfigure parameter failed: parameter pool_mode not found in parameters schema

Why this matters:

• session pooling is safe, but it does not significantly reduce PostgreSQL backend connections when application-side pools keep client sessions open.
• transaction pooling is the mode needed for high connection reuse during API / worker / SSE bursts, but it must be configured as a managed, durable cluster setting. Patching the generated ConfigMap directly would bypass the KubeBlocks source of truth and risks being overwritten by reconcile / upgrade / restore.

Minimum parameters needed for a practical rollout:

• pool_mode (session / transaction / statement, with documented compatibility caveats)
• default_pool_size
• max_client_conn
• server_idle_timeout
• server_reset_query
• optionally ignore_startup_parameters and related auth/admin settings if the template needs operator overrides

Expected behavior:

• PgBouncer parameters should be configurable through the same durable KubeBlocks API path operators use for PostgreSQL parameters, or through an explicit PgBouncer-specific operation.
• The addon should provide validation, generated examples, and clear reload-vs-restart semantics for the PgBouncer sidecar.

This issue already describes the right direction; the additional blocker is that downstream applications cannot safely adopt transaction pooling until these PgBouncer settings are first-class and managed by KubeBlocks.

[earayu]

Follow-up to make the request easier to scope:

Recommended parameter priority:

1. First batch / minimum viable rollout:
   • pool_mode
   • default_pool_size
   • max_client_conn
2. Second batch / production tuning:
   • server_idle_timeout
   • server_reset_query
   • other operator-overridable PgBouncer settings as needed

Suggested acceptance criteria:

• After creating a PostgreSQL cluster, an operator can change PgBouncer parameters through an OpsRequest or another documented KubeBlocks cluster API path, without directly patching the generated ConfigMap.
• The operation has documented validation and reload/restart behavior for the PgBouncer sidecar.
• The effective values can be verified from PgBouncer itself, for example with SHOW CONFIG, SHOW POOLS, or equivalent documented commands.
• A sample manifest / command is included for changing at least pool_mode and default_pool_size.