Open Source ERP Academy

Detail PGBouncer PostgreSQL Connection Pooler

NOTICE:

I do not use pgbouncer in production. Below was an experiment, and I did not want to lose my notes and thoughts about the topic.

PGBouncer PostgreSQL Connection Pooler

The purpose of this page is to help you install/setup PGBouncer on a dedicated machine as a connection pooler for your database. Perform the following:
  1. Create a new Ubuntu 14.04 installation
  2. Copy the below script to your home directory (call it chuboe_phbouncer.sh)
  3. Update the variables at the beginning of your script to match your environment
  4. Make the script executable using "chmod +x chuboe_phbouncer.sh"
  5. Run the script using ./chuboe_phbouncer.sh
Script to copy to your new Ubuntu server: 
chuboe_dburl=Your_DB.URL
chuboe_dbname=idempiere
chuboe_dbuser=adempiere
chuboe_dbpass=Silly
chuboe_dbport=5432
chuboe_osuser=ubuntu
chuboe_max_client_conn=300 # should be 100 times the number of application servers
chuboe_default_pool_size=25

#get rid of AWS sudo error in a VPC
wget bitbucket.org/cboecking/idempiere-installation-script/raw/default/utils/setHostName.sh
chmod +x setHostName.sh
sudo ./setHostName.sh

#install pgbouncer and postgresql client tools (like psql)
sudo apt-get update
sudo apt-get install -y pgbouncer postgresql-client

#prevent the script from prompting the user for a password
echo "*:*:*:$chuboe_dbuser:$chuboe_dbpass">>/home/$chuboe_osuser/.pgpass
sudo -u $chuboe_osuser chmod 600 .pgpass
sudo cp .pgpass /var/lib/postgresql/
sudo chown postgres:postgres /var/lib/postgresql/.pgpass

#copy usernames and passwords from main database
sudo -u postgres psql -U $chuboe_dbuser -d $chuboe_dbname -h $chuboe_dburl -c "\copy (select '\"'||rolname||'\" \"'||coalesce(rolpassword,'')||'\"'from pg_authid) to '/etc/pgbouncer/userlist.txt';"

#configure pgbouncer.idi
sudo sed -i "/\[databases]/apostgres = host=$chuboe_dburl\ntemplate1 = host=$chuboe_dburl\n$chuboe_dbname = host=$chuboe_dburl" /etc/pgbouncer/pgbouncer.ini
#note: the admin user can connect to a simulated database called pgbouncer. This helps you better understand connection and behavior details
sudo sed -i "/admin_users =/aadmin_users = postgres" /etc/pgbouncer/pgbouncer.ini
sudo sed -i "s|auth_type = trust|auth_type = md5|" /etc/pgbouncer/pgbouncer.ini
sudo sed -i "s|listen_addr = 127.0.0.1|listen_addr = *|" /etc/pgbouncer/pgbouncer.ini
sudo sed -i "s|max_client_conn = 100|max_client_conn = $chuboe_max_client_conn|" /etc/pgbouncer/pgbouncer.ini
sudo sed -i "s|default_pool_size = 20|default_pool_size = $chuboe_default_pool_size|" /etc/pgbouncer/pgbouncer.ini
sudo sed -i "s|listen_port = 6432|listen_port = $chuboe_dbport|" /etc/pgbouncer/pgbouncer.ini
#fix known issue - http://permalink.gmane.org/gmane.comp.db.postgresql.pgbouncer.general/704
sudo sed -i "s|;ignore_startup_parameters|ignore_startup_parameters|" /etc/pgbouncer/pgbouncer.ini

#set pgbouncer to auto start on boot
sudo sed -i "s|START=0|START=1|" /etc/default/pgbouncer

sudo service pgbouncer start

#Clear variables
chuboe_dburl=
chuboe_dbname=
chuboe_dbuser=
chuboe_dbpass=
chuboe_dbport=
chuboe_osuser=
chuboe_max_client_conn=
chuboe_default_pool_size=

How to view PostgreSQL Connection Details

View connection details from SQL: 
SELECT count(*) FROM pg_stat_activity; -- see count
SELECT * FROM pg_stat_activity; -- see the details of the open connections
SELECT sum(numbackends) FROM pg_stat_database; -- similar but different view
View connection details from the OS: 
ps aux | grep postgres | grep -v grep

Connect to the pgbouncer Admin Interface

psql -h localhost -p 6432 -U postgres -d pgbouncer
Show Servers;
Show Clients;
Show Pools;
Show Stats;
Note: if the request_time value should be recent. Otherwise, a connection is being hogged and effectively pulled from the queue.

Resources