Quickstart guide on using pgPool

Timothy Steward Senior Sales Engineer

Steps (as root user)




# Setup YUM repository for installing EPAS as the PEM

# repository

rpm -Uvh  http://yum.enterprisedb.com/edbrepos/edb-repo-9.6-4.noarch.rpm

# Set YUM username/password in edb.repo

export YUM_USER=<yum username>

export YUM_PASSWORD=<yum password>
sed -i "s/<username>:<password>/$YUM_USER:$YUM_PASSWORD/g" /etc/yum.repos.d/edb.repo


# Enable the EPAS 9.5 repo in edb.repo
sed -i "\/ppas95/,/gpgcheck/ s/enabled=0/enabled=1/" /etc/yum.repos.d/edb.repo


# Install EPAS 9.5

yum -y install ppas95-server

# Setup .pgpass file so that we can connect to the database without prompting for password

su - enterprisedb -c "echo \"*:*:*:enterprisedb:enterprisedb\" > ~/.pgpass"

su - enterprisedb -c "chmod 600 ~/.pgpass"


# Create the master cluster su - enterprisedb -c "mkdir /var/lib/ppas/9.5/master" su - enterprisedb -c "chmod 700 /var/lib/ppas/9.5/master"

su - enterprisedb -c "/usr/ppas-9.5/bin/initdb -D /var/lib/ppas/9.5/master"


# Set it up as a master ensureing at least 5 WALs are kept since we are not archiving

# Setting hot_standby on so that the replicas will inherit the setting from pg_basebasckup

sed -i "s/#wal_level = minimal/wal_level = hot_standby/" /var/lib/ppas/9.5/master/postgresql.conf sed -i "s/#max_wal_senders = 0/max_wal_senders = 3/" /var/lib/ppas/9.5/master/postgresql.conf

sed -i "s/#hot_standby = off/hot_standby = on/" /var/lib/ppas/9.5/master/postgresql.conf

sed -i "s/#wal_keep_segments = 0/wal_keep_segments = 5/" /var/lib/ppas/9.5/master/postgresql.conf

sed -i "s/#logging_collector = off/logging_collector = on/" /var/lib/ppas/9.5/master/postgresql.conf

sed -i "s/#host[ ]*replication/host replication/g" /var/lib/ppas/9.5/master/pg_hba.conf sed -i "s/#local[ ]*replication/local replication/g" /var/lib/ppas/9.5/master/pg_hba.conf

# Start the master and set default enterprisedb password

su - enterprisedb -c "/usr/ppas-9.5/bin/pg_ctl -w -D /var/lib/ppas/9.5/master start"

su - enterprisedb -c "/usr/ppas-9.5/bin/psql -p 5444 -d postgres -c \"alter user enterprisedb identified by enterprisedb\""


# Require passwords to login to the database and reload the cluster

sed -i "s/trust/md5/g" /var/lib/ppas/9.5/master/pg_hba.conf

su - enterprisedb -c "/usr/ppas-9.5/bin/pg_ctl -D /var/lib/ppas/9.5/master reload"


# Create and start replica cluster as a replica of master

su - enterprisedb -c "mkdir /var/lib/ppas/9.5/replica" su - enterprisedb -c "chmod 700 /var/lib/ppas/9.5/replica"

su - enterprisedb -c "/usr/ppas-9.5/bin/pg_basebackup -h localhost -p 5444 -R -D /var/lib/ppas/9.5/replica"

sed -i "s/#port = 5444/port = 5445/" /var/lib/ppas/9.5/replica/postgresql.conf

su - enterprisedb -c "/usr/ppas-9.5/bin/pg_ctl -w -D /var/lib/ppas/9.5/replica start"


# Confirm can connect to master and insert

su - enterprisedb -c "/usr/ppas-9.5/bin/psql -p 5444 -d postgres"

create table my_table (my_col text);

insert into my_table values('test');

table my_table;



# Confirm can connect to replica, see data, but not insert

su - enterprisedb -c "/usr/ppas-9.5/bin/psql -p 5445 -d postgres"

table my_table;

insert into my_table values('should fail');



# Congratulations, you now have streaming replication setup. Now let's move on to pgPool...


# Enable the enterprisedb-tools repository

sed -i "\/enterprisedb-tools/,/gpgcheck/ s/enabled=0/enabled=1/" /etc/yum.repos.d/edb.repo


# Install pgPool

yum install -y ppas-pgpool34


# Configure pgpool.conf and via weights send all reads to the replica

