Load balancing & connection pooling using Pgpool-II

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

 

 

Step 2: Add postgresql repository

 

 

We need to import GPG key and add PostgreSQL repository into our Ubuntu instance.

 

 

Repository has been added successfully,  hence update the package list and install pgpool2.

 

 

Therefore, to start pgpool2 use the pgpool2 systemd unit file.

 

 

Similarly, to stop

 

 

Step 3: Configuring pgpool2

 

 

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:

 

 

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:

 

 

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:

Install postgresql client:

Furthermore, Connecting to postgresql server via pgpool

In addition, Enter password in password prompt

Also, in the second terminal monitor the syslog

In another terminal where psql command is executed.

Create a new database, new table and insert some data into it

Above all, Connect to the newly created database:

Create new table:

Likewise, insert value in the table

Finally, read data from 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.

. . .

Comment

Add Your Comment

Be the first to comment.

css.php