PostgreSQL is quite heavy to establish connection. PostgreSQL deploy concept of process to establish connection from clients. If your connection just connect to database for short period of time, it will consume a lot of resource for each connection. To overcome this issue, connection pooling concept is utilized to handle short-time connection. Connection pooling enble connection resource reusable, so connection will be reuse among incoming connections from clients. Pgbouncer is one of the connection pooling solution that is available. It is very light weight and very easy to setup. In this post I will show how to install and configure pgbouncer to pgpool that I have created in previous post. My deployment concept is shown following .
|
|
Fig 01 Deployment Concept |
Install Pgbouncer
First, you need to install pgbouncer to the server. You can use yum to install pgbouncer as below.
[root@pgbouncer ~]#yum -y install pgbouncer
Configue pgbouncer.ini
After install pgbouncer on your server. You need to configure the configuration file be for start. But first, you need to create user and set up permission.
[root@pgbouncer ~]#adduser pguser
[root@pgbouncer ~]#usermod –aG wheel pguser
[root@pgbouncer ~]#passwd pguser
[root@pgbouncer ~]#mkdir -p /opt/pgbouncer
[root@pgbouncer ~]#chown -R gpuser:wheel /opt/pgbouncer
[root@pgbouncer ~]#su - pguser[pguser@pgbouncer ~]$vi /opt/pgbouncer/pgbouncer.ini
the configuration pgbouncer.ini, we have edited as following.
[databases]
db1 = host=pgpool00.itstikk.pro dbname=pro port=5432 user=pgpool
password=secret
[pgbouncer]
logfile = /opt/pgbouncer/pgbouncer.log
pidfile = /opt/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 6432
auth_type=md5
ignore_startup_parameters = datestyle,extra_float_digits
auth_file = userlist.txt
pool_mode = session
Create password for md5 password format. For MD5 password format, you can create by "md5" + md5(password + username)
SELECT 'md5'||md5('secretpgpool') AS passwd;
--------------------------------------------
md5e5c7045b5cf578feb27a8aa6ffc0fcc6
Next is to create userlist.txt
[pguser@pgbouncer ~]$vi /opt/pgbouncer/userlist.txt
"pgpool" "md5e5c7045b5cf578feb27a8aa6ffc0fcc6"
Finally, start pgbouncer as below.
[pguser@pgbouncer ~]$/usr/bin/pgbouncer pgbouncer.ini
2020-07-25 06:13:47.574 EDT [16719] LOG kernel file descriptor
limit: 131072 (hard: 131072); max_client_conn: 100, max expected fd
use: 132
2020-07-25 06:13:47.575 EDT [16719] LOG listening on
0.0.0.0:6432
2020-07-25 06:13:47.575 EDT [16719] LOG listening on
[::]:6432
2020-07-25 06:13:47.575 EDT [16719] LOG listening on
unix:/tmp/.s.PGSQL.6432
2020-07-25 06:13:47.575 EDT [16719] LOG process up: PgBouncer
1.13.0, libevent 2.0.21-stable (epoll), adns: evdns2, tls: OpenSSL
1.0.1e-fips 11 Feb 2013
Next is to test pgbouncer by connect through any client
Confirm Pgbouncer
To test pgbouncer whether our configuration working properly or not. We could test by connecting client to pgbouncer.
|
|
Fig 02 Testing Connection |
|
|
Fig 03 Testing Connection |
No comments:
Post a Comment