How to build and run PgBouncer in Cent OS 7

Let my first tell you that I’m not an active C/C++ developer any more. I’m into Java development for more than a decade now. One of my client wanted to use PgBouncer as Database pooling implementation besides their application pool implementation

This section will give list of steps (build and run) that I followed to run PgBouncer behind their database .

Download pgbouncer. I used following command

$ wget https://www.pgbouncer.org/downloads/files/1.15.0/pgbouncer1.15.0.tar.gz

Extract the image

$ gunzip pgbouncer-1.15.0.tar.gz
$ tar xvf pgbouncer-1.15.0.tar

Refer the README.md which talks about dependencies. Following are the list of dependencies mentioned,

[GNU Make]: https://www.gnu.org/software/make/
Version: 3.81+

[Libevent]: http://libevent.org/
Version: 2.0+

[pkg-config]: https://www.freedesktop.org/wiki/Software/pkg-config/

[OpenSSL]: https://www.openssl.org/
Version: 1.0.1+

With the quick search on the internet i learned that i need to install libtool,

$ wget http://ftp.jaist.ac.jp/pub/GNU/libtool/libtool-2.4.6.tar.gz
$ gunzip libtool-2.4.6.tar.gz
$ tar xvf libtool-2.4.6.tar

While i tried to run make config it just failed with M4 dependency failure. Following command was used to instal them,

$ wget http://ftp.gnu.org/gnu/m4/m4-1.4.18.tar.gz
I will assume that you will run gunzip and tar commands…

Following commands are ran from the M4 Installation folder, 103 vi README.md

$ ./configure –prefix=/opt/m4
$ make clean
$ make
$ make install
For clarity all target are given separately. Also, m4 folder should

Set M4 in PATH variable

Install libevent like below

$ wget http://monkey.org/~provos/libevent-1.4.14b-stable.tar.gz
$ ./configure –prefix=/opt/libevent
$ make clean install

I got a failure due to GCC compiler and libraries. I had to spent more time to understand how to install GNU build essentials in Cent – OS. Following command will help

$ sudo yum update
$ sudo yum install gcc gcc-c++ make

Now, i’m able to build libevent with out any problem

For installing pkg-config and OpenSSL i tried following commands,

$ sudo yum install pkg-config
$ sudo yum install openssl
There two were failed with no package available.

Later i got installer from internet and installed pkg-config

$ wget https://pkg-config.freedesktop.org/releases/pkg-config-0.29.2.tar.gz

My pkg-config config was failing and few internet search related to pkg-config build gave following command to build,

$ ./configure –prefix=/opt/pkg-config –with-internal-glib

At this point of time i realized I already have openssl and hence i skipped that.

In order for you to build the pgbouncer you will need following environment variable set properly

$ export LIBEVENT_CFLAGS=/opt/libevent/include
$ export LIBEVENT_LIBS=/opt/libevent/lib/libevent*so
$ export PATH=/opt/m4/bin:$PATH
$ export PATH=/opt/libtool/bin:$PATH

Now, I used following command to build pgbouncer,

$ ./configure –prefix=/opt/pgbouncer –with-libevent=/opt/libevent
$ make clean
$ make
$ make install

At last I was able to build pgbouncer successfully. With all smiling face, i ran pgbouncer to see following error,

./pgbouncer: error while loading shared libraries: libevent_extra-2.1.so.7: cannot open shared object file: No such file or directory

I copied all shared library generated out of the above steps into /usr/local/lib and set following environment variable,

$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib

Invoke pgbouncer again,

./pgbouncer
./pgbouncer: no configuration file specified
Try “./pgbouncer –help” for more information.

The above output from pgbouncer tells me that the component built successfully and all I need to configure right.

After spending couple of hours on the configuration section I can figure out following options works for my initial analysis.

[databases]
devdb = host=xxx.xxx.xx.xx port=5432 dbname=devdb user=*** password=*** 

[pgbouncer]
listen_addr = *  
listen_port = 6432  
auth_type = md5  
auth_file = /opt/pgbouncer/log/userlist.txt  
admin_users = app_admin
pool_mode = transaction  
max_client_conn = 10000
default_pool_size = 100
logfile = /opt/pgbouncer/log/pgbouncer.log
pidfile = /opt/pgbouncer/log/pgbouncer.pid
ignore_startup_parameters = extra_float_digits
query_wait_timeout = 600

Two important parameter to note,

md5 hash can be generated like below,
$ echo -n <password><username> | md5sum
With the above result prefix “md5” and keep it in the auth_file like below,

“app_admin” “md5<result>”

Following configuration to overcome some bootstrapping issue with hikari connection pool used on the application side.
ignore_startup_parameters = extra_float_digits

With the above configuration I’m able to run the pgbouncer as a proxy pooling between application and DB. Yet to analyze more about pgbouncer configuration and tune it to their application. Right now, it just sits like a mediator on the echo system, more to follow….stay tuned…

Leave a Reply Cancel reply