# PgBouncer Connection Pooler Error - Troubleshooting Guide

PgBouncer is PostgreSQL's most popular connection pooler, but misconfigurations and connection issues can cause frustrating errors. Understanding PgBouncer's architecture and error messages helps you resolve these problems quickly.

Understanding PgBouncer Errors

PgBouncer errors typically fall into these categories:

bash
# Common PgBouncer error messages:
# - "no more connections allowed"
# - "pooler error: sorry, too many clients already"
# - "authentication failed"
# - "connect failed: connection refused"
# - "server login failed: FATAL: password authentication failed"
# - "client login failed: pool 'database' does not exist"

Check PgBouncer Status

```bash # Check PgBouncer service status sudo systemctl status pgbouncer

# View PgBouncer logs sudo tail -f /var/log/pgbouncer/pgbouncer.log

# Connect to PgBouncer admin console psql -p 6432 -U pgbouncer pgbouncer

# Inside admin console, check stats pgbouncer=# SHOW POOLS; pgbouncer=# SHOW CLIENTS; pgbouncer=# SHOW SERVERS; pgbouncer=# SHOW DATABASES; ```

Authentication Failures

The most common PgBouncer error is authentication failure.

Error: "authentication failed for user"

```bash # Check authentication method cat /etc/pgbouncer/pgbouncer.ini | grep auth_type # Common values: md5, scram-sha-256, trust

# Check userlist file cat /etc/pgbouncer/userlist.txt ```

PgBouncer needs passwords in its userlist file:

```bash # Generate password hash for userlist.txt # For md5 authentication: echo "md5$(echo -n 'passwordusername' | md5sum | cut -d' ' -f1)" # Output: md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

# For scram-sha-256, use PostgreSQL to generate: psql -U postgres -c "\password username" # Then copy from pg_shadow psql -U postgres -c "SELECT rolname, rolpassword FROM pg_authid WHERE rolname = 'username';" ```

Fix userlist.txt

```bash # Edit userlist.txt sudo nano /etc/pgbouncer/userlist.txt

# Format: "username" "md5hashedpassword" "app_user" "md55a2b3c4d5e6f7g8h9i0j" "admin" "SCRAM-SHA-256$..."

# After editing, reload PgBouncer sudo systemctl reload pgbouncer

# Or via admin console psql -p 6432 -U pgbouncer pgbouncer -c "RELOAD;" ```

SCRAM-SHA-256 Authentication

PgBouncer 1.14+ supports SCRAM, but requires special configuration:

```bash # In postgresql.conf on PostgreSQL server password_encryption = 'scram-sha-256'

# In pgbouncer.ini auth_type = scram-sha-256

# Set password in userlist.txt using PostgreSQL's stored hash psql -U postgres -c "SELECT rolname, rolpassword FROM pg_authid WHERE rolname = 'app_user';" # Copy the SCRAM-SHA-256$... hash to userlist.txt ```

Trusted Authentication

For local development, trust authentication can simplify setup:

```ini # In pgbouncer.ini [databases] mydb = host=/var/run/postgresql port=5432 dbname=mydb

[pgbouncer] auth_type = trust # No password required in userlist.txt ```

Pool Exhaustion Errors

Error: "no more connections allowed (pool size)"

```bash # Check current pool usage psql -p 6432 -U pgbouncer pgbouncer -c "SHOW POOLS;"

# Output shows: # database | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait ```

Interpreting Pool Stats

sql
-- Key metrics from SHOW POOLS:
-- cl_active: Clients currently executing queries
-- cl_waiting: Clients waiting for server connection
-- sv_active: Server connections executing queries
-- sv_idle: Server connections waiting for work
-- maxwait: Longest time a client has been waiting (seconds)

Increase Pool Size

