PgBouncer Setup Guide for VPS

A Complete Guide to Installing and Configuring PgBouncer on a VPS

---

📋 Table of Contents

1. What is PgBouncer?
2. Prerequisites
3. Installation
4. PostgreSQL Configuration
5. PgBouncer Configuration
6. Authentication Setup
7. Starting PgBouncer
8. Application Changes
9. Monitoring & Management
10. Troubleshooting
11. Performance Expectations

---

What is PgBouncer?

PgBouncer is a lightweight connection pooler for PostgreSQL. It maintains a small pool of persistent database connections and reuses them across thousands of client connections.

Why Use PgBouncer?

Without PgBouncer	With PgBouncer
1000 clients = 1000 PostgreSQL connections	1000 clients = 20-200 PostgreSQL connections
High memory usage (5-10MB per connection)	Low memory usage (< 100MB total)
CPU intensive context switching	Minimal CPU overhead
Risk of connection exhaustion	Safe and stable

---

Prerequisites

• A VPS with Linux (Ubuntu/Debian recommended)
• PostgreSQL installed and running
• Root or sudo access
• Basic knowledge of command line

Recommended VPS Specs:

• 2+ CPU cores
• 4+ GB RAM
• 10+ GB storage

---

Installation

Step 1: Update System

sudo apt update
sudo apt upgrade -y

Step 2: Install PgBouncer

# Ubuntu/Debian
sudo apt install pgbouncer -y

# Verify installation
pgbouncer --version

---

PostgreSQL Configuration

Step 1: Backup Current Configuration

# Create backup directory
mkdir -p ~/backups/postgresql

# Backup postgresql.conf
sudo cp /etc/postgresql/*/main/postgresql.conf \
   ~/backups/postgresql/postgresql.conf-$(date +%F_%H-%M-%S)

Step 2: Edit PostgreSQL Configuration

sudo nano /etc/postgresql/*/main/postgresql.conf

Update these values (adjust based on your RAM):

# Connection Settings
max_connections = 300              # Total connections PostgreSQL can handle
superuser_reserved_connections = 3 # Reserved for admin

# Memory Settings (adjust for your RAM)
shared_buffers = 25% of RAM        # Example: 2GB for 8GB RAM
effective_cache_size = 70% of RAM  # Example: 5.6GB for 8GB RAM
work_mem = 32MB                    # Per operation memory
maintenance_work_mem = 1GB         # For VACUUM, CREATE INDEX

# Listen Settings
listen_addresses = 'localhost'     # Only accept local connections

Step 3: Restart PostgreSQL

sudo systemctl restart postgresql
sudo systemctl status postgresql

---

PgBouncer Configuration

Step 1: Backup Default Configuration

mkdir -p ~/backups/pgbouncer
sudo cp /etc/pgbouncer/pgbouncer.ini \
   ~/backups/pgbouncer/pgbouncer.ini-$(date +%F_%H-%M-%S)

Step 2: Edit PgBouncer Configuration

sudo nano /etc/pgbouncer/pgbouncer.ini

Complete Configuration:

[databases]
# Format: dbname = host=localhost port=5432 dbname=actual_dbname
# Add your databases here
mydatabase = host=localhost port=5432 dbname=mydatabase
app_db = host=localhost port=5432 dbname=app_db

# Optional: Include dynamic databases from separate file
# %include /etc/pgbouncer/dynamic-databases.ini

[pgbouncer]
# Network Settings
listen_addr = 0.0.0.0              # Listen on all interfaces
listen_port = 6432                  # PgBouncer port (default)

# Connection Pool Settings
pool_mode = transaction            # Best for web apps (session/statement also available)
default_pool_size = 200            # Max connections to PostgreSQL per database
min_pool_size = 20                 # Minimum connections to keep warm
reserve_pool_size = 10             # Extra connections when under load
reserve_pool_timeout = 3           # Seconds before using reserve pool
max_client_conn = 5000             # Maximum incoming client connections

# Performance Tuning
max_db_connections = 300           # Should match PostgreSQL max_connections
pool_heartbeat = 30                # Check connection health every 30 seconds
server_lifetime = 3600             # Recycle connections after 1 hour
server_idle_timeout = 600          # Close idle connections after 10 minutes
server_connect_timeout = 15        # Connection timeout in seconds
server_login_retry = 15            # Retry delay after failed login

# Logging
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
syslog = 1

# Authentication
auth_type = md5                    # Authentication method (md5/scram-sha-256/trust)
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres             # Users who can run SHOW commands
stats_users = postgres             # Users who can view statistics

Step 3: Configuration Explanation

