Managing database connections effectively is crucial for maintaining the performance and reliability of your PostgreSQL instances. PostgreSQL’s performance can degrade with an excessive number of concurrent connections, making connection pooling solutions like PgBouncer essential for high-traffic environments. In this article, we’ll walk through the steps to set up PgBouncer, a lightweight connection pooler for PostgreSQL, and configure it to optimize your database connections.
Why Use PgBouncer?
PostgreSQL can handle a limited number of concurrent connections efficiently. It is recommended to set the maximum connections lower than 10 per CPU core, with a maximum of 20 per CPU core as the limit. If your application needs to handle more user sessions, using a connection pooler like PgBouncer becomes necessary. PgBouncer reduces the overhead of establishing and closing connections by reusing a pool of established connections.
Prerequisites
Before we start, ensure you have the following dependencies installed on your system:
- PostgreSQL
- Python3 with psycopg2
You can install these dependencies using the following commands:
dnf install python3-psycopg2-2.9.9-1PGDG.rhel9.x86_64.rpm # Dependency dnf install pgbouncer-1.22.1-42PGDG.rhel9.x86_64.rpm
Setting Up PgBouncer
1. Configure User Authentication
First, we’ll create a userlist file for PgBouncer to manage user authentication.
psql -Atq -h 10.*.**.** -p 5432 -U postgres -d postgres -c "SELECT concat('"', usename, '" "', passwd, '"') FROM pg_shadow" >> /etc/pgbouncer/userlist.txt
2. Edit the PgBouncer Configuration
Next, we’ll configure the pgbouncer.ini file. Open the file in your preferred text editor:
vi /etc/pgbouncer/pgbouncer.ini
Add the following configuration:
[databases] * = port=5432 [pgbouncer] pool_mode = session listen_addr = 10.5.**.** default_pool_size = 100 reserve_pool_size = 20 max_client_conn = 500 admin_users = postgres ignore_startup_parameters = extra_float_digits logfile = /var/log/pgbouncer/pgbouncer.log pidfile = /var/run/pgbouncer/pgbouncer.pid listen_port = 6432 auth_file = /etc/pgbouncer/userlist.txt client_tls_sslmode = require client_tls_ca_file = /etc/pgbouncer/root.crt client_tls_key_file = /etc/pgbouncer/pgbouncer.key client_tls_cert_file = /etc/pgbouncer/pgbouncer.crt
3. Restart and Enable PgBouncer
After editing the configuration, reload and restart the PgBouncer service to apply the changes:
systemctl restart pgbouncer systemctl enable pgbouncer
You can check the status of the PgBouncer service to ensure it is running correctly:
systemctl status pgbouncer
4. Connect to PgBouncer
Now, you can perform your database operations through the PgBouncer port:
psql -p 6432
To connect to the PgBouncer manager interface:
psql -p 6432 pgbouncer
5. Monitoring and Managing PgBouncer
To monitor pool management:
show pools; x -- Change the psql output format for better readability:
To review statistics:
show stats;
For more commands, you can refer to:
psql -Atq -h 10.*.**.** -p 5432 -U postgres -d postgres -c "SELECT concat('"', usename, '" "', passwd, '"') FROM pg_shadow" >> /etc/pgbouncer/userlist.txt
6. Update System Limits
Finally, update the systemd service file to increase the file descriptor limits. To ensure that PgBouncer can handle a large number of concurrent connections, you need to increase the file descriptor limits. File descriptors are integral resources used by the operating system to manage open files and sockets. Each connection to the database through PgBouncer consumes a file descriptor. If the limit is too low, PgBouncer will not be able to accept additional connections once the limit is reached.
psql -Atq -h 10.*.**.** -p 5432 -U postgres -d postgres -c "SELECT concat('"', usename, '" "', passwd, '"') FROM pg_shadow" >> /etc/pgbouncer/userlist.txt
Add the following lines to the service section:
LimitNOFILE=64000: This sets the hard limit for the number of open file descriptors to 64,000. The hard limit is the maximum number of file descriptors that can be opened by the service.LimitNOFILESoft=64000: This sets the soft limit for the number of open file descriptors to 64,000. The soft limit is the value that the service starts with and can be increased up to the hard limit.
