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.

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 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.

Variable	Default value	Description
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#

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:

tpaexec upgrade <cluster_dir> \
  -e pgbouncer_package_version="<desired version>" \
  --components=pgbouncer

Refer to the section on 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 component selection for upgrade

for more information.

PgBouncer

Contents