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=#

PostgreSQL Installation Script - Source Installation

Just copy the below lines and create a file. Your linux system will be ready with custom PostgreSQL installation. You can modify the script based on your need. You can consider the below lines as a framework. Cheers !!

Environment - Amazon Linux

#!/bin/bash

set -x

sudo su - <<EOF

/usr/bin/yum -y install gcc

/usr/bin/yum -y install readline-devel

/usr/bin/yum -y install zlib-devel

echo "Adding User for Postgres server"

/usr/sbin/useradd -d /home/postgres -m postgres

read -s -p "Enter password for postgres: " password

echo $password |passwd --stdin postgres

chage postgres -M -1

#/usr/sbin/useradd -d /home/postgres -m postgres

#passwd postgres

#read -p "New password:" postgres

#read -p "Retype new password " postgres

/usr/bin/wget https://ftp.postgresql.org/pub/source/v10.1/postgresql-10.1.tar.gz

/bin/tar -xzf postgresql-10.1.tar.gz

cd postgresql-10.1

/root/postgresql-10.1/configure --prefix=/usr/local/pgsql10/

make

make install

/bin/mkdir /usr/local/pgsql10/data

/bin/chown postgres:postgres -R /usr/local/pgsql10

EOF

sudo su - postgres <<EOF

cd /usr/local/pgsql10/bin

/usr/local/pgsql10/bin/initdb -D /usr/local/pgsql10/data

/usr/local/pgsql10/bin/pg_ctl -D /usr/local/pgsql10/data start

EOF

sudo su - <<EOF

/bin/ln -s /usr/local/pgsql10/bin/psql /usr/bin

EOF

sudo su - postgres <<EOF

/usr/bin/perl -i -p -e "s/\#listen_addresses = 'localhost'/listen_addresses = '*'/;" /usr/local/pgsql10/data/postgresql.conf

/usr/bin/perl -i -p -e "s/shared_buffers = 128MB/shared_buffers = 2GB/;" /usr/local/pgsql10/data/postgresql.conf

/usr/bin/perl -i -p -e "s/\#port = 5432/port = 5433/;" /usr/local/pgsql10/data/postgresql.conf

/usr/local/pgsql10/bin/pg_ctl -D /usr/local/pgsql10/data restart

EOF