Installation steps for pgBouncer on PowerLinux

Our team has been excited ever since IBM and Enterprise DB announced availability of Postgres Plus Advanced Server on PowerLinux.

Our team recently started engaging with IBM to evaluate the scalability of PPAS on PowerLinux and to determine how to fine-tune such setups.

(It's a work-in-progress and I am not sure how much I can share here. But if you are thinking of migrating your IT infrastructure to a much more cost effective stack of Power PC running RHEL and using EnterpriseDB PPAS/PostgreSQL as database please contact me or write to success@ashnik.com).

Though I really welcome this new announcement from EnterpriseDB, one thing which disappointed me was non-availability of pgBouncer in the rpm installers provided by EnterpriseDB for PPAS on PowerLinux.

I have always preferred pgBouncer as a connection pooling tool over pgpool. It's light weight and quite easy to configure. In the past I had done some benchmarking study to see how PPAS scales with and without connection pooling. I had seen some significant gain and hence decided to install pgBouncer before I could to proceed with the performance measurement of PPAS on PowerLinux.

I faced a few issues but could finally install it and get it running. So I decided to share my experience of the same (which can hopefully help someone, as more and more people try to move away from costly hardware and proprietary database to PPAS on PowerLinux).

First thing that one should do is, make sure you have IBM Advanced ToolChain installed on your PowerLinux box.

As per my understanding, the compiler which is part IBM Advanced ToolChain is supposed to be optimized for Power PC. So it's preferred for code compilation instead of using gcc compiler shipped with Red Hat.

Generally it will be installed in /opt/atXX

So I set my path to include Advanced Toolchain:

[root@pflex75 pgbouncer]# export PATH=/opt/at7.0/bin:$PATH

I just wanted to be sure about my version of gcc compiler

[root@pflex75 pgbouncer]# which gcc

                /opt/at7.0/bin/gcc

[root@pflex75 pgbouncer]# gcc --version

gcc (GCC) 4.8.2 20130819 (Advance-Toolchain-at7.0) [ibm/gcc-4_8-branch, revision: 201854 merged from gcc-4_8-branch, revision 200989]

Copyright (C) 2013 Free Software Foundation, Inc.

This is free software; see the source for copying conditions.  There is NO

warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

I downloaded the latest version of pgbouncer (for me its 1.5.4) from pgfoundries site and extracted the content of the tar file

[root@pflex75 pgbouncer]# tar -xzvf pgbouncer-1.5.4.tar.gz

Now was the time to configure the binaries and install them as per the instruction in README file which is extracted from above tar. So I fired below commands:

[root@pflex75 pgbouncer]# cd pgbouncer-1.5.4

[root@pflex75 pgbouncer-1.5.4]# ./configure --prefix=/usr/local --with-libevent=libevent-prefix

[root@pflex75 pgbouncer-1.5.4]# make

[root@pflex75 pgbouncer-1.5.4]# make install

[root@pflex75 pgbouncer-1.5.4]# cd ..

Now I had to setup the pgBouncer and start it.

So I coped the pgbouncer.ini file to a location of my choice (/etc/pgbouncer)

[root@pflex75 pgbouncer]# mkdir -p /etc/pgbouncer

[root@pflex75 pgbouncer]# chown enterprisedb:enterprisedb -Rf /etc/pgbouncer

[root@pflex75 pgbouncer]#  cp /usr/local/share/doc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini

Then I created a userlist.txt in a location of my choice (once again /etc/pgbouncer):

[root@pflex75 pgbouncer]#  cat /etc/pgbouncer/userlist.txt

                                "enterprisedb" "password"

Now I followed all the standard steps to edit the pgbouncer to add all the address and databases. Then I went ahead to set pool size etc and then set path for userlist file.

In pgbouncer.ini file, I checked the value for logfile and pidfile. I had to ensure that these locations are present and owned by proper user (the user which I will be using for running pgbouncer must have permissions to run them).

                                logfile = /var/log/pgbouncer/pgbouncer.log

                                pidfile = /var/run/pgbouncer/pgbouncer.pid

[root@pflex75 pgbouncer]# mkdir -p /var/log/pgbouncer;

[root@pflex75 pgbouncer]# touch /var/log/pgbouncer/pgbouncer.log;

[root@pflex75 pgbouncer]# chown enterprisedb:enterprisedb -Rf /var/log/pgbouncer

[root@pflex75 pgbouncer]# mkdir -p /var/run/pgbouncer/

[root@pflex75 pgbouncer]# chown enterprisedb:enterprisedb -Rf /var/run/pgbouncer

But once I tried to run pgbouncer, I got below issue

[root@pflex75 pgbouncer]# pgbouncer  -d  /etc/pgbouncer/pgbouncer.ini

pgbouncer: error while loading shared libraries: libevent-2.0.so.5: cannot open shared object file: No such file or directory

To resolve (I followed below steps, but may be one can just resolve it by setting LD_LIBRARY):

First thing I did was removing the existing installation:

[root@pflex75 pgbouncer]# rm  /usr/local/bin/pgbouncer

[root@pflex75 pgbouncer]# rm  /usr/local/share/man/man5/pgbouncer.5

[root@pflex75 pgbouncer]# rm  /usr/local/share/man/man1/pgbouncer.1

[root@pflex75 pgbouncer]# rm -rf /usr/local/share/doc/pgbouncer

Then I removed the installer I had extracted and then extracted it afresh:

[root@pflex75 pgbouncer-1.5.4]# cd .. ; rm -rf pgbouncer-1.5.4

[root@pflex75 pgbouncer]# tar -xzvf pgbouncer-1.5.4.tar.gz

I made sure you have latest libevent was installed:

[root@pflex75 pgbouncer]# yum info libevent

                                Loaded plugins: product-id, refresh-packagekit, security, subscription-manager

                                This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.

                                Installed Packages

                                Name        : libevent

                                Arch        : ppc64

                                Version     : 1.4.13

                                Release     : 4.el6

                                Size        : 335 k

                                Repo        : installed

                                From repo   : anaconda-RedHatEnterpriseLinux-201301301505.ppc64

                                Summary     : Abstract asynchronous event notification library

                                URL         : http://monkey.org/~provos/libevent/

                                License     : BSD

                                Description : The libevent API provides a mechanism to execute a callback function

                                                    : when a specific event occurs on a file descriptor or after a timeout

                                                    : has been reached. libevent is meant to replace the asynchronous event

                                                    : loop found in event driven network servers. An application just needs

                                                    : to call event_dispatch() and can then add or remove events dynamically

                                                    : without having to change the event loop.

                                Available Packages

                                Name        : libevent

                                Arch        : ppc

                                Version     : 1.4.13

                                Release     : 4.el6

                                Size        : 66 k

                                Repo        : local

                                Summary     : Abstract asynchronous event notification library

                                URL         : http://monkey.org/~provos/libevent/

                                License     : BSD

                                Description : The libevent API provides a mechanism to execute a callback function

                                                    : when a specific event occurs on a file descriptor or after a timeout

                                                    : has been reached. libevent is meant to replace the asynchronous event

                                                    : loop found in event driven network servers. An application just needs

                                                    : to call event_dispatch() and can then add or remove events dynamically

                                                    : without having to change the event loop.

Then I set the value for LD_LIBRARY_PATH parameter to look at /usr/lib64 (for power64 system it will have the libevent)

[root@pflex75 pgbouncer-1.5.4]# export LD_LIBRARY_PATH=/usr/lib64:$LD_LIBRARY_PATH

I created an appropriate Softlink for Libevent (I believe this step was not needed)

[root@pflex75 pgbouncer-1.5.4]# ln ln -s  /usr/lib64/libevent-1.4.so.2 /usr/local/lib

[root@pflex75 pgbouncer-1.5.4]# ./configure --prefix=/usr/local --with-libevent=/usr/lib64

[root@pflex75 pgbouncer-1.5.4]# make

[root@pflex75 pgbouncer-1.5.4]# make install

[root@pflex75 ~]# pgbouncer --help

Usage: pgbouncer [OPTION]... config.ini

  -d, --daemon           Run in background (as a daemon)

  -R, --restart          Do a online restart

  -q, --quiet            Run quietly

  -v, --verbose          Increase verbosity

  -u, --user=<username>  Assume identity of <username>

  -V, --version          Show version

  -h, --help             Show this help screen and exit

Now switch user to enterprisedb (or any user which you wish to use for running pgbouncer)

[root@pflex75 ~]# su - enterprisedb

-bash-4.1$  pgbouncer  -d  /etc/pgbouncer/pgbouncer.ini

pgbouncer: error while loading shared libraries: libevent-2.0.so.5: cannot open shared object file: No such file or directory

Ahhhha!!! Isn’t that the same error I just resolved? So I added below line in .bash_profile:

                export LD_LIBRARY_PATH=/usr/lib64:$LD_LIBRARY_PATH

Now apply the same for current shell environment

-bash-4.1$ export LD_LIBRARY_PATH=/usr/lib64:$LD_LIBRARY_PATH

Try again:

-bash-4.1$  pgbouncer  -d  /etc/pgbouncer/pgbouncer.ini

2013-10-25 19:31:40.325 11412 LOG File descriptor limit: 1024 (H:4096), max_clie  nt_conn: 200, max fds possible: 250

-bash-4.1$ ps -ef | grep -i pgbouncer

495      14384     1  0 19:41 ?        00:00:00 pgbouncer -d /etc/pgbouncer/pgbouncer.ini

Wow!!! Issue resolved!!! :-)

A few more steps which won't harm (just to ensure permissions)

-bash-4.1$ chown enterprisedb:enterprisedb -Rf /usr/local/share/man/man1/pgbouncer.1

-bash-4.1$ chown enterprisedb:enterprisedb -Rf /usr/local/share/man/man5/pgbouncer.5

-bash-4.1$ chown enterprisedb:enterprisedb -Rf /usr/local/share/doc/pgbouncer