-
-
Save linuxmalaysia/0c6287c25957bc1b36af750d4abeb838 to your computer and use it in GitHub Desktop.
| #!/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 |
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/
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