Install PgBouncer Connection Pooler for PostgreSQL on VPS

August 2026 · 9 min read

PostgreSQL spawns one process per client connection, consuming roughly 5–10 MB of RAM each. When your application opens 200–500 simultaneous connections, memory runs out and performance degrades rapidly. PgBouncer is a lightweight connection pooler that sits between your application and PostgreSQL. It accepts a large number of client connections and multiplexes them onto a much smaller set of real PostgreSQL connections, letting you handle high traffic without upgrading your server.

How PgBouncer Works

PgBouncer offers three pooling modes — choose based on your application's needs:

Prerequisites

• Ubuntu 22.04 LTS or 24.04 LTS VPS
• PostgreSQL already installed and running (see Install PostgreSQL on VPS)
• sudo or root access
• At least 512 MB RAM (PgBouncer itself uses under 5 MB)

Step 1 — Install PgBouncer

PgBouncer is available in Ubuntu's standard repository:

sudo apt update
sudo apt install -y pgbouncer
pgbouncer --version

Check the service status:

sudo systemctl status pgbouncer

Step 2 — Configure pgbouncer.ini

The main configuration file is at /etc/pgbouncer/pgbouncer.ini. Edit it to match your environment:

sudo nano /etc/pgbouncer/pgbouncer.ini

Example production configuration using transaction pooling:

[databases]
; Format: alias = host=... port=... dbname=... user=...
myapp = host=127.0.0.1 port=5432 dbname=myapp_db

[pgbouncer]
; Listen address and port
listen_addr = 127.0.0.1
listen_port = 6432

; Auth method — md5 or scram-sha-256
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Pooling mode: session | transaction | statement
pool_mode = transaction

; Connection limits
max_client_conn = 200
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

; Timeouts (seconds)
server_idle_timeout = 600
client_idle_timeout = 0
query_timeout = 0
query_wait_timeout = 120

; Logging
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
stats_period = 60

; Admin
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

Step 3 — Create userlist.txt

PgBouncer needs user passwords stored as MD5 hashes:

# Generate MD5 hash for user "myapp_user" with password "mypassword"
# The hash input is: password + username concatenated
echo -n "mypasswordmyapp_user" | md5sum
# Output example: a1b2c3d4e5f6...  -

Add users to /etc/pgbouncer/userlist.txt:

sudo nano /etc/pgbouncer/userlist.txt

File format:

"myapp_user" "md5a1b2c3d4e5f6789abcdef1234567890ab"
"pgbouncer_admin" "md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

Step 4 — Use auth_query Instead (More Secure)

Rather than storing password hashes in userlist.txt, you can have PgBouncer query PostgreSQL directly for authentication via auth_query:

Create the auth function in PostgreSQL:

sudo -u postgres psql

-- Create a dedicated schema for pgbouncer
CREATE SCHEMA pgbouncer;

-- Create a security-definer function that can read pg_shadow
CREATE OR REPLACE FUNCTION pgbouncer.get_auth(p_usename TEXT)
RETURNS TABLE(username TEXT, password TEXT) AS $$
BEGIN
  RETURN QUERY
  SELECT usename::TEXT, passwd::TEXT
  FROM pg_shadow
  WHERE usename = p_usename;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create a dedicated user for pgbouncer auth lookups
CREATE USER pgbouncer_auth WITH PASSWORD 'strong_auth_password';
GRANT CONNECT ON DATABASE myapp_db TO pgbouncer_auth;
REVOKE ALL ON SCHEMA public FROM pgbouncer_auth;
GRANT USAGE ON SCHEMA pgbouncer TO pgbouncer_auth;
GRANT EXECUTE ON FUNCTION pgbouncer.get_auth(TEXT) TO pgbouncer_auth;

Add these lines to pgbouncer.ini:

auth_user = pgbouncer_auth
auth_query = SELECT username, password FROM pgbouncer.get_auth($1)

Step 5 — Create Directories and Start Service

sudo mkdir -p /var/log/pgbouncer /var/run/pgbouncer
sudo chown pgbouncer:pgbouncer /var/log/pgbouncer /var/run/pgbouncer
sudo chmod 750 /var/log/pgbouncer

sudo systemctl restart pgbouncer
sudo systemctl enable pgbouncer
sudo systemctl status pgbouncer

Step 6 — Test the Connection Through PgBouncer

Point your application to port 6432 instead of 5432:

# Test connecting through PgBouncer (port 6432)
psql -h 127.0.0.1 -p 6432 -U myapp_user -d myapp

