Quickstart guide on using pgPool

January 24, 2023

Steps (as root user)




# Setup YUM repository for installing EPAS as the PEM

# repository

rpm -Uvh  

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

Share this

Relevant Blogs

More Blogs

Using auth_method=hba in PgBouncer

PgBouncer is a great tool for improving database performance with connection pooling. I've been using it for many years, since it first became available in 2007. Since then, several improvements...
January 23, 2023

Debugging Your PostgreSQL Database Binaries

Knowing how to debug is a critical aspect of every application development life cycle. Debugging database bianaries allow you to not only recognize that an exception has occurred, but also...
January 23, 2023