connection pooling for postgresql using pgbouncer
Creating a database connection and closing it is a resource intensive task. If we are using any web application server which frequently connects and disconnects from database server will take lot of resources and time. To improve the performance and reduce the connection establishment time we use the connection pooling mechanism where there are bunch of existing connections to the database and application can use one of the connection, after completion of the work the connection is given back to the pool.
Pgbouncer is a light weight connection pooler where it caches the database connections. Applications will connect to pgbouncer on 6432 port (default), pgbouncer will create a new connection to the database server or it will use an existing connection.
Installation of PGBouncer:
Download the latest version from(https://pgbouncer.github.io/downloads).
Prerequisites:
PgBouncer depends on few things to get compiled:
- GNU Make 3.81+
- libevent 2.0
- (optional) OpenSSL 1.0.1 for TLS support.
- (optional) c-ares as alternative to libevent’s evdns.
Install all the above packages.
# Create directory to install pgbouncer
mkdir /home/postgres/pgbouncer
./configure –prefix=/home/postgres/pgbouncer
make
make install
Configuration of PGBouncer:
Login as admin user to database, execute below command and create the users list file which contain the user name and password.
COPY (
SELECT ‘”‘ || rolname || ‘” “‘ ||
CASE WHEN rolpassword IS null THEN ” ELSE rolpassword END || ‘”‘
FROM pg_authid
)
TO ‘/home/postgres/pgbouncer/userlist.txt ‘;
PGBouncer have pgbouncer.ini file which contain the all the configuration parameters. Below are some of important configuration parameters to start the Pgbouncer.
Open the file
“/home/postgres/pgbouncer/share/doc/pgbouncer/pgbouncer.ini” and set the below parameters. The file contain different headers like [databases], [authentication] etc.
Below “[databases]” we need to configure the databases for which we need to create the connection pools. These are the key value pairs to connect to the database server.
postgres=host=127.0.0.1 port=5432 user=postgres password=postgres dbname=postgres
This is like a pool of connections are created for the postgres database with postgres user.
Under “Administrative settings” we have logfile location and listener addresses etc.
logfile = /home/postgres/pgbouncer/pgbouncer.log
pidfile = /home/postgres/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 6432
Under “Authentication settings”
auth_type = md5
auth_file = /home/postgres/pgbouncer/userlist.txt
auth_hba_file = /opt/PostgreSQL/9.6/data/pg_hba.conf
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
admin_users = postgres
pool_mode = session
Under “Connection limits” (This depends on client requirement)
max_client_conn = 1000
default_pool_size = 200
By using the above configuration there are connection pool for postgres database with postgres user.
auth_file : This parameter is the list of users in the database with their passwords.
auth_hba_file : This is same as pg_hba.conf of PostgreSQL database.
auth_query : This is the query used to get the password of the user if there is no entry for it in “userlist.txt”.
admin_users : This is the user name who can administrate the pgbouncer.
If you want to create a connection pool for database for each user existing in the database use the parameter “auth_user” in the [databases]. If the user entered by the client is not in userslist.txt then the username and password can get by pgbouncer from database cluster using the query mentioned in “auth_query”.
EX:-
postgres = host=127.0.0.1 port=5432 dbname=postgres auth_user=postgres
Authentication in pgbouncer:-
Pgbouncer has “auth_file”(user list) which contain the list of users and passwords which can be used by the pgbouncer to verify the user.
If you have set the auth_user parameter in the databses part, then it will get the password of the user from database cluster by executing the query.
Starting and Stopping pgbouncer:-
./pgbouncer -d /home/postgres/pgbouncer/share/doc/pgbouncer/pgbouncer.ini &
./pgbouncer -R -d /home/postgres/pgbouncer/share/doc/pgbouncer/pgbouncer.ini &
Connecting to pgbouncer to administer:-
psql -p 6432 pgbouncer
pgbouncer=# show help;
NOTICE: Console usage
DETAIL:
SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSION
SHOW STATS|FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
SHOW DNS_HOSTS|DNS_ZONES
SET key = arg
RELOAD
PAUSE []
RESUME []
DISABLE
ENABLE
KILL
SUSPEND
SHUTDOWN
SHOW
Viswamitra's blog 