PostgreSQL Connection Pooling and Load Balancing with PgPool-II Cluster

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 , a Docker image for the sake of simplifying installation and configuration. rds_pgpool Motivation We want to offload some of the read requests to our read replicas. Load Balancing: 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. Connection Pool: 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 ( ), 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. the diagram above 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: Load balancing using PgPool-II cluster (marked with a yellow star) What is Pgpool-II? From its , it is a middleware sits between Postgres clients and servers, serves as a database proxy. The main features are: official website ->We need this to reduce overhead of connection establishment by reusing a pool of connection. Connection pooling ->We need this feature to offload some of the read requests to our read replica. Load balancing -> 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. Limiting Exceeding Connection -> This is not used as we are using AWS RDS Replication, which uses PostgreSQL native streaming replication. [ ] Replication 1 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 . In this post, I will be focusing on setting up a 2-Node PgPool-II cluster using AWS EC2. rds_pgpool 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: and script. Escalation script is run when promotion to master being initiated. De-escalation script is run when the node is being shutdown or restarted. escalation de-escalation In the Docker image, the escalation script runs a command to assign the Elastic IP to the node itself while the de-escalation script detaches and releases the Elastic IP. rds_pgpool aws-cli Step 1: Prepare EC2 Instances, Elastic IP I created 2 t2.micro instances for this tutorial. In my case, I chose as my machine image. Amazon Linux AMI 2018.03.0 (HVM) 1 instance for each PgPool-II node While creating EC2 instances, ensure they accept inbound connection of each other to ports , and . 9000 9999 9696 Next, allocate an Elastic IP but leave it disassociated for now. Allocate an Elastic IP, leave it disassociated for now Step 2: Install Docker-Compose comes with Docker but not Docker-Compose. To install it, run: Amazon Linux AMI 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. 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; 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, should be 7 and should be 3. num_init_children max_pool Step 4: Prepare docker-compose.yml Assuming these are the instance-ids and IPs: Node 1 instance id: i- 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. for Node 1 would be: docker-compose.yml version: "3"services:pgpool:restart: 'always'image: melvinkcx/rds_pgpool:0.2.8ports:- "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= - AWS_SECRET_KEY= - AWS_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 in your PgPool-II node 1. docker-compose_node1.yml For Node 2, save the following as in your PgPool-II node 2. docker-compose_node2.yml version: "3"services:pgpool:restart: 'always'image: melvinkcx/rds_pgpool:0.2.8ports:- "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= - AWS_SECRET_KEY= - AWS_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 up -d After starting both nodes, pull the logs and observe the behavior: docker logs 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 , your client apps should now be connecting to with port . 55.55.55.55 55.55.55.55 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 -W Testing Test Load Balancing To test if PgPool-II is indeed performing load balancing we run to simulate read requests. pgbench pgbench -h localhost -p 9999 -U -c 10 -T 10 -S After running , we run the following to see if queries are distributed across database nodes: pgbench psql -h localhost -p 9999 -U -W -c "show pool_nodes" You should see a table as this: 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. 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 comes with the option to run in single-node mode. Refer to the for more details. rds_docker README Resources rds_docker on Docker Hub rds_docker repository, README and FAQ 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