Install PgBouncer PostgreSQL Connection Pooling

Set up PgBouncer to efficiently manage PostgreSQL connections, reduce database load, and improve application performance with proper authentication and monitoring.

Prerequisites

PostgreSQL server installed and running
Root or sudo access
Basic understanding of PostgreSQL administration

What this solves

PgBouncer is a lightweight connection pooler for PostgreSQL that reduces database overhead by reusing connections across multiple client requests. Instead of each application creating direct database connections, PgBouncer maintains a pool of connections and efficiently distributes them. This prevents connection exhaustion, reduces memory usage, and improves response times for high-traffic applications.

Step-by-step installation

Update system packages

Start by updating your package manager to ensure you get the latest versions of all packages.

sudo apt update && sudo apt upgrade -y

sudo dnf update -y

Install PgBouncer from official repositories

PgBouncer is available in the standard repositories for all major Linux distributions. Install it using your system's package manager.

sudo apt install -y pgbouncer

sudo dnf install -y pgbouncer

Create PgBouncer configuration directory

Set up the proper directory structure and permissions for PgBouncer configuration files. This ensures secure access and proper ownership.

sudo mkdir -p /etc/pgbouncer
sudo chown postgres:postgres /etc/pgbouncer
sudo chmod 750 /etc/pgbouncer

Configure database connections

Create the main PgBouncer configuration file that defines connection pools, database settings, and performance parameters. This configuration sets up session pooling for optimal performance.

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp_production user=pgbouncer_user
myapp_staging = host=127.0.0.1 port=5432 dbname=myapp_staging user=pgbouncer_user

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
admin_users = postgres
stats_users = stats
pool_mode = session
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 5
max_db_connections = 100
max_user_connections = 100
server_reset_query = DISCARD ALL
server_check_query = SELECT 1
server_check_delay = 30
server_connect_timeout = 15
server_login_retry = 15
client_login_timeout = 60
autodb_idle_timeout = 3600
stats_period = 60
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
ignore_startup_parameters = extra_float_digits

Create user authentication file

Set up the authentication file that contains database user credentials in MD5 format. This file must match the users configured in your PostgreSQL database.

"pgbouncer_user" "md5d6a35858d61d85e4a82ab1fb044aba9d"
"stats" ""

Generate the MD5 hash for your database user password:

echo -n "passwordusername" | md5sum
Replace 'password' with actual password and 'username' with actual username

Set proper file permissions

Secure the configuration files by setting correct ownership and restrictive permissions. Only the postgres user should be able to read these files.

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

Create log directory

Set up the logging directory with proper permissions for PgBouncer to write log files and monitor connection activity.

sudo mkdir -p /var/log/pgbouncer
sudo chown postgres:postgres /var/log/pgbouncer
sudo chmod 755 /var/log/pgbouncer

Create PostgreSQL user for PgBouncer

Create a dedicated database user that PgBouncer will use to connect to PostgreSQL. This user needs minimal privileges for security.

sudo -u postgres psql -c "CREATE USER pgbouncer_user WITH PASSWORD 'your_secure_password';"
sudo -u postgres psql -c "GRANT CONNECT ON DATABASE myapp_production TO pgbouncer_user;"
sudo -u postgres psql -c "GRANT USAGE ON SCHEMA public TO pgbouncer_user;"
sudo -u postgres psql -c "GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO pgbouncer_user;"

Configure systemd service

Create a systemd service file to properly manage PgBouncer as a system service with automatic startup and proper resource limits.

[Unit]
Description=PgBouncer PostgreSQL connection pooler
Documentation=man:pgbouncer(1)
After=postgresql.service
Requires=postgresql.service

[Service]
Type=notify
User=postgres
Group=postgres
ExecStart=/usr/bin/pgbouncer /etc/pgbouncer/pgbouncer.ini
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
Restart=on-failure
RestartSec=5
LimitNOFILE=65536
TimeoutSec=300

Security settings
NoNewPrivileges=yes
PrivateTmp=yes
ProtectSystem=strict
ProtectHome=yes
ReadWritePaths=/var/log/pgbouncer /var/run/postgresql

[Install]
WantedBy=multi-user.target

Enable and start PgBouncer service

Reload systemd configuration, enable PgBouncer to start on boot, and start the service.

sudo systemctl daemon-reload
sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer
sudo systemctl status pgbouncer

