Setup pgbouncer-rr for PostgreSQL on AWS EC2

This Blog has been moved from Medium to blogs.tensult.com. All the latest content will be available there. Subscribe to our newsletter to stay updated.

Have you ever wanted to split your database load across multiple servers or clusters without impacting the configuration or code of your client applications? Or perhaps you have wished for a way to intercept and modify application queries so that you can make them use optimized tables add security filters, or hide changes you have made in the schema?

The Pgbouncer rr project is based on a pgbouncer, an open-source, PostgreSQL connection pooler. It just adds two new features:

Routing: Intelligently send queries to different database servers from one client connection; use it to partition or load balance across multiple servers or clusters.

Rewrite: Intercept and programmatically change client queries before they are sent to the server; use it to optimize or otherwise alter queries without modifying your application.

Pgbouncer-rr works the same way as pgbouncer. Any target application can be connected to pgbouncer-rr as if it were an Amazon Redshift or PostgreSQL server, and pgbouncer-rr creates a connection to the actual server or reuses an existing connection.

Setting up the EC2 Instance

Here I just created a VPC with 3 subnets: 1 public and 2 private subnets. I attached EC2 instance to the public and the RDS attached to the private subnet. The AMI I’m using here is CentOS 7. In the security group, I opened up the following port numbers 5432 for PgSQL and 5439 for pgbouncer.

Getting Started with Configuration

Install

Download pgbouncer-rr by running the following commands (Amazon Linux/RHEL/CentOS):

# install required packages
sudo yum install libevent-devel openssl-devel python-devel libtool git patch make -y

# download the latest pgbouncer distribution
git clone https://github.com/pgbouncer/pgbouncer.git

# download pgbouncer-rr extensions
git clone https://github.com/awslabs/pgbouncer-rr-patch.git

# merge pgbouncer-rr extensions into pgbouncer code
cd pgbouncer-rr-patch
./install-pgbouncer-rr-patch.sh ../pgbouncer

# build and install

cd ../pgbouncer
git submodule init
git submodule update
./autogen.sh
./configure …
make
sudo make install

Configure

Create a configuration file, using ./pgbouncer-example.ini as a starting point, adding your own database connections and python routing rules / rewrite query functions.

When checking the configuration file you can see the following parameters: listen_port

It’s a command to set the listen port, listen_aadr- its command to set the listen to that particular address.

authtype=

auth_type
How to authenticate users.

pam
PAM is used to authenticate users. This method is not compatible with databases using the auth_user option. Service name reported to PAM is “pgbouncer”. Also, pam is still not supported in the HBA configuration file.

HBA
Actual auth type is loaded from auth_hba_file. This allows different authentication methods from different access paths. Example: connection over Unix socket use peer auth method, connection over TCP must use TLS. Supported from version 1.7 onwards.

cert
A client must connect over TLS connection with valid client cert. Username is then taken from CommonName field from a certificate.

md5
Use the MD5-based password check. auth_file may contain both MD5-encrypted or plain-text passwords. This is the default authentication method.

plain
Clear-text password is sent over the wire. Deprecated.

trust
No authentication is done. Username must still exist in auth_file.

any
Like the trust method, but the username given is ignored. Requires that all databases are configured to log in as a specific user. Additionally, the console database allows any user to log in as admin.

pool_mode
Specifies when a server connection can be reused by other clients.

Session
The server is released back to pool after client disconnects. Default.

Transaction
The server is released back to pool after transaction finishes.

Statement
The server is released back to pool after query finishes. Long transactions spanning multiple statements are disallowed in this mode.

pgbouncer rr currently works as a daemon in the Linux Operating System so we are required to manually start the daemon using the following command pgbouncer -d “configuration filename”. Like a normal process, we cannot restart the pgbouncer daemon using the command systemctl restart “service name”. As of now, to review the changes we need to manually kill the pgbouncer process and need to restart the pgbouncer daemon.

Let’s discuss the EC2 instance setup:

Launch Pgbouncer rr

Run pgbouncer-rr as a daemon using the command line pgbouncer <config_file> -d. We need to start pgbouncer as a daemon in Linux machine. To do that we need to use the following command -d to start as a daemon,

pgbouncer -d config.ini -v. -d 

to start as a daemon and -v is for verbose. Check whether pg bouncer is working.

Note: When you get some errors on starting pgbouncer you need to kill the pgbouncer daemon from PID. You can use the command

sudo lsof -i : port number 

to identify the pid. Use the command sudo kill -9 PID.

Connect to RDS

To connect to the RDS use the following command

psql -h *Host address -U pguser -d pgbouncerDB1 -p 5439

As you can see in the above screenshot, you are now connected to the RDS and start working.