cancel
Showing results for 
Search instead for 
Did you mean: 

pgPool Quickstart

EDB Team Member

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.