1
2
3
4
5
6
7
8
9

Without PgBouncer:
- 1000 connections = ~10GB RAM
- Connection time: 30ms average
- Max sustainable connections: ~500

With PgBouncer:
- 1000 connections = ~2MB RAM
- Connection time: <1ms
- Max sustainable connections: 10,000+

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

┌─────────────────┐
│   Applications  │
│  (Port 6432)    │
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│    PgBouncer    │  ← Connection Pooler
│  - Auth: SCRAM  │
│  - Pool: 25/DB  │
│  - Max: 1000    │
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│   PostgreSQL    │  ← Database Server
│   (Port 5432)   │
│  - Max: 100     │
└─────────────────┘

1
2
3
4
5
6
7
8

# Salt Master Configuration
/etc/salt/master.d/
├── fileserver.conf    # Git fileserver backend
└── gitfs.conf         # Git repository configuration

# Salt Minion (on database server)
/etc/salt/minion.d/
└── minion.conf        # Master connection

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

salt/
├── pillar/
│   ├── common.sls            # Configuration data
│   └── top.sls               # Pillar targeting
├── salt-states/
│   ├── top.sls               # State targeting
│   ├── firewall/
│   │   └── init.sls          # Firewall rules
│   ├── grains/
│   │   └── init.sls          # Role assignment
│   └── pgbouncer/
│       ├── init.sls          # Main state file
│       ├── pgbouncer.ini.j2  # Config template
│       └── pgbouncer.service # Systemd unit

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

# Set server roles based on hostname patterns
set_roles_grain:
  grains.present:
    - name: roles
    - value:
      {% set hostname = grains.get('id', '') | lower %}
      {% set roles = [] %}

      # Database role
      {% if 'dbsrv' in hostname or hostname.startswith('db-') or 'database' in hostname or 'postgres' in hostname %}
        {% do roles.append('database') %}
      {% endif %}

      # Add more role patterns as needed

      {{ roles }}
    - force: True

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

# PostgreSQL configuration
postgresql:
  port: 5432
  firewall_allowed_ips:
    - 10.0.1.10/32 # App server 1
    - 10.0.1.11/32 # App server 2
    - 10.0.2.0/24 # Kubernetes pod network
    - 203.0.113.50/32 # Admin workstation

# PgBouncer configuration
pgbouncer:
  # Network settings
  listen_port: 6432
  listen_addr: "*"

  # Authentication (must match PostgreSQL)
  auth_type: scram-sha-256

  # Pooling strategy
  pool_mode: transaction # transaction | session | statement

  # Connection limits
  max_db_connections: 100 # Max to PostgreSQL per database
  max_client_conn: 1000 # Max client connections
  default_pool_size: 25 # Default pool per user+db
  min_pool_size: 5 # Minimum pooled connections
  reserve_pool_size: 5 # Reserve for immediate use
  reserve_pool_timeout: 5 # Wait time for connection (seconds)

  # Timeouts
  server_idle_timeout: 600 # Close idle server conn (10 min)
  server_lifetime: 3600 # Max server conn lifetime (1 hour)
  client_idle_timeout: 0 # No client timeout
  query_timeout: 0 # No query timeout
  query_wait_timeout: 120 # Max login time (2 minutes)

  # Admin access
  admin_users: postgres
  stats_users: postgres

  # Firewall (optional - defaults to PostgreSQL IPs)
  # firewall_allowed_ips:
  #   - 10.0.1.10/32
  #   - 10.0.1.11/32

1
2
3

base:
  "*":
    - common

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142

# PgBouncer connection pooler for PostgreSQL
# Provides connection pooling to reduce connection overhead

# Install PgBouncer package
pgbouncer-package:
  pkg.installed:
    - name: pgbouncer

# Create system user for PgBouncer
pgbouncer-user:
  user.present:
    - name: pgbouncer
    - system: True
    - home: /var/lib/pgbouncer
    - shell: /bin/false
    - createhome: False
    - require:
        - pkg: pgbouncer-package