Setting	Value	Purpose
pool_mode	transaction	Best for web apps, releases connection after each transaction
default_pool_size	200	Max concurrent PostgreSQL connections per database
max_client_conn	5000	Max apps that can connect simultaneously
server_lifetime	3600	Prevents memory leaks by recycling old connections

---

Authentication Setup

Step 1: Generate Password Hash

# For MD5 authentication
sudo -u postgres psql -c "SELECT 'md5' || md5('your_password' || 'your_username');"

# Example for user 'postgres' with password 'mypassword'
sudo -u postgres psql -c "SELECT 'md5' || md5('mypassword' || 'postgres');"
# Output: md5d8578edf8458ce06fbc5bb76a58c5ca4

Step 2: Create User List File

sudo nano /etc/pgbouncer/userlist.txt

Format:

"username" "md5hash"

Example:

"postgres" "md5d8578edf8458ce06fbc5bb76a58c5ca4"
"app_user" "md5abc123def456789..."

Step 3: Set Proper Permissions

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

---

Starting PgBouncer

Step 1: Start the Service

# Start PgBouncer
sudo systemctl start pgbouncer

# Enable auto-start on boot
sudo systemctl enable pgbouncer

# Check status
sudo systemctl status pgbouncer

Step 2: Verify It's Running

# Check if port 6432 is listening
sudo netstat -tlnp | grep 6432

# Test connection
psql -h localhost -p 6432 -U postgres -d mydatabase -c "SELECT 'PgBouncer is working!' as test;"

Step 3: Reload Configuration (when changes are made)

# Graceful reload - no downtime!
sudo systemctl reload pgbouncer

# Or restart (drops all connections)
sudo systemctl restart pgbouncer

---

Application Changes

The Only Change Required

Before (Direct PostgreSQL):

DATABASE_URL="postgresql://username:password@localhost:5432/database_name"

After (Via PgBouncer):

DATABASE_URL="postgresql://username:password@localhost:6432/database_name"

Only the port changes from 5432 to 6432!

Framework-Specific Notes

Node.js / Prisma

Before:

import { PrismaClient } from "@prisma/client";
export const prisma = new PrismaClient();

After (Remove any adapters):

import { PrismaClient } from "@prisma/client";

declare global {
  var prisma: PrismaClient | undefined;
}

export const prisma = global.prisma || new PrismaClient();

if (process.env.NODE_ENV !== "production") {
  global.prisma = prisma;
}

export default prisma;

Django

# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'mydatabase',
        'USER': 'postgres',
        'PASSWORD': 'mypassword',
        'HOST': 'localhost',
        'PORT': '6432',  # Changed from 5432
    }
}

Ruby on Rails

# config/database.yml
default: &default
  adapter: postgresql
  encoding: unicode
  host: localhost
  port: 6432  # Changed from 5432
  username: postgres
  password: mypassword
  pool: 5

---

Monitoring & Management

Useful PgBouncer Commands

# Connect to PgBouncer admin console
psql -h localhost -p 6432 -U postgres -d pgbouncer

# Inside the console, run:
SHOW POOLS;      # Show connection pools
SHOW CLIENTS;    # Show active clients
SHOW STATS;      # Show statistics
SHOW DATABASES;  # Show configured databases
SHOW CONFIG;     # Show current configuration
SHOW VERSION;    # Show PgBouncer version

Real-Time Monitoring Script

#!/bin/bash
# monitor-pgbouncer.sh

while true; do
  clear
  echo "=== PgBouncer Status - $(date) ==="
  echo ""
  echo "--- Connection Pools ---"
  psql -h localhost -p 6432 -U postgres -d pgbouncer -c "SHOW POOLS;" 2>/dev/null
  echo ""
  echo "--- Active Clients ---"
  psql -h localhost -p 6432 -U postgres -d pgbouncer -c "SHOW CLIENTS;" 2>/dev/null
  echo ""
  echo "Press Ctrl+C to exit"
  sleep 2
done

Create Monitoring Aliases

# Add to ~/.bashrc
alias pgb-stats='psql -h localhost -p 6432 -U postgres -d pgbouncer -c "SHOW STATS;"'
alias pgb-pools='psql -h localhost -p 6432 -U postgres -d pgbouncer -c "SHOW POOLS;"'
alias pgb-clients='psql -h localhost -p 6432 -U postgres -d pgbouncer -c "SHOW CLIENTS;"'
alias pgb-dbs='psql -h localhost -p 6432 -U postgres -d pgbouncer -c "SHOW DATABASES;"'

# Reload bashrc
source ~/.bashrc

---

Troubleshooting

Common Issues and Solutions

Issue 1: Authentication Failed

Error: FATAL: password authentication failed

Solutions:

