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 .

$ 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

$ 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

Your email address will not be published.

WP2Social Auto Publish Powered By : XYZScripts.com