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?
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
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
# build and install
git submodule init
git submodule update
sudo make install
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.
How to authenticate users.
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.
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.
A client must connect over TLS connection with valid client cert. Username is then taken from CommonName field from a certificate.
Use the MD5-based password check. auth_file may contain both MD5-encrypted or plain-text passwords. This is the default authentication method.
Clear-text password is sent over the wire. Deprecated.
No authentication is done. Username must still exist in auth_file.
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.
Specifies when a server connection can be reused by other clients.
The server is released back to pool after client disconnects. Default.
The server is released back to pool after transaction finishes.
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.