In this post, I will walk you through on how to perform load balancing and connection pooling with PgPool-II, what are the benefits and motivation of having PgPool-II and most importantly, how to set up a PgPool-II cluster.

In this tutorial, you will find me using `rds_pgpool`, a Docker image for the sake of simplifying installation and configuration.

### Motivation

**Load Balancing:** We want to offload some of the read requests to our read replicas.

**Connection Pool:** Instead of receiving an error when connections to our master node reaches maximum, we want to queue it. Also, we want to reduce overhead by reusing database connections.

### Our Architecture

In our case, we use AWS RDS for PostgreSQL in master-slave mode. We elected one master and data are replicated to one read slaves.

Before adding PgPool-II into our stack (_the diagram above_), database load balancing depends on respective service. Application services have to identify database requests and send them to respective database. In our case, despite the framework we use allows us to use multiple database, providing similar load balancing feature, the effort required simply doesn’t worth the while.

Therefore, I decided to give PgPool-II a try. A new layer is to be added into our stack, which results in the diagram below:

![](https://hackernoon.com/hn-images/1*AwiLBUyvgoVg3Z4mn6gQ8g.png)

Load balancing using PgPool-II cluster (marked with a yellow star)

### What is Pgpool-II?

From its [official website](http://www.pgpool.net/mediawiki/index.php/Main_Page), it is a middleware sits between Postgres clients and servers, serves as a database proxy. The main features are:

*   **Connection pooling** ->We need this to reduce overhead of connection establishment by reusing a pool of connection.
*   **Load balancing** ->We need this feature to offload some of the read requests to our read replica.
*   **Limiting Exceeding Connection** -> When connections to PostgreSQL server reaches maximum limit, further connection will be rejected, whereas by using PgPool-II, exceeding connections are queued instead of being rejected right away.
*   **Replication** -> This is not used as we are using AWS RDS Replication, which uses PostgreSQL native streaming replication. \[[1](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PostgreSQL.Replication.ReadReplicas.html#USER_PostgreSQL.Replication.ReadReplicas.Configuration)\]

### Running PgPool-II in Cluster Mode with rds\_pgpool

PgPool-II operates in 2 modes: Single-Node and Cluster Mode. To simplify installation and configuration, I created a Docker image `rds_pgpool` . In this post, I will be focusing on setting up a 2-Node PgPool-II cluster using AWS EC2.

Let’s assume our database cluster are made up of 1 master and 1 slave/read replica.

#### Prerequisite

*   PostgreSQL cluster that uses native streaming replication. (If you are using AWS RDS, with PostgreSQL version >9.3.5, streaming replication is used by default)

#### How Cluster Mode Works?

In PgPool-II cluster mode, only 1 node will be accepting connections at a time (it is, of course, the master node). The watchdog of each node will perform heartbeat checking to the master node. In case of failure to detect master node, the slave/standby node assumes the master is down and takes over the master role.

In order to take over the role of the master node, each node must first be assigned 2 scripts: `escalation` and `de-escalation` script. Escalation script is run when promotion to master being initiated. De-escalation script is run when the node is being shutdown or restarted.

In the `rds_pgpool` Docker image, the escalation script runs a `aws-cli` command to assign the Elastic IP to the node itself while the de-escalation script detaches and releases the Elastic IP.

#### Step 1: Prepare EC2 Instances, Elastic IP

I created 2 t2.micro instances for this tutorial. In my case, I chose `Amazon Linux AMI 2018.03.0 (HVM)` as my machine image.

![](https://hackernoon.com/hn-images/1*a0jc9O_YRWRtpUhsxPRo5Q.png)

1 instance for each PgPool-II node

While creating EC2 instances, ensure they accept inbound connection of each other to ports `9000` , `9999` and `9696` .

Next, allocate an Elastic IP but leave it disassociated for now.

![](https://hackernoon.com/hn-images/1*luT3fdOH-EHUK0GYal8_Gw.png)

Allocate an Elastic IP, leave it disassociated for now

#### Step 2: Install Docker-Compose

`Amazon Linux AMI` comes with Docker but not Docker-Compose. To install it, run:

curl -L https://github.com/docker/compose/releases/download/1.23.2/docker-compose-\`uname -s\`-\`uname -m\` > /usr/local/bin/docker-compose

then:

chmod +x /usr/local/bin/docker-compose

#### Step 3: Gather Database Credentials, Instances Details

We would need the following details of our database and PgPool-II nodes.

![](https://hackernoon.com/hn-images/1*YidujLGAb2j3Q4qxdN0kHQ.png)

Attributes required for each database and PgPool-II node

#### (UPDATE) Step 3.5: Calculate Max Connection Pool

For connection pooling, we have to calculate the maximum connection allowed to our master node. If this is miscalculated, it it likely that we will encounter the error `kind does not match between master(xx) slot[x](xx)` .

The rule of thumb:

num\_of\_connections = max\_connection - reserved\_connection

To check the max connection allowed, run this is `psql` :

show max\_connections;

![](https://hackernoon.com/hn-images/1*oPFMUCSr9mWzmpWezFigFw.png)

Max connections is 26. In AWS RDS, this is determined based on your instance size.

Since the reserve connection is usually 3, number of connections in our pool should be `26 — 3 = 23` .

Say now our database allows a maximum of 23 non-superuser connection, we have to adjust our configurations. There are two knobs in our case:

*   num\_init\_children -> Maximum child processes PgPool-II could spawn
*   max\_pool -> Number of connections cached per child

The rule of thumb should always be:

num\_init\_children \* max\_pool <= num\_of\_connections

Let’s say we want to have 7 child processes, the maximum connection per child should be 23 / 7 ~= 3.

Thus, `num_init_children` should be 7 and `max_pool` should be 3.

#### Step 4: Prepare docker-compose.yml

Assuming these are the instance-ids and IPs:

*   Node 1 instance id: i-[ababababab](https://bitbucket.org/melvinkcx/rds_pgpool_docker/commits/ababababab)
*   Node 1 private IP: 172.33.11.11
*   Node 2 instance id: i-xyxyxyxy
*   Node 2 private IP: 172.33.33.33
*   Elastic IP: 55.55.55.55

and these are our database attributes:

*   Master DB Hostname: xxxx.xxxx.ap-southeast-1.rds.amazonaws.com
*   Slave DB Hostname: yyyy.yyyy.ap-southeast-1.rds.amazonaws.com
*   Database name: postgres
*   Database username: postgres
*   Database password: postgres

_(UPDATE) In version 0.2.8, environment variables_ `_NUM_INIT_CHILDREN_` _and_ `_MAX_POOL_` _are added to set the connection pool size._

`docker-compose.yml` for Node 1 would be:

version: "3"  
services:   
  pgpool:  
    restart: 'always'  
    image: melvinkcx/rds\_pgpool:0.2.8  
    ports:  
      - "9999:9999"  
      - "9000:9000"  
      - "9694:9694"  
    environment:  
      - DB\_NAME=postgres  
      - DB\_USERNAME=postgres  
      - DB\_PASSWORD=postgres  
      - MASTER\_NODE\_HOSTNAME=xxxx.xxxx.ap-southeast-1.rds.amazonaws.com  
      - REPLICA\_NODE\_HOSTNAME\_0=yyyy.yyyy.ap-southeast-1.rds.amazonaws.com  
      - CLUSTER\_MODE=true   
      - NUM\_INIT\_CHILDREN=7   
      - MAX\_POOL=3  
      - AWS\_ACCESS\_KEY=**<your aws access key>**  
      - AWS\_SECRET\_KEY=**<your aws secret key>**  
      - AWS\_DEFAULT\_REGION=<your aws ec2 default region>  
      - ELASTIC\_IP=55.55.55.55  
      - SELF\_INSTANCE\_ID=i-abababab  
      - SELF\_PRIVATE\_IP=172.33.11.11  
      - STANDBY\_INSTANCE\_PRIVATE\_IP=172.33.33.33

Save it as `docker-compose_node1.yml` in your PgPool-II node 1.

For Node 2, save the following as `docker-compose_node2.yml` in your PgPool-II node 2.

version: "3"  
services:   
  pgpool:  
    restart: 'always'  
    image: melvinkcx/rds\_pgpool:0.2.8  
    ports:  
      - "9999:9999"  
      - "9000:9000"  
      - "9694:9694"  
    environment:  
      - DB\_NAME=postgres  
      - DB\_USERNAME=postgres  
      - DB\_PASSWORD=postgres  
      - MASTER\_NODE\_HOSTNAME=xxxx.xxxx.ap-southeast-1.rds.amazonaws.com  
      - REPLICA\_NODE\_HOSTNAME\_0=yyyy.yyyy.ap-southeast-1.rds.amazonaws.com  
      - CLUSTER\_MODE=true   
      - NUM\_INIT\_CHILDREN=7   
      - MAX\_POOL=3  
      - AWS\_ACCESS\_KEY=**<your aws access key>**  
      - AWS\_SECRET\_KEY=**<your aws secret key>**  
      - AWS\_DEFAULT\_REGION=<your aws ec2 default region>  
      - ELASTIC\_IP=55.55.55.55  
      - SELF\_INSTANCE\_ID=i-xyxyxyxy  
      - SELF\_PRIVATE\_IP=172.33.33.33  
      - STANDBY\_INSTANCE\_PRIVATE\_IP=172.33.11.11

#### Step 5: Run your docker-compose

On each node, navigate to the directory of your docker-compose files, run:

docker-compose -f **<your\_docker\_compose\_filename.yml>** up -d

After starting both nodes, pull the logs and observe the behavior:

docker logs **<your\_docker\_container\_id>**

![](https://hackernoon.com/hn-images/1*WVVj6l7Pm1mC9NjsUqj4ig.png)

Logs of PgPool-II node (It is now a master node)

#### Step 6: Connecting PgPool-II

To make use of PgPool-II, re-configure your client apps to connect to the cluster instead of your database instances directly.

For instance, the Elastic IP of your PgPool-II cluster is `55.55.55.55` , your client apps should now be connecting to `55.55.55.55` with port `9999`.

You can also try your connection by connecting to your PgPool-II cluster with `psql` :

    psql -h 55.55.55.55 -p 9999 -U <username> -W

### Testing

#### Test Load Balancing

To test if PgPool-II is indeed performing load balancing we run `pgbench` to simulate read requests.

    pgbench -h localhost -p 9999 -U <username> -c 10 -T 10 -S

After running `pgbench` , we run the following to see if queries are distributed across database nodes:

    psql -h localhost -p 9999 -U <username> -W -c "show pool_nodes"

You should see a table as this:

![](https://hackernoon.com/hn-images/1*RRSKjKXDHqfGcdxbr0iWLw.png)

Look at \`select\_cnt\` of \`show pool\_nodes\`;

#### Test PgPool-II Failover

To test its failover mechanism, simply stop/reboot one of the nodes. In my case, I restarted Node 1.

![](https://hackernoon.com/hn-images/1*sA659xuk1mlnyhQNoVSoMQ.png)

On the right: Node 2 detected the master node is down and hence electing itself as master.

The logs in Node 2 shown that it is able to detect Node 1 is down and has elected itself to take over Node 1.

### Running PgPool-II In Single-Node

`rds_docker` comes with the option to run in single-node mode. Refer to the [README](https://bitbucket.org/melvinkcx/rds_pgpool_docker/src/master/README.md) for more details.

### Resources

*   [rds\_docker on Docker Hub](https://hub.docker.com/r/melvinkcx/rds_pgpool)
*   [rds\_docker repository, README and FAQ](https://github.com/melvinkcx/rds_pgpool_docker)

Your claps will definitely drive me further. Feel free to give some if you find this useful. _(Don’t forget you can give up to 50_ 👏 👏 😃_)_

Amazon

Yellow

Winner of Noonies 2020 for the award Hacker Noon Contributor of the Year - REACT NATIVE.

PostgreSQL Connection Pooling and Load Balancing with PgPool-II Cluster

Too Long; Didn't Read

Companies Mentioned

@melvinkcx2

RELATED STORIES