PgBouncer Migration Guide

Step-by-step guide to deploying PgBouncer and migrating apps from direct PostgreSQL connections.

Last Updated: 2025-12-12

---

Why PgBouncer?

After host reboots, all K8s pods restart simultaneously and reconnect to PostgreSQL at once: - Problem: PostgreSQL max_connections (100) exhausted within seconds - Symptoms: CrashLoopBackOff, Authentik using 94 connections, cascade failures - Solution: PgBouncer pools connections, limiting actual PostgreSQL connections

Benefits: - 200 client connections → 50 PostgreSQL connections - Graceful handling of connection storms - Faster connection acquisition (pooled connections are pre-established) - Reduced PostgreSQL memory usage

---

Architecture Overview

┌─────────────┐    ┌─────────────┐    ┌─────────────┐
│  Authentik  │    │   PostgREST │    │   Storage   │
│   (server)  │    │             │    │   GoTrue    │
└──────┬──────┘    └──────┬──────┘    └──────┬──────┘
       │                  │                  │
       └──────────────────┼──────────────────┘
                          │
                    ┌─────▼─────┐
                    │ PgBouncer │  pgbouncer.supabase:6432
                    │  200 max  │  transaction mode
                    └─────┬─────┘
                          │ 50 max connections
                    ┌─────▼─────┐
                    │ PostgreSQL│  postgres.supabase:5432
                    │  100 max  │
                    └───────────┘

Connection endpoints: - Apps → pgbouncer.supabase.svc.cluster.local:6432 - Admin/Migrations → postgres.supabase.svc.cluster.local:5432 (direct)

---

Pre-Migration Checklist

# 1. Check current connection count
kubectl exec -n supabase postgres-0 -- psql -U postgres -c \
  "SELECT usename, count(*) FROM pg_stat_activity GROUP BY usename;"

# 2. Note which apps connect to PostgreSQL
kubectl exec -n supabase postgres-0 -- psql -U postgres -c \
  "SELECT usename, application_name, client_addr, count(*)
   FROM pg_stat_activity WHERE usename IS NOT NULL
   GROUP BY usename, application_name, client_addr
   ORDER BY count DESC;"

# 3. Verify cluster is healthy
kubectl get pods -n supabase
kubectl get pods -n authentik

---

Phase 0: PostgreSQL Setup (One-Time)

Create the auth function PgBouncer uses to look up user credentials:

kubectl exec -n supabase postgres-0 -- psql -U postgres -c "
-- Create auth lookup function for PgBouncer (SCRAM-SHA-256 compatible)
CREATE OR REPLACE FUNCTION pgbouncer_get_auth(p_usename TEXT)
RETURNS TABLE(usename name, passwd text) AS \\\$\\\$
SELECT s.usename, s.passwd::text
FROM pg_shadow s
WHERE s.usename = p_usename;
\\\$\\\$ LANGUAGE sql SECURITY DEFINER;

-- Create pgbouncer user for auth_query
DO \\\$\\\$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'pgbouncer') THEN
        CREATE USER pgbouncer WITH PASSWORD 'your-super-secret-postgres-password';
    END IF;
END \\\$\\\$;

GRANT EXECUTE ON FUNCTION pgbouncer_get_auth(text) TO pgbouncer;
"

---

Phase 1: Deploy PgBouncer

PgBouncer deployment is non-disruptive - existing connections continue working.

# Apply PgBouncer manifests
kubectl apply -f /root/tower-fleet/manifests/supabase/pgbouncer.yaml

# Wait for pod to be ready
kubectl wait --for=condition=ready pod -l app=pgbouncer -n supabase --timeout=60s

# Verify PgBouncer is running
kubectl get pods -n supabase -l app=pgbouncer
kubectl logs -n supabase -l app=pgbouncer --tail=20

# Test connectivity
kubectl exec -n supabase pgbouncer-<pod-id> -- psql -h localhost -p 6432 -U postgres -c "SHOW pools;"

Expected output from SHOW pools:

 database  |   user    | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait
-----------+-----------+-----------+------------+-----------+---------+---------+-----------+----------+---------
 postgres  | postgres  |         0 |          0 |         0 |       0 |       0 |         0 |        0 |       0

---

Phase 2: Migrate Authentik (Heaviest Consumer)

Authentik is the biggest PostgreSQL consumer (23+ connections). Migrate it first.

Important: Prepared Statements Support

