PgBouncer: A Guide to Efficiently Managing Multiple Instances
PgBouncer: A Guide to Efficiently Managing Multiple Instances
To effectively manage multiple PgBouncer instances, follow these steps
Install Prerequisites
Ensure PostgreSQL, PgBouncer, and systemd are installed and configured correctly on your Ubuntu 22.04 or later server.
Create Systemd Templates
Create template files for both the socket (/etc/systemd/system/[email protected]) and service (/etc/systemd/system/[email protected]) to define basic settings for each instance.
Configure PgBouncer
Create individual configuration files (e.g., pgbouncer-50001.ini, pgbouncer-50002.ini) based on the templates, specifying unique port numbers, log files, and PID files.
Enable and Start Instances
Use systemd commands to enable and start the newly created PgBouncer instances.
Verify Instances
Check the status of the running instances, including PID files, log files, and process information.
We can effectively optimize your PostgreSQL database's performance and scalability through the use of multiple PgBouncer instances. Detail configuration can be refer to LinuxMalaysia GIST, also read all my comments.
https://gist.github.com/linuxmalaysia/0c6287c25957bc1b36af750d4abeb838
PgBouncer is a connection pooler for PostgreSQL, designed to minimize the performance overhead of establishing new database connections. Applications can interact with PgBouncer as if it were a PostgreSQL server, while PgBouncer manages underlying connections to the actual database.
PgBouncer traditionally operates as a single process, which can limit its ability to utilize multiple CPUs on a host. To improve scalability and resource utilization, running multiple PgBouncer instances is often recommended. This guide will demonstrate how to set up and manage these instances using systemd, ensuring efficient operation and easy administration.
Using SO_REUSEPORT for Multi-Instance Deployment
PgBouncer version 1.12 introduced support for the SO_REUSEPORT socket option, allowing multiple instances to listen on the same port and share connections. This approach offers several advantages
Improved Scalability
Multiple instances can handle more concurrent connections and distribute the load across available CPUs.
Efficient Resource Utilization
By sharing connections, instances can reduce the overhead of connection establishment and management.
Simplified Configuration
All instances can listen on the same port, simplifying management and deployment.
Configuration Considerations
When running multiple instances on the same host, ensure each instance has unique settings for at least unix_socket_dir, pidfile, and logfile (if used). Note that using SO_REUSEPORT prevents direct TCP/IP connections to specific instances, which may impact monitoring and metrics collection.
Key Steps for Setting Up Multiple PgBouncer Instances with Systemd
Configure PgBouncer
Create individual configuration files for each instance, specifying unique connection pool settings, authentication credentials, and other parameters.
Create Systemd Units
Write systemd unit files for each instance, defining the service name, execution command, and other relevant settings.
Start and Manage Instances
Utilize systemd commands to start, stop, restart, or reload the PgBouncer instances as required.
PgBouncer Installation
This guide assumes you have PgBouncer installed on Ubuntu 22.04 LTS, 24.04 LTS or later. To ensure you're using the latest version (currently 1.23.1 as of September 2024), we recommend installing PostgreSQL Global Development Group's (PGDG) repository. This repository provides access to the most recent PgBouncer packages. You can find installation instructions for PGDG on the PostgreSQL
https://wiki.postgresql.org/wiki/Apt#PostgreSQL_packages_for_Debian_and_Ubuntu
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
For detailed instructions on installing PgBouncer itself, refer to the Scaleway documentation: https://www.scaleway.com/en/docs/tutorials/install-pgbouncer/. This guide provides a step-by-step process for setting up PgBouncer on your Ubuntu server.
PgBouncer Configuration
Ensure PgBouncer is configured correctly by editing /etc/pgbouncer/pgbouncer.ini. Adjust settings as needed, such as
localhost: Replace with the hostname or IP address of your PostgreSQL database.
pool_mode: Choose session or transaction based on your application requirements.
max_client_conn: Set the maximum number of concurrent client connections.
Using SCRAM-SHA-256 Authentication
Ensure your PostgreSQL database uses SCRAM-SHA-256 password encryption.
Sample Configuration
This is a sample pgbouncer.ini configuration to get you started. Remember to modify the settings as needed for your specific environment.
postgres = host=localhost port=5432
* = host=localhost port=5432
[pgbouncer]
listen_addr = localhost
listen_port = 6432
unix_socket_dir = /tmp
so_reuseport = 1
pool_mode = session
max_client_conn = 5000
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
SystemD Configuration
You can simply copy the existing PgBouncer systemd file (/lib/systemd/system/pgbouncer.service) and configuration file (/etc/pgbouncer/pgbouncer.ini) to run separate services.
if you want to run multiple instances, you can obviously just make copies of pgbouncer systemd file /lib/systemd/system/pgbouncer.service and pgbouncer configuration file /etc/pgbouncer/pgbouncer.ini, run them as entirely separate services. But here we want to make use of the systemd template system. So create a file /etc/systemd/system/[email protected] by copying /lib/systemd/system/pgbouncer.service
Creating a Systemd Unit Template
Begin by creating a systemd unit template file named [email protected] in the /etc/systemd/system directory. Copy the content from the original service file (/lib/systemd/system/pgbouncer.service), making the following modifications, A placeholder %i has been added to Description, Requires and ExecStart. This will be replaced with our input during service creation.
Template Unit File (/etc/systemd/system/[email protected]):
Description=connection pooler for PostgreSQL (%i)
Documentation=man:pgbouncer(1)
Documentation=https://www.pgbouncer.org/
After=network.target
#Requires=pgbouncer.socket
Requires=pgbouncer@%i.socket
[Service]
Type=notify
User=postgres
###ExecStart=/usr/sbin/pgbouncer /etc/pgbouncer/pgbouncer.ini
ExecStart=/usr/sbin/pgbouncer /etc/pgbouncer/pgbouncer-%i.ini
ExecReload=/bin/kill -HUP $MAINPID
KillSignal=SIGINT
#LimitNOFILE=1024
[Install]
WantedBy=multi-user.target
It's essential to run systemctl daemon-reload after making changes to systemd unit files or configuration files. This command ensures that systemd reloads its configuration, taking the new changes into account.
Limitations of Shared Unix Sockets
While the so_reuseport setting allows multiple PgBouncer instances to share TCP/IP listening sockets, it doesn't extend to Unix-domain sockets. This creates a challenge
Disable Unix Sockets: This removes some of the convenience of this approach.
Separate Configuration Files: Each instance needs a unique configuration with a different Unix socket directory, which can become cumbersome.
Socket Activation (PgBouncer 1.14+)
For PgBouncer versions 1.14 and later, we recommend using socket activation provided by systemd. This offers a more elegant solution.
Copy Systemd Unit Template, Copy /lib/systemd/system/pgbouncer.socket to /etc/systemd/system/[email protected], modifying it as follows
Systemd Unit Template (/etc/systemd/system/[email protected])
Description=Sockets for PgBouncer (%i)
[Socket]
ListenStream=6432
ListenStream=%i # Instance-specific port
ListenStream=/tmp/.s.PGSQL.%i # Instance-specific Unix socket
ReusePort=true
# additional settings that might be useful
#Backlog=
#SocketUser=
#SocketGroup=
#SocketMode=
#KeepAlive=
#KeepAliveTimeSec=
#KeepAliveIntervalSec=
#KeepAliveProbes=
#DeferAcceptSec=
#ReusePort=
[Install]
WantedBy=sockets.target
Create a template for pgbouncer.ini
Copy the Original Configuration to be a pgbouncer.ini template. Begin by copying the existing PgBouncer configuration file (/etc/pgbouncer/pgbouncer.ini) to a new location with a descriptive name, such as /etc/pgbouncer/pgbouncer-template.ini and update Log and PID File Paths
Edit the newly copied template file (pgbouncer-template.ini) and modify the following settings
pidfile = /var/run/pgbouncer/pgbouncer.pid
pidfile = /var/run/pgbouncer/pgbouncer-template.pid
These changes ensure each PgBouncer instance created from this template will have unique log and PID files. This prevents conflicts that could arise if multiple instances shared the same log and PID file paths.
Starting a New PgBouncer Instance
Ensure the primary PgBouncer instance is running. Verify its status using
Copy the template configuration file (/etc/pgbouncer/pgbouncer-template.ini) to a new location with a descriptive name, such as /etc/pgbouncer/pgbouncer-50001.ini. This name reflects the chosen administration port number (50001 in this example) and set ownership of the new file to the postgres user.
chown postgres /etc/pgbouncer/pgbouncer-50001.ini
Modify the copied configuration file (pgbouncer-50001.ini) to replace references to the template names with the specific port number (50001). You can use sed or any preferred text editor for this purpose. Here's an example using sed
sed -i "s/pgbouncer-template.log/pgbouncer-50001.log/g" /etc/pgbouncer/pgbouncer-50001.ini
sed -i "s/pgbouncer-template.pid/pgbouncer-50001.pid/g" /etc/pgbouncer/pgbouncer-50001.ini
Enable the systemd socket and service for the new PgBouncer instance, specifying the chosen port number
systemctl start [email protected] [email protected]
(systemctl enable [email protected] [email protected] --now)
The chosen port number (50001 in this example) should be unique and not used by any other application. Run systemctl daemon-reload after making changes to systemd files for them to take effect.
Verifying the New PgBouncer Instance
Verify the existence of the PID file.
Ensure its ownership and permissions are correct: owner should be postgres, and permissions should allow read access for the owner only (e.
g., -rw-------).
Check if the log file exists at /var/log/postgresql
Confirm its ownership and permissions are set to postgres with read access only for the owner.
Verify if the PgBouncer process is running with the specific configuration file. This command filters process information and searches for the PgBouncer process with the specified arguments.
Use systemctl command to obtain the status of both the socket and service for the new instance.
To view all running PgBouncer instances
Creating Additional PgBouncer Instances
To create more PgBouncer instances, simply repeat the steps outlined in the previous section, using a different port number for each instance. Ensure that the chosen port number is unique and not in use by any other application.
Resource Considerations
When adding PgBouncer instances, be mindful of your operating system's virtual CPU (vCPU) count. Avoid exceeding this limit to prevent performance degradation. You can use commands like lcpu or ntop to determine the number of vCPUs available on your system.
This guide base on this articles :-
https://www.2ndquadrant.com/en/blog/running-multiple-pgbouncer-instances-with-systemd/
https://www.enterprisedb.com/postgres-tutorials/pgbouncer-setup-extended-architecture-use-cases-and-leveraging-soreuseport
### Blog post with the help of Google Gemini. Run and verify by human.
Harisfazillah Jamel
LinuxMalaysia
15 September 2024