talas-group/04_INFRA_DEPLOIEMENT/Ansible/roles/pgbouncer

pgbouncer role

define admin password

The admin user is "postgres" and the admin password is automatically generated and stored in hashicorp vault.

You can also change the admin user from postgres to something else by changing this variable:

pgbouncer_admin_users: postgres

• There is a monitoring users that can be defined, this user can only use the SHOW command, you can activite it by setting those variables:

pgbouncer_stats_users: stats
pgbouncer_stats_users_pwd: secret

define databases

Use the following variable to define a list of databases:

pgbouncer_databases:
  - dbname_from_pgbouncer1 = host=pg_host1 port=pg_port1 dbname=pg_dbname1
  - dbname_from_pgbouncer2 = host=pg_host2 port=pg_port2 dbname=pg_dbname2
  - TEST1 = host=srv-xxx user=dbuser password=dbpass
  - FOO = host=srv-yyy port=5433 user=dbuser password=dbpass
  ...

Look at the official documentation for more syntax details: https://pgbouncer.github.io/config.html#section-databases

define listening address and port

Bellow are the default values:

pgbouncer_listen_addr: "127.0.0.1,::1"

You can specify multiple addresses like this:

pgbouncer_listen_addr: "10.185.14.1,10.12.35.11"

auth settings

• The default value is scram-sha-256, possible values are: any, trust, plain, crypt, scram-sha-256 and hba, whenever possible you should use hba

pgbouncer_auth_type: "scram-sha-256"

connection settings

• The most important setting, possible values are: session, transaction and statement. The default is session (the most conservative):

pgbouncer_pool_mode: session

• Connection limits, bellow are the default values:

pgbouncer_max_client_conn: 100
pgbouncer_default_pool_size: 20

define users in userlist.txt

Define the users like this, if "username" is not defined, it will use the key as username

pgbouncer_userlist:
  user1:
    username: user1
    password: pass1
  user2:
    password: pass2 # the username will be user2

pg_hba.conf style config

To enable pg_hba.conf style config, define this variable like this:

pgbouncer_hba_config:
  key1:
    type: host # if undefined, will use "host"
    database: db1 # if undefined, will use "key1"
    user: user1 # if undefined, will use "key1"
    address: 1.2.3.4 # if undefined, will use "127.0.0.1"
    method: scram-sha-256 # if undefined, will use "scram-sha-256"
    description: something # optional
  key2:
    type: host
    database: db2
    user: user2
    address: 5.6.7.8
    method: scram-sha-256
    description: something
  some_db_name:

Optional additional parameters

You can add any additional parameters by using a dictionary like that:

pgbouncer_additional_config:
  - { 'name' : 'param1' , 'value' : 'value1' }
  - { 'name' : 'param2' , 'value' : 'value2' }

This will add 2 lines at the end of the pgbouncer.ini like that:

param1 = value1
param2 = value2

A typical parameter that we often want is this one:

pgbouncer_additional_config:
  - { 'name' : 'ignore_startup_parameters' , 'value' : 'extra_float_digits' }

This will add the following line:

ignore_startup_parameters = extra_float_digits