Configuring pgbouncer v23

Suggest edits

TPA will install and configure pgbouncer on instances whose role contains pgbouncer.

By default, pgbouncer listens for connections on port 6432 and forwards connections to 127.0.0.1:5432 (which may be either Postgres or haproxy, depending on the architecture).

You can set the following variables on any pgbouncer instance.

VariableDefault valueDescription
pgbouncer_port6432The TCP port pgbouncer should listen on
pgbouncer_backend127.0.0.1A Postgres server to connect to
pgbouncer_backend_port5432The port that the pgbouncer_backend listens on
pgbouncer_max_client_connmax_connections×0.9The maximum number of connections allowed; the default is derived from the backend's max_connections setting if possible
pgbouncer_auth_userpgbouncer_auth_userPostgres 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.

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
← Prev

Configuring Postgres Enterprise Manager (PEM)

Next →

Configuring pgd-proxy