← back to Db Saturation Fix

DB saturation fix kit: diagnosis + immediate-relief + pool-audit + PgBouncer config

0eca9ba0bcc139f3c5033cc17f073abbafa98d26 · 2026-07-20 12:57:45 -0700 · Steve

Files touched

• A .gitignore
• A 1-immediate-relief.sh
• A 2-audit-pools.sh
• A README.md
• A pgbouncer.ini

Diff

commit 0eca9ba0bcc139f3c5033cc17f073abbafa98d26
Author: Steve <steve@designerwallcoverings.com>
Date:   Mon Jul 20 12:57:45 2026 -0700

    DB saturation fix kit: diagnosis + immediate-relief + pool-audit + PgBouncer config
---
 .gitignore            |  4 ++++
 1-immediate-relief.sh | 22 ++++++++++++++++++++++
 2-audit-pools.sh      | 27 +++++++++++++++++++++++++++
 README.md             | 20 ++++++++++++++++++++
 pgbouncer.ini         | 37 +++++++++++++++++++++++++++++++++++++
 5 files changed, 110 insertions(+)

diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..daf1dd0
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,4 @@
+node_modules/
+.env
+*.log
+.DS_Store
diff --git a/1-immediate-relief.sh b/1-immediate-relief.sh
new file mode 100755
index 0000000..79c230a
--- /dev/null
+++ b/1-immediate-relief.sh
@@ -0,0 +1,22 @@
+#!/usr/bin/env bash
+# IMMEDIATE RELIEF — run ON Kamatera (45.61.58.125) as root.
+# Stops the *errored* crash-loopers that churn/hold dw_admin connections for no benefit.
+# Reversible: `pm2 start <name>` brings any of them back. Review before running.
+set -uo pipefail
+
+echo "== current pm2 crash-loopers (errored or very high restarts) =="
+pm2 jlist | node -e 'let d="";process.stdin.on("data",c=>d+=c).on("end",()=>{
+  JSON.parse(d).map(p=>({n:p.name,r:p.pm2_env.restart_time,s:p.pm2_env.status}))
+   .filter(p=>p.s==="errored"||p.r>500).sort((a,b)=>b.r-a.r)
+   .forEach(p=>console.log(`  ${p.n}  restarts=${p.r}  ${p.s}`));}'
+
+echo
+echo "== SAFE stop: momentum-crawler is ERRORED (4023 restarts, doing nothing) =="
+read -p "Stop momentum-crawler? [y/N] " a; [ "$a" = y ] && pm2 stop momentum-crawler && echo "stopped."
+
+# koroseal-quote-api is ONLINE (2225 restarts) — it may be load-bearing. Do NOT blind-stop.
+# Inspect first:  pm2 logs koroseal-quote-api --lines 50   (fix the restart cause, don't just kill)
+
+echo
+echo "== connections after =="
+ps -C postgres -o cmd= | grep -c "postgres:"
diff --git a/2-audit-pools.sh b/2-audit-pools.sh
new file mode 100755
index 0000000..809df28
--- /dev/null
+++ b/2-audit-pools.sh
@@ -0,0 +1,27 @@
+#!/usr/bin/env bash
+# READ-ONLY audit — run ON Kamatera as root. Finds apps whose pg pool is large/unbounded.
+# Touches nothing; only greps source. The Node `pg` Pool default max is 10 — with 268 apps
+# that alone is up to 2680 potential connections against a 500 ceiling.
+set -uo pipefail
+ROOTS="/root/public-projects /root/Projects /root/DW-Agents /var/www"
+
+echo "== apps that construct a pg Pool/Client, and their max (blank = default 10) =="
+for base in $ROOTS; do
+  [ -d "$base" ] || continue
+  grep -rlE "new (Pool|Client)\(|require\(['\"]pg['\"]\)" "$base" --include=*.js --include=*.cjs 2>/dev/null \
+    | grep -v node_modules | while read -r f; do
+      maxv=$(grep -oE "max:\s*[0-9]+" "$f" | head -1)
+      printf "%-70s %s\n" "${f#/root/}" "${maxv:-max:DEFAULT(10)}"
+    done
+done | sort -u
+
+echo
+echo "== summary: apps with no explicit max (each can open up to 10) =="
+cnt=0
+for base in $ROOTS; do
+  [ -d "$base" ] || continue
+  for f in $(grep -rlE "new Pool\(" "$base" --include=*.js 2>/dev/null | grep -v node_modules); do
+    grep -qE "max:\s*[0-9]+" "$f" || cnt=$((cnt+1))
+  done
+done
+echo "unbounded-pool files: $cnt"
diff --git a/README.md b/README.md
new file mode 100644
index 0000000..6f28a6a
--- /dev/null
+++ b/README.md
@@ -0,0 +1,20 @@
+# Kamatera Postgres saturation — fix kit
+
+**Diagnosis (2026-07-20):** `dw_unified` Postgres on 45.61.58.125 is at **500/500 connections**
+(max_connections=500). 486 held by `dw_admin`, 471 actively running SELECT, only 1
+idle-in-transaction. Cause = **268 pm2 apps sharing one Postgres**, each with its own pool,
+collectively exhausting the ceiling. Crash-loopers (`momentum-crawler` 4023 restarts/errored,
+`koroseal-quote-api` 2225) churn connections on top.
+
+Everything here is for **Steve to run** — read the safety notes, nothing auto-executes.
+
+## Order of operations
+1. `bash 1-immediate-relief.sh`   → stop the *errored* crash-looper(s) to free slots NOW (reversible).
+2. `bash 2-audit-pools.sh`        → READ-ONLY: find apps with big/unbounded `pg` pools.
+3. Install PgBouncer with `pgbouncer.ini` (transaction pooling) → the durable fix.
+4. Repoint each app's `PGHOST`/port at PgBouncer (6432), lower per-app pool `max` to 2–3.
+
+## Why PgBouncer
+With 268 apps you cannot give each a raw pool and stay under 500. PgBouncer (transaction mode)
+multiplexes hundreds of client connections onto a small server pool (e.g. 40), so the fleet
+scales without touching max_connections (each raw slot ~10MB RAM).
diff --git a/pgbouncer.ini b/pgbouncer.ini
new file mode 100644
index 0000000..612fedb
--- /dev/null
+++ b/pgbouncer.ini
@@ -0,0 +1,37 @@
+;; 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}'

(oldest)  ·  back to Db Saturation Fix  ·  (newest)