Встановлення та налаштування Pg

Що ми налаштовуємо і навіщо

Ми будемо встановлювати та налаштовувати PgBouncer – легковаговий проксі-сервер, який знаходиться між вашими клієнтськими застосунками та сервером PostgreSQL. Його основне завдання – пулінг (об'єднання) з'єднань з базою даних. У типовій архітектурі кожен клієнтський застосунок (наприклад, веб-сервер) відкриває власне з'єднання з PostgreSQL. При великій кількості клієнтів або частих підключеннях/відключеннях це призводить до значного навантаження на сервер бази даних, оскільки створення та закриття кожного з'єднання – ресурсоємна операція. PostgreSQL виділяє пам'ять та процесорний час на кожне активне з'єднання, що може швидко призвести до вичерпання ресурсів та уповільнення роботи.

У підсумку читач отримає оптимізовану та більш стабільну систему бази даних. PgBouncer підтримуватиме фіксовану кількість "гарячих" з'єднань з PostgreSQL, а клієнтські застосунки підключатимуться до PgBouncer. Це знижує накладні витрати на створення з'єднань, зменшує споживання пам'яті на сервері PostgreSQL та дозволяє базі даних обробляти більше запитів з меншими затримками. Ваш застосунок працюватиме швидше, а сервер PostgreSQL буде більш стійким до пікових навантажень.

Існують альтернативи, такі як хмарні керовані бази даних (наприклад, Amazon RDS, Google Cloud SQL, Azure Database for PostgreSQL), які часто включають вбудовані рішення для пулінгу з'єднань або пропонують масштабування ресурсів за вимогою. Однак, вибір self-hosted рішення на VPS має свої переваги. По-перше, це повний контроль над конфігурацією та оптимізацією, що є критичним для специфічних робочих навантажень. По-друге, це часто значно дешевше в довгостроковій перспективі, особливо при стабільному або передбачуваному навантаженні. Нарешті, self-hosted рішення дає гнучкість в інтеграції з іншими сервісами на вашому VPS і не прив'язує вас до конкретного хмарного провайдера, що важливо для мінімізації vendor lock-in.

Який VPS-конфіг потрібен для цього завдання

Вибір конфігурації VPS для PgBouncer та PostgreSQL залежить від очікуваного навантаження, кількості одночасних з'єднань та обсягу даних. PgBouncer сам по собі дуже легковаговий і споживає мінімальну кількість ресурсів, але він виступає проксі для PostgreSQL, який може бути досить вимогливим.

Мінімальні вимоги для невеликих та середніх проєктів (до 50-100 одночасних підключень до PgBouncer)

• CPU: 2 vCPU. PostgreSQL активно використовує процесор, особливо при складних запитах.
• RAM: 4 GB. PostgreSQL може споживати багато RAM для кешування даних та буферів. PgBouncer ж вимагає дуже мало пам'яті – десятки мегабайт.
• Диск: 80 GB NVMe SSD. Для бази даних вкрай важлива швидкість дискової підсистеми. NVMe забезпечує значно вищу продуктивність порівняно зі звичайними SSD або HDD. Обсяг залежить від розміру вашої бази даних та темпів зростання.
• Мережа: 100 Mbps або 1 Gbps. Для більшості завдань достатньо 100 Mbps, але для високонавантажених застосунків з великим обсягом передаваних даних між застосунком та базою даних краще 1 Gbps.

Рекомендований VPS-план для більшості завдань

Для більш серйозних проєктів, де очікується до 200-300 одночасних підключень через PgBouncer та активна робота з базою даних, розгляньте наступні характеристики:

• CPU: 4 vCPU
• RAM: 8-16 GB
• Диск: 160-320 GB NVMe SSD
• Мережа: 1 Gbps

Наприклад, можна взяти VPS із зазначеними характеристиками, який забезпечить достатню продуктивність та стабільність для більшості веб-застосунків та сервісів.

Коли потрібен dedicated, а не VPS

Dedicated сервер стає необхідним, коли:

• Потрібна максимальна продуктивність та ізоляція: Для дуже великих баз даних, високонавантажених OLTP-систем або аналітичних платформ, де кожна мілісекундна відповідь критична, і ви не хочете ділитися ресурсами з іншими користувачами.
• Високі вимоги до I/O: Коли дискова підсистема VPS не справляється з обсягом операцій введення-виведення, особливо при використанні RAID-масивів на dedicated сервері.
• Особливі вимоги до безпеки та комплаєнсу: Деякі регуляторні вимоги можуть передбачати використання фізично ізольованого обладнання.
• Масштабування: Якщо ресурсів найбільшого VPS плану вже не вистачає, і потрібне подальше горизонтальне або вертикальне масштабування, dedicated сервер надає більше можливостей.

Локація: на що впливає

Вибір локації VPS має прямий вплив на затримку (latency) між вашим застосунком та базою даних. Ідеально, якщо ваш VPS з PgBouncer та PostgreSQL знаходиться в тому ж дата-центрі або географічному регіоні, що й сервери ваших клієнтських застосунків. Це мінімізує пінг та забезпечує найкращу швидкість обміну даними.

• Близькість до користувачів: Якщо ваші основні користувачі знаходяться в певному регіоні, розміщення сервера там покращить їхній досвід.
• Близькість до застосунків: Якщо ваш веб-сервер знаходиться в одному регіоні, а база даних в іншому, це додасть затримку до кожного запиту до БД.
• Законодавство: Деякі країни мають суворі закони щодо зберігання даних, що може вимагати розміщення даних у певній юрисдикції.

Підготовка сервера

Після провізіонінгу нового VPS необхідно виконати низку базових налаштувань для забезпечення безпеки та зручності керування. Ми будемо використовувати Ubuntu Server 24.04 LTS, оскільки це актуальна та стабільна версія на 2026 рік.

1. Оновлення системи

Насамперед оновимо список пакетів та встановлені пакети до останніх версій.

sudo apt update && sudo apt upgrade -y # Оновлюємо список пакетів та встановлюємо оновлення

2. Створення нового користувача з правами sudo

Робота під користувачем root небезпечна. Створимо нового користувача та надамо йому права sudo.

sudo adduser username # Створюємо нового користувача (замініть 'username' на бажане ім'я)
sudo usermod -aG sudo username # Додаємо користувача до групи 'sudo'

Тепер вийдіть із сесії root та увійдіть під новим користувачем.

3. Налаштування автентифікації за SSH-ключами

Це безпечніший спосіб автентифікації, ніж пароль. Якщо у вас ще немає пари SSH-ключів, згенеруйте їх на локальній машині (ssh-keygen).

mkdir -p ~/.ssh # Створюємо директорію для ключів, якщо її немає
chmod 700 ~/.ssh # Встановлюємо правильні права для директорії
nano ~/.ssh/authorized_keys # Відкриваємо файл для додавання публічного ключа

Вставте ваш публічний SSH-ключ (починається з ssh-rsa AAAA... або ssh-ed25519 AAAA...) у цей файл та збережіть (Ctrl+X, Y, Enter). Потім встановіть правильні права:

chmod 600 ~/.ssh/authorized_keys # Встановлюємо правильні права для файлу ключів

Після перевірки входу за ключем, відключіть вхід за паролем для root та всіх користувачів у /etc/ssh/sshd_config:

sudo nano /etc/ssh/sshd_config # Відкриваємо конфігурацію SSH-сервера

Знайдіть та змініть (або додайте) наступні рядки:

PermitRootLogin no
PasswordAuthentication no
ChallengeResponseAuthentication no
UsePAM no

Збережіть зміни та перезапустіть SSH-сервіс:

sudo systemctl restart sshd # Перезапускаємо SSH-сервер для застосування змін

4. Встановлення та налаштування Fail2Ban

Fail2Ban сканує логи та тимчасово блокує IP-адреси, які показують ознаки шкідливих атак (наприклад, численні невдалі спроби входу за SSH).

sudo apt install fail2ban -y # Встановлюємо Fail2Ban (версія 0.12.0+ для Ubuntu 24.04)
sudo cp /etc/fail2ban/jail.conf /etc/fail2ban/jail.local # Копіюємо конфіг для локальних змін
sudo nano /etc/fail2ban/jail.local # Редагуємо локальний конфіг

У файлі jail.local переконайтеся, що секція [sshd] увімкнена (enabled = true) та налаштуйте параметри за бажанням (bantime, findtime, maxretry). Наприклад:

[sshd]
enabled = true
port = ssh
logpath = %(sshd_log)s
backend = systemd
bantime = 1h # Час блокування IP (1 година)
findtime = 10m # Час, за який рахуються спроби (10 хвилин)
maxretry = 3 # Максимальна кількість спроб до блокування

sudo systemctl enable fail2ban # Вмикаємо автозапуск Fail2Ban при старті системи
sudo systemctl start fail2ban # Запускаємо сервіс Fail2Ban

5. Налаштування брандмауера (UFW)

UFW (Uncomplicated Firewall) – це простий інтерфейс для налаштування правил iptables. За замовчуванням дозволимо лише SSH, HTTP(S) та порти для PostgreSQL/PgBouncer.

sudo apt install ufw -y # Встановлюємо UFW
sudo ufw allow ssh # Дозволяємо SSH (порт 22)
sudo ufw allow http # Дозволяємо HTTP (порт 80)
sudo ufw allow https # Дозволяємо HTTPS (порт 443)
sudo ufw allow 5432/tcp # Дозволяємо стандартний порт PostgreSQL
sudo ufw allow 6432/tcp # Дозволяємо стандартний порт PgBouncer (ми будемо використовувати його)
sudo ufw enable # Вмикаємо брандмауер (підтвердіть 'y')
sudo ufw status # Перевіряємо статус брандмауера

Встановлення ПЗ — покроково

Тепер, коли сервер підготовлено, приступимо до встановлення PostgreSQL та PgBouncer. Ми будемо використовувати актуальні версії, доступні в репозиторіях Ubuntu 24.04 LTS або з офіційних джерел.

1. Встановлення PostgreSQL

На 2026 рік в Ubuntu 24.04 LTS, швидше за все, буде доступна PostgreSQL 16.x або 17.x через стандартні репозиторії, але для максимальної актуальності та використання нових функцій ми будемо орієнтуватися на PostgreSQL 18.0+. Для цього додамо офіційний репозиторій PostgreSQL.

sudo apt install curl gnupg2 -y # Встановлюємо утиліти для роботи з ключами та репозиторіями
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg # Додаємо GPG ключ PostgreSQL
echo "deb http://apt.postgresql.org/pub/repos/apt/ noble-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list > /dev/null # Додаємо репозиторій PostgreSQL для Ubuntu 24.04 (Noble Numbat)
sudo apt update # Оновлюємо список пакетів після додавання репозиторію
sudo apt install postgresql-18 -y # Встановлюємо PostgreSQL 18 (актуальна версія на 2026 рік)

Перевіряємо статус PostgreSQL:

sudo systemctl status postgresql # Перевіряємо, що PostgreSQL запущено

За замовчуванням PostgreSQL створює користувача postgres і базу даних postgres. Для безпеки та зручності створимо окремого користувача та базу даних для вашого застосунку.

sudo -i -u postgres # Перемикаємося на користувача postgres
psql # Запускаємо консоль psql

У консолі psql:

CREATE USER myappuser WITH PASSWORD 'your_strong_password'; -- Створюємо користувача для застосунку
CREATE DATABASE myappdb OWNER myappuser; -- Створюємо базу даних для застосунку, власником якої є myappuser
GRANT ALL PRIVILEGES ON DATABASE myappdb TO myappuser; -- Надаємо всі права на базу даних
\q # Виходимо з psql
exit # Виходимо з користувача postgres

2. Встановлення PgBouncer

PgBouncer також доступний через стандартні репозиторії Ubuntu. На 2026 рік очікується версія 1.23.0 або вище.

sudo apt install pgbouncer -y # Встановлюємо PgBouncer

Перевіряємо, що PgBouncer встановлено та його версію:

pgbouncer --version # Перевіряємо версію PgBouncer (очікується 1.23.0+)

За замовчуванням PgBouncer не запущено або налаштовано на мінімальні параметри. Ми налаштуємо його в наступному розділі.

3. Налаштування PostgreSQL для роботи з PgBouncer

PgBouncer буде підключатися до PostgreSQL як звичайний клієнт. Необхідно переконатися, що PostgreSQL дозволяє підключення з локального хоста. Редагуємо файл pg_hba.conf.

sudo nano /etc/postgresql/18/main/pg_hba.conf # Відкриваємо файл конфігурації доступу до хоста

Додайте наступний рядок у кінець файлу, щоб дозволити PgBouncer підключатися з локального хоста:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

Також переконайтеся, що PostgreSQL слухає на локальному інтерфейсі. Редагуємо postgresql.conf:

sudo nano /etc/postgresql/18/main/postgresql.conf # Відкриваємо основний конфігураційний файл PostgreSQL

Знайдіть рядок listen_addresses і переконайтеся, що він включає localhost:

listen_addresses = 'localhost' # PostgreSQL буде слухати тільки на локальному інтерфейсі

Збережіть зміни та перезапустіть PostgreSQL:

sudo systemctl restart postgresql # Перезапускаємо PostgreSQL для застосування змін

Конфігурація

Тепер ми перейдемо до детального налаштування PgBouncer. Основний конфігураційний файл знаходиться за адресою /etc/pgbouncer/pgbouncer.ini.

1. Основний конфігураційний файл PgBouncer (pgbouncer.ini)

Відкриємо файл для редагування:

sudo nano /etc/pgbouncer/pgbouncer.ini # Відкриваємо конфігураційний файл PgBouncer

Ось приклад конфігурації з важливими коментарями:

[databases]
# Ім'я бази даних, як її бачитимуть клієнти PgBouncer
# Формат: <ім'я_БД_для_клієнта> = host=<хост_PostgreSQL> port=<порт_PostgreSQL> dbname=<ім'я_БД_PostgreSQL> user=<користувач_PgBouncer>
# У нашому випадку PgBouncer буде підключатися до локального PostgreSQL
myappdb = host=127.0.0.1 port=5432 dbname=myappdb user=pgbouncer_admin

[pgbouncer]
listen_addr = 0.0.0.0 # PgBouncer буде слухати на всіх мережевих інтерфейсах
listen_port = 6432 # Порт, на якому PgBouncer прийматиме з'єднання від клієнтів

auth_type = md5 # Тип автентифікації для клієнтів, що підключаються до PgBouncer (md5, plain, hba, cert, trust, any)
auth_file = /etc/pgbouncer/userlist.txt # Файл зі списком користувачів та їхніх паролів для PgBouncer

admin_users = pgbouncer_admin # Користувачі, які можуть підключатися до псевдо-БД "pgbouncer" для керування
stats_users = pgbouncer_admin # Користувачі, які можуть переглядати статистику

pool_mode = session # Режим пулінгу: session, transaction, statement
# session: з'єднання повертається в пул лише після відключення клієнта. Найпростіший, але найменш ефективний.
# transaction: з'єднання повертається в пул після кожної транзакції. Рекомендується для більшості веб-застосунків.
# statement: з'єднання повертається в пул після кожного запиту (statement). Найагресивніший, але може порушити логіку застосунків, що використовують сесійні змінні.

default_pool_size = 20 # Кількість з'єднань, які PgBouncer підтримуватиме з кожною БД за замовчуванням
min_pool_size = 5 # Мінімальна кількість з'єднань, які PgBouncer триматиме відкритими
max_client_conn = 1000 # Максимальна кількість клієнтів, які можуть підключитися до PgBouncer
max_db_connections = 0 # Максимальна кількість з'єднань PgBouncer до однієї БД (0 = no limit)
max_user_connections = 0 # Максимальна кількість з'єднань для одного користувача (0 = no limit)

# Таймаути
server_lifetime = 3600 # З'єднання з сервером PostgreSQL буде перестворюватися кожні 3600 секунд (1 година)
server_idle_timeout = 600 # Закривати неактивні з'єднання з PostgreSQL через 600 секунд
client_idle_timeout = 300 # Закривати неактивні з'єднання клієнтів PgBouncer через 300 секунд

# Логування
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

# Налаштування TLS/SSL для з'єднань між PgBouncer та PostgreSQL
# Якщо ваш PostgreSQL налаштований на використання TLS, PgBouncer повинен його використовувати
server_tls_sslmode = prefer # Режим SSL для PgBouncer -> PostgreSQL (disable, allow, prefer, require, verify-ca, verify-full)
server_tls_ca_file = /etc/ssl/certs/ca-certificates.crt # CA сертифікат для перевірки PostgreSQL сервера
server_tls_key_file = # Якщо PgBouncer потрібен клієнтський сертифікат для PostgreSQL
server_tls_cert_file = # Якщо PgBouncer потрібен клієнтський сертифікат для PostgreSQL

# Налаштування TLS/SSL для з'єднань між клієнтами та PgBouncer
# Клієнти можуть підключатися до PgBouncer по TLS
client_tls_sslmode = disable # Режим SSL для Client -> PgBouncer (disable, allow, prefer, require, verify-ca, verify-full)
client_tls_key_file = /etc/ssl/private/pgbouncer-key.pem # Приватний ключ для TLS
client_tls_cert_file = /etc/ssl/certs/pgbouncer-cert.pem # Сертифікат для TLS

Збережіть зміни.

2. Файл користувачів PgBouncer (userlist.txt)

PgBouncer вимагає окремого файлу зі списком користувачів та їхніх паролів для автентифікації клієнтів. Ми також створимо користувача pgbouncer_admin, який використовуватиметься PgBouncer для підключення до PostgreSQL та для адміністрування самого PgBouncer.

sudo nano /etc/pgbouncer/userlist.txt # Відкриваємо файл для редагування

Додайте наступний рядок, замінивши your_pgbouncer_admin_password на надійний пароль. Для пароля клієнта myappuser використовуйте той самий пароль, що й при створенні користувача в PostgreSQL.

"pgbouncer_admin" "your_pgbouncer_admin_password"
"myappuser" "your_strong_password"

Важливо: Паролі тут зберігаються у відкритому вигляді. Переконайтеся, що права на файл userlist.txt дуже суворі.

sudo chmod 600 /etc/pgbouncer/userlist.txt # Встановлюємо суворі права

Створимо користувача pgbouncer_admin у PostgreSQL, який PgBouncer використовуватиме для підключення до бази даних.

sudo -i -u postgres # Перемикаємося на користувача postgres
psql # Запускаємо консоль psql

У консолі psql:

CREATE USER pgbouncer_admin WITH PASSWORD 'your_pgbouncer_admin_password'; -- Створюємо користувача PgBouncer
GRANT CONNECT ON DATABASE myappdb TO pgbouncer_admin; -- Надаємо права на підключення до бази даних
\q # Виходимо з psql
exit # Виходимо з користувача postgres

3. Налаштування TLS/SSL для PgBouncer

Для безпечного з'єднання між клієнтом і PgBouncer, а також між PgBouncer і PostgreSQL, рекомендується використовувати TLS. Якщо ви використовуєте Certbot для веб-сервера, ви можете повторно використовувати його сертифікати або згенерувати самопідписані для внутрішніх потреб. Для публічного доступу використовуйте Certbot/Caddy.

Припустимо, ви хочете використовувати Certbot для отримання сертифікатів. Якщо у вас вже є домен, налаштований на вашому VPS, і ви використовуєте Caddy або Nginx, ви можете отримати сертифікат для вашого домену (наприклад, pg.yourdomain.com).

sudo apt install certbot -y # Встановлюємо Certbot
sudo certbot certonly --standalone -d pg.yourdomain.com # Отримуємо сертифікат для домену (замініть на свій)

Після отримання сертифікатів Certbot помістить їх у /etc/letsencrypt/live/pg.yourdomain.com/. Скопіюйте їх у місце, доступне PgBouncer, і встановіть правильні права:

sudo mkdir -p /etc/ssl/pgbouncer # Створюємо директорію для сертифікатів PgBouncer
sudo cp /etc/letsencrypt/live/pg.yourdomain.com/fullchain.pem /etc/ssl/pgbouncer/pgbouncer-cert.pem
sudo cp /etc/letsencrypt/live/pg.yourdomain.com/privkey.pem /etc/ssl/pgbouncer/pgbouncer-key.pem
sudo chmod 600 /etc/ssl/pgbouncer/pgbouncer-key.pem # Приватний ключ повинен бути доступний тільки PgBouncer
sudo chown pgbouncer:pgbouncer /etc/ssl/pgbouncer/* # Змінюємо власника файлів на користувача pgbouncer

Оновіть pgbouncer.ini, вказавши правильні шляхи до сертифікатів:

client_tls_sslmode = require # Вимагати TLS від клієнтів
client_tls_key_file = /etc/ssl/pgbouncer/pgbouncer-key.pem
client_tls_cert_file = /etc/ssl/pgbouncer/pgbouncer-cert.pem

Якщо ви хочете, щоб PgBouncer також використовував TLS для підключення до PostgreSQL (що рекомендується), переконайтеся, що ваш PostgreSQL налаштований на TLS, і вкажіть server_tls_sslmode = require, а також server_tls_ca_file. Якщо PostgreSQL знаходиться на тому ж сервері, ви можете використовувати системні CA-сертифікати. Для PostgreSQL 18+ TLS увімкнено за замовчуванням із самопідписаним сертифікатом, але для продакшену краще використовувати CA-підписані.

4. Запуск і перевірка PgBouncer

Після всіх налаштувань перезапустіть PgBouncer.

sudo systemctl restart pgbouncer # Перезапускаємо PgBouncer
sudo systemctl enable pgbouncer # Вмикаємо автозапуск PgBouncer
sudo systemctl status pgbouncer # Перевіряємо статус PgBouncer

Переконайтеся, що PgBouncer запущено і він не містить помилок у логах (sudo journalctl -u pgbouncer).

5. Перевірка працездатності

Тепер спробуємо підключитися до бази даних через PgBouncer з локального сервера.

psql -h 127.0.0.1 -p 6432 -U myappuser -d myappdb # Підключаємося до PgBouncer

Вас попросять ввести пароль для myappuser. Після успішного підключення ви опинитеся в консолі psql, але вже через PgBouncer. Виконайте простий запит:

SELECT 1;
\conninfo # Покаже, що ви підключені до PgBouncer, а не безпосередньо до PostgreSQL
\q

Для перевірки статистики PgBouncer підключіться до спеціальної псевдо-бази даних pgbouncer з користувачем pgbouncer_admin:

psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin -d pgbouncer # Підключаємося до адмін-інтерфейсу PgBouncer

У консолі psql:

SHOW STATS; # Показати загальну статистику пулінгу
SHOW POOLS; # Показати статус пулів з'єднань
SHOW CLIENTS; # Показати активних клієнтів
SHOW SERVERS; # Показати активні з'єднання з PostgreSQL
\q

Ці команди допоможуть вам моніторити роботу PgBouncer.

Резервне копіювання та обслуговування

Надійна стратегія резервного копіювання та регулярне обслуговування є критично важливими для будь-якої production-системи з базою даних.

1. Що резервувати

• Бази даних PostgreSQL: Це найважливіший компонент. Необхідно регулярно створювати повні дампи баз даних.
• Конфігураційні файли: /etc/postgresql/18/main/postgresql.conf, /etc/postgresql/18/main/pg_hba.conf, /etc/pgbouncer/pgbouncer.ini, /etc/pgbouncer/userlist.txt, а також будь-які скрипти або файли Certbot, пов'язані з TLS.
• Дані додатків: Якщо ваш додаток зберігає користувацькі файли або інші дані на диску VPS, їх також потрібно резервувати.

2. Простий скрипт авторезервування PostgreSQL

Ми створимо простий скрипт, який створюватиме дамп усіх баз даних PostgreSQL та зберігатиме його в зашифрованому вигляді. Для шифрування та ефективного зберігання можна використовувати borgbackup або restic, але для простоти поки обмежимося pg_dumpall та стисненням.

sudo mkdir -p /opt/backups # Створюємо директорію для резервних копій
sudo nano /opt/backups/backup_pg.sh # Створюємо скрипт резервного копіювання

Вміст backup_pg.sh:

#!/bin/bash

# Шлях для збереження резервних копій
BACKUP_DIR="/opt/backups"
DATE=$(date +%Y-%m-%d_%H-%M-%S)
BACKUP_FILE="$BACKUP_DIR/postgresql_all_databases_$DATE.sql.gz"
CONFIG_BACKUP_DIR="$BACKUP_DIR/configs"

# Створюємо директорії, якщо їх немає
mkdir -p "$BACKUP_DIR"
mkdir -p "$CONFIG_BACKUP_DIR"

echo "Починаємо створення резервної копії PostgreSQL..."

# Створюємо дамп усіх баз даних PostgreSQL
sudo -u postgres pg_dumpall | gzip > "$BACKUP_FILE"

if [ $? -eq 0 ]; then
    echo "Резервну копію бази даних успішно створено: $BACKUP_FILE"
else
    echo "Помилка при створенні резервної копії бази даних!"
    exit 1
fi

echo "Починаємо резервне копіювання конфігураційних файлів..."

# Копіюємо конфігураційні файли
cp /etc/postgresql/18/main/postgresql.conf "$CONFIG_BACKUP_DIR/postgresql.conf_$DATE"
cp /etc/postgresql/18/main/pg_hba.conf "$CONFIG_BACKUP_DIR/pg_hba.conf_$DATE"
cp /etc/pgbouncer/pgbouncer.ini "$CONFIG_BACKUP_DIR/pgbouncer.ini_$DATE"
cp /etc/pgbouncer/userlist.txt "$CONFIG_BACKUP_DIR/userlist.txt_$DATE"

echo "Резервне копіювання конфігураційних файлів завершено."

# Видаляємо старі резервні копії (наприклад, старші 7 днів)
find "$BACKUP_DIR" -name "postgresql_all_databases_*.sql.gz" -mtime +7 -delete
find "$CONFIG_BACKUP_DIR" -name "*_$DATE" -mtime +7 -delete

echo "Старі резервні копії видалено."
echo "Резервне копіювання завершено."

sudo chmod +x /opt/backups/backup_pg.sh # Робимо скрипт виконуваним

Налаштуємо виконання скрипта за допомогою cron. Наприклад, щодня о 3:00 ночі.

sudo crontab -e # Відкриваємо crontab для root (або sudo crontab -e -u username)

Додайте наступний рядок у кінець файлу:

0 3 * * * /opt/backups/backup_pg.sh > /var/log/backup_pg.log 2>&1 # Щоденне резервне копіювання о 3 ранку

3. Куди зберігати резервні копії

Ніколи не зберігайте резервні копії на тому ж сервері, що й оригінальні дані. Якщо сервер вийде з ладу, ви втратите і дані, і резервні копії.

• Зовнішнє S3-сумісне об'єктне сховище: Найбільш рекомендований варіант. Дешево, надійно, масштабовано. Використовуйте утиліти типу s3cmd, rclone або awscli для автоматичної синхронізації резервних копій з S3.
• Окремий VPS: Ви можете орендувати невеликий VPS спеціально для зберігання резервних копій та використовувати rsync або scp для їх передачі по SSH.
• Мережеве сховище (NFS/SMB): Якщо у вас є власна інфраструктура.

Приклад відправки на S3 за допомогою rclone (після його налаштування):

# Додайте у ваш скрипт backup_pg.sh після створення резервних копій
echo "Відправка резервних копій на S3..."
rclone sync "$BACKUP_DIR" "my-s3-remote:my-backup-bucket/pgbouncer-vps/" # Замініть 'my-s3-remote' і 'my-backup-bucket'
echo "Відправка на S3 завершена."

4. Оновлення: rolling vs maintenance window

• Оновлення ОС та PgBouncer: Для незначних оновлень безпеки можна використовувати rolling-оновлення, але завжди з тестовим прогоном. Для мажорних версій краще планувати вікно обслуговування. PgBouncer можна перезапускати без зупинки PostgreSQL, але при цьому всі активні клієнтські з'єднання будуть розірвані.
• Оновлення PostgreSQL: Мажорні оновлення PostgreSQL (наприклад, з 17 до 18) вимагають міграції даних і завжди повинні виконуватися в заздалегідь заплановане вікно обслуговування, з повним резервним копіюванням та планом відкату. Мінорні оновлення (наприклад, 18.1 до 18.2) зазвичай безпечні та можуть бути застосовані з мінімальним часом простою.
• Моніторинг: Після будь-яких оновлень критично важливо відстежувати логи та метрики продуктивності.

Усунення несправностей + FAQ

У цьому розділі ми розглянемо типові проблеми, які можуть виникнути при роботі з PgBouncer та PostgreSQL, а також відповімо на поширені запитання.

1. Помилка "Connection refused" при підключенні до PgBouncer

Що перевірити: Переконайтеся, що PgBouncer запущений і слухає на правильній IP-адресі та порту. Перевірте статус сервісу pgbouncer (sudo systemctl status pgbouncer) та логи (sudo journalctl -u pgbouncer). Також переконайтеся, що брандмауер (UFW) дозволяє вхідні з'єднання на порт PgBouncer (за замовчуванням 6432) – sudo ufw status.

Як виправити: Якщо PgBouncer не запущений, запустіть його (sudo systemctl start pgbouncer). Якщо порт заблоковано, додайте правило UFW (sudo ufw allow 6432/tcp). Перевірте listen_addr та listen_port у /etc/pgbouncer/pgbouncer.ini.

2. Помилка "Authentication failed" при підключенні клієнта до PgBouncer

Що перевірити: Переконайтеся, що користувач і пароль, які клієнт використовує для підключення до PgBouncer, коректно вказані у файлі /etc/pgbouncer/userlist.txt. Перевірте регістр символів та друкарські помилки. Переконайтеся, що auth_type у pgbouncer.ini відповідає очікуваному (наприклад, md5).

Як виправити: Виправте пароль у userlist.txt або оновіть пароль клієнта. Перезапустіть PgBouncer (sudo systemctl restart pgbouncer) після зміни userlist.txt.

3. PgBouncer не може підключитися до PostgreSQL (помилка "server connection failed")

Що перевірити: Переконайтеся, що PostgreSQL запущений і слухає на IP-адресі та порту, вказаних у секції [databases] файлу pgbouncer.ini (за замовчуванням 127.0.0.1:5432). Перевірте статус postgresql (sudo systemctl status postgresql). Переконайтеся, що користувач, якого PgBouncer використовує для підключення до PostgreSQL (наприклад, pgbouncer_admin), існує в PostgreSQL і має правильний пароль, а також що pg_hba.conf дозволяє цьому користувачеві підключатися з 127.0.0.1.

Як виправити: Перевірте listen_addresses у /etc/postgresql/18/main/postgresql.conf та правила у /etc/postgresql/18/main/pg_hba.conf. Переконайтеся, що користувач PgBouncer (наприклад, pgbouncer_admin) створений у PostgreSQL з тим самим паролем, що й у userlist.txt. Перезапустіть PostgreSQL після змін у його конфігах.

4. Повільна робота додатка після впровадження PgBouncer

Що перевірити: Перевірте режим пулінгу (pool_mode) у pgbouncer.ini. Режим statement може викликати проблеми, якщо ваш додаток покладається на сесійні змінні або тимчасові таблиці. Переконайтеся, що default_pool_size та max_client_conn достатньо великі для вашого навантаження.

Як виправити: Спробуйте змінити pool_mode на transaction, який підходить для більшості веб-додатків. Збільште default_pool_size, щоб PgBouncer підтримував більше активних з'єднань з PostgreSQL. Проаналізуйте логи PgBouncer та PostgreSQL на предмет помилок або попереджень, пов'язаних з блокуваннями або нестачею з'єднань.

5. Який VPS-конфіг мінімально підійде?

Мінімальний VPS-конфіг для PgBouncer та PostgreSQL для невеликих проектів (до 50 одночасних підключень) повинен включати 2 vCPU, 4 GB RAM та 80 GB NVMe SSD. Цього буде достатньо для базової роботи та тестування, а також для невеликих веб-додатків. Для більш серйозних завдань рекомендується збільшувати ресурси.

6. Що обрати — VPS чи dedicated для цього завдання?

Для більшості середніх проектів та стартапів VPS буде оптимальним вибором, пропонуючи хороший баланс між вартістю та продуктивністю. Dedicated сервер рекомендується для дуже високонавантажених систем, критично важливих додатків з жорсткими вимогами до продуктивності I/O, або коли потрібна повна ізоляція ресурсів та максимальний контроль над апаратним забезпеченням. Якщо ви тільки починаєте, VPS — це більш гнучкий та економічний варіант.

7. Як моніторити PgBouncer?

Ви можете використовувати спеціальну псевдо-базу даних pgbouncer для отримання статистики. Підключіться до неї як psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin -d pgbouncer, а потім використовуйте команди SHOW STATS;, SHOW POOLS;, SHOW CLIENTS;, SHOW SERVERS;. Для більш просунутого моніторингу можна інтегрувати PgBouncer з Prometheus та Grafana, використовуючи експортер PgBouncer.

8. Як оновити PgBouncer без простою?

Для оновлення PgBouncer можна використовувати команду RELOAD через адмін-інтерфейс (psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin -d pgbouncer, потім RELOAD;). Це перезавантажить конфігурацію без розриву існуючих з'єднань. Для мажорних оновлень або оновлень, що вимагають перезапуску сервісу, можна використовувати команду PAUSE, потім RESTART, що дозволить існуючим транзакціям завершитися, перш ніж PgBouncer перезапуститься та прийме нові з'єднання.

Висновки та наступні кроки

Вітаємо! Ви успішно встановили та налаштували PgBouncer на вашому VPS, значно покращивши керування з'єднаннями з PostgreSQL. Тепер ваш додаток працюватиме стабільніше, споживатиме менше ресурсів бази даних і буде більш стійким до пікових навантажень. Ви отримали повний контроль над пулінгом з'єднань, що є ключовим елементом для масштабованих та високопродуктивних систем.

Наступні кроки для подальшої оптимізації та розвитку:

1. Моніторинг продуктивності: Впровадьте комплексну систему моніторингу (наприклад, Prometheus + Grafana) для відстеження метрик PgBouncer (кількість з'єднань, затримки) та PostgreSQL (навантаження на CPU/RAM, дискові операції, повільні запити). Це допоможе виявити вузькі місця та скоригувати конфігурацію.
2. Оптимізація запитів: Проаналізуйте повільні запити в PostgreSQL (використовуючи pg_stat_statements) та оптимізуйте їх, додаючи індекси, переписуючи запити або денормалізуючи дані. Навіть з PgBouncer неоптимізовані запити можуть стати вузьким місцем.
3. Масштабування PostgreSQL: У міру зростання навантаження розгляньте можливості горизонтального масштабування PostgreSQL, такі як реплікація (для читання), шардинг або використання логічної реплікації для аналітичних завдань. PgBouncer може бути налаштований для роботи з кількома репліками.