Pgbouncer Connection Pooling

Overview

Pigsty uses Pgbouncer as PostgreSQL connection pooling middleware, listening on port 6432 by default, proxying access to local PostgreSQL on port 5432.

This is an optional component. If you don’t have massive connections or need transaction pooling and query metrics, you can disable it, connect directly to the database, or keep it unused.

User & Database Management

Pgbouncer users and databases are auto-managed by Pigsty, applying database config and user config when creating databases and creating users.

Database Management: Databases defined in pg_databases are auto-added to Pgbouncer by default. Set pgbouncer: false to exclude specific databases.

pg_databases:
  - name: mydb                # Added to connection pool by default
    pool_auth_user: dbuser_meta # Optional, auth query user (with pgbouncer_auth_query)
    pool_mode: transaction    # Database-level pool mode
    pool_size: 50             # Default pool size
    pool_reserve: 30          # Reserve pool size
    pool_size_min: 0          # Minimum pool size
    pool_connlimit: 100       # Max database connections
  - name: internal
    pgbouncer: false          # Excluded from connection pool

User Management: Users defined in pg_users need explicit pgbouncer: true to be added to connection pool user list.

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

Since Pigsty v4.1.0, database pool fields are unified as pool_reserve and pool_connlimit; legacy aliases pool_size_reserve / pool_max_db_conn are converged.

Service Management

In Pigsty, PostgreSQL cluster Primary Service and Replica Service default to Pgbouncer port 6432. To bypass connection pool and access PostgreSQL directly, customize pg_services, or set pg_default_service_dest to postgres.

Config Management

Pgbouncer config files are in /etc/pgbouncer/, generated and managed by Pigsty:

Pigsty auto-manages database.txt and userlist.txt, updating them when creating databases or creating users.

You can manually edit config then RELOAD to apply:

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

# Reload via systemctl
$ sudo systemctl reload pgbouncer

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

Pool Management

Pgbouncer runs as the same dbsu as PostgreSQL, default postgres OS user. Pigsty provides pgb alias for easy management:

alias pgb='psql -p6432 -dpgbouncer'

Use pgb on database nodes to connect to Pgbouncer admin console for management commands and monitoring queries.

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

PAUSE

Use PAUSE to pause database connections. Pgbouncer waits for active txn/session to complete based on pool mode, then disconnects server connections. New client requests are blocked until RESUME.

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

Typical use cases:

• Online backend database switch (e.g., update connection target after switchover)
• Maintenance operations requiring all connections disconnected
• Combined with SUSPEND for Pgbouncer online restart

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

After pause, SHOW DATABASES shows paused status:

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

RESUME

Use RESUME to restore databases paused by PAUSE, KILL, or SUSPEND, allowing new connections and resuming normal service.

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

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

DISABLE

Use DISABLE to disable a database, rejecting all new client connection requests. Existing connections are unaffected.

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

Typical use cases:

• Temporarily offline a database for maintenance
• Block new connections for safe database migration
• Gradually decommission a database being removed

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

ENABLE

Use ENABLE to enable a database previously disabled by DISABLE, accepting new client connections again.

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

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

RECONNECT

Use RECONNECT to gracefully rebuild server connections. Pgbouncer closes connections when released back to pool, creating new ones when needed.

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

Typical use cases:

• Refresh connections after backend database IP change
• Reroute traffic after switchover
• Rebuild connections after DNS update

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

After RECONNECT, use WAIT_CLOSE to wait for old connections to fully release.

KILL

Use KILL to immediately disconnect all client and server connections for a database. Unlike PAUSE, KILL doesn’t wait for transaction completion - forces immediate disconnect.

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)

After KILL, new connections are blocked until RESUME.

KILL_CLIENT

Use KILL_CLIENT to terminate a specific client connection. Client ID can be obtained from SHOW CLIENTS output.

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

Use SUSPEND to suspend Pgbouncer. Flushes all socket buffers and stops listening until RESUME.

SUSPEND;              -- Suspend Pgbouncer

SUSPEND is mainly for Pgbouncer online restart (zero-downtime upgrade):

# 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

Use SHUTDOWN to shut down Pgbouncer process. Multiple shutdown modes supported:

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

Use RELOAD to reload Pgbouncer config files. Dynamically updates most config params without process restart.

RELOAD;               -- Reload config files

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

Pigsty provides playbook task to reload Pgbouncer config:

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

WAIT_CLOSE

Use WAIT_CLOSE to wait for server connections to finish closing. Typically used after RECONNECT or RELOAD to ensure old connections are fully released.

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

Monitoring

Pgbouncer provides rich SHOW commands for monitoring pool status:

Common monitoring examples:

# 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;"

For more monitoring command details, see Pgbouncer official docs.

Unix Signals

Pgbouncer supports Unix signal control, useful when admin console is unavailable:

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

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

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

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

Traffic Switching

Pigsty-managed database routes live in /etc/pgbouncer/database.txt. To move one database’s Pgbouncer traffic to another node, edit that file, reload the configuration, then drain and rebuild existing server connections:

# 1. Change only mydb's backend target to 10.10.10.12
$ sed -i -E '/^mydb[[:space:]]*=/ s#host=[^[:space:]]+#host=10.10.10.12#' /etc/pgbouncer/database.txt

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

# 3. Rebuild this database's connections and wait for old connections to close
$ pgb -c "RECONNECT mydb;"
$ pgb -c "WAIT_CLOSE mydb;"

The pgb-route function currently shipped in the source only edits /etc/pgbouncer/pgbouncer.ini. That file merely includes database.txt and does not contain the generated per-database host= routes, so the function does not change managed database backends. Do not use it in place of the procedure above.