bash
# Edit pgbouncer.ini
sudo nano /etc/pgbouncer/pgbouncer.ini
ini
[pgbouncer]
# Connection pool settings
default_pool_size = 25          # Increase if clients waiting
min_pool_size = 5               # Minimum connections to maintain
reserve_pool_size = 5           # Extra connections for spikes
reserve_pool_timeout = 3        # Seconds before using reserve
max_client_conn = 1000          # Max incoming client connections
max_db_connections = 100        # Max connections to PostgreSQL

After changes:

```bash # Reload configuration sudo systemctl reload pgbouncer

# Or restart if pool settings changed sudo systemctl restart pgbouncer ```

Database Connection Errors

Error: "pool 'database' does not exist"

PgBouncer needs database aliases configured:

```ini # In pgbouncer.ini [databases] # Format: alias = host=host port=port dbname=real_dbname

# Simple alias mydb = host=127.0.0.1 port=5432 dbname=production_db

# Using Unix socket mydb = host=/var/run/postgresql port=5432 dbname=production_db

# With different user reporting = host=db-server port=5432 dbname=analytics user=reporting_user password=secret

# Multiple databases app_db = host=primary-db port=5432 dbname=app analytics = host=analytics-db port=5432 dbname=analytics ```

Error: "connect failed: connection refused"

PostgreSQL isn't accepting connections:

```bash # Check PostgreSQL is running sudo systemctl status postgresql

# Check PostgreSQL is accepting connections psql -h localhost -U postgres -c "SELECT 1;"

# Check PostgreSQL listen addresses psql -U postgres -c "SHOW listen_addresses;" # Should not be 'localhost' if PgBouncer connects via network

# Check pg_hba.conf allows PgBouncer connections cat /etc/postgresql/16/main/pg_hba.conf | grep -v "^#" | grep -v "^$"

# Add PgBouncer IP if needed: # host all all 127.0.0.1/32 scram-sha-256 ```

Timeout Errors

Error: "query wait timeout"

Client waited too long for a connection:

```ini # In pgbouncer.ini [pgbouncer] query_timeout = 30 # Seconds before canceling query query_wait_timeout = 10 # Seconds client waits for connection

# Increase if legitimate long waits occur ```

Error: "server login failed"

PgBouncer couldn't authenticate with PostgreSQL:

```bash # Test direct connection to PostgreSQL psql -h localhost -U app_user -d mydb

# If this fails, fix PostgreSQL authentication first # Check pg_hba.conf and PostgreSQL user permissions ```

Client Connection Issues

Error: "client login failed"

```bash # Check PgBouncer logs for details sudo tail -f /var/log/pgbouncer/pgbouncer.log

# Common causes: # 1. Database not in [databases] section # 2. User not in userlist.txt # 3. Wrong password in userlist.txt # 4. auth_type mismatch ```

Client-Side Connection Strings

python
# Python - correct connection string
import psycopg2
conn = psycopg2.connect(
    host='localhost',
    port=6432,           # PgBouncer port, not 5432
    database='mydb',      # Alias from [databases] section
    user='app_user',
    password='secret'
)
javascript
// Node.js - correct connection string
const { Client } = require('pg');
const client = new Client({
    host: 'localhost',
    port: 6432,
    database: 'mydb',
    user: 'app_user',
    password: 'secret'
});

Pool Mode Issues

Understanding Pool Modes

ini
# In pgbouncer.ini
[pgbouncer]
pool_mode = transaction    # Options: session, transaction, statement

Session mode: - Client keeps server connection until disconnect - Most compatible but least efficient - Required for prepared statements, SET commands, advisory locks

Transaction mode (recommended): - Server connection released after each transaction - Most efficient for typical web apps - Cannot use prepared statements across transactions - SET commands only affect current transaction

Statement mode: - Server connection released after each statement - Most efficient but least compatible - No multi-statement transactions

Error: "prepared statement already exists"

Transaction pooling doesn't support server-side prepared statements across transactions:

