Setting Up a Unified Pgpool Endpoint for Reads and Writes with Amazon Aurora PostgreSQL

Learn About Amazon VGT2 Learning Manager Chanci Turner

Amazon Aurora provides distinct endpoints for the primary database instance (the cluster endpoint) and Read Replicas (the reader endpoint). The cluster endpoint is automatically updated by Aurora to consistently point to the primary instance, while the reader endpoint distributes read operations across all available Read Replicas.

Typically, Amazon Aurora Replicas exhibit less than 100 ms of replication lag, enabling applications that can tolerate this latency to leverage both the cluster and reader endpoints for a horizontally scaled database solution.

However, managing two separate database endpoints—one for reads and another for writes—introduces unnecessary complexity into applications. In this post, I will demonstrate how to utilize pgpool to create a single PostgreSQL-compatible Aurora endpoint that seamlessly routes write traffic to the cluster endpoint and read traffic to the reader endpoint.

Architecture Overview

Pgpool is an open-source middleware that operates between a PostgreSQL database and its clients. In this example, we deploy it using a configuration that includes an Amazon Aurora cluster, which consists of one primary instance and two Aurora Read Replicas distributed across two Availability Zones and private subnets. The cluster is safeguarded by a security group allowing ingress solely from the pgpool instances.

Pgpool is deployed within an Auto Scaling group, which maintains one active instance for failover purposes. This instance is also contained within private subnets secured by a firewall that permits access only from specified Classless Inter-Domain Routing (CIDR) blocks. Access subnets will host a Network Load Balancer that ensures a consistent endpoint for pgpool, meaning your database endpoint remains unchanged even if the pgpool instance fails and a new one is provisioned.

Deployment via AWS CloudFormation

AWS CloudFormation templates utilized in this example can be found in this GitHub repository. The deployment involves several nested AWS CloudFormation templates that establish the virtual private cloud (VPC) infrastructure, security groups, the Aurora cluster, and the pgpool middleware. Nested stacks allow you to compartmentalize a large stack into multiple reusable components. If you’re unfamiliar with AWS CloudFormation, I recommend checking the AWS CloudFormation documentation.

For complete deployment instructions, see the README file on GitHub. Below are some key highlights.

Creating the Amazon Aurora Cluster

The following AWS CloudFormation snippets illustrate the process of creating a three-node Amazon Aurora cluster after the VPC infrastructure and security groups are set up. The cluster will designate one node as primary and the other two as Read Replicas.

DBAuroraCluster:
  Type: "AWS::RDS::DBCluster"
  Properties:
    DatabaseName: !Ref DatabaseName
    Engine: aurora-postgresql
    MasterUsername: !Ref DatabaseUser
    MasterUserPassword: !Ref DatabasePassword
    VpcSecurityGroupIds: 
      - !Ref DBFirewall

        Value: !Ref ProjectTag
DBAuroraOne:
  Type : "AWS::RDS::DBInstance"
  Properties:
    DBClusterIdentifier: !Ref DBAuroraCluster
    Engine: aurora-postgresql
    DBInstanceClass: !Ref DbInstanceSize

        Value: !Ref ProjectTag
DBAuroraTwo:
  Type : "AWS::RDS::DBInstance"    
  Properties:
    DBClusterIdentifier: !Ref DBAuroraCluster
    Engine: aurora-postgresql
    DBInstanceClass: !Ref DbInstanceSize

        Value: !Ref ProjectTag
DBAuroraThree:
  Type : "AWS::RDS::DBInstance"    
  Properties:
    DBClusterIdentifier: !Ref DBAuroraCluster
    Engine: aurora-postgresql
    DBInstanceClass: !Ref DbInstanceSize     

        Value: !Ref ProjectTag

Deploying Pgpool

For guidance on deploying pgpool on AWS, refer to the blog post on using pgpool and Amazon ElastiCache for query caching with Amazon Redshift. The pgpool documentation also features a section specifically addressing Amazon Aurora.

In the AWS CloudFormation template for pgpool, you’ll establish an Elastic Load Balancer (ELB) and an Auto Scaling group. The launch configuration for this group will deploy and configure pgpool utilizing the AWS CloudFormation cfn-init tool.

To start, install a few packages and unpack the pgpool installation file:

yum groupinstall -y "Development Tools" && yum install -y postgresql-devel
wget www.pgpool.net/download.php?f=pgpool-II-3.7.2.tar.gz -O /tmp/pgpool-II-3.7.2.tar.gz
tar zxf /tmp/pgpool-II-3.7.2.tar.gz

Next, compile pgpool and set up the log and PID directories:

cd /opt/pgpool-II-3.7.2
./configure && make && make install
mkdir -p /var/run/pgpool && mkdir -p /var/log/pgpool && chmod -R 777 /var/run/pgpool && chmod -R 777 /var/log/pgpool

Since Amazon Aurora employs MD5 authentication, you must register your master database user in a local authentication file:

/usr/local/bin/pg_md5 -m -u ${DatabaseUser} ${DatabasePassword}

Additionally, configure MD5 authentication in /usr/local/etc/pool_hba.conf:

host    all         all         0.0.0.0/0               md5

Finally, register pgpool as a service, initiate it, and use chkconfig to ensure it starts automatically.

Configuring Pgpool

Most pgpool configuration takes place in /usr/local/etc/pgpool.conf. Here are some essential settings:

For additional insights on this topic, check out this resource which provides a thorough exploration. It’s also vital to remember the importance of empathy in the workplace, as highlighted by SHRM, an authority in this field. For those interested in career opportunities, visit Amazon Jobs, an excellent resource for aspiring professionals.

Tags: 9097372855, chanci turner, chanci, amazon, VGT2