# 1. Check auth_type in pgbouncer.ini
sudo grep auth_type /etc/pgbouncer/pgbouncer.ini

# 2. Regenerate correct MD5 hash
sudo -u postgres psql -c "SELECT 'md5' || md5('your_password' || 'username');"

# 3. Update userlist.txt with correct hash
sudo nano /etc/pgbouncer/userlist.txt

# 4. Reload PgBouncer
sudo systemctl reload pgbouncer

Issue 2: Connection Refused

Error: could not connect to server: Connection refused

Solutions:

# 1. Check if PgBouncer is running
sudo systemctl status pgbouncer

# 2. Check if port 6432 is listening
sudo netstat -tlnp | grep 6432

# 3. Start PgBouncer
sudo systemctl start pgbouncer

# 4. Check firewall
sudo ufw allow 6432/tcp

Issue 3: Address Already in Use

Error: Address already in use

Solutions:

# 1. Find what's using port 6432
sudo lsof -i :6432

# 2. Kill the process or stop the service
sudo systemctl stop pgbouncer

# 3. Start fresh
sudo systemctl start pgbouncer

Issue 4: SASL Authentication Failed

Error: SASL authentication failed

Solution:

# Change auth_type to md5 in pgbouncer.ini
sudo sed -i 's/auth_type = .*/auth_type = md5/' /etc/pgbouncer/pgbouncer.ini

# Or use scram-sha-256 if PostgreSQL uses it
sudo sed -i 's/auth_type = .*/auth_type = scram-sha-256/' /etc/pgbouncer/pgbouncer.ini

# Reload
sudo systemctl reload pgbouncer

Logs and Debugging

# View PgBouncer logs
sudo journalctl -u pgbouncer -f

# View last 50 lines
sudo journalctl -u pgbouncer -n 50

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

---

Performance Expectations

Based on VPS Specs

VPS RAM	Recommended max_connections	Expected TPS (simple queries)
4 GB	100-150	20,000-30,000
8 GB	150-200	30,000-50,000
16 GB	200-300	50,000-80,000
32 GB	300-400	80,000-100,000
64 GB+	400-500	100,000+

Benchmark Commands

# Install Apache Bench
sudo apt install apache2-utils

# Test PgBouncer performance
pgbench -h localhost -p 6432 -U postgres -i -s 10 your_database
pgbench -h localhost -p 6432 -U postgres -c 50 -j 4 -T 30 your_database
pgbench -h localhost -p 6432 -U postgres -c 50 -j 4 -T 30 -S your_database

---

Quick Reference Card

Essential Commands

# Service Management
sudo systemctl start pgbouncer
sudo systemctl stop pgbouncer
sudo systemctl restart pgbouncer
sudo systemctl reload pgbouncer
sudo systemctl status pgbouncer

# Configuration
sudo nano /etc/pgbouncer/pgbouncer.ini
sudo nano /etc/pgbouncer/userlist.txt

# Testing
psql -h localhost -p 6432 -U postgres -d your_db -c "SELECT 1;"

# Monitoring
psql -h localhost -p 6432 -U postgres -d pgbouncer -c "SHOW POOLS;"

# Logs
sudo journalctl -u pgbouncer -f

File Locations

File	Path
Main config	/etc/pgbouncer/pgbouncer.ini
User list	/etc/pgbouncer/userlist.txt
Logs	journalctl -u pgbouncer
Backups	~/backups/pgbouncer/

---

Checklist for New Projects

• PgBouncer installed (sudo apt install pgbouncer)
• PostgreSQL configured (max_connections = 300)
• pgbouncer.ini configured with databases
• userlist.txt created with correct password hashes
• PgBouncer service running (sudo systemctl start pgbouncer)
• Firewall allows port 6432 (sudo ufw allow 6432)
• Application .env uses port 6432 instead of 5432
• Test connection: psql -h localhost -p 6432 -U user -d db
• Monitor: psql -p 6432 -d pgbouncer -c "SHOW POOLS;"

---

Conclusion

PgBouncer is a powerful tool that dramatically improves PostgreSQL scalability with minimal configuration changes. The key points to remember:

1. Only change the port in your application from 5432 to 6432
2. Configure PgBouncer once on your VPS
3. Monitor regularly using the admin commands
4. Use transaction pooling for web applications

With this setup, your VPS can handle thousands of concurrent connections while PostgreSQL only manages a few hundred.

---

Support & Resources

• Official PgBouncer Documentation
• PostgreSQL Connection Pooling
• GitHub Repository for this Guide

---

Version: 1.0
Last Updated: April 2026
Tested On: Ubuntu 24.04, PostgreSQL 16+, PgBouncer 1.22+

---

Happy connection pooling! 🚀