# 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

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