How to configure PgBouncer with BigAnimal Cluster

PgBouncer is a lightweight connection pooler for PostgreSQL. This article focus on the basic connection configuration steps for PgBouncer and BigAnimal. The connection pool settings may vary for the different application architectures and doesn't cover in this article.

Note: BigAnimal also supports the native PgBouncer service, only supporting session pool mode. You can enable it on the Additional Settings page when creating or updating your single or high availability BigAnimal cluster.

Topology

• A MacOS which using PSQL as the client to connect to the remote PgBouncer service
• A Ubuntu VM which installed PgBouncer service 
• A BigAnimal Cluster

Install PgBouncer

• Install PgBouncer on Ubuntu : apt-get install -y pgbouncer

Config PgBouncer

1. Update PgBouncer configuration file /etc/pgbouncer/pgbouncer.ini
   
   ------------------------------------------------------------------------------------------------
   

   [databases]
   

   [database name] = host=<BigAnimal Cluster Host> port=<BigAnimal Cluster Port> user=<BigAnimal Cluster User> dbname=<BigAnimal Cluster Dbname>

   [pgbouncer]
   

   listen_addr = *  (use * meaning “listen on all addresses”)
   

   listen_port = 6432     (Which port to listen on)

   auth_type = scram-sha-256 (How to authenticate users. Update base on the actual setting.)

   auth_file = /etc/pgbouncer/userlist.txt     (The name of the file to load user names and passwords from)
   ------------------------------------------------------------------------------------------------

   
   * The [database name] defined in [databases] section is used when a client connect to PgBouncer. The database name “pgbouncer” is reserved for the admin console and cannot be used as a key here.
   
   * BigAnimal Cluster Connection Info could be collected from the BigAnimal Portal
   
   
   
   

2. Add BigAnimal cluster username and password in auth file /etc/pgbouncer/userlist.txt 

   • "<BigAnimal Cluster User>" "<BigAnimal Cluster Password>"

Launch PgBouncer

pgbouncer -d pgbouncer.ini

Connect BigAnimal cluster through PgBouncer 

1. Using PSQL as the client to connect to the remote PgBouncer service:
   psql -h <PgBouncer Service Host> -p <PgBounder Listen Port> -U <BigAnimal Cluster User> <database name>
2. Input the password of the database user