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

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

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

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

Установка:

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

Для Linux ставится стандартно
```
apt install pgbouncer
```
.

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

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

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

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

Создайте или отредактируйте файл 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 как сервис:

Windows в командной строке под администратором
```
pgbouncer --regservice pgbouncer.ini
```
После этого запускаем сервис pgBouncer.

Linux
```
pgbouncer -d pgbouncer.ini
```

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

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

Windows

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

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:

Windows в командной строке под администратором
```
net stop "O&K Print Watch Service" & sc start "O&K Print Watch Service"
```
Linux
```
systemctl restart printwatch
```

Использование PostgreSQL как сервер базы данных в O&K Print Watch.
Настройка соединения с БД PostgreSQL.