nanjozy/pgbouncer repository overview

# docker-compose.yml
version: '3'
services:
  pgbouncer:
    image: nanjozy/pgbouncer:latest
    container_name: pgbouncer
    ports:
      - "5432:5432"
    volumes:
      - ./pgbouncer.ini:/etc/pgbouncer/pgbouncer.ini
      - ./userlist.txt:/etc/pgbouncer/userlist.txt
    restart: always

kind: Deployment
apiVersion: apps/v1
metadata:
  name: pgbouncer
  namespace: pgbouncer
  labels:
    k8s-app: pgbouncer
  annotations: {}
spec:
  replicas: 1
  selector:
    matchLabels:
      k8s-app: pgbouncer
  template:
    metadata:
      labels:
        k8s-app: pgbouncer
    spec:
      volumes:
        - name: cfg
          secret:
            secretName: pgbouncer
            defaultMode: 420
      containers:
        - name: pgbouncer
          image: 'nanjozy/pgbouncer:latest'
          ports:
            - name: pg
              containerPort: 5432
              protocol: TCP
          env:
            - name: TZ
              value: Asia/Shanghai
          resources:
            limits:
              cpu: '1'
              memory: 2Gi
            requests:
              cpu: 100m
              memory: 512Mi
          volumeMounts:
            - name: cfg
              readOnly: true
              mountPath: /etc/pgbouncer/userlist.txt
              subPath: userlist.txt
            - name: cfg
              readOnly: true
              mountPath: /etc/pgbouncer/pgbouncer.ini
              subPath: pgbouncer.ini
          livenessProbe:
            exec:
              command:
                - bash
                - '-c'
                - >-
                  PGPASSWORD='monitor' psql -h 127.0.0.1 -p 5432 -U monitor -d
                  pgbouncer -c "SHOW VERSION"
            timeoutSeconds: 1
            periodSeconds: 10
            successThreshold: 1
            failureThreshold: 3
          readinessProbe:
            exec:
              command:
                - bash
                - '-c'
                - >-
                  PGPASSWORD='monitor' psql -h 127.0.0.1 -p 5432 -U monitor -d
                  pgbouncer -c "SHOW VERSION"
            timeoutSeconds: 1
            periodSeconds: 10
            successThreshold: 1
            failureThreshold: 3
          imagePullPolicy: IfNotPresent
          securityContext:
            privileged: false
      restartPolicy: Always
      terminationGracePeriodSeconds: 30
      dnsPolicy: ClusterFirst
      securityContext: {}
      affinity: {}
      schedulerName: default-scheduler
  strategy:
    type: RollingUpdate
    rollingUpdate:
      maxUnavailable: 0
      maxSurge: 1
  revisionHistoryLimit: 10
  progressDeadlineSeconds: 600

kind: Service
apiVersion: v1
metadata:
  name: pgbouncer
  namespace: pgbouncer
  labels:
    k8s-app: pgbouncer
spec:
  ports:
    - name: pgbouncer
      protocol: TCP
      port: 5432
      targetPort: pg
  selector:
    k8s-app: pgbouncer
  type: ClusterIP
  sessionAffinity: None

kind: Secret
apiVersion: v1
metadata:
  name: pgbouncer
  namespace: pgbouncer
  labels:
    qcloud-app: pg-bouncer
data:
  pgbouncer.ini: ''
  userlist.txt: ''
type: Opaque

[databases]

;; Wildcard configuration
;; Meaning: Any database name not explicitly defined will apply this rule.
;; host=127.0.0.1: Connect to the backend Postgres on the local machine.
;; dbname=postgres: This is a special fallback. If the database name requested by the client doesn't match here, try connecting to the backend postgres database (or a database with the same name, depending on specific version behavior; explicitly specifying dbname is usually recommended).
;; pool_size=50: [Important] Allow a maximum of 50 concurrent backend connections for each matched database.
;; reserve_pool=20: If the 50 connections are full, allow borrowing 20 extra emergency connections.

