Install Pgbouncer in Linux

Environment - Amazon Linux/RHEL

Pre-Requiste:

Should have PG installed.( Please refer - http://postgresql-open-source.blogspot.in/2017/12/postgresql-installation-script-source.html)

Installation Steps-

1. Download and install libevent from 'http://libevent.org/'(latest release) which is necessary for pgbouncer

sudo su -
yum install wget
wget https://github.com/libevent/libevent/releases/download/release-2.1.8-stable/libevent-2.1.8-stable.tar.gz
tar -xzf libevent-2.1.8-stable.tar.gz
cd libevent-2.1.8-stable
yum install gcc
./configure
make
make install

2. Download pgbouncer from this link 'http://pgfoundry.org/frs/?group_id=1000258'

wget http://pgfoundry.org/frs/download.php/3393/pgbouncer-1.5.4.tar.gz
tar -xzf pgbouncer-1.5.4.tar.gz
cd pgbouncer-1.5.4
./configure --prefix=/usr/local
make
make install

3. Enter the lib file location into '/etc/ld.so.conf.d/libevent-i386.conf' and save the file and load

vi /etc/ld.so.conf.d/libevent-i386.conf and enter /usr/local/lib/ and save the file and load
ldconfig

4. Create log directory

mkdir -p /var/log/pgbouncer
mkdir -p /var/run/pgbouncer

5. Change the ownership to 'postgres' for both the directory
chown postgres:postgres /var/log/pgbouncer
chown postgres:postgres /var/run/pgbouncer


6. Change the ownership to 'postgres' for pgbouncer binary

cd /usr/local/bin/
chown -R postgres:postgres pgbouncer


7. copy all the roles and passoword of postgres

[postgres@ip-172-31-74-77 pgsql10]$ psql

postgres=# \o /usr/local/pgsql10/pgbouncer.auth
postgres=# select '"'||rolname||'"'||' "'||rolpassword||'"' from pg_authid;
postgres=# \o
postgres=# \! cat /usr/local/pgsql10/pgbouncer.auth
                     ?column?                   
--------------------------------------------------
 "new_user" "md500084d8780bcdf83b7345e04c09e0584"
 "postgres" "md532e12f215ba27cb750c9e093ce4b5127"
(7 rows)


8. Edit the 'pgbouncer.auth' file to have only 'role' and 'password'

vi /usr/local/pgsql10/pgbouncer.auth

"new_user" "md500084d8780bcdf83b7345e04c09e0584"
"postgres" "md532e12f215ba27cb750c9e093ce4b5127"


9. Edit the Configuration File

vi /etc/pgbouncer.ini

[databases]
* = host=127.0.0.1 port=5432

[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 6432
unix_socket_dir = /tmp
#auth_type = any
auth_type = trust
auth_file = /usr/local/pgsql10/pgbouncer.auth
admin_users = postgres
stats_users = postgres
pool_mode = transaction
server_reset_query = DISCARD ALL;
server_check_query = select 1
server_check_delay = 10
max_client_conn = 1000
default_pool_size = 20
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1



10. Start pgbouncer

[postgres@ip-172-31-74-77 bin]$ /usr/local/bin/pgbouncer -d /etc/pgbouncer.ini
2017-12-15 04:15:34.821 4794 LOG File descriptor limit: 1024 (H:4096), max_client_conn: 1000, max fds possible: 1010


11. Check if pgbouncer is installed successfully -

[postgres@ip-172-31-74-77 pgbouncer]$ psql -p 6432 -U postgres -d pgbouncer
psql (10.1, server 1.5.4/bouncer)
Type "help" for help.

pgbouncer=# show help;
NOTICE:  Console usage
DETAIL: 
SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSION
SHOW STATS|FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
SHOW DNS_HOSTS|DNS_ZONES
SET key = arg
RELOAD
PAUSE [<db>]
RESUME [<db>]
KILL <db>
SUSPEND
SHUTDOWN
SHOW
pgbouncer=#