Pgbouncer Connection Pooling

Overview

Pigsty uses Pgbouncer for PostgreSQL connection pooling. By default, it listens on port 6432 and proxies connections to local PostgreSQL on port 5432.

This component is optional. If you do not need many connections, transaction pooling, or query metrics, you can disable it. You can also connect directly to the database or leave Pgbouncer unused.

User & Database Management

Pigsty automatically manages Pgbouncer users and databases. When you create databases or users, it applies the database configuration and user configuration.

Database Management: By default, Pigsty adds databases from pg_databases to Pgbouncer. To exclude a database, set pgbouncer: false.

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: To add a user from pg_users to the pool user list, explicitly set pgbouncer: true.

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, use pool_reserve and pool_connlimit for database pool fields. These names replace the legacy aliases pool_size_reserve and pool_max_db_conn.

Service Management

By default, the PostgreSQL Primary Service and Replica Service connect to Pgbouncer on port 6432. To connect directly to PostgreSQL, customize pg_services. Alternatively, set pg_default_service_dest to postgres.

Config Management

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

Pigsty automatically manages database.txt and userlist.txt. It updates them when you create databases or users.

You can edit configuration manually, then run RELOAD to apply the changes:

# 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. The default is the postgres operating system user. Pigsty provides the pgb alias for administration:

alias pgb='psql -p6432 -dpgbouncer'

On database nodes, use pgb to connect to the Pgbouncer administration console. You can then run administration commands and monitoring queries.

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

PAUSE

Use PAUSE to pause database connections. Pgbouncer waits for the active transaction or session to finish, according to the pool mode. It then disconnects server connections. New client requests wait 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. This allows new connections and resumes 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 reject all new client connections for a database. Existing connections continue.

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 that DISABLE stopped. The database accepts 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 rebuild server connections gracefully. Pgbouncer closes connections when they return to the pool. It creates new connections as 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 disconnect all client and server connections for a database immediately. Unlike PAUSE, it does not wait for transactions to finish.

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. Get the client ID from SHOW CLIENTS.

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. It 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 stop the Pgbouncer process. The command supports several shutdown modes:

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 read the Pgbouncer configuration files again. It dynamically updates most parameters without a 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 until server connections finish closing. Usually, run it after RECONNECT or RELOAD to verify that old connections have closed.

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 these SHOW commands to monitor 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

Use Unix signals to control Pgbouncer when the administration 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 stores managed database routes in /etc/pgbouncer/database.txt. To move Pgbouncer traffic for one database to another node, edit this file and reload the configuration. Then drain and rebuild the 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 edits only /etc/pgbouncer/pgbouncer.ini. That file includes database.txt but does not contain the generated host= routes for each database. Thus, the function does not change managed database backends. Use the procedure above instead.