* = host=127.0.0.1 dbname=postgres port=5432 pool_size=50 reserve_pool=20

[pgbouncer]
;; --- Core Mode ---


;; Transaction mode: This is mandatory for high-concurrency Web applications.
;; Connections are occupied only during the transaction, greatly improving the concurrency reuse rate.
pool_mode = transaction
listen_port = 5432
listen_addr = 0.0.0.0


;; --- Authentication Security ---
;; scram-sha-256: A more secure authentication method than md5 (requires Postgres 10+ support).
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt


;; --- Administrative Permissions ---
;; admin_users: Users allowed to log in to the pgbouncer virtual database to execute management commands like RELOAD/PAUSE.
admin_users = postgres
;; stats_users: Only allowed to view statistics (SHOW STATS), cannot execute management commands; suitable for monitoring systems (e.g., Prometheus exporter).
stats_users = monitor




;; --- Compatibility Settings ---
;; Ignore startup parameter: extra_float_digits
;; This is a common configuration. Drivers like Java JDBC or Npgsql send this parameter upon connection.
;; PgBouncer defaults to not recognizing it and will error out; adding this line prevents driver connection failures.
ignore_startup_parameters = extra_float_digits


;; --- Connection Pool Capacity Control ---
;; Frontend limit: Allow 5000 clients to connect simultaneously (including idle and queued ones).
max_client_conn = 5000


;; Backend default limit: If pool_size is not specified in [databases], this value is used.
;; (Since you used the wildcard * and specified pool_size above, this default value might not actually be used).
default_pool_size = 50
;; Minimum idle connections: Keep 2 connections open during idle times to avoid cold start latency.
min_pool_size = 2
;; Reserve connection pool: Buffer pool size to handle traffic spikes.
reserve_pool_size = 20
;; Reserve pool timeout: If the standard pool is full, wait 5 seconds before the client starts using the reserve pool.
reserve_pool_timeout = 5.0
;; --- Timeouts ---

;; Backend connection lifetime: Automatically recycle and recreate after 1 hour to prevent memory leaks.
server_lifetime = 3600
;; Backend idle timeout: Close backend connection after being idle for 10 minutes.
server_idle_timeout = 600




;; Client idle timeout: 10 minutes.
;; [Note] If your application layer uses a connection pool (e.g., HikariCP, Druid),
;; ensure the application layer's maxLifeTime is shorter than this value, otherwise PgBouncer might cut off connections the app thinks are still alive, causing application errors.
client_idle_timeout = 600
query_timeout = 60

;; --- TCP/OS Level Optimization ---

;; Enable port reuse to improve performance of establishing new connections under high concurrency (Linux Only).
so_reuseport = 1
;; TCP Keepalive settings: Prevent cloud provider firewalls from silently dropping long connections.
tcp_keepalive = 1
tcp_keepcnt = 5
tcp_keepidle = 30
tcp_keepintvl = 30



;; --- Logging & Monitoring ---
;; Log level: 0 is minimal (recommended for production).
verbose = 0
;; Log severe connection pool errors.
log_pooler_errors = 1
;; Enable statistics logging.
log_stats = 1


; Log connection establishment (1=enabled)
; Recommended to close (0) under high concurrency and short connection modes, otherwise log volume is huge; can be enabled (1) for long connection modes.
log_connections = 1


; Log connection disconnection (1=enabled)
log_disconnections = 1


;; Output QPS and traffic statistics in the log every 60 seconds.
stats_period = 60



;; Very useful feature: Append the client's real IP to application_name.
;; When querying pg_stat_activity in Postgres, you can see which machine initiated the request.
application_name_add_host = 1

;; --- TLS/SSL Settings ---

;; allow: Allow encrypted connections, but do not enforce them. If the backend supports SSL, try to use it; otherwise, use plaintext.
server_tls_sslmode = allow
server_tls_ciphers = normal

"username" "password_hash"