# Inspect the pool via the admin console
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer
pgbouncer=# SHOW POOLS;
pgbouncer=# SHOW CLIENTS;
pgbouncer=# SHOW SERVERS;
pgbouncer=# SHOW STATS;

Step 7 — Tune pool_size for Your Workload

The right pool size depends on your server resources and traffic pattern:

Step 8 — Reload Config Without Restarting

PgBouncer supports live config reloads without dropping existing connections:

# Via the admin console
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer
pgbouncer=# RELOAD;

# Via systemctl
sudo systemctl reload pgbouncer

# Via SIGHUP signal
sudo kill -SIGHUP $(cat /var/run/pgbouncer/pgbouncer.pid)

Verify PgBouncer Is Working Correctly

# 1. Check service status
sudo systemctl status pgbouncer

# 2. Verify the listen port
ss -tlnp | grep 6432

# 3. View pool statistics
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer -c "SHOW POOLS;"

# 4. View active client connections
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer -c "SHOW CLIENTS;"

# 5. View cumulative stats (queries/second, avg response time)
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer -c "SHOW STATS_TOTALS;"

Troubleshooting Common Issues

Error: "no more connections allowed (max_client_conn)"

The number of client connections has hit the max_client_conn limit and PgBouncer is rejecting new connections.

# Increase max_client_conn in pgbouncer.ini, then reload
max_client_conn = 500

sudo systemctl reload pgbouncer

# Check current clients
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer -c "SHOW CLIENTS;"

Error: "ERROR: prepared statement already exists"

This happens when using pool_mode = transaction with applications that use prepared statements (some Django ORM versions, some drivers in named-statement mode).

# Option 1: Switch to session mode (less pooling efficiency)
pool_mode = session

# Option 2 (recommended): Add server_reset_query to clear state between clients
server_reset_query = DISCARD ALL

# For Django: disable persistent connections when using pgbouncer
DATABASES = {
    'default': {
        'CONN_MAX_AGE': 0,
    }
}

Error: "could not connect to server: Connection refused" on port 6432

PgBouncer is not running or has a configuration syntax error.

# Check if the process is running
ps aux | grep pgbouncer

# Read the error log
sudo tail -50 /var/log/pgbouncer/pgbouncer.log

# Validate config syntax
pgbouncer --check-config /etc/pgbouncer/pgbouncer.ini

# Restart and check journal
sudo systemctl restart pgbouncer
sudo journalctl -u pgbouncer -n 50

Error: "Auth failed" or password authentication failed

The hash in userlist.txt is wrong, or the auth_type does not match PostgreSQL's password_encryption setting.

# Check PostgreSQL's password encryption mode
sudo grep -i "password_encryption" /etc/postgresql/*/main/postgresql.conf

# Regenerate the MD5 hash (password + username concatenated, no spaces)
echo -n "mypasswordmyapp_user" | md5sum
# Add to userlist.txt as: "myapp_user" "md5HASH_HERE"

# Reload pgbouncer after editing userlist
sudo systemctl reload pgbouncer

Error: query_wait_timeout — clients waiting too long

The pool is exhausted: all server connections are in use and clients are queued waiting for one to become free.

# Increase reserve pool and default pool size
reserve_pool_size = 10
reserve_pool_timeout = 3
default_pool_size = 30

# Or raise the wait timeout (seconds)
query_wait_timeout = 180

sudo systemctl reload pgbouncer

Frequently Asked Questions

Should I use transaction mode or session mode?

For standard web applications (Django, Rails, Express, FastAPI), use pool_mode = transaction — it gives the most benefit because a server connection is returned to the pool the moment a transaction ends, allowing 20 server connections to serve hundreds of simultaneous HTTP requests. The exception is if your application uses advisory locks, temporary tables, SET session variables, or LISTEN/NOTIFY, in which case you must use session mode.

Is PgBouncer safe enough for production?

Yes. PgBouncer has been running in production at companies like Instagram, GitLab, and Heroku for over 15 years. Set listen_addr = 127.0.0.1 so it only binds to localhost, never exposing port 6432 to the internet. Add TLS if you need encrypted connections between client and PgBouncer.

Do I need to adjust my ORM settings (SQLAlchemy, Prisma)?

Yes — disable the ORM's built-in connection pool to avoid double-pooling. For SQLAlchemy, set pool_size=1, max_overflow=0, pool_pre_ping=True or switch to NullPool. For Prisma, set connection_limit=1 in the database URL. Let PgBouncer handle all pooling logic, since it operates at a lower level with more visibility into actual PostgreSQL connection state.