Pgbouncer Administrator Handbook

[Edit] Pgbouncer (5)

Directory [Hide] 1 pgbouncer (5) 1.1 name 1.2 Summary 1.3 Description 1.4 [Pgbouncer] segment 1.4.1 Common settings 1.4.1.1 logfile 1.4.1.2 pidfile 1.4.1.3 listen_addr 1.4.1.4 listen_port 1.4.1.5 unix_socket_dir 1.4.1.6 auth_file 1.4.1.7 auth_type 1.4.1.8 pool_mode 1.4.1.9 max_client_conn 1.4.1.10 default_pool_size 1.4.1.11 server_round_robin 1.4.2 Log Settings 1.4.2.1 syslog 1.4.2.2 syslog_facility 1.4.2.3 log_conn Ections 1.4.2.4 log_disconnections 1.4.2.5 log_pooler_errors 1.4.3 Console access control 1.4.3.1 admin_users 1.4.3.2 stats_users 1.4.4 Company Connect health Check, timeout 1.4.4.1 server_reset_query 1.4.4.2 server_check_delay 1.4.4.3 server_check_query 1.4.4.4 server_lifetime 1.4.4.5 server_idle_timeout 1.4.4.6 server_connect_timeout 1.4.4.7 server_login_retry 1.4.4.8 client_login_timeout 1.4.5 Dangerous timeout 1.4.5.1 query_timeout 1.4.5.2 client_idle_timeout 1.4.6 The underlying network settings 1.4.6.1 pkt_buf 1.4.6.2 tcp_defer_accept 1.4.6.3 Tcp_socket_buffer 1.4.6.4 tcp_keepalive 1.4.6.5 tcp_keepcnt 1.4.6.6 tcp_keepidle 1.4.6.7 TCP_KEEPINTVL 1.5 [Databases] section 1.5.1 Position parameter 1.5.1.1 dbname 1.5.1.2 Host 1.5.1.3 Port 1.5.1.4 User, password 1.5.2 connection pool size per database 1.5.2.1 pool_size 1.5.3 additional parameters 1.5.3.1 client_encoding 1.5.3.2 E 1.5.3.3 TimeZone 1.6 example 1.7 see Also

[Edit] name

Pgbouncer-postgresql a lightweight connection pool. [edit] Summary

[databases] db = ...

[Pgbouncer] ... [Edit] Description

The configuration file is in "INI" format. The name of the segment is between "and". To Or the line that begins with ' "is treated as a comment and is ignored. ";" appearing at a point other than the beginning of the line. and ' "will not be recognized. [Edit] [pgbouncer] section [edit] Common settings [edit] logfile

Declares a log file. The log file is kept open until Kill-hup or RELOAD on the console; After the completion.

Default: Not set. [edit] pidfile

Declare the PID file. It is not allowed to be a daemon without pidfile.

Default: No settings. [edit] listen_addr

Declares a IPV4 address for listening on a TCP connection. or * means "listen to all the addresses." If not set, only UNIX socket connections are allowed.

Default: No settings [edit] listen_port

which port to monitor. Applies to TCP and UNIX sockets.

Default: 6000 [edit] unix_socket_dir

Declares the location of the Unix socket. Applies to listening for sockets and server connections. If set to an empty string, the Unix socket is closed.

Default:/tmp [edit] auth_file

The user name and password are loaded from this file. The file format used is the same as the PostgreSQL pg_auth/pg_pwd file, so you can point directly to the backend file.

Default: Not set. [edit] auth_type

How to authenticate users.

MD5 use MD5 as the basis for password checking. Auth_file can contain MD5 encrypted or plaintext passwords. The default value.

Crypt uses crypt (3) as the basis for password checking. Auth_file must contain a plaintext password.

Plain is sending a plaintext password on the line.

Trust does not do certification. The user name must still exist in the auth_file.

Any like trust, but given the user name is ignored. All databases are required to be configured to log on with a specific user. [edit] pool_mode

Declares when a server connection is marked for reuse by another client.

Session after the client disconnects, the server releases back to the connection pool. Default.

The Transaction Server releases back to the connection pool after the transaction completes.

