pgbouncer
Manages database connection pools.
Synopsis
pgbouncer [OPTION ...] config.ini OPTION [ -d | --daemon ] [ -R | --restart ] [ -q | --quiet ] [ -v | --verbose ] [ {-u | --user}=username ] pgbouncer [ -V | --version ] | [ -h | --help ]
Description
PgBouncer is a light-weight connection pool manager for Greenplum and PostgreSQL databases. Databases may be on different Greenplum Database clusters or PostgreSQL backends. PgBouncer creates a pool for each database user and database combination. A pooled connection can only be reused for another connection request for the same user and database. The client application connects to the connection pool's host and port instead of the Greenplum Database master host and port. PgBouncer either creates a new database connection for the client or reuses an existing connection. When the client disconnects, the connection is returned to the pool for re-use.
PgBouncer supports the standard connection interface that PostgreSQL and Greenplum Database share. A client requesting a database connection provides the host name and port where PgBouncer is running, as well as the database name, username, and password. PgBouncer looks up the requested database (which may be an alias for the actual database) in its configuration file to find the host name, port, and database name for the database connection. The configuration file entry also determines how to authenticate the user and what database role will be used for the connection—a "forced user" can override the username provided with the client's connection request.
PgBouncer requires an authentication file, a text file that contains a list of users and passwords. Passwords may be either clear text, MD5-encoded, or an LDAP/AD lookup string. You can also set up PgBouncer to query the pg_shadow table in the destination database for users that are not in the authentication file.
- Session pooling – When a client connects, a connection is assigned to it as long as it remains connected. When the client disconnects, the connection is placed back into the pool.
- Transaction pooling – A connection is assigned to a client for the duration of a transaction. When PgBouncer notices the transaction is done, the connection is placed back into the pool. This mode can be used only with applications that do not use features that depend upon a session.
- Statement pooling – Statement pooling is like transaction pooling, but multi-statement transactions are not allowed. This mode is intended to enforce autocommit mode on the client and is targeted for PL/Proxy on PostgreSQL.
A default pool mode can be set for the PgBouncer instance and the mode can be overridden for individual databases and users.
By connecting to a virtual pgbouncer database, you can monitor and manage PgBouncer using SQL-like commands. Configuration parameters can be changed without having to restart PgBouncer, and the configuration file can be reloaded to pick up changes.
PgBouncer does not yet support SSL connections. If you want to encrypt traffic between clients and PgBouncer, you can use stunnel, a free software utility that creates TLS-encrypted tunnels using the OpenSSL cryptography library. See "Securing PgBouncer Connections with stunnel" in the Greenplum Database Administrator Guide for directions.
See the PgBouncer FAQ for additional usage information.
Options
- -d | --daemon
- Run PgBouncer as a daemon (a background process). The default is to run as a foreground process.
- PgBouncer displays start up messages when starting as a daemon. To disable the display of messages add the -q option.
- To shut down a PgBouncer daemon, log in to the administration console and issue the
SHUTDOWN command. Note: This option does not work on Windows servers.
- -R | --restart
- Restart PgBouncer using the specified command line arguments. Non-TLS connections to databases are maintained during restart; TLS connections are dropped.
- If you specify only the -R option, PgBouncer displays log
information on the command line after restart. To restart PgBouncer as a daemon
specify the options -Rd.Note: Works only if the operating system supports Unix sockets and the PgBouncer configuration has no unix_socket_dir. This option does not work on Windows servers.
- -q | --quiet
- Run quietly. Do not display messages on the command line (stdout).
- -v | --verbose
- Increase message verbosity. Display additional messages. Can be used multiple times.
- {-u | --user}=username
- The PgBouncer process assumes the identity of username.
- -V | --version
- Show version and exit.
- -h | --help
- Show help message and exit.
PgBouncer Configuration File
The PgBouncer configuration file (usually pgbouncer.ini) is in the "ini" format. Section names are enclosed in square braces ([ and ]). Lines beginning with ";" or "#" are comments and are ignored. The characters ";" and "#" are not recognized when they appear later in the line.
Synopsys
[databases] db = ... [pgbouncer] ... [users] ...
Description
[databases] Section
The databases section contains key=value pairs, where the key is a database name and the value is a libpq connect-string list of key=value pairs.
- Enclose names in double quotes (")
- Represent a double-quote within an identifier with two consecutive double quote characters
The database name "*" is the fallback database. The value for this key is a connect string for the requested database. Automatically created database entries like these are cleaned up if they remain idle longer then the time specified in autodb_idle_timeout parameter.
%include filename
Location Parameters
The following parameters may be included in the value to specify the location of the database.
- dbname
- The destination database name.
Default: same as the client-side database name.
- host
- The name or IP address of the Greenplum master host. Host names are resolved at connect time. If DNS returns several results, they are used in a round-robin manner. The DNS result is cached and the dns_max_ttl parameter determines when the cache entry expires.
-
Default: not set, means the connection is made through a Unix socket.
- port
- The Greenplum Database master port. Default: 5432
- user, password
- If user= is set, all connections to the destination database are
made with the specified user. This means there will be just one pool for the
database.
If the user= parameter is not set, PgBouncer attempts to log in to the destination database with the user name passed by the client. This means there will be one pool for each user who connects to the database.
- auth_user
- If auth_user is set, any user who is not specified in auth_file is authenticated by querying the pg_shadow table in the database as the auth_user. The auth_user password must be set in the auth_file.
Pool Configuration
- pool_size
- Set maximum size of pools for this database. If not set, the default_pool_size is used.
- connect_query
- Query to be executed after a connection is established, but before allowing the connection to be used by any clients. If the query raises errors, they are logged but ignored otherwise.
- pool_mode
- Set the pool mode for this database. If not set, the default pool_mode is used.
- max_db_connections
-
Set a database-wide maximum number of connections for this database. The total number of connections for all pools for this database will not exceed this value.
Extra Parameters
The following parameters allow setting default parameters on server connections.
Note that since version 1.1 PgBouncer tracks client changes for their values, so their use in pgbouncer.ini is deprecated now.
- client_encoding
- Ask specific client_encoding from server.
- datestyle
- Ask specific datestyle from server.
- timezone
- Ask specific timezone from server.
[pgbouncer] Section
- logfile
- Specifies the location of the log file. The log file is kept open. After log
rotation execute kill -HUP or run the RELOAD;
command in the PgBouncer Administrative Console.
Default: not set.
Note: On Windows machines, the service must be stopped and started. - pidfile
- The name of the pid file. Without a pidfile, PgBouncer cannot be run as a
background process (daemon).
Default: not set.
- listen_addr
- A list of interface addresses where PgBouncer listens for TCP connections. You may
also use *, which means to listen on all interfaces. If not set,
only Unix socket connections are allowed.
Addresses can be specified numerically (IPv4/IPv6) or by name.
Default: not set
- listen_port
- Which port to listen on. Applies to both TCP and Unix sockets.
Default: 6432
- unix_socket_dir
- Specifies location for Unix sockets. Applies to both listening socket and server
connections. If set to an empty string, Unix sockets are disabled. Required for
online reboot (-R option) to work. Note: Not supported on Windows machines.
Default: /tmp
- unix_socket_mode
- Filesystem mode for Unix socket.
Default: 0777
- unix_socket_group
- Group name to use for Unix socket.
Default: not set
- user
- If set, specifies the Unix user to change to after startup. This only works if
PgBouncer is started as root or if user is the same as the current
user. Note: Not supported on Windows machines.
Default: not set
- auth_file
- The name of the file containing the user names and passwords to load. The file
format is the same as the Greenplum Database pg_auth/pg_pwd
file, so this parameter can be set to one of those backend files. See Authentication File Format for
details.
Default: not set.
- auth_hba_file
- HBA configuration file to use when auth_type is hba.
- auth_type
- How to authenticate users.
- hba
- Actual auth type is loaded from auth_hba_file. This allows different authentication methods different access paths. For example Unix socket connections use peer auth method; TCP connections must use TLS.
- cert
- Clients must connect with TLS using a valid client certificate. The client's username is taken from CommonName field in the certificate.
- md5
- Use MD5-based password check. auth_file may contain both MD5-encrypted or plain-text passwords. This is the default authentication method.
- plain
- Clear-text password is sent over wire. Deprecated.
- trust
- No authentication is done. The username must still exist in the auth_file.
- any
- Like the trust method, but the username supplied is ignored. Requires that all databases are configured to log in with a specific user. Additionally, the console database allows any user to log in as admin.
- auth_query
- Query to load a user's password from database. If a user does not exist in the
auth_file and the database entry includes an
auth_user, this query is run in the database as
auth_user to lookup up the user.
Default: SELECT usename, passwd FROM pg_shadow WHERE usename=$1
- pool_mode
- Specifies when a server connection can be reused by other clients.
- session
- Connection is returned to the pool when the client disconnects. Default.
- transaction
- Connection is returned to the pool when the transaction finishes.
- statement
- Connection is returned to the pool when the current query finishes. Long transactions with multiple statements are disallowed in this mode.
- max_client_conn
- Maximum number of client connections allowed. When increased then the file
descriptor limits should also be increased. The actual number of file descriptors
used is more than max_client_conn. The theoretical maximum used,
when each user connects with its own username to the server
is:
max_client_conn + (max_pool_size * total_databases * total_users)
If a database user is specified in the connect string, all users connect using the same username. Then the theoretical maximum connections is:
max_client_conn + (max_pool_size * total_databases)
The theoretical maximum should be never reached, unless somone deliberately crafts a load for it. Still, it means you should set the number of file descriptors to a safely high number. Search for ulimit in your operating system documentation.Note: ulimit does not apply in a Windows environment.Default: 100
- default_pool_size
- The number of server connections to allow per user/database pair. This can be
overridden in the per-database configuration.
Default: 20
- min_pool_size
- Add more server connections to the pool when it is lower than this number. This
improves behavior when the usual load drops and then returns suddenly after a period
of total inactivity.
Default: 0 (disabled)
- reserve_pool_size
- The number of additional connections to allow for a pool. 0 disables.
Default: 0 (disabled)
- reserve_pool_timeout
- If a client has not been serviced in this many seconds, PgBouncer enables use of
additional connections from reserve pool. 0 disables.
Default: 5.0
- max_db_connections
- The maximum number of connections per database. If you hit the limit, closing a
client connection to one pool does not immediately allow a server connection to be
established for another pool, because the server connection for the first pool is
still open. Once the server connection closes (due to idle timeout), a new server
connection will be opened for the waiting pool.
Default: unlimited
- max_user_connections
- The maximum number of connections per-user. When you hit the limit, closing a client connection to one pool does not immediately allow a connection to be established for another pool, because the connection for the first pool is still open. After the connection for the first pool has closed (due to idle timeout), a new server connection is opened for the waiting pool.
- server_round_robin
- By default, PgBouncer reuses server connections in LIFO (last-in, first-out)
order, so that a few connections get the most load. This provides the best
performance when a single server serves a database. But if there is TCP round-robin
behind a database IP, then it is better if PgBouncer also uses connections in that
manner to achieve uniform load.
Default: 0
- ignore_startup_parameters
- By default, PgBouncer allows only parameters it can keep track of in startup
packets: client_encoding, datestyle,
timezone, and standard_conforming_strings.
All others parameters raise an error. To allow other parameters, specify them here so that PgBouncer can ignore them.
Default: empty
- disable_pqexec
- Disable Simple Query protocol (PQexec). Unlike Extended Query protocol, Simple
Query protocol allows multiple queries in one packet, which allows some classes of
SQL-injection attacks. Disabling it can improve security. This means that only
clients that exclusively use Extended Query protocol will work.
Default: 0
- application_name_add_host
- Add the client host address and port to the application name setting set on
connection start. This helps in identifying the source of bad queries. The setting
is overwritten without detection if the application executes SET
APPLICATION_NAME after connecting.
Default: 1
Log Settings
- syslog
- Toggles syslog on and off. On Windows, eventlog is used instead.
Default: 0
- syslog_ident
- Under what name to send logs to syslog.
Default: pgbouncer
- syslog_facility
- Under what facility to send logs to syslog. Some possibilities are:
auth, authpriv, daemon,
user, local0-7
Default: daemon
- log_connections
- Log successful logins.
Default: 1
- log_disconnections
- Log disconnections, with reasons.
Default: 1
- log_pooler_errors
- Log error messages that the pooler sends to clients.
Default: 1
- stats_period
- How often to write aggregated statistics to the log.
Default: 60
Console Access Control
- admin_users
- Comma-separated list of database users that are allowed to connect and run all
commands on console. Ignored when auth_mode=any, in which case any
username is allowed in as admin.
Default: empty
- stats_users
- Comma-separated list of database users that are allowed to connect and run
read-only queries on console. Thats means all SHOW commands except SHOW
FDS.
Default: empty.
Connection Sanity Checks, Timeouts
- server_reset_query
- Query sent to server on connection release, before making it available to other
clients. At that moment no transaction is in progress so it should not include
ABORT or ROLLBACK.
A good choice for Postgres 8.2 and below, and Greenplum Database, is:
server_reset_query = RESET ALL; SET SESSION AUTHORIZATION DEFAULT;
For Postgre 8.3 and above, the following is sufficient:
server_reset_query = DISCARD ALL;
When transaction pooling is used, the server_reset_query should be empty, as clients should not use any session features. If clients do use session features, they will be broken because transaction pooling does not guarantee that the next query will run on the same connection.
Default: DISCARD ALL
- server_reset_query_always
-
Whether server_reset_query should be run in all pooling modes. When this setting is off (default), the server_reset_query will be run only in pools that are in sessions pooling mode. Connections in transaction pooling mode should not have any need for reset query.
Default: 0
- server_check_delay
- How long to keep released connections available for re-use without running
sanity-check queries on it. If 0 then the query is run always.
Default: 30.0
- server_check_query
- A simple do-nothing query to test the server connection.
If an empty string, then sanity checking is disabled.
Default: SELECT 1;
- server_lifetime
- The pooler tries to close server connections that have been connected longer than
this number of seconds. Setting it to 0 means the connection is to be used only
once, then closed.
Default: 3600.0
- server_idle_timeout
- If a server connection has been idle more than this many seconds it is dropped. If
this parameter is set to 0, timeout is disabled. [seconds]
Default: 600.0
- server_connect_timeout
- If connection and login will not finish in this number of seconds, the connection
will be closed.
Default: 15.0
- server_login_retry
- If a login fails due to failure from connect() or authentication,
the pooler waits this many seconds before retrying to connect.
Default: 15.0
- client_login_timeout
- If a client connects but does not manage to login in this number of seconds, it is
disconnected. This is needed to avoid dead connections stalling
SUSPEND and thus online restart.
Default: 60.0
- autodb_idle_timeout
- If database pools created automatically (via "*") have been unused this many
seconds, they are freed. Their statistics are also forgotten.
Default: 3600.0
- dns_max_ttl
- How long to cache DNS lookups, in seconds. If a DNS lookup returns several
answers, PgBouncer round-robins between them in the meantime. The actual DNS TTL is
ignored.
Default: 15.0
- dns_nxdomain_ttl
- How long error and NXDOMAIN DNS lookups can be cached, in seconds.
Default: 15.0
- dns_zone_check_period
- Period to check if zone serial numbers have changed.
PgBouncer can collect DNS zones from hostnames (everything after first dot) and then periodically check if the zone serial numbers change. If changes are detected, all hostnames in that zone are looked up again. If any host IP changes, its connections are invalidated.
Works only with UDNS backend (--with-udns to configure).
Default: 0.0 (disabled)
Dangerous Timeouts
Setting tje following timeouts can cause unexpected errors.
- query_timeout
- Queries running longer than this (seconds) are canceled. This parameter should be
used only with a slightly smaller server-side statement_timeout, to
trap queries with network problems. [seconds]
Default: 0.0 (disabled)
- query_wait_timeout
- The maximum time, in seconds, queries are allowed to wait for execution. If the
query is not assigned a connection during that time, the client is disconnected.
This is used to prevent unresponsive servers from grabbing up
connections.
Default: 0.0 (disabled)
- client_idle_timeout
- Client connections idling longer than this many seconds are closed. This should be
larger than the client-side connection lifetime settings, and only used for network
problems.
Default: 0.0 (disabled)
- idle_transaction_timeout
- If client has been in "idle in transaction" state longer than this (seconds), it
is disconnected.
Default: 0.0 (disabled)
Low-level Network Settings
- pkt_buf
- Internal buffer size for packets. Affects the size of TCP packets sent and general
memory usage. Actual libpq packets can be larger than this so there is no need to
set it large.
Default: 2048
- max_packet_size
- Maximum size for packets that PgBouncer accepts. One packet is either one query or
one result set row. A full result set can be larger.
Default: 2147483647
- listen_backlog
- Backlog argument for the listen(2) system call. It how many new
unanswered connection attempts are kept in queue. When the queue is full, further
new connection attemps are dropped.
Default: 128
- sbuf_loopcnt
- How many times to process data on one connection, before proceeding. Without this
limit, one connection with a big result set can stall PgBouncer for a long time. One
loop processes one pkt_buf amount of data. 0 means no
limit.
Default: 5
- suspend_timeout
- How many seconds to wait for buffer flush during SUSPEND or
reboot (-R). Connection is dropped if flush does not
succeed.
Default: 10
- tcp_defer_accept
- For details on this and other TCP options, please see the tcp(7) man
page.
Default: 45 on Linux, otherwise 0
- tcp_socket_buffer
- Default: not set
- tcp_keepalive
- Turns on basic keepalive with OS defaults.
On Linux, the system defaults are tcp_keepidle=7200, tcp_keepintvl=75, tcp_keepcnt=9.
Default: 1
- tcp_keepcnt
- Default: not set
- tcp_keepidle
- Default: not set
- tcp_keepintvl
- Default: not set
[users] Section
This section contains key=value pairs, where the key is a user name and the value is a libpq connect-string list of key=value pairs.
Pool configuration
- pool_mode
- Set the pool mode to be used for all connections from this user. If not set, the database or default pool_mode is used.
Example Configuration Files
Minimal Configuration
[databases] template1 = host=127.0.0.1 dbname=template1 auth_user=gpadmin [pgbouncer] pool_mode = session listen_port = 6543 listen_addr = 127.0.0.1 auth_type = md5 auth_file = users.txt logfile = pgbouncer.log pidfile = pgbouncer.pid admin_users = someuser stats_users = stat_collector
Use connection parameters passed by the client:
[databases] * = [pgbouncer] listen_port = 65432 listen_addr = 0.0.0.0 auth_type = trust auth_file = bouncer/users.txt logfile = pgbouncer.log pidfile = pgbouncer.pid ignore_startup_parameters=options
Database Defaults
[databases] ; foodb over unix socket foodb = ; redirect bardb to bazdb on localhost bardb = host=127.0.0.1 dbname=bazdb ; access to destination database will go with single user forcedb = host=127.0.0.1 port=300 user=baz password=foo client_encoding=UNICODE datestyle=ISO
PgBouncer Authentication File Format
PgBouncer requires its own user database, a text file in following format:
"username1" "password" ... "username2" "md5abcdef012342345" ...
There is one line per user. Each line must have at least two fields. Fields are enclosed in double quotes ("). The first field is the user name and the second is either a plain-text or an MD5-encoded password. The remainder of the line is ignored.
This file format is similar to text files used by Greenplum Database for authentication information, and PgBouncer can work directly with the Greenplum Database authentication files.
To avoid plain-text passwords, encode user passwords with MD5. The format for an MD5 encoded password is:
"md5" + md5(password + username)
For example, the following command generates the MD5 string for the user admin with password 1234:
$ echo -n "1234admin" | md5sum $ 45f2603610af569b6155c45067268c6b
md545f2603610af569b6155c45067268c6b
PgBouncer HBA File Format
The PgBouncer HBA file follows the format of the PostgreSQL pg_hba.conf file. See http://www.postgresql.org/docs/9.4/static/auth-pg-hba-conf.html for details.
- Supported record types: local, host, hostssl, hostnossl.
- Database field: Supports all, sameuser, @file, multiple names. Not supported: replication, samerole, samegroup.
- Username field: Supports all, @file, multiple names. Not supported +groupname.
- Address field: Supported IPv4, IPv6. Not supported: DNS names, domain prefixes.
- Auth-method field: Supported methods: trust, reject, jd5, password, peer, cert. Not supported: gss, sspi, ident, ldap, radius, pam. Also username map (map=) parameter is not supported.
PgBouncer Administration Console Commands
The PgBouncer Administration Console is accessed by connecting to the database pgbouncer.
$ psql -p 6543 pgbouncer
Only users listed in configuration parameters admin_users or stats_users can log in to the console. However, when auth_mode=any, then any user may log in as a stats_user.
The user name pgbouncer may also log in without a password through a Unix socket if the client has the same Unix user UID as the running process.
Administration Console Command Syntax
pgbouncer=# show help; NOTICE: Console usage DETAIL: SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSION SHOW STATS|FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM SHOW DNS_HOSTS|DNS_ZONES SET key = arg RELOAD PAUSE [<db>] RESUME [<db>] DISABLE <db> ENABLE <db> KILL <db> SUSPEND SHUTDOWN
Administration Commands
From the PgBouncer Administrator console you can control connections between PgBouncer and Greenplum Database.You can also set PgBouncer configuration parameters.
The following PgBouncer administration commands control the PgBouncer process.
- PAUSE [database]
- If no database is specified, PgBouncer tries to disconnect from all servers, first waiting for all queries to complete. The command will not return before all queries are finished. This command is to be used to prepare to restart the database.
- If a database name is specified, only that database is paused.
- If you run a PAUSE database command, and then a PAUSE command to pause all databases, you must execute two RESUME commands, one for all databases, and one for the named database.
- SUSPEND
- All socket buffers are flushed and PgBouncer stops listening for data on them. The command will not return before all buffers are empty. To be used when rebooting PgBouncer online.
- RESUME [ database ]
- Resume work from a previous PAUSE or SUSPEND command.
- If a database was specified for the PAUSE command, the database must also be specified with the RESUME command.
- After pausing all databases with the PAUSE command, resuming a single database with RESUME database is not supported.
- DISABLE database
- Reject all new client connections on the database.
- ENABLE database
- Allow new client connections on the database.
- KILL database
- Immediately drop all client and server connections to the named database.
- SHUTDOWN
- Stop PgBouncer process. To exit from the psql command line session, enter \q.
- RELOAD
- The PgBouncer process reloads the current configuration file and updates the changeable settings.
- SET key = value
- Override specified configuration setting. See the SHOW CONFIG; command.
SHOW Command
ACTIVE_SOCKETS
Column | Description |
---|---|
type | S, for server, C for client. |
user | Username pgbouncer uses to connect to server. |
database | Database name. |
state | State of the server connection, one of active, used or idle. |
addr | IP address of PostgreSQL server. |
port | Port of PostgreSQL server. |
local_addr | Connection start address on local machine. |
local_port | Connection start port on local machine. |
connect_time | When the connection was made. |
request_time | When last request was issued. |
ptr | Address of internal object for this connection. Used as unique ID. |
link | Address of client connection the server is paired with. |
recv_pos | Receive position in the I/O buffer. |
pkt_pos | Parse position in the I/O buffer. |
pkt_remain | Number of packets remaining on the socket. |
send_pos | Send position in the packet. |
send_remain | Total packet length remaining to send. |
pkt_avail | Amount of I/O buffer left to parse. |
send_avail | Amount of I/O buffer left to send. |
CLIENTS
Column | Description |
---|---|
type | C, for client. |
user | Client connected user. |
database | Database name. |
state | State of the client connection, one of active, used, waiting or idle. |
addr | IP address of client, or unix for a socket connection. |
port | Port client is connected to. |
local_addr | Connection end address on local machine. |
local_port | Connection end port on local machine. |
connect_time | Timestamp of connect time. |
request_time | Timestamp of latest client request. |
ptr | Address of internal object for this connection. Used as unique ID. |
link | Address of server connection the client is paired with. |
remote_pid | Process ID, if client connects with Unix socket and the OS supports getting it. |
CONFIG
List of current PgBouncer parameter settings
Column | Description |
---|---|
key | Configuration variable name |
value | Configuration value |
changeable | Either yes or no. Shows whether the variable can be changed while running. If no, the variable can be changed only at boot time. |
DATABASES
Column | Description |
---|---|
name | Name of configured database entry. |
host | Host pgbouncer connects to. |
port | Port pgbouncer connects to. |
database | Actual database name pgbouncer connects to. |
force_user | When user is part of the connection string, the connection between pgbouncer and the database server is forced to the given user, whatever the client user. |
pool_size | Maximum number of server connections. |
reserve_pool | The number of additional connections that can be created if the pool reaches pool_size. |
pool_mode | The database's override pool_mode or NULL if the default will be used instead. |
max_connections | Maximum number of connections for all pools for this database. |
current_connections | The total count of connections for all pools for this database. |
DNS_ZONES
Column | Description |
---|---|
zonename | Zone name |
serial | Current DNS serial number |
count | Hostnames belonging to this zone |
FDS
SHOW FDS is an internal command used for an online restart, for example when upgrading to a new PgBouncer version. It shows a list of file descriptors in use with the internal state attached to them. This command blocks the internal event loop, so it should not be used while PgBouncer is in use.
When the connected user has username "pgbouncer", connects through a Unix socket, and has the same UID as the running process, the actual file descriptors are passed over the connection. Note: This does not work on Windows machines.
Column | Description |
---|---|
fd | File descriptor numeric value. |
task | One of pooler, client, or server. |
user | User of the connection using the file descriptor. |
database | Database of the connection using the file descriptor. |
addr | IP address of the connection using the file descriptor, "unix" if a Unix socket is used. |
port | Port used by the connection using the file descriptor. |
cancel | Cancel key for this connection. |
link | File descriptor for corresponding server/client. NULL if idle. |
client_encoding | Character set used for the database. |
std_strings | This controls whether ordinary string literals ('...') treat backslashes literally, as specified in the SQL standard. |
datestyle | Display format for date and time values. |
timezone | The timezone for interpreting and displaying time stamps. |
LISTS
Shows the following PgBouncer statistcs in two columns: the item label and value.
Item | Description |
---|---|
databases | Count of databases. |
users | Count of users. |
pools | Count of pools. |
free_clients | Count of free clients. |
used_clients | Count of used clients. |
login_clients | Count of clients in login state. |
free_servers | Count of free servers. |
used_servers | Count of used servers. |
dns_names | Count of DNS names. |
dns_zones | Count of DNS zones. |
dns_queries | Count of DNS queries. |
dns_pending | Count of in-flight DNS queries. |
MEM
- user_cache
- db_cache
- pool_cache
- server_cache
- client_cache
- iobuf_cache
Column | Description |
---|---|
name | Name of cache. |
size | The size of a single slot in the cache. |
used | Number of used slots in the cache. |
free | The number of available slots in the cache. |
memtotal | Total bytes used by the cache. |
POOLS
A new pool entry is made for each pair of (database, user).
Column | Description |
---|---|
database | Database name. |
user | User name. |
cl_active | Client connections that are linked to server connection and can process queries. |
cl_waiting | Client connections have sent queries but have not yet got a server connection. |
sv_active | Server connections that linked to client. |
sv_idle | Server connections that are unused and immediately usable for client queries. |
sv_used | Server connections that have been idle more than server_check_delay. The server_check_query query must be run on them before they can be used. |
sv_tested | Server connections that are currently running either server_reset_query or server_check_query. |
sv_login | Server connections currently in process of logging in. |
maxwait | How long the first (oldest) client in the queue has waited, in seconds. If this begins to increase, the current pool of servers does not handle requests fast enough. The cause may be either an overloaded server or the pool_size setting is too small. |
pool_mode | The pooling mode in use. |
SERVERS
Column | Description |
---|---|
type | S, for server. |
user | User ID that pgbouncer uses to connect to server. |
database | Database name. |
state | State of the pgbouncer server connection, one of active, used, or idle. |
addr | IP address of the Greenplum or PostgreSQL server. |
port | Port of the Greenplum or PostgreSQL server. |
local_addr | Connection start address on local machine. |
local_port | Connection start port on local machine. |
connect_time | When the connection was made. |
request_time | When the last request was issued. |
ptr | Address of the internal object for this connection. Used as unique ID. |
link | Address of gthe client connection the server is paired with. |
remote_pid | Pid of backend server process. If the connection is made over Unix socket and the OS supports getting process ID info, it is the OS pid. Otherwise it is extracted from the cancel packet the server sent, which should be PID in case server is PostgresSQL, but it is a random number in case server is another PgBouncer. |
STATS
Shows statistics.
Column | Description |
---|---|
database | Statistics are presented per database. |
total_requests | Total number of SQL requests pooled by pgbouncer. |
total_received | Total volume in bytes of network traffic received by pgbouncer. |
total_sent | Total volume in bytes of network traffic sent by pgbouncer. |
total_query_time | Total number of microseconds spent by pgbouncer when actively connected to the database server. |
avg_req | Average requests per second in last stat period. |
avg_recv | Average received (from clients) bytes per second. |
avg_sent | Average sent (to clients) bytes per second. |
avg_query | Average query duration in microseconds. |
USERS
Column | Description |
---|---|
name | The user name |
pool_mode | The user's override pool_mode, or NULL if the default will be used instead. |
VERSION
Display PgBouncer version information.