In this post, We are going to perform load balance with master-slave mode and connection pooling using Pgpool on a Amazon RDS postgresql cluster.
What is Pgpool-II?
Pgpool-II is a middleware that works between PostgreSQL servers and a PostgreSQL database client.
It provides the following features:
- Connection Pooling
- Replication
- Load Balancing
- Limiting Exceeding Connections
- In Memory Query Cache
Master-Slave mode:
Pgpool-II load balancing of select queries works with Master-Slave mode to distribute the read load on the standby backend nodes, when load balancing is enabled with Master-Slave mode Pgpool-II sends the writing queries to the primary node in Master Slave mode and read queries to other nodes.
You can read more about it here.
Now, Let’s start with the installation:
Prepare the RDS instances:
Writable instance: Start a new RDS instance.
For this tutorial we’ll be using t2.micro instance with postgresql version 11.5r1 (latest supported postgresql version).
Note: During the creation of DB instance Backup retention for DB instance must be enabled.
All the DDL (Data definition language) queries will be directed to writable RDS instance by pgpool.
Readable Replica: After the instance is in available state, select the instance and click on the actions menu to select the create read replica option as a result a read replica instance will be created.
Prepare the EC2 instance:
For the sake of this tutorial, we’ll be using a t2.micro instance with Ubuntu 18.04 LTS operating system.
Installing Pgpool-II:
We’ll be installing pgpool-II version 4.1.0 (karasukiboshi)
Step 1: Update the existing packages of distribution
|
1 |
sudo apt update |
Step 2: Add postgresql repository
|
1 2 |
echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" \ |sudo tee /etc/apt/sources.list.d/pgdg.list |
We need to import GPG key and add PostgreSQL repository into our Ubuntu instance.
|
1 |
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - |
Repository has been added successfully, hence update the package list and install pgpool2.
|
1 2 |
sudo apt update sudo apt install -y pgpool2 |
Therefore, to start pgpool2 use the pgpool2 systemd unit file.
|
1 |
sudo systemctl start pgpool2 |
Similarly, to stop
|
1 |
sudo systemctl stop pgpool2 |
Step 3: Configuring pgpool2
|
1 |
sudo vim /etc/pgpool2/pgpool.conf |
Most of the configuration of pgpool is already done in file /etc/pgpool2/pgpool.conf as a result only some parameters are required to change.
The table below shows the important ones.
| listen_addresses | ‘*’ |
| backend_hostname0 | Amazon RDS postgresql writeable endpoint. |
| backend_port0 | 5432 |
| backend_flag0 | ALWAYS_MASTER |
| backend_weight0 | 1 |
| backend_hostname1 | Amazon RDS postgresql read replica endpoint. |
| backend_port1 | 5432 |
| backend_weight1 | 2 |
| enable_pool_hba | on |
| pool_passwd | ‘pool_passwd’ |
| replication_mode | off |
| load_balance_mode | on |
| master_slave_mode | on |
| master_slave_sub_mode | ‘stream’ |
| sr_check_period | 0 |
| health_check_period | 5 |
| health_check_user | postgres |
| health_check_password | Password for postgres user |
| health_check_max_retries | 20 |
| health_check_retry_delay | 1 |
| failover_on_backend_error | off |
Step 4: Creating the pool_passwd file:
Above all, We’ll be using pg_md5 command line utility to generate the pool_passwd file here’s the syntax of the command:
|
1 |
pg_md5 --md5auth --username=username password |
Here, replace username with your postgresql username and also replace password with your own postgresql password.
Step 5: In addition to this, we’ll check if both write instance and read replica are recognized by pgpool:
|
1 |
cat /var/log/postgresql/pgpool_status |
Note: The file would show ‘up’ for both the instances, as a result traffic on both these instances will be distributed.
Testing the installation:
Furthermore, To test the installation enable the log parameters in
/etc/pgpool2/pgpool.conf file:
| log_destination | ‘syslog’ |
| log_statement | on |
| log_per_node_statement | on |
In addition, Restart pgpool:
|
1 |
sudo systemctl restart pgpool2 |
Install postgresql client:
|
1 |
sudo apt install postgresql-client |
Furthermore, Connecting to postgresql server via pgpool
|
1 |
psql -h localhost -p 5432 -U postgres -W |
In addition, Enter password in password prompt
Also, in the second terminal monitor the syslog
|
1 |
tail -f /var/log/syslog |
In another terminal where psql command is executed.
Create a new database, new table and insert some data into it
|
1 |
CREATE DATABASE TEST; |
Above all, Connect to the newly created database:
|
1 |
\c TEST |
Create new table:
|
1 |
CREATE TABLE TEST_TABLE(VAL VARCHAR(10)); |
Likewise, insert value in the table
|
1 |
INSERT INTO TEST_TABLE VALUES('DATA'); |
Finally, read data from table
|
1 |
SELECT * FROM TEST_TABLE; |
Therefore, DB node number 0 in the logs shows that the write instance is being used and node 1 shows that read replica is being used.
Note: If there are read queries within the transaction they are forwarded to the write instance, otherwise read queries are load balanced between both the instances.