Pgbouncer Introduction and use Mode

1. What is Pgbouncer
Pgbouncer is a lightweight, PostgreSQL database connection Pooling tool that provides a unified view of links to clients.


1. Download the Pgbouncer installation package
http://pgfoundry.org/frs/?group_id=1000258&release_id=1952, this download of the installation package is pgbouncer-1.5.2.tar.gz


2. Download Libevent Package
http://monkey.org/~provos/libevent/, see the Readme in the Pgbouncer source pack
This thing is also a good thing, in a lot of places to use


3. Install Libevent
$ CD Libevent-2.0.19-stable
$./configure--prefix=/home/postgres/libevent
$ make
$ make Install


4. Install Pgbouncer
$ CD pgbouncer-1.5.2
$./configure--prefix=/home/postgres/pgbouncer/--with-libevent=/home/postgres/libevent/
$ make
$ make Install
The role of Pgbouncer
A.pgbouncer can establish a connecting bridge between the backend database and the front-end application, and the connection between the Pgbouncer and the backend database.
B. Limit client connections to prevent excessive or malicious connection requests.

Characteristics of Pgbouncer
A. Low memory consumption (default is 2k/connection), because bouncer does not need to accept the full packet every time
B. You can connect different databases to one machine and keep the client transparent
C. Support for online reconfiguration without rebooting
D. Support only the V3 protocol, so the backend version must be >=7.4 (from Pgsqldb Chinese station)

2. How to Install
I was installed on Ubuntu Pgbouncer, very simple, through the Apt-get install way can be smoothly installed. After the installation is complete, you can use Dpkg-l "Pgbouncer" to see the list of files that the pkg contains.
Xuepeng @ A: ~ > $ dpkg-l "pgbouncer"/. /usr/usr/share/usr/share/man/usr/share/man/man1/usr/share/man/man1/pgbouncer.1.gz/usr/share/man/man5/usr/share /man/man5/pgbouncer.5.gz/usr/share/man/man8/usr/share/man/man8/pgbouncer.8.gz/usr/share/doc/usr/share/doc/ pgbouncer/usr/share/doc/pgbouncer/readme/usr/share/doc/pgbouncer/authors/usr/share/doc/pgbouncer/ Pgbouncer.ini.example.gz/usr/share/doc/pgbouncer/readme. Debian/usr/share/doc/pgbouncer/copyright/usr/share/doc/pgbouncer/news.gz/usr/share/doc/pgbouncer/changelog. Debian.gz/usr/sbin/usr/sbin/pgbouncer/etc/etc/default/etc/default/pgbouncer/etc/init.d/etc/init.d/pgbouncer/ Etc/pgbouncer.ini man and Doc's path we are not concerned about, is Pgbouncer's help and documentation, using man can be seen. Running is used only for/usr/sbin/pgbouncer and/etc/pgbouncer.ini two files. /etc/init.d/pgbouncer is used when the OS is started, regardless of the first time.

3. How to configure
The man 5 Pgbouncer can see an example of a minimal configuration provided in Manaul as follows:
           [databases]             pg_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        &nbSp;    admin_users = someuser             stats_users = stat_collector
The above configuration explains that the pgbouncer created a connection pool for the template1 on 127.0.0.1, which is pg_template1 to the caller's rendering, and maps to template1. All requests for access to the pg_template1 on the Pbbouncer are forwarded to the template1 for completion.

Pool_mode indicates the model of the connection pool, Pgbouncer currently supports three types of connection pool models. The session, transaction and statment are three levels respectively.
A. session. Session-level links. Pgbouncer only reclaims an assigned link when the client's session ends
B. Transaction transaction-level connections. When the transaction completes, Pgbouncer reclaims the assigned link. This means that the client can only monopolize this link in a transaction, and that non transactional requests for the database are not exclusive links.
C. Statement statement-level links. After any request to the database is complete, Pgbouncer will recycle the link. In this mode, the client cannot use the transaction, or it can cause inconsistent data.
The default setting for Pgbouncer is the session link.

Listen_port and LISTEN_ADDR are the addresses and port numbers that Pgbouncer listens to.

Auth_type and Auth_file are bppgbouncer to complete client identity authentication. Auth_file Save the username and password, depending on the method of authentication (AUTH_TYPE), the content of Auth_file is also different.
MD5: Based on MD5 password verification, auth_file need to have ordinary text and MD5 value of two forms of password;
Crypt: Based on crypt password Authentication (man 3 crypt), Auth_file must contain a text password;
Plain: PlainText verification method;
Trust: Do not verify, but Auth_file still need to save the user name;
Any: also does not authenticate, and does not need to save the user name in the auth_file. However, this approach requires that the user name be explicitly logged in the pg_template1 for the real database. such as: Pg_template1 = host=127.0.0.1 user=exampleuser dbname=template1. Otherwise, it will be an error.

What needs to be explained is: The username and password in auth_file must use double quotes, otherwise it will be an error.

LogFile and Pidfile respectively save the path of the log file and the PID file.

Admin_users: Lists which users can log in Pgbouncer for management, separated by commas
Stats_users: Lists which users can log in Pgbouncer for read-only operations, such as listing server status, accessing links, and so on, but cannot perform reload.

4. Configuration instance
After we've learned some of the key configuration items above, we'll practice a simple configuration instance.
The hypothetical scenario is as follows: We have two PostgreSQL db, named Main and PgD1, each with a db called Xuepeng, which now needs to be managed with two DB through Pgbouncer. Enables the client to pass
Psql-h 127.0.0.1-p 6000-u Xuepeng Main_xuepeng
Psql-h 127.0.0.1-p 6000-u Xuepeng Pgd1_xuepeng
Access to different real db. (Since these programs are all running on one machine, so IP is 127.0.0.1, and for how to configure different PostgreSQL instances on a single machine, see Running multiple PostgreSQL instances on a single computer)

First modify the [databases] block in Pgbouncer.ini to add the following two lines:
Main_xuepeng = host=127.0.0.1 port=5432 Dbname=xuepeng