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…