# Create configuration directory
pgbouncer-config-dir:
  file.directory:
    - name: /etc/pgbouncer
    - user: pgbouncer
    - group: pgbouncer
    - mode: "0750"
    - require:
        - user: pgbouncer-user

# Create log directory
pgbouncer-log-dir:
  file.directory:
    - name: /var/log/pgbouncer
    - user: pgbouncer
    - group: pgbouncer
    - mode: "0755"
    - require:
        - user: pgbouncer-user

# Create runtime directory
pgbouncer-runtime-dir:
  file.directory:
    - name: /var/run/pgbouncer
    - user: pgbouncer
    - group: pgbouncer
    - mode: "0755"
    - require:
        - user: pgbouncer-user

# Persist runtime directory across reboots
pgbouncer-tmpfiles-config:
  file.managed:
    - name: /etc/tmpfiles.d/pgbouncer.conf
    - contents: |
        # PgBouncer runtime directory
        d /var/run/pgbouncer 0755 pgbouncer pgbouncer -
    - user: root
    - group: root
    - mode: "0644"
    - require:
        - user: pgbouncer-user

# Deploy PgBouncer configuration
pgbouncer-config:
  file.managed:
    - name: /etc/pgbouncer/pgbouncer.ini
    - source: salt://pgbouncer/pgbouncer.ini.j2
    - template: jinja
    - user: pgbouncer
    - group: pgbouncer
    - mode: "0640"
    - require:
        - file: pgbouncer-config-dir

# Generate userlist from PostgreSQL
pgbouncer-generate-userlist:
  cmd.run:
    - name: |
        cat > /etc/pgbouncer/userlist.txt << 'EOF'
        ;; PgBouncer userlist.txt - Generated automatically
        ;; Format: "username" "password_hash"

        EOF
        # Extract password hashes from PostgreSQL
        sudo -u postgres psql -t -A -c "SELECT '\"' || rolname || '\" \"' || rolpassword || '\"' FROM pg_authid WHERE rolpassword IS NOT NULL;" >> /etc/pgbouncer/userlist.txt
        chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt
        chmod 640 /etc/pgbouncer/userlist.txt
    - require:
        - file: pgbouncer-config-dir
    - unless: test -f /etc/pgbouncer/userlist.txt

# Update userlist when passwords change
pgbouncer-update-userlist:
  cmd.run:
    - name: |
        sudo -u postgres psql -t -A -c "SELECT '\"' || rolname || '\" \"' || rolpassword || '\"' FROM pg_authid WHERE rolpassword IS NOT NULL;" > /tmp/userlist_new.txt
        cat > /etc/pgbouncer/userlist.txt << 'EOF'
        ;; PgBouncer userlist.txt - Generated automatically
        ;; Format: "username" "password_hash"

        EOF
        cat /tmp/userlist_new.txt >> /etc/pgbouncer/userlist.txt
        rm -f /tmp/userlist_new.txt
        chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt
        chmod 640 /etc/pgbouncer/userlist.txt
    - onlyif: test -f /etc/pgbouncer/userlist.txt
    - require:
        - cmd: pgbouncer-generate-userlist

# Deploy systemd service file
pgbouncer-service-file:
  file.managed:
    - name: /etc/systemd/system/pgbouncer.service
    - source: salt://pgbouncer/pgbouncer.service
    - user: root
    - group: root
    - mode: "0644"
    - require:
        - pkg: pgbouncer-package

# Reload systemd when service changes
pgbouncer-systemd-reload:
  cmd.run:
    - name: systemctl daemon-reload
    - onchanges:
        - file: pgbouncer-service-file

# Enable and start PgBouncer service
pgbouncer-service:
  service.running:
    - name: pgbouncer
    - enable: True
    - require:
        - file: pgbouncer-config
        - file: pgbouncer-runtime-dir
        - file: pgbouncer-tmpfiles-config
        - cmd: pgbouncer-generate-userlist
        - file: pgbouncer-service-file
        - cmd: pgbouncer-systemd-reload
    - watch:
        - file: pgbouncer-config
        - cmd: pgbouncer-update-userlist

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131

;; PgBouncer configuration file
;; Generated by Salt - DO NOT EDIT MANUALLY

