pgbouncer [-d][-R][-v][-u user] <pgbouncer.ini>
pgbouncer -V|-h
On Windows, the options are:
pgbouncer.exe [-v][-u user] <pgbouncer.ini>
pgbouncer.exe -V|-h
Additional options for setting up a Windows service:
pgbouncer.exe --regservice <pgbouncer.ini>
pgbouncer.exe --unregservice <pgbouncer.ini>
pgbouncer is a PostgreSQL connection pooler. Any target application can be connected to pgbouncer as if it were a PostgreSQL server, and pgbouncer will create a connection to the actual server, or it will reuse one of its existing connections.
The aim of pgbouncer is to lower the performance impact of opening new connections to PostgreSQL.
In order not to compromise transaction semantics for connection pooling, pgbouncer supports several types of pooling when rotating connections:
Most polite method. When a client connects, a server connection will be assigned to it for the whole duration the client stays connected. When the client disconnects, the server connection will be put back into the pool. This is the default method.
A server connection is assigned to a client only during a transaction. When PgBouncer notices that transaction is over, the server connection will be put back into the pool.
Most aggressive method. The server connection will be put back into the pool immediately after a query completes. Multi-statement transactions are disallowed in this mode as they would break.
The administration interface of pgbouncer consists of some new
SHOW commands available when connected to a special “virtual”
database pgbouncer.
Basic setup and usage is as follows.
Create a pgbouncer.ini file. Details in pgbouncer(5). Simple example:
[databases]
template1 = host=localhost port=5432 dbname=template1
[pgbouncer]
listen_port = 6432
listen_addr = localhost
auth_type = md5
auth_file = userlist.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = someuser
Create a userlist.txt file that contains the users allowed in:
"someuser" "same_password_as_in_server"
Launch pgbouncer:
$ pgbouncer -d pgbouncer.ini
Have your application (or the psql client) connect to pgbouncer instead of directly to the PostgreSQL server:
$ psql -p 6432 -U someuser template1
Manage pgbouncer by connecting to the special administration
database pgbouncer and issuing SHOW HELP; to begin:
$ psql -p 6432 -U someuser pgbouncer
pgbouncer=# SHOW HELP;
NOTICE: Console usage
DETAIL:
SHOW [HELP|CONFIG|DATABASES|FDS|POOLS|CLIENTS|SERVERS|SOCKETS|LISTS|VERSION|...]
SET key = arg
RELOAD
PAUSE
SUSPEND
RESUME
SHUTDOWN
[...]
If you made changes to the pgbouncer.ini file, you can reload it with:
pgbouncer=# RELOAD;
-d, --daemonIn daemon mode, setting pidfile as well as logfile or syslog
is required. No log messages will be written to stderr after
going into the background.
Note: Does not work on Windows; pgbouncer need to run as service there.
-R, --rebootunix_socket_dir
is not disabled in configuration. Does not work on Windows.
Does not work with TLS connections, they are dropped.-u USERNAME, --user=USERNAME-v, --verbose-q, --quiet-V, --version-h, --help--regservice--unregserviceThe console is available by connecting as normal to the database pgbouncer:
$ psql -p 6432 pgbouncer
Only users listed in the configuration parameters admin_users or stats_users
are allowed to log in to the console. (Except when auth_type=any, then
any user is allowed in as a stats_user.)
Additionally, the user name pgbouncer is allowed to log in without password, if the login comes via the Unix socket and the client has same Unix user UID as the running process.
The admin console currently only supports the simple query protocol. Some drivers use the extended query protocol for all commands; these drivers will not work for this.
The SHOW commands output information. Each command is described below.
Shows statistics. In this and related commands, the total figures are
since process start, the averages are updated every stats_period.
stats_period, in microseconds
(averaged per second within that period).Subset of SHOW STATS showing the total values (total_).
Subset of SHOW STATS showing the average values (avg_).
Like SHOW STATS but aggregated across all databases.
application_name set on the linked client connection,
or empty if this is not set, or if there is no linked connection.max_prepared_statements setting.application_name set by the client
for this connection, or empty if this was not set.A new pool entry is made for each couple of (database, user).
server_check_delay,
so they need server_check_query to run on them before they can be used again.server_reset_query
or server_check_query.A new peer_pool entry is made for each configured peer.
Show following internal information, in columns (not rows):
Internal command - shows list of file descriptors in use with internal state attached to them.
When the connected user has the user name “pgbouncer”, connects through the Unix socket and has same the UID as the running process, the actual FDs are passed over the connection. This mechanism is used to do an online restart. Note: This does not work on Windows.
This command also blocks the internal event loop, so it should not be used while PgBouncer is in use.
Shows low-level information about sockets or only active sockets. This includes the information shown under SHOW CLIENTS and SHOW SERVERS as well as other more low-level information.
Show the current configuration settings, one per row, with the following columns:
Shows low-level information about the current sizes of various internal memory allocations. The information presented is subject to change.
Show host names in DNS cache.
Show DNS zones in cache.
Show the PgBouncer version string.
Show the PgBouncer state settings. Current states are active, paused and suspended.
PgBouncer tries to disconnect from all servers. Disconnecting each server connection waits for that server connection to be released according to the server pool’s pooling mode (in transaction pooling mode, the transaction must complete, in statement mode, the statement must complete, and in session pooling mode the client must disconnect). The command will not return before all server connections have been disconnected. To be used at the time of database restart.
If database name is given, only that database will be paused.
New client connections to a paused database will wait until RESUME is called.
Reject all new client connections on the given database.
Allow new client connections after a previous DISABLE command.
Close each open server connection for the given database, or all databases, after it is released (according to the pooling mode), even if its lifetime is not up yet. New server connections can be made immediately and will connect as necessary according to the pool size settings.
This command is useful when the server connection setup has changed, for example to perform a gradual switchover to a new server. It is not necessary to run this command when the connection string in pgbouncer.ini has been changed and reloaded (see RELOAD) or when DNS resolution has changed, because then the equivalent of this command will be run automatically. This command is only necessary if something downstream of PgBouncer routes the connections.
After this command is run, there could be an extended period where some server connections go to an old destination and some server connections go to a new destination. This is likely only sensible when switching read-only traffic between read-only replicas, or when switching between nodes of a multimaster replication setup. If all connections need to be switched at the same time, PAUSE is recommended instead. To close server connections without waiting (for example, in emergency failover rather than gradual switchover scenarios), also consider KILL.
Immediately drop all client and server connections on given database.
New client connections to a killed database will wait until RESUME is called.
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 at the time of PgBouncer online reboot.
New client connections to a suspended database will wait until RESUME is called.
Resume work from previous KILL, PAUSE, or SUSPEND command.
The PgBouncer process will exit.
The PgBouncer process will reload its configuration files and update
changeable settings. This includes the main configuration file as
well as the files specified by the settings auth_file and
auth_hba_file.
PgBouncer notices when a configuration file reload changes the connection parameters of a database definition. An existing server connection to the old destination will be closed when the server connection is next released (according to the pooling mode), and new server connections will immediately use the updated connection parameters.
Wait until all server connections, either of the specified database or of all databases, have cleared the “close_needed” state (see SHOW SERVERS). This can be called after a RECONNECT or RELOAD to wait until the respective configuration change has been fully activated, for example in switchover scripts.
Changes a configuration setting (see also SHOW CONFIG). For example:
SET log_connections = 1;
SET server_check_query = 'select 2';
(Note that this command is run on the PgBouncer admin console and sets PgBouncer settings. A SET command run on another database will be passed to the PostgreSQL backend like any other SQL command.)
From the Libevent documentation:
It is possible to disable support for epoll, kqueue, devpoll, poll or select by setting the environment variable EVENT_NOEPOLL, EVENT_NOKQUEUE, EVENT_NODEVPOLL, EVENT_NOPOLL or EVENT_NOSELECT, respectively.
By setting the environment variable EVENT_SHOW_METHOD, libevent displays the kernel notification method that it uses.
pgbouncer(5) - man page of configuration settings descriptions