```python # Problem code with transaction pooling cursor.execute("PREPARE get_user AS SELECT * FROM users WHERE id = $1") cursor.execute("EXECUTE get_user(1)")

# Solution 1: Use client-side prepared statements cursor.execute("SELECT * FROM users WHERE id = %s", (1,))

# Solution 2: Use session pooling (less efficient) # In pgbouncer.ini for this database: mydb = host=localhost port=5432 dbname=mydb pool_mode=session ```

Server Connection Problems

Error: "server closed connection unexpectedly"

```bash # Check PostgreSQL logs sudo tail -f /var/log/postgresql/postgresql-*-main.log

# Common causes: # 1. PostgreSQL restarted # 2. Statement timeout # 3. Idle session timeout # 4. Server-side disconnection ```

Configure Server Health Checks

ini
# In pgbouncer.ini
[pgbouncer]
server_check_delay = 30      # Seconds between health checks
server_check_query = SELECT 1 # Query to test connection
server_connect_timeout = 15  # Seconds to wait for connection
server_idle_timeout = 600    # Close idle server connections after 10 min
server_lifetime = 3600       # Close connections after 1 hour

Monitoring PgBouncer

```sql -- Connect to admin console psql -p 6432 -U pgbouncer pgbouncer

-- Show all clients waiting SHOW CLIENTS;

-- Show all server connections SHOW SERVERS;

-- Show pool statistics SHOW POOLS;

-- Show database configuration SHOW DATABASES;

-- Show memory usage SHOW MEM;

-- Show DNS hostnames SHOW DNS_HOSTS;

-- Show version SHOW VERSION; ```

Useful Admin Commands

```sql -- Pause all new client connections PAUSE;

-- Resume client connections RESUME;

-- Disable new connections to specific database DISABLE mydb;

-- Re-enable database ENABLE mydb;

-- Kill all client connections to a database KILL mydb;

-- Reload configuration RELOAD;

-- Shut down PgBouncer SHUTDOWN; ```

PgBouncer Configuration Best Practices

```ini # Complete example pgbouncer.ini [databases] ; Production database mydb = host=127.0.0.1 port=5432 dbname=production

; Read replica for analytics analytics = host=replica.internal port=5432 dbname=production

[pgbouncer] ; Network settings listen_addr = 127.0.0.1 listen_port = 6432 unix_socket_dir = /var/run/postgresql

; Authentication auth_type = scram-sha-256 auth_file = /etc/pgbouncer/userlist.txt

; Pool configuration pool_mode = transaction default_pool_size = 20 min_pool_size = 5 reserve_pool_size = 5 reserve_pool_timeout = 3 max_client_conn = 500 max_db_connections = 100

; Timeouts server_connect_timeout = 15 server_idle_timeout = 600 server_lifetime = 3600 query_timeout = 30 query_wait_timeout = 10 client_idle_timeout = 0 client_login_timeout = 60

; Health checks server_check_delay = 30 server_check_query = SELECT 1

; Logging log_connections = 0 log_disconnections = 0 log_pooler_errors = 1 log_stats = 1 stats_period = 60 verbose = 0

; Admin access admin_users = postgres,admin stats_users = stats,monitor ```

Troubleshooting Checklist

  1. 1.Authentication: Verify userlist.txt passwords match PostgreSQL
  2. 2.Database config: Ensure databases are defined in [databases] section
  3. 3.Network: Check PostgreSQL allows connections from PgBouncer host
  4. 4.Pool size: Increase if cl_waiting is consistently high
  5. 5.Timeouts: Adjust query_wait_timeout if legitimate queries timeout
  6. 6.Pool mode: Use transaction mode for most apps, session mode if needed
  7. 7.Logs: Check both PgBouncer and PostgreSQL logs for details

PgBouncer errors usually stem from authentication mismatches or pool exhaustion. Keep passwords synchronized, size pools appropriately, and monitor pool statistics to prevent issues.