Best Practices for Using PgBouncer

Introduction to PgBouncer

PgBouncer is a lightweight connection pooler for PostgreSQL. It can:

• Cache connections to PostgreSQL. When a connection request is received, an idle process is allocated. PostgreSQL does not need to fork a new process to establish a connection. No resources need to be used for creating a new process and establishing a connection.
• Improve the connection usage and prevent excessive invalid connections from consuming too many database resources and causing high CPU usage.
• Restrict client connections to prevent excessive or malicious connection requests.

It is lightweight because:

• It uses libevent for socket communication, improving the communication efficiency.
• It uses C language and only 2 KB of memory is consumed by each connection.

PgBouncer supports the following types of connection pooling:

• Session pooling: PgBouncer does not reclaim the allocated connection until the client session ends.
• Transaction pooling: PgBouncer reclaims the allocated connection after the transaction is complete. The client only has exclusive access to a connection during a transaction. Non-transaction requests do not have exclusive connections.
• Statement pooling: PgBouncer reclaims the connection any time a database request completes. In this mode, the client cannot use transactions to prevent data inconsistency.

The default pooling type for PgBouncer is session. You are advised to change it to transaction.

Installation and Configuration

Before deploying PgBouncer on the cloud, purchase an ECS. To reduce network latency, you are advised to select the same VPC and subnet as those of the backend RDS instance for the ECS. After the purchase is complete, log in to the ECS to set up the environment.

1. PgBouncer is based on libevent, so you need to install the libevent-devel and openssl-devel dependencies.

   yum install -y libevent-devel
   yum install -y openssl-devel

2. After that, download the source code from the PgBouncer official website and compile the code and install PgBouncer as a regular user.

   su - pgbouncer
   tar -zxvf pgbouncer-1.19.0.tar.gz
   cd  pgbouncer-1.19.0
   ./configure --prefix=/usr/local 
   make
   make install

3. Create the following directories to store the files (such as logs and process IDs) generated by PgBouncer:

   mkdir -p /etc/pgbouncer/
   mkdir -p /var/log/pgbouncer/
   mkdir -p /var/run/pgbouncer/

4. Before starting PgBouncer, build the configuration file pgbouncer.ini.

   [databases]
   * = host=127.0.0.1 port=5432
   [pgbouncer]
   logfile = /var/log/pgbouncer/pgbouncer.log
   pidfile = /var/run/pgbouncer/pgbouncer.pid
   listen_addr = *
   listen_port = 6432
   auth_type = md5
   auth_file = /etc/pgbouncer/userlist.txt
   admin_users = postgres
   stats_users = stats, postgres                            
   pool_mode = transaction                     
   server_reset_query = DISCARD ALL              
   max_client_conn = 100
   default_pool_size = 20
   ;; resolve: unsupported startup parameter: extra_float_digits
   ;;ignore_startup_parameters = extra_float_digits

   For details about the parameters in the configuration file, see the official PgBouncer documentation.

Starting PgBouncer

PgBouncer cannot be started as root. It has to be started as a regular user.

pgbouncer -d /etc/pgbouncer/pgbouncer.ini

After it is started, run netstat -tunlp | grep pgbouncer to check the listening port of the connection pool and then connect to the DB instance.

psql -U root -d postgres -h 127.0.0.1 -p 6432 
Password for user root: 
psql (12.13)
Type "help" for help.
postgres=> \l
                                   List of databases
   Name    |   Owner   | Encoding |   Collate   |    Ctype    |    Access privileges    
-----------+-----------+----------+-------------+-------------+-------------------------
 postgres  | pgbouncer | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | pgbouncer | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pgbouncer           
           |           |          |             |             | pgbouncer=CTc/pgbouncer
 template1 | pgbouncer | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pgbouncer

Stopping PgBouncer

You can run the kill command to stop it.

kill `cat /var/run/pgbouncer/pgbouncer.pid`

cat /var/run/pgbouncer/pgbouncer.pid | xargs kill -9

PgBouncer Management

PgBouncer provides a virtual database pgbouncer, which provides a database operation interface like PostgreSQL. It is not a real database, but a command line interface virtualized by PgBouncer. To log in to this virtual database, run the following command:

psql -p 6432 -d pgbouncer

If some configuration parameters are modified, you do not need to restart PgBouncer but run reload for the modifications to be applied.

pgbouncer=# reload;
RELOAD

After login, you can run show help to check the command help, run show clients to check the client connection information, and run show pools to check the connection pool information.

An Example of Read/Write Splitting

PgBouncer cannot automatically parse or split read and write requests. Read and write requests need to be distinguished on the application side.

1. Modify the database information in the pgbouncer.ini file and add the connection configurations of the primary instance and read replica to the file. In this example, the parameters are set as follows:

   [databases]
   ;; * = host=127.0.0.1 port=5432
   # The connection information of the read replica.
   mydb_read: host=10.7.131.69 port=5432 dbname=postgres user=root password=***
   # The connection information of the primary instance.
   mydb_write:  host=10.8.115.171 port=5432 dbname=postgres user=root password=***
   [pgbouncer]
   logfile = /var/log/pgbouncer/pgbouncer.log
   pidfile = /var/run/pgbouncer/pgbouncer.pid
   listen_addr = *
   listen_port = 6432
   auth_type = md5
   auth_file = /etc/pgbouncer/userlist.txt
   admin_users = postgres
   stats_users = stats, postgres                            
   pool_mode = transaction                     
   server_reset_query = DISCARD ALL 
   max_client_conn = 100
   default_pool_size = 20                    
   ;; resolve: unsupported startup parameter: extra_float_digits
   ;;ignore_startup_parameters = extra_float_digits

2. Check whether the primary instance and read replica can be connected. The primary instance and read replica have been connected using psql and read/write splitting is supported.

   psql -U root -d mydb_write -h 127.0.0.1 -p 6432
   Password for user root:
   psql  (14.6)
   mydb_write=>  SELECT pg_is_in_recovery();
     pg_is_in_recovery
   ----------------------
     f
   (1 row)
   psql -U root -d mydb_read -h 127.0.0.1 -p 6432
   Password for user root:
   psql  (14.6)
   mydb_read=>  SELECT pg_is_in_recovery();
   pg_is_in_recovery
   ----------------------
     t
   (1 row)