PgBouncer 1.21+ is required for Authentik. Authentik's embedded outpost uses prepared statements (via Go's pgx library), which were traditionally incompatible with transaction pooling. PgBouncer 1.21+ added max_prepared_statements to handle this.

Required PgBouncer config (in pgbouncer.ini):

pool_mode = transaction
max_prepared_statements = 100  # Required for Authentik

Without max_prepared_statements, you'll see errors like:

ERROR: prepared statement "stmtcache_..." already exists (SQLSTATE 42P05)

Step 2.1: Update Authentik Helm Values

Edit /root/tower-fleet/manifests/authentik/values.yaml:

authentik:
  postgresql:
    host: pgbouncer.supabase.svc.cluster.local  # Changed from postgres.supabase.svc.cluster.local
    port: 6432                                   # Changed from 5432
    name: postgres
    user: authentik
    password: ""  # Set via env from secret

global:
  env:
    # ... existing env vars ...
    # Required for PgBouncer transaction pooling mode
    - name: AUTHENTIK_POSTGRESQL__DISABLE_SERVER_SIDE_CURSORS
      value: "true"

Note: DISABLE_SERVER_SIDE_CURSORS is required because server-side cursors maintain state across queries, which is incompatible with transaction-based pooling.

Step 2.2: Apply Changes

# Upgrade Authentik with new values
helm upgrade --install authentik authentik/authentik \
  -n authentik \
  -f /root/tower-fleet/manifests/authentik/values.yaml

# Watch pod restart
kubectl get pods -n authentik -w

# Verify Authentik is healthy
kubectl get pods -n authentik
kubectl logs -n authentik -l app.kubernetes.io/name=authentik-server --tail=30

Step 2.3: Verify Connection Pooling

# Check PgBouncer pools (should show authentik connections)
kubectl exec -n supabase -l app=pgbouncer -- psql -h localhost -p 6432 -U postgres -c "SHOW pools;"

# Check PostgreSQL direct connections (should be lower)
kubectl exec -n supabase postgres-0 -- psql -U postgres -c \
  "SELECT usename, count(*) FROM pg_stat_activity GROUP BY usename;"

Expected: Authentik connections now go through PgBouncer, PostgreSQL shows fewer direct connections.

---

Phase 3: Migrate Supabase Services

Supabase internal services (PostgREST, Storage, GoTrue) connect via environment variables.

Step 3.1: Update Supabase ConfigMap

Edit /root/tower-fleet/manifests/supabase/configmap.yaml to add PgBouncer URL:

data:
  # Add PgBouncer connection string
  PGBOUNCER_URL: "postgres://postgres:your-password@pgbouncer.supabase.svc.cluster.local:6432/postgres"

Step 3.2: Update Each Service

PostgREST (postgrest.yaml):

env:
  - name: PGRST_DB_URI
    value: "postgres://authenticator:your-password@pgbouncer.supabase.svc.cluster.local:6432/postgres"

Storage (storage-api.yaml):

env:
  - name: DATABASE_URL
    value: "postgres://postgres:your-password@pgbouncer.supabase.svc.cluster.local:6432/postgres"

GoTrue (gotrue.yaml):

env:
  - name: GOTRUE_DB_DATABASE_URL
    value: "postgres://postgres:your-password@pgbouncer.supabase.svc.cluster.local:6432/postgres"

Step 3.3: Apply and Verify

# Apply updated manifests
kubectl apply -f /root/tower-fleet/manifests/supabase/

# Restart services to pick up new config
kubectl rollout restart deployment -n supabase rest
kubectl rollout restart deployment -n supabase storage
kubectl rollout restart deployment -n supabase gotrue

# Verify all pods healthy
kubectl get pods -n supabase

---

Phase 4: Verify Migration

Connection Count Check

# PgBouncer pools (should show activity)
kubectl exec -n supabase $(kubectl get pod -n supabase -l app=pgbouncer -o jsonpath='{.items[0].metadata.name}') \
  -- psql -h localhost -p 6432 -U postgres -c "SHOW pools;"

# Direct PostgreSQL connections (should be minimal)
kubectl exec -n supabase postgres-0 -- psql -U postgres -c \
  "SELECT usename, count(*) FROM pg_stat_activity GROUP BY usename;"

Expected results: - PgBouncer pools: Multiple active pools for postgres, authentik users - PostgreSQL direct: Only internal processes (pg_cron, background workers) + PgBouncer connections

