cancel
Showing results for 
Search instead for 
Did you mean: 

pgPool 3.4 Quickstart

Community Manager

Objective

Quickly (less than five minutes) install and configure pgPool to load balance in front of an EPAS master and EPAS streaming replica.

 

Prerequisites

Running instance of RHEL 6/7 with root access

 

Components

There are three primary components to this quickstart:

  1. EPAS Master Cluster

  2. EPAS Replica Cluster

  3. pgPool

 

 

Steps (as root user)

 

#!/bin/bash

# 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;
exit

# 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');
exit

# 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-slave /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();
exit

# Congratulations!  You have successfully installed, configured, and connected to your first 
# pgPool loadbalancer in front of a master cluster and streaming replica cluster.

Tips

  • 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).

 

Summary

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.

 

Please send questions/comments to sales@enterprisedb.com

 

 

1 Comment
Level 3 Traveller

Nice post