Pgbouncer Connection Pooling

pg_databases:
  - name: mydb                # Added to connection pool by default
    pool_mode: transaction    # Database-level pool mode
    pool_size: 64             # Default pool size
  - name: internal
    pgbouncer: false          # Excluded from connection pool

pg_users:
  - name: dbuser_app
    password: DBUser.App
    pgbouncer: true           # Add to connection pool user list
    pool_mode: transaction    # User-level pool mode

# Edit config
$ vim /etc/pgbouncer/pgbouncer.ini

# Reload via systemctl
$ sudo systemctl reload pgbouncer

# Reload as pg_dbsu / postgres user
$ pgb -c "RELOAD;"

alias pgb="psql -p 6432 -d pgbouncer -U postgres"

$ pgb
pgbouncer=# SHOW POOLS;
pgbouncer=# SHOW CLIENTS;
pgbouncer=# SHOW SERVERS;

PAUSE [db];           -- Pause specified database, or all if not specified

$ pgb -c "PAUSE mydb;"        # Pause mydb database
$ pgb -c "PAUSE;"             # Pause all databases

pgbouncer=# SHOW DATABASES;
   name   |   host    | port | database | ... | paused | disabled
----------+-----------+------+----------+-----+--------+----------
 mydb     | /var/run  | 5432 | mydb     | ... |      1 |        0

RESUME [db];          -- Resume specified database, or all if not specified

$ pgb -c "RESUME mydb;"       # Resume mydb database
$ pgb -c "RESUME;"            # Resume all databases

DISABLE db;           -- Disable specified database (database name required)

$ pgb -c "DISABLE mydb;"      # Disable mydb, new connections rejected

ENABLE db;            -- Enable specified database (database name required)

$ pgb -c "ENABLE mydb;"       # Enable mydb, allow new connections

RECONNECT [db];       -- Rebuild server connections for database, or all if not specified

$ pgb -c "RECONNECT mydb;"    # Rebuild mydb server connections
$ pgb -c "RECONNECT;"         # Rebuild all server connections

KILL [db];            -- Kill all connections for database, or all (except admin) if not specified

$ pgb -c "KILL mydb;"         # Force disconnect all mydb connections
$ pgb -c "KILL;"              # Force disconnect all database connections (except admin)

KILL_CLIENT id;       -- Terminate client connection with specified ID

# View client connections
$ pgb -c "SHOW CLIENTS;"

# Terminate specific client (assuming ptr column shows ID 0x1234567890)
$ pgb -c "KILL_CLIENT 0x1234567890;"

SUSPEND;              -- Suspend Pgbouncer

# 1. Suspend current Pgbouncer
$ pgb -c "SUSPEND;"

# 2. Start new Pgbouncer process (with -R option to take over sockets)
$ pgbouncer -R /etc/pgbouncer/pgbouncer.ini

# 3. New process takes over, old process exits automatically

SHUTDOWN;                      -- Immediate shutdown
SHUTDOWN WAIT_FOR_SERVERS;     -- Wait for server connections to release
SHUTDOWN WAIT_FOR_CLIENTS;     -- Wait for clients to disconnect (zero-downtime rolling restart)

$ pgb -c "SHUTDOWN WAIT_FOR_CLIENTS;"   # Graceful shutdown, wait for clients

RELOAD;               -- Reload config files

$ pgb -c "RELOAD;"              # Reload via admin console
$ systemctl reload pgbouncer    # Reload via systemd
$ kill -SIGHUP $(cat /var/run/pgbouncer/pgbouncer.pid)  # Reload via signal

./pgsql.yml -l <cls> -t pgbouncer_reload    # Reload cluster Pgbouncer config

WAIT_CLOSE [db];      -- Wait for server connections to close, or all if not specified

# Complete connection rebuild flow
$ pgb -c "RECONNECT mydb;"
$ pgb -c "WAIT_CLOSE mydb;"    # Wait for old connections to release

# View pool status
$ pgb -c "SHOW POOLS;"

# View client connections
$ pgb -c "SHOW CLIENTS;"

# View server connections
$ pgb -c "SHOW SERVERS;"

# View statistics
$ pgb -c "SHOW STATS;"

# View database status
$ pgb -c "SHOW DATABASES;"

# Reload config via signal
$ kill -SIGHUP $(cat /var/run/pgbouncer/pgbouncer.pid)

# Graceful shutdown via signal
$ kill -SIGTERM $(cat /var/run/pgbouncer/pgbouncer.pid)

# Pause via signal
$ kill -SIGUSR1 $(cat /var/run/pgbouncer/pgbouncer.pid)

# Resume via signal
$ kill -SIGUSR2 $(cat /var/run/pgbouncer/pgbouncer.pid)

# Definition (already in /etc/profile.d/pg-alias.sh)
function pgb-route(){
  local ip=${1-'\/var\/run\/postgresql'}
  sed -ie "s/host=[^[:space:]]\+/host=${ip}/g" /etc/pgbouncer/pgbouncer.ini
  cat /etc/pgbouncer/pgbouncer.ini
}

# Usage: Route traffic to 10.10.10.12
$ pgb-route 10.10.10.12
$ pgb -c "RECONNECT; WAIT_CLOSE;"

# 1. Modify route target
$ pgb-route 10.10.10.12

# 2. Reload config
$ pgb -c "RELOAD;"

# 3. Rebuild connections and wait for old connections to release
$ pgb -c "RECONNECT;"
$ pgb -c "WAIT_CLOSE;"