[databases]
; Database definitions
; Format: dbname = host=hostname port=port dbname=dbname

; Default database (postgres)
postgres = host=127.0.0.1 port={{ pillar.get('postgresql', {}).get('port', 5432) }} dbname=postgres

; Wildcard to allow any database connection through PgBouncer
; Applications can connect to any database that exists in PostgreSQL
* = host=127.0.0.1 port={{ pillar.get('postgresql', {}).get('port', 5432) }}

[pgbouncer]
;;;
;;; Administrative settings
;;;

logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

;;;
;;; Where to wait for clients
;;;

; IP address or * to listen on all interfaces
listen_addr = {{ pillar.get('pgbouncer', {}).get('listen_addr', '*') }}
listen_port = {{ pillar.get('pgbouncer', {}).get('listen_port', 6432) }}

; Unix socket is also used for local connections
unix_socket_dir = /var/run/pgbouncer

;;;
;;; Authentication settings
;;;

; Authentication type: plain, md5, scram-sha-256, or trust
auth_type = {{ pillar.get('pgbouncer', {}).get('auth_type', 'scram-sha-256') }}
auth_file = /etc/pgbouncer/userlist.txt

;;;
;;; Connection pooling settings
;;;

; Pooling mode: session, transaction, or statement
; session - client stays connected for entire session
; transaction - client can use different connections (recommended)
; statement - most aggressive, connections returned after each statement
pool_mode = {{ pillar.get('pgbouncer', {}).get('pool_mode', 'transaction') }}

; Maximum number of server connections to allow per database
max_db_connections = {{ pillar.get('pgbouncer', {}).get('max_db_connections', 100) }}

; Maximum number of client connections to allow
max_client_conn = {{ pillar.get('pgbouncer', {}).get('max_client_conn', 1000) }}

; Default pool size (connections per user+database pair)
default_pool_size = {{ pillar.get('pgbouncer', {}).get('default_pool_size', 25) }}

; Minimum number of pooled connections
min_pool_size = {{ pillar.get('pgbouncer', {}).get('min_pool_size', 5) }}

; How long to keep released connections available for immediate re-use
reserve_pool_size = {{ pillar.get('pgbouncer', {}).get('reserve_pool_size', 5) }}

; Maximum time a client connection can wait for a server connection (seconds)
reserve_pool_timeout = {{ pillar.get('pgbouncer', {}).get('reserve_pool_timeout', 5) }}

;;;
;;; Connection limits
;;;

; Close server connection if it has been idle for this many seconds
server_idle_timeout = {{ pillar.get('pgbouncer', {}).get('server_idle_timeout', 600) }}

; Close server connection if it has been connected for this many seconds
server_lifetime = {{ pillar.get('pgbouncer', {}).get('server_lifetime', 3600) }}

; Close client connection if it has been idle for this many seconds
client_idle_timeout = {{ pillar.get('pgbouncer', {}).get('client_idle_timeout', 0) }}

; Cancel currently running query if client has been waiting too long
query_timeout = {{ pillar.get('pgbouncer', {}).get('query_timeout', 0) }}

; Close client connection if login takes more than this many seconds
query_wait_timeout = {{ pillar.get('pgbouncer', {}).get('query_wait_timeout', 120) }}

;;;
;;; TLS settings (optional)
;;;

; TLS is disabled by default
; Uncomment and configure if you need TLS
;client_tls_sslmode = prefer
;client_tls_ca_file = /etc/pgbouncer/root.crt
;client_tls_cert_file = /etc/pgbouncer/server.crt
;client_tls_key_file = /etc/pgbouncer/server.key

;;;
;;; Logging
;;;

; Log level: error, warning, info, debug
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

; Log successful logins
verbose = 0

;;;
;;; Console access control
;;;

; Users allowed to access admin console
admin_users = {{ pillar.get('pgbouncer', {}).get('admin_users', 'postgres') }}

; Users allowed to view statistics but not change settings
stats_users = {{ pillar.get('pgbouncer', {}).get('stats_users', 'postgres') }}

