chaddyc/pgbouncer repository overview

# Build the image
docker build -t my-pgbouncer .

# Run the container
docker run -d \
  --name pgbouncer \
  -p 6432:6432 \
  -e DB_HOST=postgres-server \
  -e DB_PORT=5432 \
  -e DB_USER=myuser \
  -e DB_PASSWORD=mypassword \
  -e DB_NAME=myapp \
  my-pgbouncer

PGBOUNCER_MAX_CLIENT_CONN=100          # Maximum client connections
PGBOUNCER_DEFAULT_POOL_SIZE=20         # Default pool size per database
PGBOUNCER_MIN_POOL_SIZE=5              # Minimum pool size
PGBOUNCER_RESERVE_POOL_SIZE=5          # Reserved connections
PGBOUNCER_MAX_DB_CONNECTIONS=50        # Maximum database connections
PGBOUNCER_MAX_USER_CONNECTIONS=50      # Maximum connections per user

PGBOUNCER_SERVER_LIFETIME=3600         # Connection lifetime
PGBOUNCER_SERVER_IDLE_TIMEOUT=600      # Server idle timeout
PGBOUNCER_CLIENT_IDLE_TIMEOUT=0        # Client idle timeout (0 = disabled)

PGBOUNCER_LOG_CONNECTIONS=1            # Log connections (0/1)
PGBOUNCER_LOG_DISCONNECTIONS=1         # Log disconnections (0/1)
PGBOUNCER_LOG_POOLER_ERRORS=1          # Log pooler errors (0/1)
PGBOUNCER_STATS_PERIOD=60              # Stats logging period

PGBOUNCER_SERVER_RESET_QUERY="DISCARD ALL"
PGBOUNCER_IGNORE_STARTUP_PARAMETERS="extra_float_digits"

PGBOUNCER_ADMIN_USERS=myuser           # Users with admin access
PGBOUNCER_STATS_USERS=myuser           # Users with stats access

version: '3.8'

services:
  postgres:
    image: postgres:18.0
    environment:
      POSTGRES_DB: myapp
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
    volumes:
      - postgres_data:/var/lib/postgresql/18/docker
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U myuser -d myapp"]
      interval: 10s
      timeout: 5s
      retries: 5

  pgbouncer:
    build: .
    environment:
      DB_HOST: postgres
      DB_PORT: 5432
      DB_USER: myuser
      DB_PASSWORD: mypassword
      DB_NAME: myapp
      PGBOUNCER_POOL_MODE: transaction
      PGBOUNCER_DEFAULT_POOL_SIZE: 20
    ports:
      - "6432:6432"
    depends_on:
      postgres:
        condition: service_healthy
    healthcheck:
      test: ["CMD-SHELL", "psql -h localhost -p 6432 -U myuser -d myapp -c 'SELECT 1;' || exit 1"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  postgres_data:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgbouncer
spec:
  replicas: 2
  selector:
    matchLabels:
      app: pgbouncer
  template:
    metadata:
      labels:
        app: pgbouncer
    spec:
      containers:
      - name: pgbouncer
        image: my-pgbouncer:latest
        ports:
        - containerPort: 6432
        env:
        - name: DB_HOST
          value: "postgres-service"
        - name: DB_PORT
          value: "5432"
        - name: DB_USER
          valueFrom:
            secretKeyRef:
              name: postgres-credentials
              key: username
        - name: DB_PASSWORD
          valueFrom:
            secretKeyRef:
              name: postgres-credentials
              key: password
        - name: DB_NAME
          value: "myapp"
        livenessProbe:
          exec:
            command:
            - psql
            - -h
            - localhost
            - -p
            - "6432"
            - -U
            - $(DB_USER)
            - -d
            - $(DB_NAME)
            - -c
            - "SELECT 1;"
          initialDelaySeconds: 30
          periodSeconds: 10
        resources:
          requests:
            memory: "64Mi"
            cpu: "50m"
          limits:
            memory: "128Mi"
            cpu: "100m"
---
apiVersion: v1
kind: Service
metadata:
  name: pgbouncer-service
spec:
  selector:
    app: pgbouncer
  ports:
  - protocol: TCP
    port: 6432
    targetPort: 6432

# Connect using psql
psql -h localhost -p 6432 -U myuser -d myapp

# Connection string
postgresql://myuser:mypassword@localhost:6432/myapp

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

# Show active pools
SHOW POOLS;

# Show database configuration
SHOW DATABASES;

# Show connection statistics
SHOW STATS;

# Show current configuration
SHOW CONFIG;

# Reload configuration (after changes)
RELOAD;

# Pause all activity
PAUSE;

# Resume activity
RESUME;

import psycopg2

conn = psycopg2.connect(
    host="pgbouncer-host",
    port=6432,
    database="myapp",
    user="myuser",
    password="mypassword"
)

const { Pool } = require('pg');

const pool = new Pool({
  host: 'pgbouncer-host',
  port: 6432,
  database: 'myapp',
  user: 'myuser',
  password: 'mypassword',
});

import (
    "database/sql"
    _ "github.com/lib/pq"
)

db, err := sql.Open("postgres", 
    "host=pgbouncer-host port=6432 user=myuser password=mypassword dbname=myapp sslmode=disable")

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseConnection {
    private static final String URL = "jdbc:postgresql://pgbouncer-host:6432/myapp";
    private static final String USER = "myuser";
    private static final String PASSWORD = "mypassword";
    
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
    
    // Using HikariCP connection pool (recommended)
    public static HikariDataSource createDataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(URL);
        config.setUsername(USER);
        config.setPassword(PASSWORD);
        config.setMaximumPoolSize(20);
        return new HikariDataSource(config);
    }
}