cp /etc/ppas-pgpool34/pgpool.conf.sample-master-replica /etc/ppas-pgpool34/pgpool.conf

chown enterprisedb:enterprisedb /etc/ppas-pgpool34/pgpool.conf

sed -i "s/backend_hostname0 = .*$/backend_hostname0 = 'localhost'/" /etc/ppas-pgpool34/pgpool.conf sed -i "s/backend_port0 = .*$/backend_port0 = 5444/" /etc/ppas-pgpool34/pgpool.conf sed -i "s/backend_weight0 = .*$/backend_weight0 = 0/" /etc/ppas-pgpool34/pgpool.conf sed -i "s/#backend_hostname1 = .*$/backend_hostname1 = 'localhost'/" /etc/ppas-pgpool34/pgpool.conf sed -i "s/#backend_port1 = .*$/backend_port1 = 5445/" /etc/ppas-pgpool34/pgpool.conf sed -i "s/#backend_weight1 = .*$/backend_weight1 = 1/" /etc/ppas-pgpool34/pgpool.conf

sed -i "s,pid_file_name = .*$,pid_file_name = '/var/run/ppas-pgpool34/pgpool.pid'," /etc/ppas-pgpool34/pgpool.conf

sed -i "s/enable_pool_hba = off/enable_pool_hba = on/" /etc/ppas-pgpool34/pgpool.conf

sed -i "s/sr_check_user = .*$/sr_check_user = 'enterprisedb'/" /etc/ppas-pgpool34/pgpool.conf

sed -i "s/sr_check_password = .*$/sr_check_password = 'enterprisedb'/" /etc/ppas-pgpool34/pgpool.conf


# Configure pool_hba.conf

cp /etc/ppas-pgpool34/pool_hba.conf.sample /etc/ppas-pgpool34/pool_hba.conf

chown enterprisedb:enterprisedb /etc/ppas-pgpool34/pool_hba.conf

sed -i "s/trust/md5/g" /etc/ppas-pgpool34/pool_hba.conf


# Configure the pgPool password file with usernames/passwords currently in the database

chown enterprisedb:enterprisedb /etc/ppas-pgpool34/pool_passwd

su - enterprisedb -c "psql -d postgres -c \"select usename || ':' || passwd from pg_shadow;\" | grep : | xargs -l > /etc/ppas-pgpool34/pool_passwd"


# Start pgPool, wait for it to start, and show that the pool processes are running

su - enterprisedb -c "/usr/ppas/pgpool34/bin/pgpool -D && sleep 1"

ps -ef | grep pgpool


# Restart pgPool (just so that you know how)

su - enterprisedb -c "/usr/ppas/pgpool34/bin/pgpool -m fast stop"

su - enterprisedb -c "/usr/ppas/pgpool34/bin/pgpool -D && sleep 1"


# Connect to the pgPool load balancer

su - enterprisedb -c "psql -p 9999 -d postgres"

SHOW pool_version;

SHOW pool_nodes;


-- Some other commands to show pgPool info

-- SHOW pool_status;

-- SHOW pool_processes;

-- SHOW pool_pools;


-- Show what is in the table, which port (master/replica) you are connected to, and do an insert

table my_table;

select inet_server_port();

insert into my_table values ('from load balancer');


-- Force a query to go to the master

/* NO LOAD BALANCE */ select inet_server_port();



# Congratulations! You have successfully installed, configured, and connected to your first

# pgPool loadbalancer in front of a master cluster and streaming replica cluster.



  • More detailed information on pgPool is available here:


  • pgPool supports a limited amount of authentication methods - pretty much md5 and trust.  For md5, you must specify your user passwords in the pgPool pool_passwd file.  Each time a database user changes their password, you must update that file (which can be done via a simple command as shown in the steps above).


This guide is intended to get you up and started with a default installation of pgPool in front of a master and streaming replica. There is much more to pgPool that is not covered in this quickstart, the intent here is only to get you a simple configuration working as quickly as possible.

Timothy StewardSenior Sales Engineer

Tim Steward is a Sales Engineer at EDB with over 20 years of database experience. During this time, he has developed a true passion for databases, and the evolution of technology. Tim enjoys the excitement he receives by knowing he helped a customer solve complex IT challenges by using his knowledge of database technology. Fresh out of college, Tim became an Oracle DBA within the fundraising industry, which is where he learned to pay attention to details and be an effective listener. As his career evolved he became a consultant within the healthcare industry where he expanded his database skills to Sybase. During his consulting years, Tim added SQL Server and the MySQL database to his skill set, Tim also spent several years supporting Oracle E-Business Suite.