;;;
;;; Connection sanity checks, timeouts
;;;

; Check server_check_query on connect and after check_delay seconds
server_check_query = SELECT 1
server_check_delay = 30

; Use application_name to identify connections
application_name_add_host = 1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

[Unit]
Description=PgBouncer - PostgreSQL connection pooler
Documentation=man:pgbouncer(1)
After=network.target postgresql.service
Wants=postgresql.service

[Service]
Type=forking
User=pgbouncer
Group=pgbouncer
PIDFile=/var/run/pgbouncer/pgbouncer.pid

# Create runtime directory (ExecStartPre runs as root with + prefix)
RuntimeDirectory=pgbouncer
RuntimeDirectoryMode=0755
ExecStartPre=+/usr/bin/mkdir -p /var/run/pgbouncer
ExecStartPre=+/usr/bin/chown pgbouncer:pgbouncer /var/run/pgbouncer
ExecStartPre=+/usr/bin/chmod 0755 /var/run/pgbouncer

# PgBouncer configuration
ExecStart=/usr/bin/pgbouncer -d /etc/pgbouncer/pgbouncer.ini
ExecReload=/bin/kill -HUP $MAINPID

# Security settings
NoNewPrivileges=true
PrivateTmp=true
ProtectSystem=strict
ProtectHome=true
ReadWritePaths=/var/log/pgbouncer /var/run/pgbouncer

# Resource limits
LimitNOFILE=65536

# Restart policy
Restart=on-failure
RestartSec=5s

[Install]
WantedBy=multi-user.target

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

# Database: restrict PgBouncer port to whitelisted IPs
{% set pgbouncer_allowed_ips = pillar.get('pgbouncer', {}).get('firewall_allowed_ips', pg_allowed_ips) %}
{% set pgbouncer_port = pillar.get('pgbouncer', {}).get('listen_port', 6432) %}

{% if pgbouncer_allowed_ips %}
# Create firewall rules for each whitelisted IP for PgBouncer port
{% for ip in pgbouncer_allowed_ips %}
pgbouncer-{{ pgbouncer_port }}-{{ ip | replace('.', '-') | replace('/', '-') }}:
  cmd.run:
    - name: firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="{{ ip }}" port port="{{ pgbouncer_port }}" protocol="tcp" accept'
    - unless: firewall-cmd --list-rich-rules | grep -q "{{ pgbouncer_port }}.*{{ ip | replace('/', '\/') }}"
    - require:
      - service: firewalld-service
{% endfor %}

# Reload firewall after adding PgBouncer rules
firewall-reload-after-pgbouncer-rules:
  cmd.run:
    - name: firewall-cmd --reload
    - onchanges:
{% for ip in pgbouncer_allowed_ips %}
      - cmd: pgbouncer-{{ pgbouncer_port }}-{{ ip | replace('.', '-') | replace('/', '-') }}
{% endfor %}
{% else %}
# INFO: PgBouncer will use same IPs as PostgreSQL by default
pgbouncer-using-postgresql-ips:
  test.configurable_test_state:
    - name: INFO - PgBouncer using PostgreSQL allowed IPs
    - changes: False
    - result: True
    - comment: |
        INFO: No separate IPs configured in pillar.pgbouncer.firewall_allowed_ips
        PgBouncer port {{ pgbouncer_port }} uses same IPs as PostgreSQL.
        To configure different IPs for PgBouncer, add them to pillar/common.sls
{% endif %}
{% endif %}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

base:
  "*":
    - grains # Sets roles based on hostname
    - hardening # SSH hardening, system updates
    - firewall # Firewall rules (role-based)
    - users # User management

  # Database servers (role: database)
  "roles:database":
    - match: grain
    - pgbouncer # PostgreSQL connection pooler

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# Add all files to git
git add salt/

# Commit changes
git commit -m "Add PgBouncer configuration with SaltStack

- PgBouncer state for installation and configuration
- Systemd service with proper PID tracking
- Firewall rules for port 6432
- Pillar configuration for connection pooling
- Automatic user synchronization from PostgreSQL"

# Push to repository
git push origin main

