1. What is pgbouncer?
Pgbouncer is a lightweight PostgreSQL database connection pool tool that provides clients with a unified connection view.
Functions of pgbouncer
A. pgbouncer can establish a bridge between the backend database and the front-end application. pgbouncer can connect to the backend database.
B. restrict client connections to prevent excessive or malicious connection requests.
Features of pgbouncer
A. Low memory consumption (2 k/connection by default), because bouncer does not need to accept complete data packets every time.
B. You can connect different databases to a single machine, which is transparent to the client.
C. Support online reconfiguration without restarting
D. Only the V3 protocol is supported. Therefore, the backend version must be greater than or equal to 7.4.
(Imported from the Chinese site pgsqldb)
2. How to install
I installed pgbouncer on Ubuntu. It is very simple. You can install it smoothly through apt-Get install. After installation, you can use dpkg-L "pgbouncer" to view the list of files contained in this PKG.
We will not pay attention to the man and Doc paths. It is the help and description file of pgbouncer. You can see it using man. Only the/usr/sbin/pgbouncer and/etc/pgbouncer. ini files are used for running. /Etc/init. d/pgbouncer is used when the OS is started.
3. How to configure
MAN 5 pgbouncer shows an example of the minimum configuration provided in manaul:
The above configuration shows that the pgbouncer has created a connection pool for template1 on 127.0.0.1. The database name displayed by the connection pool to the caller is pg_template1, Which is mapped to template1. All requests to access pg_template1 on pbbouncer are forwarded to template1.
Pool_mode indicates the connection pool model. pgbouncer currently supports three connection pool models. The three levels are session, transaction, and statment.
A. session. Session-level link. Pgbouncer will reclaim the allocated link only when the session with the client ends
B. Transaction-level connections. After the transaction is completed, pgbouncer recycles the allocated links. That is to say, the client can only monopolize this link in transactions. Non-transaction requests to the database do not have exclusive links.
C. Statement statement-level link. After any request to the database is completed, the pgbouncer recycles the link. In this mode, the client cannot use transactions; otherwise, data inconsistency may occur.
The default setting of pgbouncer is session link.
Listen_port and listen_addr are the addresses and port numbers listened by pgbouncer.
Auth_type and auth_file are used by bppgbouncer to authenticate the client. The username and password are saved in auth_file. The content of auth_file varies depending on the authentication method (auth_type.
MD5: MD5-based password verification. The auth_file must have two types of passwords: common text and MD5 values;
Crypt: Password Verification Based on crypt (MAN 3 crypt). auth_file must contain a text password;
Plain: plaintext verification method;
Trust: Do not verify, but auth_file still needs to save the user name;
Any: the user name is not verified, and the user name does not need to be saved in auth_file. However, this method requires the user name in pg_template1 to log on to the real database. For example, pg_template1 = host = 127.0.0.1 user = exampleuser dbname = template1. otherwise, an error is returned.
Note that the username and password in auth_file must use double quotation marks. Otherwise, an error is returned.
Logfile and pidfile respectively Save the path of the log file and PID file.
Admin_users: lists which users can log on to pgbouncer for management, separated by commas (,).
Stats_users: List users who can log on to pgbouncer for read-only operations, such as list server status and access links, but cannot perform reload.
4. Configure the instance
After learning about the key configuration items above, we will perform a simple instance configuration exercise.
The hypothetical scenario is as follows: we have two PostgreSQL databases named main and pgd1 respectively. Each of them has a database named xuepeng. Now we need to manage the two databases through pgbouncer. Allows the client
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 different real databases. (because these programs run on one machine, the IP addresses are 127.0.0.1. For how to configure different PostgreSQL instances on one machine, see running multiple PostgreSQL instances on one machine)
First, modify the [databases] Block in pgbouncer. ini and add the following two lines:
This indicates that main_xuepeng is the ing of port 5432 and pgd1_xuepeng is the ing of port 5433.
Then modify the auth_type value to trust. (because it is used for local testing, verification is not used. This is not recommended in the production environment .)
Create the/tmp/users.txt file and write the following content:
Modify auth_file to/tmp/users.txt, admin_users to admin, and stats_users to readonly.
In this way, the configuration of the simplest version is completed, and other parameters are not adjusted.
5. Start running
Start pgbouncer: pgbouncer-V/etc/pgbouncer. ini
We recommend that you use the-V parameter for initial use and troubleshooting. This parameter enables pgbouncer to output more detailed logs to facilitate problem locating. In actual use, the-D parameter is used to indicate how the subsequent program runs.
Connect main_xuepeng: Psql-H 127.0.0.1-P 6000-u xuepeng main_xuepeng
At this time, you can enter the PostgreSQL dB with the port number 5432. Similarly, use Psql-H 127.0.0.1-P 6000-u xuepeng pdd1_xuepeng to enter the PostgreSQL dB instance with the port number 5433.
6. How to manage pgbouncer
Pgbouncer actually has a virtual dB named "pgbouncer". If we execute
Pgsql-H 127.0.0.1-P 6000-u admin pgbouncer
You will be directed to the Management Terminal. -U is followed by the user name, and "pgbouncer" is the name of the login dB. When this name is found, pgbouncer considers that the user needs to manage pgbouncer itself, it will not request the real dB.
After logging on through admin, show help can see the operations that can be performed as follows:
You can use the show name to list the current status information, or execute reload to re-read the content of the configuration file (this command can be used after the configuration file is changed, the configuration item takes effect without restarting the program ).
If you log on using pgsql-H 127.0.0.1-P 6000-u readonly pgbouncer, the error message "error: Admin Access needed" will be displayed during reload.
7. How to stop
Log on to pgbouncer as admin and run shutdown.
We recommend that you use the-V parameter for the first time and pay attention to the log input of pgbouncer to find a lot of interesting things.
8. Reference
Http://pgbouncer.projects.postgresql.org/doc/config.html
Https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