Configure connection pooling parameters

Optimize pool sizing

Fine-tune connection pool settings based on your application load. These parameters control how PgBouncer manages connections between clients and PostgreSQL.

# Pool mode configuration
pool_mode = transaction  # More aggressive pooling
pool_mode = session     # Default, safer for complex applications
pool_mode = statement   # Most aggressive, limited compatibility

Connection limits
max_client_conn = 2000        # Maximum client connections
default_pool_size = 50        # Connections per database
min_pool_size = 10            # Minimum connections to maintain
reserve_pool_size = 10        # Additional connections for high load
reserve_pool_timeout = 3      # Seconds to wait for reserved connection

Database connection limits
max_db_connections = 200      # Total database connections
max_user_connections = 200    # Connections per user

Configure performance tuning

Adjust timeout and connection management settings to optimize performance for your specific workload and network conditions.

# Connection timeouts
server_connect_timeout = 15   # Seconds to wait for database connection
client_login_timeout = 60     # Client authentication timeout
server_login_retry = 15       # Retry failed database connections
idle_transaction_timeout = 0  # Close idle transactions (0 = disabled)

Connection lifecycle
server_lifetime = 3600        # Max connection age in seconds
server_idle_timeout = 600     # Close idle server connections
server_reset_query = DISCARD ALL  # Reset connection state
server_check_query = SELECT 1     # Health check query
server_check_delay = 30           # Health check interval

Memory and performance
max_packet_size = 2147483647  # Maximum packet size
pkt_buf = 4096               # Packet buffer size
listen_backlog = 128         # TCP listen backlog
tcp_keepalive = 1            # Enable TCP keepalive
tcp_keepcnt = 3              # Keepalive probes
tcp_keepidle = 600           # Keepalive start delay
tcp_keepintvl = 30           # Keepalive probe interval

Configure monitoring and logging

Set up comprehensive logging and statistics collection to monitor PgBouncer performance and troubleshoot connection issues.

# Logging configuration
log_connections = 1          # Log new connections
log_disconnections = 1       # Log connection closures
log_pooler_errors = 1        # Log pooler errors
verbose = 0                  # Verbose logging (0-2)
syslog = 0                   # Use syslog
syslog_facility = daemon     # Syslog facility
syslog_ident = pgbouncer     # Syslog identifier

Statistics and monitoring
stats_period = 60            # Statistics reporting interval
stats_users = stats,postgres # Users who can view statistics
admin_users = postgres       # Users with admin access

Query filtering
ignore_startup_parameters = extra_float_digits,geqo
application_name_add_host = 1

Restart PgBouncer with new configuration

Apply the configuration changes by restarting the PgBouncer service.

sudo systemctl restart pgbouncer
sudo systemctl status pgbouncer

Set up authentication and security

Configure multiple authentication methods

Set up different authentication types for various use cases. MD5 provides a good balance of security and compatibility.

# Authentication settings
auth_type = md5              # md5, scram-sha-256, trust, reject
auth_file = /etc/pgbouncer/userlist.txt
auth_hba_file = /etc/pgbouncer/pg_hba.conf  # Optional HBA file
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

Create HBA configuration for advanced access control

Set up host-based authentication rules to control which clients can connect to specific databases from specific IP addresses.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all            postgres                                peer
host    all            all             127.0.0.1/32           md5
host    all            all             10.0.0.0/8             md5
host    all            all             192.168.0.0/16         md5
host    all            all             172.16.0.0/12          md5
Reject all other connections
host    all            all             0.0.0.0/0              reject

Set up SSL/TLS encryption

Configure SSL encryption for secure communication between clients and PgBouncer, and between PgBouncer and PostgreSQL.

# SSL configuration for client connections
client_tls_sslmode = prefer         # disable, allow, prefer, require
client_tls_key_file = /etc/ssl/private/pgbouncer.key
client_tls_cert_file = /etc/ssl/certs/pgbouncer.crt
client_tls_ca_file = /etc/ssl/certs/ca-certificates.crt
client_tls_protocols = secure        # all, secure, tlsv1.0, tlsv1.1, tlsv1.2, tlsv1.3
client_tls_ciphers = ECDHE+AESGCM:ECDHE+CHACHA20:DHE+AESGCM:DHE+CHACHA20:!aNULL:!MD5:!DSS