1
2
3
4
5

# Update gitfs cache to pull latest changes
sudo salt-run fileserver.update

# Verify pgbouncer state is available
sudo salt-run fileserver.file_list | grep pgbouncer

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# Refresh pillar data
sudo salt 'db*' saltutil.refresh_pillar

# Test what will be applied (dry run)
sudo salt 'db*' state.apply test=True

# Apply pgbouncer state
sudo salt 'db*' state.apply pgbouncer

# Or apply all states
sudo salt 'db*' state.apply

1
2
3
4
5
6
7

# Verify pgbouncer is running
sudo salt 'db*' cmd.run 'systemctl status pgbouncer'

# Expected output:
# ● pgbouncer.service - PgBouncer - PostgreSQL connection pooler
#      Loaded: loaded (/etc/systemd/system/pgbouncer.service; enabled)
#      Active: active (running) since...

1
2
3
4
5
6

# Check if PgBouncer is listening
sudo salt 'db*' cmd.run 'ss -tlnp | grep 6432'

# Expected output:
# LISTEN 0      128          0.0.0.0:6432       0.0.0.0:*    users:(("pgbouncer",pid=XXXXX,fd=7))
# LISTEN 0      128             [::]:6432          [::]:*    users:(("pgbouncer",pid=XXXXX,fd=8))

1
2
3
4

# Verify firewall rules
sudo salt 'db*' cmd.run 'firewall-cmd --list-rich-rules | grep 6432'

# Should show rules for each allowed IP

1
2
3
4
5
6
7
8

# Connect to PgBouncer
psql -h db-server.example.com -p 6432 -U your_user -d your_database

# Check connection info
SELECT version();

# Exit
\q

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

# Connect as admin user
psql -h db-server.example.com -p 6432 -U postgres -d pgbouncer

# View pool statistics
SHOW POOLS;

# View database statistics
SHOW DATABASES;

# View client statistics
SHOW CLIENTS;

# View server statistics
SHOW SERVERS;

# View configuration
SHOW CONFIG;

1
2
3
4
5

# View PgBouncer logs
sudo salt 'db*' cmd.run 'tail -50 /var/log/pgbouncer/pgbouncer.log'

# Monitor in real-time
sudo salt 'db*' cmd.run 'tail -f /var/log/pgbouncer/pgbouncer.log'

1
2
3

# In pillar/common.sls
pgbouncer:
  auth_type: scram-sha-256 # More secure than md5

1
2
3
4
5

# On PostgreSQL server, set secure passwords
sudo -u postgres psql
ALTER USER username WITH PASSWORD 'strong_random_password';

# PgBouncer will automatically sync the password hash

1
2
3
4
5
6

# Restrict to specific IPs only
postgresql:
  firewall_allowed_ips:
    - 10.0.1.10/32 # Specific app server
    - 10.0.2.0/24 # Kubernetes pod network
    # Never use 0.0.0.0/0 in production!

1
2
3
4
5

# In pgbouncer.ini.j2
client_tls_sslmode = require
client_tls_ca_file = /etc/pgbouncer/ca.crt
client_tls_cert_file = /etc/pgbouncer/server.crt
client_tls_key_file = /etc/pgbouncer/server.key

1
2
3
4
5
6
7
8

# Check configuration file permissions
ls -la /etc/pgbouncer/
# pgbouncer.ini should be 0640 (owner: pgbouncer)
# userlist.txt should be 0640 (owner: pgbouncer)

# Check log directory
ls -la /var/log/pgbouncer/
# Should be owned by pgbouncer:pgbouncer

1
2
3
4
5
6
7

# Check SELinux status
getenforce

# Allow PgBouncer to bind to port 6432
semanage port -a -t postgresql_port_t -p tcp 6432

# Or create custom SELinux policy if needed

1
2
3
4
5

Optimal pool size = (Core count × 2) + effective_spindle_count
For 8-core server with SSD: (8 × 2) + 1 = 17

Conservative starting point: 25
Maximum recommended: 100

1
2

pgbouncer:
  pool_mode: transaction

1
2

pgbouncer:
  pool_mode: session

