How to configure connection pooling with PgBouncer

Configure PgBouncer to act as a lightweight connection pooler for your PostgreSQL database. These steps target PostgreSQL 16 and PgBouncer 1.23 on Ubuntu 24.04. You will reduce database load and handle concurrent client requests efficiently.

Prerequisites

• Ubuntu 24.04 or Debian 12 server with root or sudo access.
• PostgreSQL 16 installed and running on the local server.
• Network access to the PostgreSQL port (default 5432).
• A valid PostgreSQL database user with login privileges.

Step 1: Install PgBouncer

Download the PgBouncer binary and install it to the system. Use the official release archive to ensure you get the latest stable version compatible with your PostgreSQL release.

wget https://github.com/pgbouncer/pgbouncer/releases/download/1.23.0/pgbouncer-1.23.0-linux-x86_64.tar.gz
tar xzf pgbouncer-1.23.0-linux-x86_64.tar.gz
mv pgbouncer-1.23.0 /usr/local/bin/pgbouncer
rm pgbouncer-1.23.0-linux-x86_64.tar.gz

Verify the binary is accessible by running the version check command below. You should see the version number and build date.

pgbouncer -v

Expected output:

pgbouncer 1.23.0 (Linux x86_64)

Step 2: Configure the Pooling Mode

Create the main configuration file for PgBouncer. This file defines the connection string to the PostgreSQL server, the pool size, and the pooling mode. Use transaction pooling for most web applications to balance performance and resource usage.

cat > /etc/pgbouncer/pgbouncer.ini  /etc/pgbouncer/userlist.txt

Ensure the PgBouncer process has read permissions for this file.

chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt
chmod 640 /etc/pgbouncer/userlist.txt

Step 4: Create the Unix Socket Directory

Create the directory where PgBouncer will store its Unix socket files. This allows local applications to connect without using TCP/IP. Set the correct ownership so the PgBouncer user can write to it.

mkdir -p /var/run/pgbouncer
chown pgbouncer:pgbouncer /var/run/pgbouncer
chmod 755 /var/run/pgbouncer

Step 5: Start the PgBouncer Service

Create a systemd unit file to manage the PgBouncer process. This ensures the service starts automatically on boot and can be managed via systemctl commands.

cat > /etc/systemd/system/pgbouncer.service