SSL configuration for server connections
server_tls_sslmode = prefer         # Connection to PostgreSQL
server_tls_ca_file = /etc/ssl/certs/ca-certificates.crt
server_tls_key_file = /etc/ssl/private/pgbouncer.key
server_tls_cert_file = /etc/ssl/certs/pgbouncer.crt
server_tls_protocols = secure

Generate SSL certificates

Create SSL certificates for encrypted connections. For production, use certificates from a trusted CA.

# Generate private key
sudo openssl genpkey -algorithm RSA -out /etc/ssl/private/pgbouncer.key -pkcs8 -aes256

Generate certificate signing request
sudo openssl req -new -key /etc/ssl/private/pgbouncer.key -out /tmp/pgbouncer.csr

Generate self-signed certificate (for testing)
sudo openssl x509 -req -days 365 -in /tmp/pgbouncer.csr -signkey /etc/ssl/private/pgbouncer.key -out /etc/ssl/certs/pgbouncer.crt

Set proper permissions
sudo chmod 600 /etc/ssl/private/pgbouncer.key
sudo chmod 644 /etc/ssl/certs/pgbouncer.crt
sudo chown postgres:postgres /etc/ssl/private/pgbouncer.key
sudo chown postgres:postgres /etc/ssl/certs/pgbouncer.crt

Optimize performance and monitoring

Configure system resource limits

Adjust system limits to handle high connection loads and optimize performance for connection pooling workloads.

postgres soft nofile 65536
postgres hard nofile 65536
postgres soft nproc 32768
postgres hard nproc 32768

Optimize kernel parameters

Configure network and memory parameters for optimal connection handling and performance under high load conditions.

# Network optimizations
net.core.somaxconn = 65536
net.ipv4.tcp_max_syn_backlog = 65536
net.core.netdev_max_backlog = 5000
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_keepalive_time = 120
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_rmem = 4096 87380 134217728
net.ipv4.tcp_wmem = 4096 65536 134217728
net.core.rmem_max = 134217728
net.core.wmem_max = 134217728

Memory and file handling
vm.swappiness = 1
fs.file-max = 2097152

Apply kernel parameter changes

Load the new kernel parameters and verify they are applied correctly.

sudo sysctl -p /etc/sysctl.d/99-pgbouncer.conf
sudo sysctl -a | grep -E '(somaxconn|tcp_max_syn_backlog|file-max)'

Set up log rotation

Configure automatic log rotation to prevent log files from consuming too much disk space.