1
2

pgbouncer:
  pool_mode: statement

1
2
3
4
5
6
7
8
9

pgbouncer:
  # Server timeouts
  server_idle_timeout: 600 # 10 minutes - closes idle connections
  server_lifetime: 3600 # 1 hour - recycles old connections

  # Client timeouts
  client_idle_timeout: 0 # Never timeout idle clients
  query_timeout: 0 # No query timeout (let PostgreSQL handle)
  query_wait_timeout: 120 # 2 minutes - max time to wait for connection

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

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

-- Pool utilization
SHOW POOLS;
-- Watch: cl_active, sv_active, sv_idle

-- Wait time (should be near 0)
SHOW STATS;
-- Watch: avg_wait_time, max_wait_time

-- Configuration audit
SHOW CONFIG;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

# Check detailed logs
journalctl -u pgbouncer -n 100 --no-pager

# Test configuration syntax
sudo -u pgbouncer /usr/bin/pgbouncer -v /etc/pgbouncer/pgbouncer.ini

# Check runtime directory exists
ls -la /var/run/pgbouncer/

# Manually create if missing
sudo mkdir -p /var/run/pgbouncer
sudo chown pgbouncer:pgbouncer /var/run/pgbouncer

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

# Verify userlist.txt exists and has content
cat /etc/pgbouncer/userlist.txt

# Should show password hashes for users
# "username" "SCRAM-SHA-256$..."

# Regenerate userlist
sudo -u postgres psql -t -A -c \
  "SELECT '\"' || rolname || '\" \"' || rolpassword || '\"' \
   FROM pg_authid WHERE rolpassword IS NOT NULL;" \
  > /tmp/userlist.txt

sudo mv /tmp/userlist.txt /etc/pgbouncer/userlist.txt
sudo chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt
sudo chmod 640 /etc/pgbouncer/userlist.txt

# Reload PgBouncer
sudo systemctl reload pgbouncer

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# Check firewall rules
firewall-cmd --list-rich-rules | grep 6432

# Test from client
telnet db-server.example.com 6432

# Check connection limits
sudo -u postgres psql -h localhost -p 6432 -d pgbouncer
SHOW CONFIG;
-- Look at max_client_conn, max_db_connections

1
2
3
4
5
6
7
8

# Increase pool size
# Edit pillar/common.sls
pgbouncer:
  default_pool_size: 50  # Increase from 25
  max_db_connections: 200  # Increase from 100

# Apply changes
sudo salt 'db*' state.apply pgbouncer

1
2
3
4
5
6
7
8

# Prometheus exporter configuration example
pgbouncer_exporter:
  metrics:
    - cl_active # Active client connections
    - sv_active # Active server connections
    - sv_idle # Idle server connections
    - avg_wait_time # Average wait for connection
    - total_wait_time # Total time clients waited

1
2
3
4
5
6
7
8

alerts:
  - name: PgBouncerHighWaitTime
    condition: avg_wait_time > 100ms
    severity: warning

  - name: PgBouncerPoolSaturation
    condition: sv_active / max_db_connections > 0.9
    severity: critical

1
2
3
4
5
6
7

# Review logs for errors
sudo grep ERROR /var/log/pgbouncer/pgbouncer.log

# Check statistics
psql -h localhost -p 6432 -U postgres -d pgbouncer -c "SHOW STATS;"

# Verify all expected databases are accessible

1
2
3
4
5
6
7

# Review and update firewall rules
sudo firewall-cmd --list-rich-rules | grep 6432

# Audit user access
sudo cat /etc/pgbouncer/userlist.txt

# Test failover procedure

1
2
3
4
5
6
7
8
9

# Always use Salt for changes
# 1. Update pillar/common.sls
# 2. Commit to git
# 3. Apply via Salt

sudo salt 'db*' state.apply pgbouncer


# PgBouncer will reload automatically via watch directive

1
2
3
4
5
6
7

-- On PostgreSQL
CREATE USER newuser WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE your_db TO newuser;