Statement after the query has finished, the server releases the connection pool back. Long transactions spanning multiple statements are not allowed in this mode. [edit] max_client_conn

The maximum number of client connections allowed. If you increase this value, it is best to increase the file descriptor limit as well. Please note that the actual number of file descriptors is always more than max_client_conn. The maximum number of descriptors to be used theoretically is:

  Max_client_conn + (max_pool_size * total_databases * total_users)

This assumes that each user is connecting to the server with its user name. If the database user declares in the connection string (all users are connected to the same user), then the maximum value of the theory is:

  Max_client_conn + (Max_pool_size * total_databases)

The maximum theoretical value should never be achieved unless someone intentionally designed a special load for him. Still, it means you should give FD some space.

Look at the Ulimit in your favorite hand album.

Default: [edit] default_pool_size

The number of allowed server connections per user/database. Can be overridden in database-related configurations.

Default: [edit] Server_round_robin

By default, pgbouncer the server-side connection in a LIFO manner, so that most of the load is borne by a small number of connections. This is the best performance when you have only one server to do database service. But if there is a TCP rotation mechanism behind the database IP, then it's best to use the pbcouncer, so that you can get a consistent load.

Default: 0 [Edit] Log Settings [edit] syslog

Toggle Syslog on/Off

Default: 0 [edit] syslog_facility

Under what facilities is the log sent to the syslog. May be: autho,authopriv,daemon,user,local0-7

Default: Daemon [edit] log_connections

Log logs that have been successfully logged.

Default: 1 [edit] log_disconnections

Record the reason for the disconnect.

Default: 1 [edit] log_pooler_errors

Logs the error log that connection Chi Fa sent to the client.

Default: 1 [Edit] Console access control [edit] admin_users

Lists all users who are allowed to run commands on the console.

Default: Empty [edit] stats_users

Lists the users who are allowed to run read-only queries on the console. This means all the show commands except show FDS.

default: Empty. [edit] Connect health Check, timeout [edit] server_reset_query

When the connection is released, the server can be used by other clients before the server is sent to the query. At that point, there is no transaction in process, so it should not include ABORT or ROLLBACK.

8.2 and earlier versions of the better way are:

Server_reset_query = Reset All; SET session AUTHORIZATION DEFAULT;

For a 8.3 or higher version, we can do this:

Server_reset_query = Discard all; [edit] Server_check_delay

How long to keep a freed connection in the immediate available state without performing a health query. If 0, the query is always running.

Default: [edit] Server_check_query

Simply do nothing for the query to check whether the server connection is alive.

If it is an empty string, then the health check is turned off.

Default: SELECT 1; [edit] Server_lifetime

Connection pooling attempts to turn off server connections that have been connected longer than this value.

Default: 3600 [edit] Server_idle_timeout

If the server connection is idle longer than this, there is too much connectivity in the connection pool, so this can be turned off.

Default: [edit] Server_connect_timeout

If the connection and login will not be completed during this time, the connection will be closed.

Default: [edit] Server_login_retry

If the login failed to authenticate from connect (), the connection pool waits for the time and then tries to reconnect.

Default: [edit] Client_login_timeout

If the client logs on, but cannot complete the login during this time, it will be disconnected. The main purpose is to avoid dead connections that are still SUSPEND and thus lead to online reboots.

Default: [Edit] Dangerous timeout

Set the following timeout for unknown reasons. [edit] Query_timeout

Queries that run longer than this section are canceled. This should be used in conjunction with smaller server-side statement_timeout to help deal with network problems only.

Default: 0 (OFF) [edit] Client_idle_timeout

Client connections are managed when they are idle for more than this period of time. This should be longer than the lifetime of the client connection, so that only network problems can be handled.

default: 0 (OFF) [edit] The underlying network settings [edit] pkt_buf

The internal buffer size used for the network package. Affects the TCP packet emitted and the general memory usage. The actual LIBPQ package can be larger than this, so there is no need to set it too large.

Default: 2048 [edit] tcp_defer_accept

The details of the following options should be obtained from Man 7 TCP

Default: In Linux is 45, otherwise it is 0