# Harisfazillah Jamel - 11 Sept 2024 # Need to create systemd template for pgbouncer@.service and pgbouncer@.socket # By using so_reuseport in pgbouncer setting # and copy of /etc/pgbouncer/pgbouncer.ini to /etc/pgbouncer/pgbouncer.ini # Do this after all the tune and test run with single pgbouncer done. # https://www.2ndquadrant.com/en/blog/run…

configure_pgbouncer.sh

#!/bin/bash
# Harisfazillah Jamel - 11 Sept 2024
# Need to create systemd template for pgbouncer@.service and pgbouncer@.socket
# By using so_reuseport in pgbouncer setting
# and copy of /etc/pgbouncer/pgbouncer.ini to /etc/pgbouncer/pgbouncer.ini
# Do this after all the tune and test run with single pgbouncer done.
# 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
# Please check for running pgbouncer if you find this script in your server
# ps -ef | grep pgbouncer
# use multitail to tail many log fails. multitail /var/log/postgresql/pgbouncer-50001.log /var/log/postgresql/pgbouncer-50002.log
# More detail in this LinuxMalaysia GIST comments. 

# Check if an argument is provided
if [ -z "$1" ]; then
  echo "Error: Please provide a port number as an argument."
  echo "Example usage: ./configure_pgbouncer.sh 12345"
  exit 1
fi

# Get the port number from the argument
port_number="$1"

# Enable systemd services (replace template with the port number)
systemctl enable pgbouncer@"$port_number".socket pgbouncer@"$port_number".service

# Copy configuration file and set ownership
cp /etc/pgbouncer/pgbouncer-template.ini /etc/pgbouncer/pgbouncer-"$port_number".ini
chown postgres /etc/pgbouncer/pgbouncer-"$port_number".ini

# Update configuration file (replace template references with port number)
sed -i "s/pgbouncer-template.log/pgbouncer-$port_number.log/g" /etc/pgbouncer/pgbouncer-"$port_number".ini
sed -i "s/pgbouncer-template.pid/pgbouncer-$port_number.pid/g" /etc/pgbouncer/pgbouncer-"$port_number".ini

# Start systemd services

systemctl start pgbouncer@"$port_number".socket pgbouncer@"$port_number".service

# Check if PID and log files exist

if [ -f "/var/run/postgresql/pgbouncer-$port_number.pid" ]; then
  echo "PID file /var/run/postgresql/pgbouncer-$port_number.pid exists."
else
  echo "PID file /var/run/postgresql/pgbouncer-$port_number.pid does not exist."
fi

if [ -f "/var/log/postgresql/pgbouncer-$port_number.log" ]; then
  echo "Log file /var/log/postgresql/pgbouncer-$port_number.log exists."
    tail /var/log/postgresql/pgbouncer-$port_number.log
else
  echo "Log file /var/log/postgresql/pgbouncer-$port_number.log does not exist."
fi

# Check if pgbouncer process is running
if ps -ef | grep "/usr/sbin/pgbouncer /etc/pgbouncer/pgbouncer-$port_number.ini" | grep -v grep > /dev/null; then
  echo "pgbouncer process is running."
else
  echo "pgbouncer process is not running."
fi

systemctl status pgbouncer@"$port_number".socket pgbouncer@"$port_number".service

exit

https://www.pgbouncer.org/config.html

Refer to so_reuseport

Each PgBouncer instance on the same host needs different settings for at least unix_socket_dir and pidfile, as well as logfile if that is used. Also note that if you make use of this option, you can no longer connect to a specific PgBouncer instance via TCP/IP, which might have implications for monitoring and metrics collection.

PgBouncer: A Guide to Efficiently Managing Multiple Instances

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.

This guide assumes you have PgBouncer installed on Ubuntu 22.04 LTS. 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 apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Detailed PgBouncer Installation (Optional)

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.

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.

[databases]

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

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/pgbouncer@.service by copying /lib/systemd/system/pgbouncer.service

Creating a Systemd Unit Template

Begin by creating a systemd unit template file named pgbouncer@.service 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/pgbouncer@.service):

[Unit]
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: A Better Solution (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/pgbouncer@.service, modifying it as follows

Systemd Unit Template (/etc/systemd/system/pgbouncer@.service)

[Unit]
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

Copy the Original Configuration: 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

logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

to

logfile = /var/log/pgbouncer/pgbouncer-template.log
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

systemctl status pgbouncer.socket pgbouncer.service

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.

cp /etc/pgbouncer/pgbouncer-template.ini /etc/pgbouncer/pgbouncer-50001.ini
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 enable pgbouncer@50001.socket pgbouncer@50001.service

systemctl start pgbouncer@50001.socket pgbouncer@50001.service

or

systemctl enable pgbouncer@50001.socket pgbouncer@50001.service --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.

Verify the existence of the PID file.

ls -lisah /var/run/postgresql/pgbouncer-50001.pid

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

ls -lisah /var/log/postgresql/pgbouncer-50001.log

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.

ps -ef | grep "/usr/sbin/pgbouncer /etc/pgbouncer/pgbouncer-50001.ini" | grep -v grep

Use systemctl command to obtain the status of both the socket and service for the new instance.

systemctl status pgbouncer@50001.socket pgbouncer@50001.service

To view all running PgBouncer instances

ps -ef | grep pgbouncer | grep -v grep

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.

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/pgbouncer@.socket) and service (/etc/systemd/system/pgbouncer@.service) 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.

By following these steps, you 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, read all my comments.

https://gist.github.com/linuxmalaysia/0c6287c25957bc1b36af750d4abeb838

https://www.linuxmalaysia.com/2024/09/pgbouncer-guide-to-efficiently-managing.html

Blog post with the help of Google Gemini. Run and verify by human.

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