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.

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 pgd1_xuepeng = host=127.0.0.1 port=5433 Dbname=xuepeng This indicates that M Ain_xuepeng is a map with a port of 5432, Pgd1_xuepeng is a map with a port of 5433.

Then modify the value of Auth_type to trust. (because it is a native test, no validation is used, and the production environment does not recommend doing so.) )
Next, create the/tmp/users.txt file and write the following:
"Admin" "Password" "readonly" "Password" "Xuepeng" "password" modify the Auth_file value to/tmp/users.txt, the value of admin_users is admin, STA The value of the Ts_users is readonly.
This allows us to complete the simplest version of the configuration, the other parameters are temporarily not adjusted.

5. Start run
Start Pgbouncer:pgbouncer-v/etc/pgbouncer.ini
The-v parameter is recommended for initial use and for checking problems, which enables pgbouncer to play a more detailed log to facilitate locating problems. Use the-D argument when it is actually used to indicate that it is running in a background program.
Connect main_xuepeng:psql-h 127.0.0.1-p 6000-u Xuepeng Main_xuepeng
This will allow you to enter the PostgreSQL db with the port number 5432. Similarly, using the psql-h 127.0.0.1-p 6000-u Xuepeng Pdd1_xuepeng can enter the DB instance of PostgreSQL with port number 5433.

6. How to manage Pgbouncer
Pgbouncer actually has a virtual DB presence, the name is "Pgbouncer" if we execute
Pgsql-h 127.0.0.1-p 6000-u Admin pgbouncer
Will go into the management terminal. -U is the user name, "Pgbouncer" is the DB name of the login, when the discovery is this name, pgbouncer that the user is to the pgbouncer itself to manage, will not be to request the real db.

When you log on through admin, show help sees what you can do, as follows:
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 show Pgbouncer =# can use Show names to list current state information, and to perform reload to reread the contents of the configuration file (which enables configuration entries to take effect without restarting the program after the configuration file changes).

If you are logged in by Pgsql-h 127.0.0.1-p 6000-u readonly pgbouncer, the error message "Reload Access Error:admin" is prompted when you execute the needed.

7. How to stop
With admin login Pgbouncer, execute shutdown.

When first used, it is recommended to use the-v parameter, and focus on Pgbouncer log input, you can find a lot of interesting things:)


8. For reference
Http://pgbouncer.projects.postgresql.org/doc/config.html
Https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer