Efficient PostgreSQL Management: A Complete Guide to Installing and Configuring PgBouncer for Connection Pooling

Efficient PostgreSQL Management: A Complete Guide to Installing and Configuring PgBouncer for Connection Pooling


2 min read

PgBouncer is a popular lightweight connection pooler for PostgreSQL, effectively managing database connections for improved performance. This guide provides detailed steps for installing and configuring PgBouncer for optimal PostgreSQL thread handling.

Step 1: Installing PgBouncer

For Linux-based Systems (e.g., Ubuntu, CentOS):

  1. Update Package List (Ubuntu):

     sudo apt-get update

    For CentOS/RHEL, use sudo yum update.

  2. Install PgBouncer:

    • Ubuntu:

        sudo apt-get install pgbouncer
    • CentOS/RHEL:

        sudo yum install pgbouncer

For Windows:

  • Download the latest binary from the PgBouncer official website and follow the installation instructions.

Step 2: Configuring PgBouncer

Basic Configuration

  1. Edit PgBouncer Configuration File:

    • The default configuration file is usually located at /etc/pgbouncer/pgbouncer.ini.

    • Use a text editor to open the file, e.g., sudo nano /etc/pgbouncer/pgbouncer.ini.

  2. Configure Connection Settings:

    • Set the listening address and port:

        your_database_name = 
        listen_addr =
        listen_port = 6432
    • your_database_name should be replaced with your actual database name.

  3. Configure Authentication:

    • Specify the user authentication file (usually userlist.txt):

        auth_type = md5
        auth_file = /etc/pgbouncer/userlist.txt
    • In userlist.txt, add users and passwords in the format "username" "password".

  4. Pool Mode:

    • Choose a pool mode (session, transaction, or statement pooling):

        pool_mode = session

Advanced Settings (Optional)

  • Max Client Connections:

      max_client_conn = 100
  • Default Pool Size:

      default_pool_size = 20

Step 3: Managing User Authentication

  1. Edit the Userlist File:

    • Located at /etc/pgbouncer/userlist.txt.

    • Format: "username" "md5encryptedpassword".

  2. Encrypt Passwords:

    • Use a tool like pg_md5 to encrypt passwords.

Step 4: Starting PgBouncer

  1. Start the PgBouncer Service:

    • Ubuntu/CentOS:

        sudo systemctl start pgbouncer
    • Ensure the service is enabled to start on boot:

        sudo systemctl enable pgbouncer
  2. Verify PgBouncer is Running:

    • Check the status:

        sudo systemctl status pgbouncer

Step 5: Connecting to PostgreSQL via PgBouncer

  • Connect using a PostgreSQL client, specifying PgBouncer's port:

      psql -h -p 6432 -U username dbname


By following these steps, you will have successfully installed and configured PgBouncer for PostgreSQL. This setup will help manage database connections more efficiently, leading to improved performance, especially in environments with high concurrent connections. Regular monitoring and fine-tuning of PgBouncer's settings based on your specific workload can further enhance its effectiveness.