- What is Pgbouncer?
PgBouncer maintains a pool of connections for each unique user, database pair. It's typically configured to hand out one of these connections to a new incoming client connection and return it back to the pool when the client disconnects.
Prerequisite:- Postgres should be configured and the user/database should be created which we're going to use (In this article the username/database is sdc/sdc)
We need to follow the below steps to integrate Pgbouncer in front on PostgreSQL
1. We need to download the pgbouncer package according to the OS (This article is for Centos/Redhat)
yum install pgbouncer -y
2. Create a PgBouncer configuration file, for example, pgbouncer.ini with below content.
[databases]
sdc = host=server.example.com auth_user=sdc dbname=sdc
[pgbouncer]
listen_addr = server.example.com
listen_port = 16432
auth_file = /var/lib/pgsql/userlist.txt
max_client_conn = 50
ignore_startup_parameters = extra_float_digits, timezone
server_reset_query = RESET ALL; SET SESSION AUTHORIZATION DEFAULT;
query_wait_timeout= 0
auth_type = md5
admin_users = sdc
pool_mode = session
In the above file, we need to update the configuration according to the requirement like database name, username, user list file location where we'll save the credentials Info, authentication type, etc.
3. Now we need to create the userlist.txt file with the credentials info for the user which we're going to use for authentication, We're using auth_type as md5, So our password should be in md5 format and the format for an MD5-encoded password is:
"md5" + MD5(<password>)
Ex:- echo -n "userpass" |md5sum
and the content of the file followed by <username> <md5 password>
$ vi userlist.txt
"sdc" "md581DC9BDB52D04DC20036DBD8313ED055"
4. Now we need to launch pgbouncer:
$ pgbouncer pgbouncer.ini
5. We can verify the connection to Postgres via pgbouncer using the below command
psql -U sdc -p 16432 -h `hostname -f` sdc