pgbouncer
pgbouncer — aPostgres Pro connection pooler
Synopsis
On Linux systems:
pgbouncer [ -d ] [ -R ] [ -v ] [ -uuser ] pgbouncer.ini
pgbouncer -V | -h
On Windows systems:
pgbouncer [ -v ] [ -uuser ] pgbouncer.ini
pgbouncer -V | -h
To usepgbouncer as a Windows service:
pgbouncer.exe --regservice pgbouncer.ini
pgbouncer.exe --unregservice pgbouncer.ini
Description
pgbouncer is aPostgres Pro connection pooler. Any target application can be connected topgbouncer as if it were aPostgres Pro server, andpgbouncer will create a connection to the actual server, or it will reuse one of its existing connections.
The aim ofpgbouncer is to lower the performance impact of opening new connections toPostgres Pro.
In order not to compromise transaction semantics for connection pooling,pgbouncer supports several types of pooling when rotating connections:
- Session pooling
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.
- Transaction pooling
A server connection is assigned to a client only during a transaction. Whenpgbouncer notices that transaction is over, the server connection will be put back into the pool.
- Statement pooling
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 ofpgbouncer consists of some newSHOW commands available when connected to a special“virtual” databasepgbouncer.
Quick Start
Basic setup and usage is as follows.
Create a
pgbouncer.inifile. Details in thepgbouncer(5)man page. Simple example:[databases]template1 = host=127.0.0.1 port=5432 dbname=template1[pgbouncer]listen_port = 6543listen_addr = 127.0.0.1auth_type = md5auth_file = userlist.txtlogfile = pgbouncer.logpidfile = pgbouncer.pidadmin_users = someuser
Create a
userlist.txtfile that contains the users allowed in:"someuser" "same_password_as_in_server"
Launchpgbouncer:
$ pgbouncer -d pgbouncer.ini
Note
The above command does not work on Windows systems. Instead,pgbouncer must be launched as a service that first needs to be registered, as follows:
pgbouncer --regservice
Have your application (or the
psqlclient) connect topgbouncer instead of directly to thePostgres Pro server:$ psql -p 6543 -U someuser template1
Managepgbouncer by connecting to the special administration databasepgbouncer and issuing
SHOW HELP;to begin:$ psql -p 6543 -U someuser pgbouncerpgbouncer=# SHOW HELP;NOTICE: Console usageDETAIL: 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.inifile, you can reload it with:pgbouncer=# RELOAD;
Options
-dRun in the background. Without it, the process will run in the foreground.
Note
Does not work on Windows,pgbouncer needs to run as service there.
-RDo an online restart. That means connecting to the running process, loading the open sockets from it, and then using them. If there is no active process, boot normally.
-uuser-v-q-V-h--regservice--unregservice
Admin Console
The console is available by connecting as normal to the databasepgbouncer:
$ psql -p 6543 pgbouncer
Additionally, the user namepgbouncer is allowed to log in without password, if the login comes via the Unix socket and the client has same Unix useruid as the running process.
Show Commands
TheSHOW commands output information. Each command is described below.
SHOW STATS
databasetotal_xact_counttotal_query_counttotal_receivedTotal volume in bytes of network traffic received bypgbouncer.
total_senttotal_xact_timetotal_query_timetotal_wait_timeTime spent by clients waiting for a server, in microseconds.
avg_xact_countavg_query_countavg_recvavg_sentavg_xact_timeavg_query_timeavg_wait_timeTime spent by clients waiting for a server, in microseconds (average per second).
SHOW SERVERS
typeuserdatabasestateState of thepgbouncer server connection, one of
active,usedoridle.addrportlocal_addrlocal_portconnect_timerequest_timewaitwait_usclose_neededptrAddress of internal object for this connection. Used as unique ID.
linkremote_pidPID of backend server process. In case connection is made over Unix socket and OS supports getting process ID info, its OSPID. Otherwise it's extracted from cancel packet the server sent, which should be thePID in case the server isPostgres Pro, but it's a random number in case the server is anotherpgbouncer.
tlsA string with TLS connection information, or empty if not using TLS.
SHOW CLIENTS
typeuserdatabasestateState of the client connection, one of
active,used,waitingoridle.addrportlocal_addrlocal_portconnect_timerequest_timewaitwait_usclose_neededptrAddress of internal object for this connection. Used as unique ID.
linkremote_pidProcess ID, in case client connects over Unix socket and OS supports getting it.
tlsA string with TLS connection information, or empty if not using TLS.
SHOW POOLS
A new pool entry is made for each couple of (database, user).
databaseusercl_activeClient connections that are linked to server connection and can process queries.
cl_waitingClient connections have sent queries but have not yet got a server connection.
sv_activesv_idleServer connections that are unused and immediately usable for client queries.
sv_usedsv_testedServer connections that are currently running either
server_reset_queryorserver_check_query.sv_loginmaxwaitmaxwait_uspool_mode
SHOW LISTS
SHOW DATABASES
SHOW FDS
Internal command — shows list of file descriptors (FDs) in use with internal state attached to them.
When the connected user has the user namepgbouncer, connects through the Unix socket and has the sameUID as the running process, the actual FDs are passed over the connection. This mechanism is used to do an online restart.
This command also blocks the internal event loop, so it should not be used whilepgbouncer is in use.
Process Controlling Commands
PAUSE [db]
If database name is given, only that database will be paused.
New client connections to a paused database will wait untilRESUME is called.
KILLdb
Immediately drop all client and server connections on given database.
New client connections to a killed database will wait untilRESUME is called.
WAIT_CLOSE [db]
Wait until all server connections, either of the specified database or of all databases, have cleared theclose_needed state (seethe section called “SHOW SERVERS”). This can be called after aRECONNECT orRELOAD to wait until the respective configuration change has been fully activated, for example in switchover scripts.
Other Commands
SETkey =arg
Changes a configuration setting (see alsothe section called “SHOW CONFIG”). For example:
SET log_connections = 1;SET server_check_query = 'select 2';
(Note that this command is run on thepgbouncer admin console and setspgbouncer settings. ASET command run on another database will be passed to thePostgres Pro backend like any other SQL command.)
Signals
SIGHUPReload config. Same as issuing the command
RELOADon the console.SIGINTSafe shutdown. Same as issuing
PAUSEandSHUTDOWNon the console.SIGTERMImmediate shutdown. Same as issuing
SHUTDOWNon the console.SIGUSR1Same as issuing
PAUSEon the console.SIGUSR2Same as issuing
RESUMEon the console.
Libevent Settings
From the libevent documentation:
It is possible to disable support for
epoll,kqueue,devpoll,poll, orselectby setting the environment variableEVENT_NOEPOLL,EVENT_NOKQUEUE,EVENT_NODEVPOLL,EVENT_NOPOLLorEVENT_NOSELECT, respectively.By setting the environment variable
EVENT_SHOW_METHOD,libeventdisplays the kernel notification method that it uses.
pgbouncer.ini Configuration File
The configuration file is in the.ini format. Section names are between "[" and "]". Lines starting with ";" or "#" are taken as comments and ignored. The characters ";" and "#" are not recognized when they appear later in the line.
Generic Settings
logfileSpecifies log file. Log file is kept open so after rotation
kill -HUPor on consoleRELOAD;should be done. Note: On Windows systems, the service must be stopped and started.Default: not set
pidfileSpecifies the PID file. Without a
pidfile, daemonization is not allowed.Default: not set
listen_addrSpecifies list of addresses, where to listen for TCP connections. You may also use
*meaning "listen on all addresses". When not set, only Unix socket connections are allowed.Addresses can be specified numerically (IPv4/IPv6) or by name.
Default: not set
listen_portWhich port to listen on. Applies to both TCP and Unix sockets.
Default: 6432
unix_socket_dirSpecifies 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) to work. Note: Not supported on Windows systems.
Default: /tmp
unix_socket_modeFile system mode for Unix socket.
Default: 0777
unix_socket_groupGroup name to use for Unix socket.
Default: not set
userIf set, specifies the Unix user to change to after startup. Works only ifpgbouncer is started as root or if it's already running as given user.
Note: Not supported on Windows systems.
Default: not set
auth_fileThe name of the file to load user names and passwords from. Seethe section called “Authentication File Format” for details.
Default: not set
auth_hba_fileHBA configuration file to use when
auth_typeishba. Supported from version 1.7 onwards.Default: not set
auth_typeHow to authenticate users.
pamPluggable Authentication Modules (PAM) method is used to authenticate users,
auth_fileis ignored. This method is not compatible with databases usingauth_useroption. Service name reported toPAM ispgbouncer. Also,PAM is still not supported inHBA configuration file.hbaActual authentication type is loaded from
auth_hba_file. This allows different authentication methods different access paths. Example: connection over Unix socket usespeerauthentication method, connection overTCP must useTLS. Supported from version 1.7 onwards.certClient must connect overTLS connection with valid client certificate. Username is then taken from CommonName field from certificate.
md5Use MD5-based password check. This is the default authentication method.
auth_filemay contain both MD5-encrypted or plain-text passwords. Ifmd5is configured and a user has a SCRAM secret, then SCRAM authentication is used automatically instead.scram-sha-256Use password check with SCRAM-SHA-256.
auth_filehas to contain SCRAM secrets or plain-text passwords. Note that SCRAM secrets can only be used for verifying the password of a client but not for logging into a server. To be able to use SCRAM on server connections, use plain-text passwords.plainClear-text password is sent over wire. Deprecated.
trustNo authentication is done. Username must still exist in
auth_file.anyLike the
trustmethod, but the username given is ignored. Requires that all databases are configured to log in as specific user. Additionally, the console database allows any user to log in as admin.
auth_queryQuery to load user's password from database.
Direct access to
pg_shadowrequires admin rights. It's preferable to use non-admin user that calls SECURITY DEFINER function instead.Note that the query is run inside target database, so if a function is used it needs to be installed into each database.
Default:
SELECT usename, passwd FROM pg_shadow WHERE usename=$1auth_userIf
auth_useris set, any user not specified inauth_filewill be queried through theauth_queryquery frompg_shadowin the database usingauth_user.auth_user's password will be taken fromauth_file.Direct access to
pg_shadowrequires admin rights. It's preferable to use non-admin user that calls SECURITY DEFINER function instead.Default: not set
pool_modeSpecifies when a server connection can be reused by other clients.
sessionServer is released back to pool after client disconnects. Default.
transactionServer is released back to pool after transaction finishes.
statementServer is released back to pool after query finishes. Long transactions spanning multiple statements are disallowed in this mode.
max_client_connMaximum number of client connections allowed. When increased, the file descriptor limits should also be increased. Note that actual number of file descriptors used is more than
max_client_conn. If each user connects under its own username to server, theoretical maximum used is:max_client_conn + (max pool_size * total databases * total users)
If a database user is specified in connect string (all users connect under same username), the theoretical maximum is:
max_client_conn + (max pool_size * total databases)
The theoretical maximum should be never reached, unless somebody deliberately crafts special load for it. Still, it means you should set the number of file descriptors to a safely high number.
Search for
ulimitin your favorite shell man page. Note:ulimitdoes not apply in a Windows environment.Default: 100
default_pool_sizeHow many server connections to allow per user/database pair. Can be overridden in the per-database configuration.
Default: 20
min_pool_sizeAdd more server connections to pool if below this number. Improves behavior when usual load suddenly comes back after a period of total inactivity.
Default: 0 (disabled)
reserve_pool_sizeHow many additional connections to allow to a pool. The 0 value disables this parameter.
Default: 0 (disabled)
reserve_pool_timeoutIf a client has not been serviced in this many seconds,pgbouncer enables use of additional connections from reserve pool. The 0 value disables this parameter.
Default: 5.0
max_db_connectionsDo not allow more than this many connections per-database (regardless of pool — i.e. user). It should be noted that when you hit the limit, closing a client connection to one pool will 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 immediately be opened for the waiting pool.
Default: unlimited
max_user_connectionsDo not allow more than this many connections per-user (regardless of pool — i.e. user). It should be noted that when you hit the limit, closing a client connection to one pool will 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 immediately be opened for the waiting pool.
server_round_robinBy default,pgbouncer reuses server connections in LIFO (last-in, first-out) manner, so that few connections get the most load. This gives best performance if you have a single server serving a database. But if there isTCP round-robin behind a database IP, then it is better ifpgbouncer also uses connections in that manner, thus achieving uniform load.
ignore_startup_parametersdisable_pqexecapplication_name_add_hostconffileservice_namejob_namestats_periodSets how often the averages shown in various
SHOWcommands are updated and how often aggregated statistics are written to the log (but seelog_stats). [seconds]Default: 60
Log Settings
syslogTogglessyslog on/off. On Windows systems,eventlog is used instead.
Default: 0
syslog_identUnder what name to send logs tosyslog.
Default:
pgbouncer(program name)syslog_facilityUnder what facility to send logs tosyslog. Possibilities:
auth,authpriv,daemon,user,local0-7.Default: daemon
log_connectionsLog successful logins.
Default: 1
log_disconnectionsLog disconnections with reasons.
Default: 1
log_pooler_errorsLog error messages pooler sends to clients.
Default: 1
stats_periodPeriod for writing aggregated stats into log.
Default: 60
log_statsWrite aggregated statistics into the log, every
stats_period. This can be disabled if external monitoring tools are used to grab the same data fromSHOWcommands.Default: 1
verboseIncrease verbosity. Mirrors "-v" switch on command line. Using "-v -v" on command line is same as
verbose=2in configuration file.Default: 0
Console Access Control
admin_usersComma-separated list of database users that are allowed to connect and run all commands on console. Ignored when
auth_typeisany, in which case any username is allowed in as admin.Default: empty
stats_usersComma-separated list of database users that are allowed to connect and run read-only queries on console. That means all SHOW commands except SHOW FDS.
Default: empty.
Connection Sanity Checks, Timeouts
server_reset_queryQuery 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
ABORTorROLLBACK.The query is supposed to clean any changes made to database session so that next client gets connection in well-defined state. Default is
DISCARD ALLwhich cleans everything, but that leaves next client no pre-cached state. It can be made lighter, e.g.DEALLOCATE ALLto just drop prepared statements, if application does not break when some state is kept around.When transaction pooling is used, the
server_reset_queryis not used, as clients must not use any session-based features as each transaction ends up in different connection and thus gets different session state.Default: DISCARD ALL
server_reset_query_alwaysWhether
server_reset_queryshould be run in all pooling modes. When this setting is off (default), theserver_reset_querywill be run only in pools that are in sessions-pooling mode. Connections in transaction-pooling mode should not have any need for reset query.It is workaround for broken setups that run apps that use session features over transaction-pooledpgbouncer. It changes non-deterministic breakage to deterministic breakage — client always lose their state after each transaction.
Default: 0
server_check_delayHow long to keep released connections available for immediate re-use, without running sanity-check queries on it. If 0 then the query is always run.
Default: 30.0
server_check_querySimple do-nothing query to check if the server connection is alive.
If an empty string, then sanity checking is disabled.
Default: SELECT 1;
server_fast_closeDisconnect a server in session pooling mode immediately or after the end of the current transaction if it is in
close_neededmode (set byRECONNECT,RELOADthat changes connection settings, or DNS change), rather than waiting for the session end. In statement or transaction pooling mode, this has no effect since that is the default behavior there.If because of this setting a server connection is closed before the end of the client session, the client connection is also closed. This ensures that the client notices that the session has been interrupted.
This setting makes connection configuration changes take effect sooner if session pooling and long-running sessions are used. The downside is that client sessions are liable to be interrupted by a configuration change, so client applications will need logic to reconnect and reestablish session state. But note that no transactions will be lost, because running transactions are not interrupted, only idle sessions.
Default: 0
server_lifetimeThe pooler will close an unused server connection that has been connected longer than this. Setting it to 0 means the connection is to be used only once, then closed. [seconds]
Default: 3600.0
server_idle_timeoutIf a server connection has been idle more than this many seconds it will be dropped. If 0 then timeout is disabled. [seconds]
Default: 600.0
server_connect_timeoutIf connection and login won't finish in this amount of time, the connection will be closed. [seconds]
Default: 15.0
server_login_retryIf login failed, because of failure from connect() or authentication that pooler waits this much before retrying to connect. [seconds]
Default: 15.0
client_login_timeoutIf a client connects but does not manage to login in this amount of time, it will be disconnected. Mainly needed to avoid dead connections stalling SUSPEND and thus online restart. [seconds]
Default: 60.0
autodb_idle_timeoutIf the automatically created (via "*") database pools have been unused this many seconds, they are freed. The negative aspect of that is that their statistics are also forgotten. [seconds]
Default: 3600.0
dns_max_ttlHow long the DNS lookups can be cached. If a DNS lookup returns several answers,pgbouncer will robin-between them in the meantime. Actual DNS TTL is ignored. [seconds]
Default: 15.0
dns_nxdomain_ttlHow long error and NXDOMAIN DNS lookups can be cached. [seconds]
Default: 15.0
dns_zone_check_periodPeriod to check if zone serial has changed.
Works only with UDNS and c-ares backends (
--with-udnsor--with-caresto configure).
TLS Settings
client_tls_sslmodeTLS mode to use for connections from clients.TLS connections are disabled by default. When enabled,
client_tls_key_fileandclient_tls_cert_filemust be also configured to set up key and certpgbouncer uses to accept client connections.client_tls_key_fileclient_tls_cert_fileclient_tls_ca_fileclient_tls_protocolsWhichTLS protocol versions are allowed. Allowed values:
tlsv1.0,tlsv1.1,tlsv1.2,tlsv1.3. Shortcuts:all(tlsv1.0,tlsv1.1,tlsv1.2,tlsv1.3),secure(tlsv1.2,tlsv1.3),legacy(all).client_tls_ciphersclient_tls_ecdhcurveElliptic Curve name to use for ECDH key exchanges.
Allowed values:
none(DH is disabled),auto(256-bit ECDH), curve name.client_tls_dheparamsAllowed values:
none(DH is disabled),auto(2048-bit DH),legacy(1024-bit DH).server_tls_sslmodeTLS mode to use for connections toPostgres Pro servers.TLS connections are disabled by default.
disablepreferTLS connection is always requested first fromPostgres Pro, when refused connection will be established over plainTCP. Server certificate is not validated.
requireConnection must go overTLS. If server rejects it, plainTCP is not attempted. Server certificate is not validated.
verify-caConnection must go overTLS and server certificate must be valid according to
server_tls_ca_file. Server host name is not checked against certificate.verify-fullConnection must go overTLS and server certificate must be valid according to
server_tls_ca_file. Server host name must match certificate info.
server_tls_ca_fileRoot certificate file to validatePostgres Pro server certificates.
Default: unset.
server_tls_key_filePrivate key forpgbouncer to authenticate againstPostgres Pro server.
Default: not set.
server_tls_cert_fileCertificate for private key.Postgres Pro server can validate it.
Default: not set.
server_tls_protocolsserver_tls_ciphers
Dangerous Timeouts
Low-Level Network Settings
pkt_bufInternal buffer size for packets. Affects size ofTCP packets sent and general memory usage. Actuallibpq packets can be larger than this, so, no need to set it large.
max_packet_sizelisten_backlogsbuf_loopcntHow many times to process data on one connection, before proceeding. Without this limit, one connection with a big result set can stallpgbouncer for a long time. One loop processes one
pkt_bufamount of data. 0 means no limit.Default: 5
suspend_timeoutHow many seconds to wait for buffer flush during SUSPEND or reboot (-R). Connection is dropped if flush does not succeed.
Default: 10
tcp_defer_acceptFor details on this and otherTCP options, please see
man 7 tcp.tcp_socket_buffertcp_keepalivetcp_keepcnttcp_keepidletcp_keepintvl
Section [databases]
"*" acts as fallback database: if the exact name does not exist, its value is taken as connect string for requested database. Such automatically created database entries are cleaned up if they stay idle longer than the time specified inautodb_idle_timeout parameter.
dbnameDestination database name.
Default: same as client-side database name.
hostHost name or IP address to connect to. Host names are resolved at connect time, the result is cached per
dns_max_ttlparameter. When a host name's resolution changes, existing server connections are automatically closed when they are released (according to the pooling mode), and new server connections immediately use the new resolution. IfDNS returns several results, they are used in round-robin manner.portuserpasswordThe length for
passwordis limited to 160 characters maximum.If no password is specified here, the password from the
auth_fileorauth_querywill be used.auth_userpool_sizeSet maximum size of pools for this database. If not set, the
default_pool_sizeis used.reserve_poolSet additional connections for this database. If not set,
reserve_pool_sizeis used.connect_querypool_modeSet the pool mode specific to this database. If not set, the default pool_mode is used.
max_db_connectionsclient_encodingdatestyletimezone
Include Directive
%includefilenameIf the file name is not absolute path it is taken as relative to current working directory.
Authentication File Format
pgbouncer needs its own user database. The users are loaded from a text file in following format:
"username1" "password" ..."username2" "md5abcdef012342345" ..."username2" "SCRAM-SHA-256$iterations:salt$storedkey:serverkey"
Postgres Pro MD5-hidden password format:
"md5" + md5(password + username)
So useradmin with password1234 will have MD5-hidden passwordmd545f2603610af569b6155c45067268c6b.
Postgres Pro SCRAM secret format:
SCRAM-SHA-256$iterations:salt$storedkey:serverkey
HBA File Format
It follows the format ofPostgres Propg_hba.conf file described inSection 19.1.
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: Only methods supported bypgbouncer's
auth_typeare supported, exceptanyandpam, which only work globally. Username map (map=) parameter is not supported.
Example
Minimal config:
[databases]template1 = host=127.0.0.1 dbname=template1 auth_user=someuser[pgbouncer]pool_mode = sessionlisten_port = 6543listen_addr = 127.0.0.1auth_type = md5auth_file = users.txtlogfile = pgbouncer.logpidfile = pgbouncer.pidadmin_users = someuserstats_users = stat_collector
Database defaults:
[databases]; foodb over Unix socketfoodb =; redirect bardb to bazdb on localhostbardb = host=127.0.0.1 dbname=bazdb; access to destination database will go with single userforcedb = host=127.0.0.1 port=300 user=baz password=foo client_encoding=UNICODE datestyle=ISO
Example of a secure function forauth_query:
CREATE OR REPLACE FUNCTION pgbouncer.user_lookup(in i_username text, out uname text, out phash text)RETURNS record AS $$BEGIN SELECT usename, passwd FROM pg_catalog.pg_shadow WHERE usename = i_username INTO uname, phash; RETURN;END;$$ LANGUAGE plpgsql SECURITY DEFINER;REVOKE ALL ON FUNCTION pgbouncer.user_lookup(text) FROM public, pgbouncer;GRANT EXECUTE ON FUNCTION pgbouncer.user_lookup(text) TO pgbouncer;