`pgbouncer.ini`

38 lines
;; PgBouncer — durable fix for "268 apps, one Postgres".
;; Install:  apt-get install pgbouncer
;; Put this at /etc/pgbouncer/pgbouncer.ini, then point each app's DB client at
;; host=127.0.0.1 port=6432 (instead of the raw 5432/socket). Lower each app's pool max to 2-3.

[databases]
;; Front every DB the fleet uses. Add one line per DB name in use.
dw_unified = host=/var/run/postgresql port=5432 dbname=dw_unified
crunified  = host=/var/run/postgresql port=5432 dbname=crunified
* = host=/var/run/postgresql port=5432

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

;; Transaction pooling = the whole point: a client connection only holds a server
;; connection for the duration of a transaction, so hundreds of idle app clients
;; share a small real pool.
pool_mode = transaction

;; Real connections to Postgres stay tiny vs the 500 ceiling — leaves headroom for
;; admin + replication + any app still on the raw socket during migration.
max_client_conn = 2000        ;; how many app connections PgBouncer will accept
default_pool_size = 20        ;; server conns per (user,db) pair
min_pool_size = 2
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 60
;; NOTE: transaction mode disallows session-level features (LISTEN/NOTIFY, session
;; advisory locks, prepared statements across txns). Apps using those need session mode
;; or a direct connection — audit before flipping the whole fleet.

;; /etc/pgbouncer/userlist.txt format (md5 of password+username):
;;   "dw_admin" "md5<hash>"
;; generate:  echo -n "md5"; echo -n "<password>dw_admin" | md5sum | awk '{print $1}'