Operating System: Ubuntu 20 LTS Focal
Reference
Note: PgPool not included in Ubuntu 18 apt repo https://dev-qa.com/148225/how-to-install-pgpool-on-ubuntu-18-04-bionic
Installation
- Update apt
sudo apt update
- Install pgpool2
sudo apt install -y pgpool2
- Install psql client
sudo apt -y install postgresql-client-12
- Install pgbench to test load balancing
sudo apt -y install postgresql-contrib sudo systemctl stop postgresql sudo systemctl disable postgresql
Configuring for RDS backend (Primary + Read Replica)
Configuration files are located in /etc/pgpool2
- Create backup of configuration files
cd /etc/pgpool2 sudo cp pgpool.conf pgpool.conf.bak sudo cp pool_hba.conf pool_hba.conf.bak sudo cp pool_passwd pool_passwd.bak
Modify the following configuration files
- pgpool.conf
listen_addresses = '*' port = 5432 pcp_listen_addresses = '*' pcp_port = 9898 pcp_socket_dir = '/var/run/postgresql' listen_backlog_multiplier = 1 backend_hostname0 = 'master URL endpoint' backend_port0 = 5432 backend_weight0 = 1 backend_flag0 = 'ALWAYS_MASTER|DISALLOW_TO_FAILOVER' backend_hostname1 = 'read replica URL endpoint' backend_port1 = 5432 backend_weight1 = 1 backend_flag1 = 'ALLOW_TO_FAILOVER' enable_pool_hba = on pool_passwd = 'pool_passwd' ssl = off num_init_children = 8 max_pool = 10 connection_cache = off replication_mode = off load_balance_mode = on master_slave_mode = on
- pool_hba.conf
local all all md5 host all all 127.0.0.1/32 md5 # Need to add the remote source IP / CIDR address allowed to connect to pgpool # 0.0.0.0/0 for testing only host all all 0.0.0.0/0 md5
Use pg_md5 tool to generate credentials in md5 format with the username & password that is used to connect to the backend RDS postgresDB
cd /etc/pgpool2
sudo pg_md5 -m -u ${DatabaseUser} ${DatabasePassword}Add in all other usernames and passwords that will be used to connect to RDS backend
- To avoid password prompt or password in command parameter, pg_md5 can read user name:password pairs from file. It will add all user names and md5 hashed password to pool_passwd authentication file.
$ cat users.txt username1:secretpassword1 username2:secretpassword1 $ pg_md5 -m -f /path/to/pgpool.conf -i users.txt trying to read username:password pairs from file users.txt $ cat /path/to/pool_passwd username1:md533314126ba0b187df1e37f5ce6a489a8 username2:md58ae92c6e1d6a48d80e2583fe715e2b36
Restart pgpool2 for configuration to take effect
sudo systemctl restart pgpool2
Testing Load Balancing
- Initialize DB for pgbench
pgbench -h localhost -p 5432 -U postgres -i
- Use pgbench to generate load
pgbench -h localhost -p 5432 -U postgres -c 10 -T 10 -S
- Run command to see if queries are distributed across database nodes:
psql --dbname=postgres --host=localhost --username=postgres --port=5432 --command="SHOW POOL_NODES"