From 77f08a3beea144e3eddbf34da0a26b1f6893e3ee Mon Sep 17 00:00:00 2001 From: Marko Kreen Date: Mon, 8 Oct 2007 10:06:02 +0000 Subject: [PATCH] revert back to old doc names --- doc/Makefile | 13 +- doc/config.txt | 311 +++++++++++++++------- doc/pgbouncer.1 | 50 +++- doc/pgbouncer.5 | 16 +- doc/pgbouncer.cmdline.txt | 450 -------------------------------- doc/pgbouncer.config.txt | 381 --------------------------- doc/usage.txt | 527 +++++++++++++++++++++++++++++--------- 7 files changed, 692 insertions(+), 1056 deletions(-) delete mode 100644 doc/pgbouncer.cmdline.txt delete mode 100644 doc/pgbouncer.config.txt diff --git a/doc/Makefile b/doc/Makefile index 3555873..8cc549d 100644 --- a/doc/Makefile +++ b/doc/Makefile @@ -8,7 +8,7 @@ MANPAGES = pgbouncer.1 pgbouncer.5 all: $(MANPAGES) -html: pgbouncer.config.html pgbouncer.cmdline.html +html: config.html usage.html install: $(MANPAGES) mkdir -p $(DESTDIR)$(mandir)/man1/ @@ -19,14 +19,14 @@ install: $(MANPAGES) upload: devupload.sh overview.txt $(wiki) devupload.sh todo.txt $(wiki)/ToDo - devupload.sh usage.txt $(wiki)/UsageInfo - devupload.sh config.txt $(wiki)/ConfigFile + #devupload.sh usage.txt $(wiki)/UsageInfo + #devupload.sh config.txt $(wiki)/ConfigFile ifneq ($(XMLTO),) -pgbouncer.1: pgbouncer.cmdline.xml +pgbouncer.1: usage.xml xmlto man $< -pgbouncer.5: pgbouncer.config.xml +pgbouncer.5: config.xml xmlto man $< endif @@ -48,3 +48,6 @@ distclean: clean realclean: distclean rm -f *.[1-9] +web: + rsync -av $(HTML) $(web)/doc/ + diff --git a/doc/config.txt b/doc/config.txt index 09ccf03..ee37abe 100644 --- a/doc/config.txt +++ b/doc/config.txt @@ -1,106 +1,142 @@ -#pragma section-numbers 2 += pgbouncer(5) = -= PgBouncer Config = +== NAME == -[[TableOfContents]] +pgbouncer - Lightweight connection pooler for PostgreSQL. -Config file is in "ini" format. Section names are between "[" and "]". -Lines starting with ";" or "#" are taken as comment and ignored. The characters -";" and "#" are not recognized when they appear later in the line. +== SYNOPSIS == + [databases] + db = ... + [pgbouncer] + ... -== Section [pgbouncer] == +== DESCRIPTION == + +Config file is in "ini" format. Section names are between " and +". Lines starting with ";" or "#" are taken as comment and +ignored. The characters ";" and "#" are not recognized when they +appear later in the line. + +== SECTION [pgbouncer] == === Generic settings === ==== logfile ==== -Specifies log file. Logging is done by open/write/close, so it can be safely + +Specifies log file. Logging is done by open/write/close, so it can be safely rotasted, without informing pooler. Default: not set. - ==== pidfile ==== -Specifies pid file. Without pidfile, the daemonization is not allowed. -Default: not set. +Specifies pid file. Without pidfile, the daemonization is not allowed. +Default: not set. ==== listen_addr ==== -Specifies IPv4 address, where to listen for TCP connections. Or "*" -meaning "listen on all addresses". When not set, only unix socket + +Specifies IPv4 address, where to listen for TCP connections. Or +++"*"+++ +meaning 'listen on all addresses'. When not set, only unix socket connections are allowed. Default: not set. ==== listen_port ==== -On which port to listen on. Applies to both TCP and Unix sockets. + +On which port to listen on. Applies to both TCP and Unix sockets. Default: 6000 ==== unix_socket_dir ==== -Specifies location for Unix sockets. Applies to both listening socket -and server connections. If set to empty string, Unix sockets are disabled. -Default: /tmp +Specifies location for Unix sockets. Applies to both listening socket and +server connections. If set to empty string, Unix sockets are disabled. + +Default: +/tmp+ ==== auth_file ==== -Load user names and passwords from this file. File format used -is same as for PostgreSQL pg_auth/pg_pwd file, so can be pointed -directly to backend file. +Load user names and passwords from this file. File format used is same as for +PostgreSQL pg_auth/pg_pwd file, so can be pointed directly to backend file. Default: not set. ==== auth_type ==== -How to authenticate users. - md5:: - Use MD5-based password check. auth_file may contain both md5-encrypted - or plain-text passwords. +How to authenticate users. - crypt:: - Use crypt(3) based bassword check. auth_file must contain plain-text - passwords. +md5:: + Use MD5-based password check. auth_file may contain both md5-encrypted + or plain-text passwords. Default. - plain:: - Clear-text password is sent over wire. +crypt:: + Use crypt(3) based bassword check. auth_file must contain plain-text + passwords. - trust:: - No authentication is done. Username must still exists in auth_file. +plain:: + Clear-text password is sent over wire. - any:: - Like `trust` but username given is ignored. Requires that all databases - have configured to log in as specific user. +trust:: + No authentication is done. Username must still exists in auth_file. -Default: md5 +any:: + Like trust but username given is ignored. Requires that all databases + have configured to log in as specific user. ==== pool_mode ==== -Specifies when server connection is tagged as reusable for other clients. - session:: - Server is released back to pool after client disconnects. +Specifies when server connection is tagged as reusable for other clients. - transaction:: - Server is released back to pool after transaction finishes. +session:: + Server is released back to pool after client disconnects. Default. - statement:: - Server is released back to pool after query finishes. Long transactions - spanning multiple statements are disallowed in this mode. +transaction:: + Server is released back to pool after transaction finishes. -Default: `session`. +statement:: + Server is released back to pool after query finishes. Long transactions + spanning multiple statements are disallowed in this mode. ==== max_client_conn ==== -Maximin number of client connections allowed. +Maximum number of client connections allowed. When increased then the file +descriptor limits should also be increased. Note that actual number of file +descriptiors used is more that max_client_conn. Theoretical maximum used is: + + max_client_conn + (max_pool_size * total_databases * total_users) + +if each user connects under it's own username to server. If 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 give fds liberately. + +Search for `ulimit` in your favourite shell man page. + +Default: 100 ==== default_pool_size ==== -How many server connection to allow per user/database pair. -Can be overrided in per-database config. +How many server connection to allow per user/database pair. Can be overrided in +per-database config. + +Default: 20 +==== server_round_robin ==== +By default, pgbouncer reuses server connections in LIFO manner, so that few +connections get the most load. This gives best performance if you have single +server serving a database. But if there is TCP round-robin behind a database +IP then it's better if pgbouncer also uses connections in that manner, thus +achieving uniform load. + +Default: 0 === Log settings === @@ -125,88 +161,104 @@ Default: 1 === Console access control === ==== admin_users ==== + List of users that are allowed to run all commands on console. -==== stats_users ==== +Default: empty -List of users that are allowed to run read-only queries on console. -Thats means all SHOW commands except SHOW FDS. +==== stats_users ==== +List of users that are allowed to run read-only queries on console. Thats means +all SHOW commands except SHOW FDS. +Default: empty. === Connection sanity checks, timeouts === ==== server_check_delay ==== -How long to keep released immidiately available, without running -sanity-check query on it. If 0 then the query is ran always. +How long to keep released immidiately available, without running sanity-check +query on it. If 0 then the query is ran always. + +Default: 30 ==== server_check_query ==== -Good variants are `SELECT 1;`, to just see if connection is alive -and `ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT` -to do full reset. +Simple do-nothing query to check if server connection is alive. If empty string, then sanity checking is disabled. +Default: SELECT 1; + ==== server_lifetime ==== -Pooler tries to close server connections that are been connected -longer than this. +Pooler tries to close server connections that are been connected longer than +this. + +Default: 3600 ==== server_idle_timeout ==== If server connection has been idle more than this then there's too many -connections in the pool and this on can be dropped. +connections in the pool and this one can be dropped. + +Default: 600 ==== server_connect_timeout ==== -If connection and login wont finish in this time, the connection will -be closed. +If connection and login wont finish in this time, the connection will be +closed. + +Default: 15 ==== server_login_retry ==== -If login failed, because of failure from connect() or authentication -that pooler waits this much before retrying to connect. +If login failed, because of failure from connect() or authentication that +pooler waits this much before retrying to connect. -==== query_timeout ==== +Default: 15 -Queries running longer than that are canceled. This should be used -only with slightly smaller server-side statement_timeout, to apply only -for network problems. +==== client_login_timeout ==== -Default: 0 (disabled) +If client connect but does not manage to login in this time, it will be +disconnected. Mainly needed to avoid dead connections stalling SUSPEND and thus +online restart. -==== client_idle_timeout ==== +Default: 60 -Client connections idling longer than that are closed. This should be -larger then client-side connection lifetime settings, to apply only for -network problems. +=== Dangerous timeouts === -Default: 0 (disabled) +Setting following timeouts cause unexcpected errors. -==== client_login_timeout ==== +==== query_timeout ==== -If client connect but does not manage to login in this time, -it will be disconnected. Mainly needed to avoid dead connections -stalling SUSPEND and thus online restart. +Queries running longer than that are canceled. This should be used only with +slightly smaller server-side statement_timeout, to apply only for network +problems. Default: 0 (disabled) +==== client_idle_timeout ==== + +Client connections idling longer than that are closed. This should be larger +then client-side connection lifetime settings, to apply only for network +problems. + +Default: 0 (disabled) === Low-level network settings === ==== pkt_buf ==== -Internal buffer size for packets. Affects size of TCP packets sent -and general memory usage. Actual libpq packets can be larger than this -so no need to set it large. +Internal buffer size for packets. Affects size of TCP packets sent and general +memory usage. Actual libpq packets can be larger than this so no need to set it +large. Default: 2048 ==== tcp_defer_accept ==== -Details about following options shouldbe looked from `man 7 tcp` +Details about following options shouldbe looked from man 7 tcp Default: 45 on Linux, otherwise 0 @@ -219,48 +271,111 @@ Default: not set Default: Not set ==== tcp_keepcnt ==== + Default: not set ==== tcp_keepidle ==== + Default: not set + ==== tcp_keepintvl ==== -Default: not set +Default: not set +== SECTION [databases] == -== Section [databases] == +This contains key=value pairs where key will be taken as database name and +value as libpq-connstring style list of key=value pairs. As actual libpq is not +used, so not all features from libpq can be used (service=, quoting). -This contains key=value pairs where key will be taken as database name and value as -libpq-connstring style list of key=value pairs. As actual libpq is not used, so -not all features from libpq can be used (service=, quoting). +=== Location parameters === -=== dbname === +==== dbname ==== Destination database name. Default: same as client-side database name. -=== host === +==== host ==== IP-address to connect to. Default: not set, meaning to use unix-socket. -=== port === +==== port ==== Default: 5432 -=== user, password === +==== user, password ==== + +If +user=+ is set, all connections to destination database will be +done with that user, meaning that there will be only one pool for this +database. + +Otherwise pgbouncer tries to log into destination database with client +username, meaning that there will be one pool per user. + +=== Per-database pool Size === + +==== pool_size ==== + +Set maximum size of pools for this database. If not set, +the default_pool_size is used. + +=== Extra parameters === + +They allow setting default parameters on server connection. + +Note that since version 1.1 PgBouncer honours client +changes for there values, so their use in pgbouncer.ini +is deprecated. + +==== client_encoding ==== + +Ask specific +client_encoding+ from server. + +==== datestyle ==== + +Ask specific +datestyle+ from server. + +==== timezone ==== + +Ask specific +timezone+ from server. + +== Example == + +=== Minimal config === + + [databases] + template1 = host=127.0.0.1 dbname=template1 + + [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 + +=== Database defaults === + + [databases] + + ; foodb over unix socket + foodb = + + ; redirect bardb to bazdb on localhost + bardb = host=127.0.0.1 dbname=bazdb + + ; acceess to dest database will go with single user + forcedb = host=127.0.0.1 port=300 user=baz password=foo client_encoding=UNICODE datestyle=ISO -If user= is set, all connections to destination database will be done -with that user, meaning that there will be only one pool for this database. +== SEE ALSO == -Otherwise pgbouncer tries to log into destination database with client username, -meaning that there will be one pool per user. +pgbouncer(1) -=== client_encoding, datestyle === +https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer[] -As pgbouncer does not pass client startup packet to server, there is no way of specifying -startup paramenters to dest database. These paramenters make possible to set startup -paramenters in pgbouncer config. Escpecially, client_encoding=UNICODE is needed to work -around JDBC driver bug. diff --git a/doc/pgbouncer.1 b/doc/pgbouncer.1 index 500a0b8..b70c452 100644 --- a/doc/pgbouncer.1 +++ b/doc/pgbouncer.1 @@ -1,11 +1,11 @@ .\" Title: pgbouncer .\" Author: .\" Generator: DocBook XSL Stylesheets v1.71.1 -.\" Date: 10/03/2007 +.\" Date: 10/08/2007 .\" Manual: .\" Source: .\" -.TH "PGBOUNCER" "1" "10/03/2007" "" "" +.TH "PGBOUNCER" "1" "10/08/2007" "" "" .\" disable hyphenation .nh .\" disable justification (adjust text to left margin only) @@ -255,14 +255,14 @@ S, for server. .PP user .RS 4 - +Username pgbouncer -connected user. +uses to connect to server. .RE .PP database .RS 4 -database name. +database name on server. .RE .PP state @@ -284,6 +284,16 @@ port Port of PostgreSQL server. .RE .PP +local_addr +.RS 4 +Connection start address on local machine. +.RE +.PP +local_port +.RS 4 +Connection start port on local machine. +.RE +.PP connect_time .RS 4 When the connection was made. @@ -293,6 +303,16 @@ request_time .RS 4 When last request was issued. .RE +.PP +ptr +.RS 4 +Address of internal object for this connection. Used as unique ID. +.RE +.PP +link +.RS 4 +Address of client connection the server is paired with. +.RE .RE .sp .it 1 an-trap @@ -337,6 +357,16 @@ port port client is connected to. .RE .PP +local_addr +.RS 4 +Connection end address on local machine. +.RE +.PP +local_port +.RS 4 +Connection end port on local machine. +.RE +.PP connect_time .RS 4 Timestamp of later client connection. @@ -346,6 +376,16 @@ request_time .RS 4 Timestamp of later client request. .RE +.PP +ptr +.RS 4 +Address of internal object for this connection. Used as unique ID. +.RE +.PP +link +.RS 4 +Address of server connection the client is paired with. +.RE .RE .sp .it 1 an-trap diff --git a/doc/pgbouncer.5 b/doc/pgbouncer.5 index 2317d5c..7039fec 100644 --- a/doc/pgbouncer.5 +++ b/doc/pgbouncer.5 @@ -1,11 +1,11 @@ .\" Title: pgbouncer .\" Author: .\" Generator: DocBook XSL Stylesheets v1.71.1 -.\" Date: 10/03/2007 +.\" Date: 10/08/2007 .\" Manual: .\" Source: .\" -.TH "PGBOUNCER" "5" "10/03/2007" "" "" +.TH "PGBOUNCER" "5" "10/08/2007" "" "" .\" disable hyphenation .nh .\" disable justification (adjust text to left margin only) @@ -208,6 +208,18 @@ How many server connection to allow per user/database pair. Can be overrided in Default: 20 .sp .RE +.sp +.it 1 an-trap +.nr an-no-space-flag 1 +.nr an-break-flag 1 +.br +\fBserver_round_robin\fR +.RS +By default, pgbouncer reuses server connections in LIFO manner, so that few connections get the most load. This gives best performance if you have single server serving a database. But if there is TCP round\-robin behind a database IP then it's better if pgbouncer also uses connections in that manner, thus achieving uniform load. +.sp +Default: 0 +.sp +.RE .SS "Log settings" .sp .it 1 an-trap diff --git a/doc/pgbouncer.cmdline.txt b/doc/pgbouncer.cmdline.txt deleted file mode 100644 index cd1c749..0000000 --- a/doc/pgbouncer.cmdline.txt +++ /dev/null @@ -1,450 +0,0 @@ -= pgbouncer(1) = - -== NAME == - -pgbouncer - Lightweight connection pooler for PostgreSQL. - -== SYNOPSIS == - - pgbouncer [-d][-R][-v] - pgbouncer -V|-h - -== DESCRIPTION == - -+pgbouncer+ is a PostgreSQL connection pooler. Any target application -can be connected to +pgbouncer+ as if it were a PostgreSQL server, -and +pgbouncer+ will manage to connect to the server, or to -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 levels of brutality when -rotating connections: - -Session pooling:: - Most polite method. When client connects, a server connection will - be assigned to it for the whole duration it stays connected. When - client disconnects, the server connection will be put back into pool. - -Transaction pooling:: - Server connection is assigned to client only during a transaction. - When PgBouncer notices that transaction is over, the server will - be put back into pool. - -Statement pooling:: - Most aggressive method. The server connection will be put back into - pool immidiately 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+. - -== QUICK-START == - -Basic setup and usage as following. - - 1. Create config file. Details in +pgbouncer(5)+. Simplest: - - [databases] - template1 = host=127.0.0.1 port=5432 dbname=template1 - - [pgbouncer] - 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 - - 2. Create users.txt file: - - "someuser" "same_password_as_in_server" - - 3. Launch +pgbouncer+: - - $ pgbouncer -d pgbouncer.ini - - 4. have your application (or the +psql+ client) connect to - +pgbouncer+ instead of directly to PostgreSQL server. - - $ psql -h 127.0.0.1 -p 6543 -U someuser template1 - - 5. manage +pgbouncer+ by connecting to special administration - database +pgbouncer+ and issue +show help;+ to begin: - - $ psql -h 127.0.0.1 -p 6543 -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 - - 6. If you change some changeable configuration settings in the - configuration file, you can reload the file with: - - pgbouncer=# RELOAD; - -== COMMAND LINE SWITCHES == - --d:: - Run in background. Without it the process will run in foreground. - --R:: - Do a online restart. That means connecting to running process, loading - open sockets from it and using them. - --v:: - Increase verbosity. - --V:: - Show version. - --h:: - Show short help. - - -== ADMIN CONSOLE == - -Console is available by connecting into standard listen_addr/listen_port -and specifying string +pgbouncer+ as database. - - $ psql -h 127.0.0.1 -p 6543 pgbouncer - -=== SHOW COMMANDS === - -There is always a extra database available: "pgbouncer". When -connecected to it, there is possible to look and change pooler -settings. - -The +SHOW+ commands output some rows, the columns contained are -described here. - -==== SHOW STATS; ==== - -Shows statistics. - -database:: - Statistics are presented per database. - -total_requests:: - Total number of +SQL+ requests pooled by +pgbouncer+. - -total_received:: - Total volume in bytes of network trafic received by +pgbouncer+. - -total_sent:: - Total volume in bytes of network trafic sent by +pgbouncer+. - -total_query_time:: - Total number of milliseconds spent by +pgloader+ when actively - connected to PostgreSQL. - -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 milliseconds. - -==== SHOW SERVERS; ==== - -type:: - S, for server. - -user:: - Username +pgbouncer+ uses to connect to server. - -database:: - database name on server. - -state:: - State of the pgbouncer 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. - -==== SHOW CLIENTS; ==== - -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. - -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 later client connection. - -request_time:: - Timestamp of later client request. - -ptr:: - Address of internal object for this connection. - Used as unique ID. - -link:: - Address of server connection the client is paired with. - -==== SHOW POOLS; ==== - -A new pool entry is made for each couple of (database, user). - -database:: - database name. - -user:: - username. - -cl_active:: - Count of currently +active+ client connections. - -cl_waiting:: - Count of currently +waiting+ client connections. - -sv_active:: - Count of currently +active+ server connections. - -sv_idle:: - Count of currently +idle+ server connections. - -sv_used:: - Count of currently +used+ server connections. - -sv_tested:: - Count of currently +tested+ server connections. - -sv_login:: - Count of server connections currently +login+ to PostgreSQL. - -maxwait:: - How long has first (oldest) client in queue waited, in second. - If this start increasing, then current pool of servers does - not handle requests quick enough. Reason may be either overloaded - server or just too small pool_size. - - -==== SHOW LISTS; ==== - -Show following internal information, in columns (not rows): - -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. - -==== SHOW USERS; ==== - -Show one line per user, under the +name+ column name. - -==== SHOW DATABASES; ==== - -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 PostgreSQL is forced to the given user, whatever the - client user. - -pool_size:: - Maximum number of server connections. - -==== SHOW FDS; ==== - -Shows list of fds in use. When the connected user has username -"pgbouncer", connects thru unix socket and has same UID as running -process, the actual fds are passed over connection. This mechanism is -used to do online restart. - -fd:: - File descriptor numeric value. - -task:: - One of +pooler+, +client+ or +server+. - -user:: - User of the connection using the FD. - -database:: - database of the connection using the FD. - -addr:: - IP address of the connection using the FD, +unix+ if a unix socket - is used. - -port:: - port used by the connection using the FD. - -cancel:: - cancel key for this connection. - -link:: - fd for corresponding server/client. NULL if idle. - -==== SHOW CONFIG; ==== - -Show the current configuration settings, one per row, with following -columns: - -key:: - configuration variable name - -value:: - configures value - -changeable:: - Either +yes+ or +no+, shows if the variable is changeable when running. - If +no+, the variable can be changed only boot-time. - -=== PROCESS CONTROLLING COMMANDS === - -==== PAUSE; ==== - -PgBouncer tries to disconnect from all servers, first waiting for all queries -to complete. The command will not return before all is done. - -==== SUSPEND; ==== - -All socket buffers are flushed and PgBouncer stops listening data on them. The -command will not return before all is done. - -==== RESUME; ==== - -Resume work from previous +PAUSE+ or +SUSPEND+ command. - -==== SHUTDOWN; ==== - -The PgBouncer process will exit. - -==== RELOAD; ==== - -The PgBouncer process will reload its configuration file and update -changeable settings. - -=== SIGNALS === - -SIGHUP:: - Reload config. Same as issuing command +RELOAD;+ on console. - -SIGINT:: - Safe shutdown. Same as issuing +PAUSE;+ and +SHUTDOWN;+ on console. - -SIGTERM:: - Immediate shutdown. Same as issuing +SHUTDOWN;+ on console. - -=== LIBEVENT SETTINGS === - -From libevent docs: - - 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. - -=== AUTHENTICATION FILE FORMAT === - -PgBouncer needs its own user database. The users are loaded from text -file that should be in same format as PostgreSQL's +pg_auth/pg_pwd+ -file. - - "username1" "password" ... - "username2" "md12342345234" ... - -There shoud be at least 2 fields, surrounded by double quotes. First -is username and second either plain-text or md5-hashed -password. PgBouncer ignores rest of the line. - -Such file format allows to direct PgBouncer directly to PostgreSQL -user file under data directory. - -== SEE ALSO == - -pgbouncer(5) - configuration settings descriptions. - -https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer[] diff --git a/doc/pgbouncer.config.txt b/doc/pgbouncer.config.txt deleted file mode 100644 index ee37abe..0000000 --- a/doc/pgbouncer.config.txt +++ /dev/null @@ -1,381 +0,0 @@ -= pgbouncer(5) = - -== NAME == - -pgbouncer - Lightweight connection pooler for PostgreSQL. - -== SYNOPSIS == - - [databases] - db = ... - - [pgbouncer] - ... - -== DESCRIPTION == - -Config file is in "ini" format. Section names are between " and -". Lines starting with ";" or "#" are taken as comment and -ignored. The characters ";" and "#" are not recognized when they -appear later in the line. - -== SECTION [pgbouncer] == - -=== Generic settings === - -==== logfile ==== - -Specifies log file. Logging is done by open/write/close, so it can be safely -rotasted, without informing pooler. - -Default: not set. - -==== pidfile ==== - -Specifies pid file. Without pidfile, the daemonization is not allowed. - -Default: not set. - -==== listen_addr ==== - -Specifies IPv4 address, where to listen for TCP connections. Or +++"*"+++ -meaning 'listen on all addresses'. When not set, only unix socket -connections are allowed. - -Default: not set. - -==== listen_port ==== - -On which port to listen on. Applies to both TCP and Unix sockets. - -Default: 6000 - -==== unix_socket_dir ==== - -Specifies location for Unix sockets. Applies to both listening socket and -server connections. If set to empty string, Unix sockets are disabled. - -Default: +/tmp+ - -==== auth_file ==== - -Load user names and passwords from this file. File format used is same as for -PostgreSQL pg_auth/pg_pwd file, so can be pointed directly to backend file. - -Default: not set. - -==== auth_type ==== - -How to authenticate users. - -md5:: - Use MD5-based password check. auth_file may contain both md5-encrypted - or plain-text passwords. Default. - -crypt:: - Use crypt(3) based bassword check. auth_file must contain plain-text - passwords. - -plain:: - Clear-text password is sent over wire. - -trust:: - No authentication is done. Username must still exists in auth_file. - -any:: - Like trust but username given is ignored. Requires that all databases - have configured to log in as specific user. - -==== pool_mode ==== - -Specifies when server connection is tagged as reusable for other clients. - -session:: - Server is released back to pool after client disconnects. Default. - -transaction:: - Server is released back to pool after transaction finishes. - -statement:: - Server is released back to pool after query finishes. Long transactions - spanning 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. Note that actual number of file -descriptiors used is more that max_client_conn. Theoretical maximum used is: - - max_client_conn + (max_pool_size * total_databases * total_users) - -if each user connects under it's own username to server. If 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 give fds liberately. - -Search for `ulimit` in your favourite shell man page. - -Default: 100 - -==== default_pool_size ==== - -How many server connection to allow per user/database pair. Can be overrided in -per-database config. - -Default: 20 - -==== server_round_robin ==== - -By default, pgbouncer reuses server connections in LIFO manner, so that few -connections get the most load. This gives best performance if you have single -server serving a database. But if there is TCP round-robin behind a database -IP then it's better if pgbouncer also uses connections in that manner, thus -achieving uniform load. - -Default: 0 - -=== Log settings === - -==== log_connections ==== - -Log successful logins. - -Default: 1 - -==== log_disconnections ==== - -Log disconnections with reasons. - -Default: 1 - -==== log_pooler_errors ==== - -Log error messaged pooler sends to clients. - -Default: 1 - -=== Console access control === - -==== admin_users ==== - -List of users that are allowed to run all commands on console. - -Default: empty - -==== stats_users ==== - -List of users that are allowed to run read-only queries on console. Thats means -all SHOW commands except SHOW FDS. - -Default: empty. - -=== Connection sanity checks, timeouts === - -==== server_check_delay ==== - -How long to keep released immidiately available, without running sanity-check -query on it. If 0 then the query is ran always. - -Default: 30 - -==== server_check_query ==== - -Simple do-nothing query to check if server connection is alive. - -If empty string, then sanity checking is disabled. - -Default: SELECT 1; - -==== server_lifetime ==== - -Pooler tries to close server connections that are been connected longer than -this. - -Default: 3600 - -==== server_idle_timeout ==== - -If server connection has been idle more than this then there's too many -connections in the pool and this one can be dropped. - -Default: 600 - -==== server_connect_timeout ==== - -If connection and login wont finish in this time, the connection will be -closed. - -Default: 15 - -==== server_login_retry ==== - -If login failed, because of failure from connect() or authentication that -pooler waits this much before retrying to connect. - -Default: 15 - -==== client_login_timeout ==== - -If client connect but does not manage to login in this time, it will be -disconnected. Mainly needed to avoid dead connections stalling SUSPEND and thus -online restart. - -Default: 60 - -=== Dangerous timeouts === - -Setting following timeouts cause unexcpected errors. - -==== query_timeout ==== - -Queries running longer than that are canceled. This should be used only with -slightly smaller server-side statement_timeout, to apply only for network -problems. - -Default: 0 (disabled) - -==== client_idle_timeout ==== - -Client connections idling longer than that are closed. This should be larger -then client-side connection lifetime settings, to apply only for network -problems. - -Default: 0 (disabled) - -=== Low-level network settings === - -==== pkt_buf ==== - -Internal buffer size for packets. Affects size of TCP packets sent and general -memory usage. Actual libpq packets can be larger than this so no need to set it -large. - -Default: 2048 - -==== tcp_defer_accept ==== - -Details about following options shouldbe looked from man 7 tcp - -Default: 45 on Linux, otherwise 0 - -==== tcp_socket_buffer ==== - -Default: not set - -==== tcp_keepalive ==== - -Default: Not set - -==== tcp_keepcnt ==== - -Default: not set - -==== tcp_keepidle ==== - -Default: not set - -==== tcp_keepintvl ==== - -Default: not set - -== SECTION [databases] == - -This contains key=value pairs where key will be taken as database name and -value as libpq-connstring style list of key=value pairs. As actual libpq is not -used, so not all features from libpq can be used (service=, quoting). - -=== Location parameters === - -==== dbname ==== - -Destination database name. - -Default: same as client-side database name. - -==== host ==== - -IP-address to connect to. - -Default: not set, meaning to use unix-socket. - -==== port ==== - -Default: 5432 - -==== user, password ==== - -If +user=+ is set, all connections to destination database will be -done with that user, meaning that there will be only one pool for this -database. - -Otherwise pgbouncer tries to log into destination database with client -username, meaning that there will be one pool per user. - -=== Per-database pool Size === - -==== pool_size ==== - -Set maximum size of pools for this database. If not set, -the default_pool_size is used. - -=== Extra parameters === - -They allow setting default parameters on server connection. - -Note that since version 1.1 PgBouncer honours client -changes for there values, so their use in pgbouncer.ini -is deprecated. - -==== client_encoding ==== - -Ask specific +client_encoding+ from server. - -==== datestyle ==== - -Ask specific +datestyle+ from server. - -==== timezone ==== - -Ask specific +timezone+ from server. - -== Example == - -=== Minimal config === - - [databases] - template1 = host=127.0.0.1 dbname=template1 - - [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 - -=== Database defaults === - - [databases] - - ; foodb over unix socket - foodb = - - ; redirect bardb to bazdb on localhost - bardb = host=127.0.0.1 dbname=bazdb - - ; acceess to dest database will go with single user - forcedb = host=127.0.0.1 port=300 user=baz password=foo client_encoding=UNICODE datestyle=ISO - -== SEE ALSO == - -pgbouncer(1) - -https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer[] - diff --git a/doc/usage.txt b/doc/usage.txt index 960800f..cd1c749 100644 --- a/doc/usage.txt +++ b/doc/usage.txt @@ -1,153 +1,450 @@ -#pragma section-numbers 2 += pgbouncer(1) = -= PgBouncer usage details = +== NAME == -[[TableOfContents]] +pgbouncer - Lightweight connection pooler for PostgreSQL. -== Building == +== SYNOPSIS == -PgBouncer uses [http://monkey.org/~provos/libevent/ libevent] -for low-level socket handling. When this is installed just run: + pgbouncer [-d][-R][-v] + pgbouncer -V|-h -{{{ -$ ./configure --prefix=/usr/local --with-libevent=/prefix -$ make -$ make install -}}} +== DESCRIPTION == -== Command line usage == -{{{ -pgbouncer [-d][-R][-v] config.ini -pgbouncer -V|-h -}}} ++pgbouncer+ is a PostgreSQL connection pooler. Any target application +can be connected to +pgbouncer+ as if it were a PostgreSQL server, +and +pgbouncer+ will manage to connect to the server, or to +reuse one of its existing connections. -Where switches are: +The aim of +pgbouncer+ is to lower the performance impact of opening +new connections to PostgreSQL. - -d:: - Run in background. Without it the process will run in foreground. +In order not to compromise transaction semantics for connection +pooling, +pgbouncer+ supports several levels of brutality when +rotating connections: - -R:: - Do a online restart. That means connecting to running process, - loading open sockets from it and using them. +Session pooling:: + Most polite method. When client connects, a server connection will + be assigned to it for the whole duration it stays connected. When + client disconnects, the server connection will be put back into pool. - -v:: - Increase verbosity. +Transaction pooling:: + Server connection is assigned to client only during a transaction. + When PgBouncer notices that transaction is over, the server will + be put back into pool. - -V:: - Show version. +Statement pooling:: + Most aggressive method. The server connection will be put back into + pool immidiately after a query completes. Multi-statement + transactions are disallowed in this mode as they would break. - -h:: - Show short help. +The administration interface of +pgbouncer+ consists of some new ++SHOW+ commands available when connected to a special 'virtual' +database +pgbouncer+. +== QUICK-START == -== Admin Console == +Basic setup and usage as following. -There is always a extra database available: "pgbouncer". -When connecected to it, there is possible to look and change -pooler settings. + 1. Create config file. Details in +pgbouncer(5)+. Simplest: + + [databases] + template1 = host=127.0.0.1 port=5432 dbname=template1 + + [pgbouncer] + 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 + + 2. Create users.txt file: + + "someuser" "same_password_as_in_server" + + 3. Launch +pgbouncer+: + + $ pgbouncer -d pgbouncer.ini + + 4. have your application (or the +psql+ client) connect to + +pgbouncer+ instead of directly to PostgreSQL server. + + $ psql -h 127.0.0.1 -p 6543 -U someuser template1 + + 5. manage +pgbouncer+ by connecting to special administration + database +pgbouncer+ and issue +show help;+ to begin: + + $ psql -h 127.0.0.1 -p 6543 -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 + + 6. If you change some changeable configuration settings in the + configuration file, you can reload the file with: + + pgbouncer=# RELOAD; + +== COMMAND LINE SWITCHES == + +-d:: + Run in background. Without it the process will run in foreground. + +-R:: + Do a online restart. That means connecting to running process, loading + open sockets from it and using them. + +-v:: + Increase verbosity. + +-V:: + Show version. + +-h:: + Show short help. + + +== ADMIN CONSOLE == + +Console is available by connecting into standard listen_addr/listen_port +and specifying string +pgbouncer+ as database. + + $ psql -h 127.0.0.1 -p 6543 pgbouncer + +=== SHOW COMMANDS === + +There is always a extra database available: "pgbouncer". When +connecected to it, there is possible to look and change pooler +settings. + +The +SHOW+ commands output some rows, the columns contained are +described here. + +==== SHOW STATS; ==== -{{{ -SHOW STATS; -}}} Shows statistics. -{{{ -SHOW SERVERS; -SHOW CLIENTS; -SHOW POOLS; -SHOW LISTS; -}}} -Shows internal info. - -{{{ -SHOW USERS; -SHOW DATABASES; -}}} -Shows loaded users and databases. - -{{{ -SHOW FDS; -}}} -Shows list of fds in use. When the connected user has username -"pgbouncer", connects thru unix socket and has same UID as running process -the actual fds are passed over connection. This mechanism is used -to do online restart. - -{{{ -PAUSE; -}}} -PgBouncer tries to disconnect from all servers, first waiting for -all queries to complete. The command will not return before all is done. - -{{{ -SUSPEND; -}}} - -All socket buffers are flushed and PgBouncer stops listening data on them. -The command will not return before all is done. - -{{{ -RESUME -}}} - -Resume work from previous PAUSE or SUSPEND command. - -{{{ -SHUTDOWN -}}} -The PgBouncer process will exit. +database:: + Statistics are presented per database. + +total_requests:: + Total number of +SQL+ requests pooled by +pgbouncer+. + +total_received:: + Total volume in bytes of network trafic received by +pgbouncer+. + +total_sent:: + Total volume in bytes of network trafic sent by +pgbouncer+. + +total_query_time:: + Total number of milliseconds spent by +pgloader+ when actively + connected to PostgreSQL. + +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 milliseconds. + +==== SHOW SERVERS; ==== + +type:: + S, for server. + +user:: + Username +pgbouncer+ uses to connect to server. + +database:: + database name on server. + +state:: + State of the pgbouncer 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. + +==== SHOW CLIENTS; ==== + +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. + +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 later client connection. + +request_time:: + Timestamp of later client request. + +ptr:: + Address of internal object for this connection. + Used as unique ID. + +link:: + Address of server connection the client is paired with. +==== SHOW POOLS; ==== -== Online restart == +A new pool entry is made for each couple of (database, user). -PgBouncer supports restart without dropping connections. When launched -with switch "-R", it will connect to running PgBouncer process via -unix socket and issues commands: +database:: + database name. -{{{ -SUSPEND; -SHOW FDS; -SHUTDOWN; -}}} -Then it waits until old process shuts down and then starts listening on -aquired sockets. +user:: + username. -== Signals == +cl_active:: + Count of currently +active+ client connections. - SIGHUP:: - Reload config. +cl_waiting:: + Count of currently +waiting+ client connections. - SIGINT:: - Safe shutdown. +sv_active:: + Count of currently +active+ server connections. - SIGTERM:: - Immidiate shutdown. +sv_idle:: + Count of currently +idle+ server connections. -== libevent settings == +sv_used:: + Count of currently +used+ server connections. + +sv_tested:: + Count of currently +tested+ server connections. + +sv_login:: + Count of server connections currently +login+ to PostgreSQL. + +maxwait:: + How long has first (oldest) client in queue waited, in second. + If this start increasing, then current pool of servers does + not handle requests quick enough. Reason may be either overloaded + server or just too small pool_size. + + +==== SHOW LISTS; ==== + +Show following internal information, in columns (not rows): + +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. + +==== SHOW USERS; ==== + +Show one line per user, under the +name+ column name. + +==== SHOW DATABASES; ==== + +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 PostgreSQL is forced to the given user, whatever the + client user. + +pool_size:: + Maximum number of server connections. + +==== SHOW FDS; ==== + +Shows list of fds in use. When the connected user has username +"pgbouncer", connects thru unix socket and has same UID as running +process, the actual fds are passed over connection. This mechanism is +used to do online restart. + +fd:: + File descriptor numeric value. + +task:: + One of +pooler+, +client+ or +server+. + +user:: + User of the connection using the FD. + +database:: + database of the connection using the FD. + +addr:: + IP address of the connection using the FD, +unix+ if a unix socket + is used. + +port:: + port used by the connection using the FD. + +cancel:: + cancel key for this connection. + +link:: + fd for corresponding server/client. NULL if idle. + +==== SHOW CONFIG; ==== + +Show the current configuration settings, one per row, with following +columns: + +key:: + configuration variable name + +value:: + configures value + +changeable:: + Either +yes+ or +no+, shows if the variable is changeable when running. + If +no+, the variable can be changed only boot-time. + +=== PROCESS CONTROLLING COMMANDS === + +==== PAUSE; ==== + +PgBouncer tries to disconnect from all servers, first waiting for all queries +to complete. The command will not return before all is done. + +==== SUSPEND; ==== + +All socket buffers are flushed and PgBouncer stops listening data on them. The +command will not return before all is done. + +==== RESUME; ==== + +Resume work from previous +PAUSE+ or +SUSPEND+ command. + +==== SHUTDOWN; ==== + +The PgBouncer process will exit. + +==== RELOAD; ==== + +The PgBouncer process will reload its configuration file and update +changeable settings. + +=== SIGNALS === + +SIGHUP:: + Reload config. Same as issuing command +RELOAD;+ on console. + +SIGINT:: + Safe shutdown. Same as issuing +PAUSE;+ and +SHUTDOWN;+ on console. + +SIGTERM:: + Immediate shutdown. Same as issuing +SHUTDOWN;+ on console. + +=== LIBEVENT SETTINGS === From libevent docs: -{{{ -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. -}}} + 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. -== Authentication file format == +=== AUTHENTICATION FILE FORMAT === -PgBouncer needs its own user database. The users are loaded from -text file that should be in same format as PostgreSQL's pg_auth/pg_pwd +PgBouncer needs its own user database. The users are loaded from text +file that should be in same format as PostgreSQL's +pg_auth/pg_pwd+ file. -{{{ -"username1" "password" ... -"username2" "md12342345234" ... -}}} + "username1" "password" ... + "username2" "md12342345234" ... -There shoud be at least 2 fields, surrounded by double quotes. First -is username and second either plain-text or md5-hashed password. -PgBouncer ignores rest of the line. +There shoud be at least 2 fields, surrounded by double quotes. First +is username and second either plain-text or md5-hashed +password. PgBouncer ignores rest of the line. Such file format allows to direct PgBouncer directly to PostgreSQL user file under data directory. + +== SEE ALSO == + +pgbouncer(5) - configuration settings descriptions. + +https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer[] -- 2.50.1