using Npgsql;
using Microsoft.Extensions.DependencyInjection;

// Basic connection
var connectionString = "Host=pgbouncer-host;Port=6432;Database=myapp;Username=myuser;Password=mypassword";

using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();

// ASP.NET Core DI registration
services.AddDbContext<ApplicationDbContext>(options =>
    options.UseNpgsql("Host=pgbouncer-host;Port=6432;Database=myapp;Username=myuser;Password=mypassword"));

// Connection pooling with NpgsqlDataSource (.NET 6+)
var dataSourceBuilder = new NpgsqlDataSourceBuilder(connectionString);
dataSourceBuilder.UseLoggerFactory(loggerFactory);
await using var dataSource = dataSourceBuilder.Build();
await using var connection = await dataSource.OpenConnectionAsync();

# Check container health
docker ps

# View health check logs
docker inspect pgbouncer | jq '.[0].State.Health'

# View PgBouncer logs
docker logs pgbouncer

# View logs inside container
docker exec pgbouncer tail -f /var/log/pgbouncer/pgbouncer.log

-- Connect to pgbouncer admin
psql -h localhost -p 6432 -U myuser -d pgbouncer

-- Pool statistics
SHOW STATS;

-- Pool status
SHOW POOLS;

-- Active clients
SHOW CLIENTS;

-- Active servers
SHOW SERVERS;

version: '3.8'

services:
  # ... pgbouncer service ...
  
  pgbouncer-exporter:
    image: prometheuscommunity/pgbouncer-exporter:latest
    environment:
      PGBOUNCER_EXPORTER_HOST: pgbouncer
      PGBOUNCER_EXPORTER_PORT: 6432
      PGBOUNCER_EXPORTER_USER: myuser
      PGBOUNCER_EXPORTER_PASSWORD: mypassword
    ports:
      - "9127:9127"

# Check database connectivity
docker exec pgbouncer psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "SELECT 1;"

# Check environment variables
docker exec pgbouncer env | grep -E "^(DB_|PGBOUNCER_)"

# Verify userlist.txt generation
docker exec pgbouncer cat /etc/pgbouncer/userlist.txt

# Check MD5 hash generation
docker exec pgbouncer sh -c 'echo -n "${DB_PASSWORD}${DB_USER}" | md5sum'

-- Check pool utilization
SHOW POOLS;
SHOW STATS;

-- Increase pool sizes if needed

# View generated configuration
docker exec pgbouncer cat /etc/pgbouncer/pgbouncer.ini

# Test configuration syntax
docker exec pgbouncer pgbouncer -v /etc/pgbouncer/pgbouncer.ini

# Add to environment variables
PGBOUNCER_VERBOSE=2
PGBOUNCER_LOG_CONNECTIONS=1
PGBOUNCER_LOG_DISCONNECTIONS=1
PGBOUNCER_LOG_POOLER_ERRORS=1