Establishment of postgresql database connection pool pgbouncer

 

 

Author: He Weiping
Source:Http://www.pgsqldb.org/mwiki/index.php/Pgbouncer%E6%90%AD%E5%BB%BA%E6%95%B0%E6%8D% AE %E5%BA%93%E8%BF%9E%E6%8E%A5%E6%B1%A0 

 

 

 

Recently, we have used PostgreSQL for backend databases in some projects. Due to the need to control the client connection usage type and connections, pgbouncer is used for implementation.

Pgbouncer-related basic documentsPgsqldb Chinese siteI have already provided many documents, so I will not describe them more here. I will share with you some of the key points and problems I have encountered in practice.

OK, there is not much lung talk. Start with the question. d

 

 

Gbouncer Introduction

• What is pgbouncer?

Pgbouncer is a lightweight database connection pool.Download

• Functions of pgbouncer

1. Pgbouncer can establish a bridge between the backend database and the front-end application. pgbouncer can connect to the backend database.
2. Restrict client connections to prevent excessive or malicious connection requests.

• Features of pgbouncer

1. Low memory consumption (2 k/connection by default), because the bouncer does not need to accept the complete data packet every time.
2. Different databases can be connected to one machine, while the client is transparent.
3. Supports online reconfiguration without restarting
4. Only V3 protocol is supported. Therefore, the backend version must be greater than or equal to 7.4.

 

Start using pgbouncer

• Basic INI file configuration (Suppose we have created a new INI File/usr/local/pgsql/CONF/pgbouncer. INI)

1. Add the connection string of the target database, which indicates the backend databases in which pgbouncer will establish the connection, for example:

template1 = host=127.0.0.1 port=5432 dbname=template1

2. Set the listening port of pgbouncer, Port = 5555, default value: 6000

3. Create a user list file and add user information. This user is the connection user name that can be used by the client, for example:

A. zhaoyi @ zhaoyi-LAPTOP: [~] $ Echo "user" "password">/usr/local/pgsql/user.txt B. Set in ini: auth_file =/usr/local/pgsql/user.txt

4. Create an admin user and add admin_users = user in the configuration. You can use this user name to connect to pgbouncer and view the running status. Note: This user is an existing user in user.txt.

• Start and test the connection/view the running status

1. Start: pgbouncer-D pgbouncer. ini

2. test connection: Psql-H 127.0.0.1-P 6000-u user template1

3. Use the admin user to connect to pgbouncer to view the Configuration:

psql -h 127.0.0.1 -p 6000 -U user pgbouncerpgbouncer=# show config;

3. Use the admin user to connect to pgbouncer to view the running status:

Pgbouncer = # Show stats; pgbouncer = # Show lists; pgbouncer = # Show pools; pgbouncer = # Show databases; # use the following command to view other running parameters: pgbouncer = # Show help;

4. Parameter Modification: If you have modified the relevant parameters in the INI file, you must run the following command to notify bouncer to re-read the configuration content:

pgbouncer=# reload;

Pgbouncer connection type

1. Session pool connection: a session is the Life Cycle. when the client is disconnected, the connection is closed and released back to the connection pool.
2. Transaction connection: the server connection is only assigned to the client in the transaction process.
3. Statement connection: after each query ends, the server connection is immediately put back into the connection pool. Transactions with many statements are not allowed in this mode. Otherwise, the following prompt is displayed, for example:

test=# begin; ERROR:  Long transactions not allowed   server closed the connection unexpectedly   This probably means the server terminated abnormally   before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded.

Client Connection Method

Here, I will briefly describe how the server handles different client connection methods, using Perl as an example:

$dbh = DBI -> connect('dbi:Pg:dbname=test;host=127.0.0.1;port=6000', 'postgres', ' ' ) or die $DBI::errstr;

1. When the client is autocommit, transaction type = statements type
2. The pool type is transaction. If the client is not autocommit, commit must be displayed; otherwise, the connection is not released.
3. If the pool type is statements, the connection does not support long statements. Therefore, the client must use autocommit. Otherwise, an error will still be reported.

Autocommit Configuration:

{'AutoCommit' => 0}

Connection parameter configuration and client status

• Max_client_conn

1. This is the maximum number of allowed client connections
2. You can view the information through show clients. When the client segment connection is greater than this setting, the client will obtain the following information:

 psql: ERROR:  no more connections allowed

• Default_pool_size

1. This is the maximum number of connections allowed in the current connection pool.
2. For example, default_pool_size = 3. If the connection pool does not have a spare connection, if the connected client attempts to execute a query request to the backend through pgbouncer, the client will always process the waiting state, when an idle connection is released, for example, when other clients are disconnected, pgbouncer places the request in the connection pool and sends a service request to the backend. After the backend receives the request, for example

 template1=# select * from test; --no result

The above tests are based on pgbouncer version 1.1.1 + Psql (PostgreSQL) 8.3.3

 

Related Documents

• Config file documentation
• Usage document
• User Manual
• Administrator Manual

References:
Http://pgfoundry.org/projects/pgbouncer/