Использование PostgreSQL и pgBouncer с большим количеством клиентов

Обычно PostgreSQL может справиться с сотнями или тысячами одновременных подключений при правильной настройке. Однако оптимальное количество пользователей для вашего конкретного случая может потребовать тестирования и настройки.

В случае, если ваша база данных достигает критической точки, есть несколько стратегий, которые можно применить для увеличения производительности PostgreSQL, такие как оптимизация запросов, настройка ресурсов сервера, горизонтальное масштабирование и использование кэширования.

pgBouncer - это программа, которая помогает оптимизировать производительность PostgreSQL за счет уменьшения количества подключений к серверу. Она работает как посредник между клиентами и серверами, обслуживая запросы на подключение, выполнение запросов и получение результатов. Это позволяет уменьшить количество соединений, которые непосредственно обращаются к PostgreSQL, что может улучшить производительность при большом количестве клиентов.

Установка:

1. Для Windows можно скачать готовые бинарные модули pgBouncer - https://github.com/pgbouncer/pgbouncer/releases


2. Для Linux ставится стандартно

   apt install pgbouncer

   .

Настройка userlist.txt:

1. Создайте или отредактируйте файл userlist.txt в директории, где установлен pgBouncer.


2. Добавьте строку в формате "username" "password", где указываете имя пользователя и пароль для PostgreSQL, например

   "prnwatch_user" "prnwatch_password"

Настройка pgbouncer.ini:

1. Создайте или отредактируйте файл pgbouncer.ini в директории, где установлен pgBouncer:

   [databases]
   prnwatch_template = host=127.0.0.1 port=5432 dbname=PRNWATCH auth_user=prnwatch_user
   
   [pgbouncer]
   listen_port = 6432
   listen_addr = *
   auth_type = md5
   auth_file = d:\PGBouncer\userlist.txt
   logfile = d:\PGBouncer\pgbouncer.log
   pidfile = d:\PGBouncer\pgbouncer.pid
   admin_users = prnwatch_user
   pool_mode = transaction
   default_pool_size = 100
   max_client_conn = 10000

Раздел [databases]:

• prnwatch_template - имя шаблона


• host - адрес хоста PostgreSQL


• port - порт PostgreSQL


• auth_user - имя пользователя из userlist.txt

Раздел [pgbouncer]:

• listen_port - порт на котором принимает подключения pgBouncer


• listen_addr - адрес на котором слушает pgBouncer. Для удаленного подключения клиентов необходимо использовать *


• auth_type - тип аутентифкации. md5 - можно использовать пароли в userlist.txt как в текстовом виде, так и md5. Например:

  "prnwatch_user" "md5145c741eaed0b9c05ae8444b74d987f0"

• auth_file - Полный путь к userlist.txt


• logfile - Полный путь, где будет храниться лог файл


• pidfile - Полный путь, где будет храниться pid файл


• admin_users - Имя пользователя из userlist.txt которому разрешен административный доступ к pgBouncer. Для этого можно создать отдельного пользователя.


• pool_mode - Модель пула соединений, необходимо использовать transaction


• max_client_conn - Максимальное число клиентских соединений.


• default_pool_size - Размер пула соединений. По умолчанию 20, можно увеличить в зависимости от числа клиентов и настроек PostgreSQL.

Регистрация pgBouncer как сервис:

1. Windows в командной строке под администратором

   pgbouncer --regservice pgbouncer.ini

   После этого запускаем сервис pgBouncer.


2. Linux

   pgbouncer -d pgbouncer.ini

На каждом клиенте O&K Print Watch указываем новое соединения БД через pgBouncer.

Меняем строку соединения с БД в O&K Print Watch:

1. Windows

   Provider=MSDASQL;Driver={PostgreSQL Unicode};Server=ИМЯ_ХОСТА_PGBOUNCER;Port=6432;Database=prnwatch_template;UID=prnwatch_user;PWD=prnwatch_password;C8=0

2. Linux

   host=ИМЯ_ХОСТА_PGBOUNCER port=6432 dbname=prnwatch_template user=prnwatch_user password=prnwatch_password binary_parameters=yes

При использовании соединения с БД через pgBouncer необходимо отключить Use serverside prepare. В Windows версии O&K Print Watch для этого используется параметр C8=0 в строке соединения, в Linux версии binary_parameters=yes.

Перезапускаем O&K Print Watch Service:

1. Windows в командной строке под администратором

   net stop "O&K Print Watch Service" & sc start "O&K Print Watch Service"

2. Linux

   systemctl restart printwatch