Setting Up Two PgBouncer Instances for Database Connection Pooling on CentOS 7.0 with Different Listening Ports

In this article, we will cover the process of setting up two PgBouncer instances on a single VM running CentOS 7.0, with each instance listening on a different TCP port. PgBouncer is a popular, lightweight, and highly efficient connection pooler for PostgreSQL databases. It reduces the overhead of establishing new connections and helps manage a large number of connections efficiently.

Prerequisites

Before we begin, ensure that you have the following prerequisites in place:

1. A CentOS 7.0 system with root or sudo access.
2. PostgreSQL installed and configured.

Subtitle 1.1: Installing PgBouncer

To install PgBouncer, use the following commands:

sudo yum install -y postgresql-contrib
sudo yum install -y pgbouncer

Subtitle 1.2: Configuring PgBouncer

Create a new configuration file for the second PgBouncer instance:

sudo cp /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer_6433.ini

Edit the configuration files for both instances:

sudo nano /etc/pgbouncer/pgbouncer.ini
sudo nano /etc/pgbouncer/pgbouncer_6433.ini

Update the following parameters in both configuration files:

• listen_addr: Set the IP address and port for each instance (e.g., 127.0.0.1:6432 and 127.0.0.1:6433).
• auth_file: Specify the path to the authentication file (e.g., /etc/pgbouncer/userlist.txt).
• auth_type: Set to md5 for MD5-based authentication.
• database: Define the databases to be managed by PgBouncer.

Subtitle 1.3: Creating the Authentication File

Create the authentication file:

sudo nano /etc/pgbouncer/userlist.txt

Add the following content:

"username" "password"

Replace username and password with the actual credentials for your PostgreSQL databases.

Subtitle 1.4: Starting and Enabling PgBouncer

Start and enable the PgBouncer service for both instances:

sudo systemctl start pgbouncer
sudo systemctl start pgbouncer_6433
sudo systemctl enable pgbouncer
sudo systemctl enable pgbouncer_6433

Subtitle 1.5: Testing the PgBouncer Instances

Test the PgBouncer instances using the following commands:

psql -h 127.0.0.1 -p 6432 -U username database_name
psql -h 127.0.0.1 -p 6433 -U username database_name

Summary

In this article, we have demonstrated how to set up two PgBouncer instances on a single CentOS 7.0 VM, with each instance listening on a different TCP port. This setup allows for efficient management and connection pooling for PostgreSQL databases.

References

• PgBouncer Documentation
• PostgreSQL Documentation

• Type: Online resources

--endarticle--