-- PgBouncer will sync automatically on next state.apply
-- Or manually trigger update:
sudo salt 'db*' cmd.run "bash -c 'sudo -u postgres psql -t -A -c \"SELECT ...\" > /etc/pgbouncer/userlist.txt && systemctl reload pgbouncer'"

1
2
3
4
5
6
7
8
9

# Pillar configuration
pgbouncer_nodes:
  - db-primary.example.com
  - db-replica-1.example.com
  - db-replica-2.example.com

# Point PgBouncer to local PostgreSQL
[databases]
* = host=127.0.0.1 port=5432

1
2
3
4
5

# Application health check endpoint
curl -f http://db-server:9127/metrics || exit 1

# Or simple connection test
psql -h localhost -p 6432 -U health_check -d postgres -c "SELECT 1" > /dev/null

1
2
3
4
5

# Configuration is in git, but also backup runtime state
tar -czf pgbouncer-backup-$(date +%Y%m%d).tar.gz \
  /etc/pgbouncer/ \
  /etc/systemd/system/pgbouncer.service \
  /etc/tmpfiles.d/pgbouncer.conf

1
2
3
4
5
6

# PgBouncer state is idempotent
# To rebuild from scratch:
sudo salt 'db-new-server' state.apply pgbouncer

# Verifies deployment
sudo salt 'db-new-server' cmd.run 'systemctl status pgbouncer'

1
2
3
4
5
6

# Monitor CPU usage
sudo salt 'db*' cmd.run 'top -b -n 1 | grep pgbouncer'

# If pgbouncer CPU > 80%, consider:
# 1. Multiple PgBouncer instances (different ports)
# 2. Horizontal scaling (more servers)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

┌─────────────┐     ┌─────────────┐
│   App #1    │     │   App #2    │
│ + PgBouncer │     │ + PgBouncer │
└──────┬──────┘     └──────┬──────┘
       │                   │
       └────────┬──────────┘
                ▼
       ┌─────────────────┐
       │   PostgreSQL    │
       │   (Port 5432)   │
       └─────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

┌─────────┐
│   HAProxy/    │
│   Nginx       │
└───────┬───────┘
        │
    ┌───┴───┬───────┐
    ▼       ▼       ▼
┌────────┐ ┌────────┐ ┌────────┐
│PgBouncer│ │PgBouncer│ │PgBouncer│
│  #1     │ │  #2     │ │  #3     │
└────┬───┘ └────┬───┘ └────┬───┘
     └──────────┼──────────┘
                ▼
         ┌─────────────┐
         │ PostgreSQL  │
         └─────────────┘

1
2
3
4
5
6

# Deploy PgBouncer without changing applications
sudo salt 'db*' state.apply pgbouncer

# Both ports available:
# - PostgreSQL: 5432 (existing)
# - PgBouncer: 6432 (new)

1
2
3
4
5
6

# Update application connection strings
# Old: postgresql://db-server:5432/mydb
# New: postgresql://db-server:6432/mydb

# Test with single application instance
# Monitor logs for issues

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

# After all applications migrated:
# - Update firewall to restrict 5432 (admin only)
# - Keep 6432 open for applications

# Pillar update
postgresql:
  firewall_allowed_ips:
    - 10.0.0.100/32  # Admin workstation only

pgbouncer:
  firewall_allowed_ips:
    - 10.0.1.0/24    # Application servers

1
2
3
4
5

Scenario: 1000 concurrent connections
- PostgreSQL RAM: 10GB (10MB × 1000)
- Connection overhead: 30ms average
- Max sustainable: ~500 connections
- Database server: 32GB RAM, $400/month

1
2
3
4
5
6
7

Scenario: 1000 concurrent connections
- PgBouncer RAM: 2MB (2KB × 1000)
- PostgreSQL RAM: 1GB (10MB × 100 pooled)
- Connection overhead: <1ms
- Max sustainable: 10,000+ connections
- Database server: 16GB RAM, $200/month
- Savings: $200/month = $2,400/year

1
2
3
4
5

Setup time: 4 hours
Annual savings: $2,400
Payback period: < 1 week
Performance improvement: 30x faster connections
Scalability: 20x more connections