Simulate Connection Storm

To verify PgBouncer handles reconnection storms:

# Scale down all consumers
kubectl scale deployment -n authentik authentik-server --replicas=0
kubectl scale deployment -n authentik authentik-worker --replicas=0

# Wait for connections to drop
sleep 10

# Scale back up simultaneously
kubectl scale deployment -n authentik authentik-server --replicas=1
kubectl scale deployment -n authentik authentik-worker --replicas=1

# Watch for CrashLoopBackOff (should NOT occur with PgBouncer)
kubectl get pods -n authentik -w

---

Troubleshooting

PgBouncer Pod Won't Start

# Check logs
kubectl logs -n supabase -l app=pgbouncer

# Common issues:
# - userlist.txt format error (must be "user" "md5hash")
# - pgbouncer.ini syntax error
# - Wrong MD5 hash format

Apps Can't Connect Through PgBouncer

# Test from within cluster
kubectl run -n supabase psql-test --rm -it --image=postgres:15 -- \
  psql "postgres://postgres:your-password@pgbouncer.supabase.svc.cluster.local:6432/postgres" \
  -c "SELECT 1"

# Check PgBouncer stats
kubectl exec -n supabase -l app=pgbouncer -- psql -h localhost -p 6432 -U postgres -c "SHOW stats;"

Connection Limit Exceeded

If you still hit connection limits:

1. Check max_db_connections in pgbouncer.ini (currently 50)
2. Increase if needed, but remember PostgreSQL max is 100
3. Consider reducing default_pool_size per user

# Edit configmap
kubectl edit configmap -n supabase pgbouncer-config

# Restart PgBouncer to apply
kubectl rollout restart deployment -n supabase pgbouncer

Prepared Statement Errors (Authentik)

Symptom: Authentik forward auth returns 500 errors, white screen after login. Logs show:

ERROR: prepared statement "stmtcache_..." already exists (SQLSTATE 42P05)
failed to save session

Cause: Authentik's embedded outpost uses Go's pgx library which uses prepared statements. Transaction pooling reuses connections, and prepared statements from previous sessions conflict.

Solution (requires PgBouncer 1.21+):

1. Enable prepared statement support in PgBouncer:

   # In pgbouncer.ini [pgbouncer] section
   max_prepared_statements = 100
   

2. Add Authentik env var to disable server-side cursors:

   # In authentik values.yaml global.env
   - name: AUTHENTIK_POSTGRESQL__DISABLE_SERVER_SIDE_CURSORS
     value: "true"
   

3. Apply changes:

   kubectl apply -f /root/tower-fleet/manifests/supabase/pgbouncer.yaml
   kubectl rollout restart deployment -n supabase pgbouncer
   helm upgrade authentik authentik/authentik -n authentik -f values.yaml
   

Verify fix:

# Should return 401 (not 500)
curl -s -H "Host: radarr.bogocat.com" http://10.89.97.220/outpost.goauthentik.io/auth/nginx

---

Rollback Procedure

If PgBouncer causes issues, rollback by reverting connection strings:

Authentik Rollback

# In values.yaml, revert to:
authentik:
  postgresql:
    host: postgres.supabase.svc.cluster.local
    port: 5432

helm upgrade authentik authentik/authentik -n authentik -f values.yaml

Supabase Rollback

Revert DATABASE_URL and PGRST_DB_URI to use postgres.supabase:5432.

---

Monitoring

PgBouncer Admin Commands

# Connect to PgBouncer admin
kubectl exec -it -n supabase $(kubectl get pod -n supabase -l app=pgbouncer -o jsonpath='{.items[0].metadata.name}') \
  -- psql -h localhost -p 6432 -U postgres pgbouncer

# Useful commands:
SHOW pools;      # Active pools and connections
SHOW stats;      # Query stats
SHOW clients;    # Connected clients
SHOW servers;    # Backend PostgreSQL connections
SHOW config;     # Current configuration

Key Metrics to Watch

• cl_active: Active client connections (should be < 200)
• sv_active: Active server connections to PostgreSQL (should be < 50)
• cl_waiting: Clients waiting for connection (should be 0 normally)
• maxwait: Maximum time a client waited (should be < 1 second)

---

Related Documentation

• Host Shutdown Procedure
• Post-Reboot Recovery
• Roadmap - PgBouncer Item