Steps (as root user)
#!/bin/bash
# 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;
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-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();
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:
http://www.pgpool.net/mediawiki/index.php/Main_Page
- 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.