PgBouncer ========= PgBouncer package version ------------------------- By default, TPA installs the latest available version of PgBouncer. The version of the PgBouncer package that is installed can be specified by including ``pgbouncer_package_version: xxx`` under the ``cluster_vars`` section of the ``config.yml`` file. .. code:: yaml cluster_vars: … pgbouncer_package_version: 1.8* … You may use any version specifier that apt or yum would accept. If your version does not match, try appending a ``*`` wildcard. This is often necessary when the package version has an epoch qualifier like ``2:...`` . Configuring PgBouncer --------------------- TPA will install and configure PgBouncer on instances whose ``role`` contains ``pgbouncer`` . By default, PgBouncer listens for connections on port 6432 and, if no ``pgbouncer_backend`` is specified, forwards connections to ``127.0.0.1:5432`` (which may be either Postgres or :ref:`Configuring haproxy ` , depending on the architecture). .. Note Using PgBouncer to route traffic to the primary:: If you are using the M1 architecture with repmgr you can set `repmgr_redirect_pgbouncer: true` hash under `cluster_vars` to have PgBouncer connections directed to the primary. The PgBouncer will be automatically updated on failover to route to the new primary. You should use this option in combination with setting `pgbouncer_backend` to the primary instance name to ensure that the cluster is initially deployed with PgBouncer configured to route to the primary. .. :: You can set the following variables on any `pgbouncer` instance. .. csv-table:: :header: Variable,Default value,Description :widths: 12,12,18 :align: left :class: longtable `pgbouncer_port`,6432,The TCP port pgbouncer should listen on `pgbouncer_backend`,127.0.0.1,A Postgres server to connect to `pgbouncer_backend_port`,5432,The port that the `pgbouncer_backend` listens on `pgbouncer_max_client_conn`,`max_connections`×0.9,The maximum number of connections allowed; the default is derived from the backend's `max_connections` setting if possible `pgbouncer_auth_user`,pgbouncer_auth_user,Postgres user to use for authentication Databases --------- By default, TPA will generate ``/etc/pgbouncer/pgbouncer.databases.ini`` with a single wildcard ``*`` entry under ``[databases]`` to forward all connections to the backend server. You can set ``pgbouncer_databases`` as shown in the example below to change the database configuration. Authentication -------------- PgBouncer will connect to Postgres as the ``pgbouncer_auth_user`` and execute the (already configured) ``auth_query`` to authenticate users. The ``pgbouncer_get_auth()`` function used as the ``auth_query`` by PgBouncer is created in a single database, the ``pgbouncer_auth_database`` . Execute permissions are granted on this function to the ``pgbouncer_auth_user`` . Example ------- .. code:: yaml instances: - Name: one vars: max_connections: 300 - Name: two - Name: proxy role: - pgbouncer vars: pgbouncer_backend: one pgbouncer_databases: - name: dbname options: pool_mode: transaction dbname: otherdb - name: bdrdb options: host: two port: 6543 Minor update for PgBouncer using ``tpaexec upgrade`` ---------------------------------------------------- When trying to upgrade to a specific package version, ensure the ``pgbouncer_package_version`` in ``config.yml`` is updated to reflect the desired version. The desired version can also be passed as an extra argument to the ``tpaexec upgrade`` command with: .. code:: shell tpaexec upgrade \ -e pgbouncer_package_version="" \ --components=pgbouncer Refer to the section on :ref:`package version selection and upgrade ` for more information. To select PgBouncer for upgrade, ensure the ``--components`` flag passed to the ``tpaexec upgrade`` command contains ``pgbouncer`` (or ``all`` ) Refer to the section on :ref:`component selection for upgrade ` for more information.