/var/log/pgbouncer/*.log {
    daily
    missingok
    rotate 14
    compress
    delaycompress
    notifempty
    copytruncate
    su postgres postgres
    postrotate
        /bin/systemctl reload pgbouncer
    endscript
}

Configure monitoring with Prometheus integration

Set up monitoring integration to track connection pool performance and health metrics. This works well with PostgreSQL monitoring setups.

# Enable stats collection
stats_period = 60
stats_users = prometheus,stats

Create monitoring database entry
[databases]
postgres = host=127.0.0.1 port=5432 dbname=postgres user=pgbouncer_user

Verify your setup

Test PgBouncer functionality and verify that connection pooling is working correctly.

# Check service status
sudo systemctl status pgbouncer

Verify PgBouncer is listening
sudo netstat -tlnp | grep 6432

Test connection through PgBouncer
psql -h 127.0.0.1 -p 6432 -U pgbouncer_user -d myapp

Connect to PgBouncer admin interface
psql -h 127.0.0.1 -p 6432 -U postgres -d pgbouncer

View pool statistics
psql -h 127.0.0.1 -p 6432 -U postgres -d pgbouncer -c "SHOW POOLS;"

View client connections
psql -h 127.0.0.1 -p 6432 -U postgres -d pgbouncer -c "SHOW CLIENTS;"

View server connections
psql -h 127.0.0.1 -p 6432 -U postgres -d pgbouncer -c "SHOW SERVERS;"

Check configuration
psql -h 127.0.0.1 -p 6432 -U postgres -d pgbouncer -c "SHOW CONFIG;"

View statistics
psql -h 127.0.0.1 -p 6432 -U postgres -d pgbouncer -c "SHOW STATS;"

Check log files
sudo tail -f /var/log/pgbouncer/pgbouncer.log

Common issues

Symptom	Cause	Fix
PgBouncer won't start	Configuration syntax error	`sudo -u postgres pgbouncer -v /etc/pgbouncer/pgbouncer.ini` to check config
Authentication failed	Incorrect userlist.txt format	Regenerate MD5 hash: `echo -n "passwordusername" \| md5sum`
Too many client connections	max_client_conn too low	Increase `max_client_conn` and restart PgBouncer
Connection timeouts	Pool exhaustion	Increase `default_pool_size` or tune `pool_mode`
SSL connection errors	Certificate permissions	`sudo chmod 600 /etc/ssl/private/pgbouncer.key`
High connection latency	Inefficient pool mode	Switch to `pool_mode = transaction` if compatible
Statistics not updating	stats_period disabled	Set `stats_period = 60` and add stats_users
Memory usage growing	Connection leaks	Check `server_lifetime` and `server_idle_timeout` settings

Next steps

Running this in production?

Want this handled for you? Setting up PgBouncer once is straightforward. Keeping it patched, monitored, backed up and tuned across environments is the harder part. See how we run infrastructure like this for European SaaS and e-commerce teams.

Automated install script

Run this to automate the entire setup

install.sh

#!/usr/bin/env bash
set -euo pipefail

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m'

# Global variables
DB_NAME="${1:-myapp_production}"
DB_USER="${2:-pgbouncer_user}"
DB_PASSWORD="${3:-$(openssl rand -base64 32)}"
LISTEN_PORT="${4:-6432}"
PGBOUNCER_CONFIG_DIR="/etc/pgbouncer"
PGBOUNCER_LOG_DIR="/var/log/pgbouncer"

# Usage function
usage() {
    echo "Usage: $0 [db_name] [db_user] [db_password] [listen_port]"
    echo "  db_name:     Database name (default: myapp_production)"
    echo "  db_user:     Database user (default: pgbouncer_user)"
    echo "  db_password: Database password (default: auto-generated)"
    echo "  listen_port: PgBouncer listen port (default: 6432)"
    exit 1
}

# Cleanup function for rollback
cleanup() {
    echo -e "${RED}[ERROR] Installation failed. Cleaning up...${NC}"
    systemctl stop pgbouncer 2>/dev/null || true
    systemctl disable pgbouncer 2>/dev/null || true
    rm -rf "$PGBOUNCER_CONFIG_DIR" 2>/dev/null || true
    rm -rf "$PGBOUNCER_LOG_DIR" 2>/dev/null || true
    sudo -u postgres psql -c "DROP USER IF EXISTS $DB_USER;" 2>/dev/null || true
    exit 1
}

trap cleanup ERR

# Check prerequisites
check_prerequisites() {
    echo -e "${YELLOW}[1/12] Checking prerequisites...${NC}"
    
    if [[ $EUID -ne 0 ]]; then
        echo -e "${RED}This script must be run as root${NC}"
        exit 1
    fi
    
    if ! command -v psql >/dev/null 2>&1; then
        echo -e "${RED}PostgreSQL client (psql) is required but not installed${NC}"
        exit 1
    fi
    
    if ! systemctl is-active --quiet postgresql; then
        echo -e "${RED}PostgreSQL service is not running${NC}"
        exit 1
    fi
    
    echo -e "${GREEN}Prerequisites check passed${NC}"
}

# Detect distribution
detect_distro() {
    echo -e "${YELLOW}[2/12] Detecting distribution...${NC}"
    
    if [ -f /etc/os-release ]; then
        . /etc/os-release
        case "$ID" in
            ubuntu|debian)
                PKG_MGR="apt"
                PKG_UPDATE="apt update && apt upgrade -y"
                PKG_INSTALL="apt install -y"
                FIREWALL_CMD="ufw"
                ;;
            almalinux|rocky|centos|rhel|ol|fedora)
                PKG_MGR="dnf"
                PKG_UPDATE="dnf update -y"
                PKG_INSTALL="dnf install -y"
                FIREWALL_CMD="firewall-cmd"
                ;;
            amzn)
                PKG_MGR="yum"
                PKG_UPDATE="yum update -y"
                PKG_INSTALL="yum install -y"
                FIREWALL_CMD="firewall-cmd"
                ;;
            *)
                echo -e "${RED}Unsupported distribution: $ID${NC}"
                exit 1
                ;;
        esac
    else
        echo -e "${RED}Cannot detect distribution${NC}"
        exit 1
    fi
    
    echo -e "${GREEN}Detected: $PRETTY_NAME${NC}"
}

# Update system packages
update_system() {
    echo -e "${YELLOW}[3/12] Updating system packages...${NC}"
    $PKG_UPDATE
    echo -e "${GREEN}System packages updated${NC}"
}

# Install PgBouncer
install_pgbouncer() {
    echo -e "${YELLOW}[4/12] Installing PgBouncer...${NC}"
    $PKG_INSTALL pgbouncer openssl
    echo -e "${GREEN}PgBouncer installed${NC}"
}

# Create configuration directory
create_config_dir() {
    echo -e "${YELLOW}[5/12] Creating PgBouncer configuration directory...${NC}"
    mkdir -p "$PGBOUNCER_CONFIG_DIR"
    chown postgres:postgres "$PGBOUNCER_CONFIG_DIR"
    chmod 750 "$PGBOUNCER_CONFIG_DIR"
    echo -e "${GREEN}Configuration directory created${NC}"
}

# Generate MD5 hash for password
generate_md5_hash() {
    echo -n "${DB_PASSWORD}${DB_USER}" | md5sum | cut -d' ' -f1
}

# Create PgBouncer configuration
create_pgbouncer_config() {
    echo -e "${YELLOW}[6/12] Creating PgBouncer configuration...${NC}"
    
    cat > "$PGBOUNCER_CONFIG_DIR/pgbouncer.ini" << EOF
[databases]
${DB_NAME} = host=127.0.0.1 port=5432 dbname=${DB_NAME} user=${DB_USER}

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = ${LISTEN_PORT}
auth_type = md5
auth_file = ${PGBOUNCER_CONFIG_DIR}/userlist.txt
logfile = ${PGBOUNCER_LOG_DIR}/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
admin_users = postgres
stats_users = stats
pool_mode = session
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 5
max_db_connections = 100
max_user_connections = 100
server_reset_query = DISCARD ALL
server_check_query = SELECT 1
server_check_delay = 30
server_connect_timeout = 15
server_login_retry = 15
client_login_timeout = 60
autodb_idle_timeout = 3600
stats_period = 60
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
ignore_startup_parameters = extra_float_digits
EOF
    
    echo -e "${GREEN}PgBouncer configuration created${NC}"
}

# Create user authentication file
create_userlist() {
    echo -e "${YELLOW}[7/12] Creating user authentication file...${NC}"
    
    local md5_hash="md5$(generate_md5_hash)"
    
    cat > "$PGBOUNCER_CONFIG_DIR/userlist.txt" << EOF
"${DB_USER}" "${md5_hash}"
"stats" ""
EOF
    
    echo -e "${GREEN}User authentication file created${NC}"
}

# Set file permissions
set_permissions() {
    echo -e "${YELLOW}[8/12] Setting file permissions...${NC}"
    
    chown postgres:postgres "$PGBOUNCER_CONFIG_DIR/pgbouncer.ini"
    chown postgres:postgres "$PGBOUNCER_CONFIG_DIR/userlist.txt"
    chmod 640 "$PGBOUNCER_CONFIG_DIR/pgbouncer.ini"
    chmod 640 "$PGBOUNCER_CONFIG_DIR/userlist.txt"
    
    echo -e "${GREEN}File permissions set${NC}"
}

# Create log directory
create_log_dir() {
    echo -e "${YELLOW}[9/12] Creating log directory...${NC}"
    
    mkdir -p "$PGBOUNCER_LOG_DIR"
    chown postgres:postgres "$PGBOUNCER_LOG_DIR"
    chmod 755 "$PGBOUNCER_LOG_DIR"
    
    echo -e "${GREEN}Log directory created${NC}"
}

# Create database user
create_db_user() {
    echo -e "${YELLOW}[10/12] Creating PostgreSQL user...${NC}"
    
    sudo -u postgres psql -c "CREATE USER ${DB_USER} WITH PASSWORD '${DB_PASSWORD}';" || true
    sudo -u postgres psql -c "GRANT CONNECT ON DATABASE ${DB_NAME} TO ${DB_USER};" || true
    sudo -u postgres psql -c "GRANT USAGE ON SCHEMA public TO ${DB_USER};" || true
    sudo -u postgres psql -c "GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO ${DB_USER};" || true
    sudo -u postgres psql -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ${DB_USER};" || true
    
    echo -e "${GREEN}Database user created${NC}"
}

# Start and enable PgBouncer service
start_service() {
    echo -e "${YELLOW}[11/12] Starting and enabling PgBouncer service...${NC}"
    
    systemctl daemon-reload
    systemctl enable pgbouncer
    systemctl start pgbouncer
    
    echo -e "${GREEN}PgBouncer service started and enabled${NC}"
}

# Verify installation
verify_installation() {
    echo -e "${YELLOW}[12/12] Verifying installation...${NC}"
    
    # Check if service is running
    if ! systemctl is-active --quiet pgbouncer; then
        echo -e "${RED}PgBouncer service is not running${NC}"
        exit 1
    fi
    
    # Check if port is listening
    if ! netstat -tuln 2>/dev/null | grep -q ":${LISTEN_PORT}" && ! ss -tuln 2>/dev/null | grep -q ":${LISTEN_PORT}"; then
        echo -e "${RED}PgBouncer is not listening on port ${LISTEN_PORT}${NC}"
        exit 1
    fi
    
    # Test connection
    if ! sudo -u postgres psql -h 127.0.0.1 -p "${LISTEN_PORT}" -d "${DB_NAME}" -c "SELECT 1;" >/dev/null 2>&1; then
        echo -e "${YELLOW}Warning: Direct connection test failed, but this may be expected${NC}"
    fi
    
    echo -e "${GREEN}Installation verification completed successfully${NC}"
}

# Main function
main() {
    check_prerequisites
    detect_distro
    update_system
    install_pgbouncer
    create_config_dir
    create_pgbouncer_config
    create_userlist
    set_permissions
    create_log_dir
    create_db_user
    start_service
    verify_installation
    
    echo -e "${GREEN}PgBouncer installation completed successfully!${NC}"
    echo -e "${YELLOW}Configuration summary:${NC}"
    echo "  Database: $DB_NAME"
    echo "  User: $DB_USER"
    echo "  Password: $DB_PASSWORD"
    echo "  Listen Port: $LISTEN_PORT"
    echo "  Connection string: postgresql://$DB_USER:$DB_PASSWORD@127.0.0.1:$LISTEN_PORT/$DB_NAME"
}

# Run main function
main "$@"

Review the script before running. Execute with: bash install.sh

#pgbouncer #postgresql #connection pooling #database performance #database optimization

Install and configure PgBouncer for PostgreSQL connection pooling with performance optimization

Prerequisites

What this solves

Step-by-step installation

Update system packages

Install PgBouncer from official repositories

Create PgBouncer configuration directory

Configure database connections

Create user authentication file

Replace 'password' with actual password and 'username' with actual username

Set proper file permissions

Create log directory

Create PostgreSQL user for PgBouncer

Configure systemd service

Security settings

Enable and start PgBouncer service

Configure connection pooling parameters

Optimize pool sizing

pool_mode = session # Default, safer for complex applications

pool_mode = statement # Most aggressive, limited compatibility

Connection limits

Database connection limits

Configure performance tuning

Connection lifecycle

Memory and performance

Configure monitoring and logging

Statistics and monitoring

Query filtering

Restart PgBouncer with new configuration

Set up authentication and security

Configure multiple authentication methods

Create HBA configuration for advanced access control

Reject all other connections

Set up SSL/TLS encryption

SSL configuration for server connections

Generate SSL certificates

Generate certificate signing request

Generate self-signed certificate (for testing)

Set proper permissions

Optimize performance and monitoring

Configure system resource limits

Optimize kernel parameters

Memory and file handling

Apply kernel parameter changes

Set up log rotation

Configure monitoring with Prometheus integration

Create monitoring database entry

Verify your setup

Verify PgBouncer is listening

Test connection through PgBouncer

Connect to PgBouncer admin interface

View pool statistics

View client connections

View server connections

Check configuration

View statistics

Check log files

Common issues

Next steps

Running this in production?

Related tutorials

Benchmark database performance with sysbench and fio integration

Configure MySQL binary log backup and point-in-time recovery

Set up MySQL backup monitoring with Prometheus alerts and Grafana dashboards

Don't want to manage this yourself?

`Replace 'password' with actual password and 'username' with actual username`