Стаття також доступна російською (перейти до перегляду).

Зміст:

• Що потрібно перед встановленням
• Встановлення PostgreSQL
• Перевірка роботи PostgreSQL
• Системний користувач postgres
• Базові команди psql
• Створення користувача PostgreSQL
• Створення бази даних
• Перевірка підключення новим користувачем
• Де знаходяться налаштування PostgreSQL
• Віддалене підключення до PostgreSQL
• Перезапуск PostgreSQL після зміни конфігурації
• Видалення користувача або бази
• Типові проблеми
• Що варто налаштувати на production-сервері
• Ресурси для PostgreSQL на VPS
• Висновок

PostgreSQL — реляційна система керування базами даних для сайтів, API, CRM та серверних застосунків. Матеріал для початківця з VPS або виділеним сервером на Ubuntu. Тут показано, як встановити PostgreSQL на Ubuntu, перевірити сервіс, створити роль і базу, а також підготувати безпечне підключення. Перший доступ до PostgreSQL через SSH виконується з локального терміналу.

На відміну від MySQL або MariaDB, PostgreSQL має власну модель ролей та конфігураційних файлів, тому команди адміністрування відрізняються. Для роботи потрібні SSH-доступ, користувач Linux із правами sudo та IP-адреса сервера. Якщо VPS ще немає, перегляньте VPS-хостинг Freehost. Готовий варіант з Ubuntu доступний на сторінці VPS з Ubuntu.

Що потрібно перед встановленням

Увійдіть на сервер через SSH:

ssh user@SERVER_IP

Замініть user на ім'я Linux-користувача, а SERVER_IP — на адресу VPS. Перевірте реліз Ubuntu та вільне місце:

lsb_release -ds
f -h /

Оновіть індекси пакетів. Повне оновлення системи перед встановленням PostgreSQL не є обов'язковим, але його можна виконати під час планового обслуговування:

sudo apt update
sudo apt upgrade

Команда apt update оновлює локальні списки пакетів. Команда sudo apt upgrade може бути корисною для планового обслуговування, але для базового встановлення PostgreSQL не є обов'язковою. Перед великим оновленням перевірте backup, вільне місце та доступ до консолі VPS.

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

Стандартний репозиторій Ubuntu містить потрібний пакет. Для базового сценарію не потрібно додавати сторонній репозиторій:

sudo apt install postgresql postgresql-contrib

Пакет postgresql додає сервер, клієнт psql та systemd-сервіс. Пакет postgresql-contrib містить додаткові розширення. Після інсталяції система створює початковий кластер та запускає сервер.

Перевірте версію клієнта:

psql --version

Версія PostgreSQL залежить від релізу Ubuntu та підключених джерел пакетів. На перевіреному VPS команда повернула PostgreSQL 16.15.

Перевірка встановлення: версія 16.15, активний сервіс та автозапуск.

Перевірка роботи PostgreSQL

Після встановлення сервіс зазвичай уже запущений. Перевірте його через systemctl:

sudo systemctl is-active postgresql
sudo systemctl is-enabled postgresql
sudo systemctl --no-pager status postgresql

Очікувані відповіді — active та enabled. Статус active (exited) для агрегованої служби не обов'язково означає збій: systemd керує окремим кластером. Переглянути його можна так:

pg_lsclusters

Якщо сервіс не запущений, спочатку перегляньте журнал:

sudo journalctl -u postgresql --since "30 min ago" --no-pager

Для запуску або автозапуску використовуйте такі команди лише після перевірки причини зупинки:

sudo systemctl start postgresql

sudo systemctl enable postgresql

Системний користувач postgres

Два рівні доступу

Під час інсталяції Ubuntu створює системного користувача postgres. Він потрібен операційній системі для запуску процесів. Усередині СУБД існує окрема роль з таким самим ім'ям. Системний користувач та роль — різні об'єкти.

Увійти в консоль PostgreSQL локально можна від імені системного користувача:

sudo -u postgres psql

Промпт postgres=# означає, що відкрита консоль адміністратора. Для застосунку не використовуйте роль postgres: створіть окремий користувач PostgreSQL з обмеженими правами.

Перевірка локального SQL-запиту від імені системної ролі postgres.

Базові команди psql

Клієнт psql PostgreSQL приймає SQL-запити та службові метакоманди. Основна шпаргалка для роботи з базою:

• \l — список баз даних;

• \du — список ролей та їхніх атрибутів;

• \c database_name — перехід до іншої бази;

• \dt — таблиці поточної бази;

• \q — вихід із консолі.

Метакоманди починаються зі зворотної косої риски та не потребують крапки з комою. SQL-команди завершуються символом ;. Інструмент PostgreSQL psql зручний для швидкої перевірки стану та прав доступу. Для коротких діагностичних запитів комбінація PostgreSQL psql не потребує графічного клієнта.

sudo -u postgres psql -X -P pager=off -c "SELECT datname, pg_get_userbyid(datdba) FROM pg_database ORDER BY datname;"

Команда psql PostgreSQL показує назви баз та їхніх власників.

Створення користувача PostgreSQL

Для застосунку створіть окремого користувача PostgreSQL. Відкрийте консоль від імені адміністратора:

sudo -u postgres psql

Виконайте SQL-команду створення ролі з правом входу:

CREATE USER appuser WITH PASSWORD 'ЗАМІНІТЬ_НА_СИЛЬНИЙ_ПАРОЛЬ';

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

\du appuser

Створення бази даних

Створіть окрему базу та одразу призначте її власником роль застосунку:

CREATE DATABASE appdb OWNER appuser;

Власник бази даних отримує права на керування її об'єктами, але доступи до окремих таблиць все одно треба планувати. Після створення перевірте список баз:

\l
\q

Перевірка підключення новим користувачем

Вийдіть із консолі адміністратора та перевірте підключення до PostgreSQL новою роллю:

psql -h localhost -U appuser -d appdb

Параметр -h задає сервер, -U — користувача, -d — базу даних. Адреса 127.0.0.1 перевіряє TCP-підключення, яке зазвичай використовує застосунок. Без -h клієнт може застосувати Unix-сокет та інше правило доступу.

Після введення пароля виконайте тестовий запит:

SELECT current_database(), current_user;
\q

Рядок з URL для змінної середовища має вигляд:

postgresql://appuser:ПАРОЛЬ@127.0.0.1:5432/appdb

Не додавайте реальний пароль до репозиторію. Для продакшену використовуйте змінні середовища або сховище секретів.

Де знаходяться налаштування PostgreSQL

Що входить у PostgreSQL конфігурація

Під запитом «PostgreSQL конфігурація» мають на увазі набір параметрів сервера та правил автентифікації. Таке налаштування PostgreSQL впливає на мережу, права доступу, пам'ять і журнали, тому перед редагуванням збережіть копію.

Назви postgresql.conf та pg_hba.conf варто запам'ятати: перший файл описує роботу сервера, другий — перевірку клієнтів.

Основні параметри зберігаються у двох файлах. Разом вони визначають мережеву поведінку та правила доступу:

• postgresql.conf — адреса прослуховування, порт, пам'ять, журнали та інші параметри сервера;

• pg_hba.conf — правила, хто, звідки та до якої бази може виконати автентифікацію.

Типовий шлях на Ubuntu має змінний номер версії:

/etc/postgresql/<version>/main/postgresql.conf
/etc/postgresql/<version>/main/pg_hba.conf

Точні шляхи та поточні параметри краще отримати через SQL:

sudo -u postgres psql -X -P pager=off -c "SHOW config_file;"
sudo -u postgres psql -X -P pager=off -c "SHOW hba_file;"
sudo -u postgres psql -X -P pager=off -c "SHOW listen_addresses; SHOW port;"

На перевіреному сервері використовується каталог /etc/postgresql/16/main/, адреса localhost та порт 5432. Не копіюйте номер версії без перевірки.

Перевірка конфігураційних файлів, адреси та порту 5432.

Віддалене підключення до PostgreSQL

Для застосунку на тому самому VPS достатньо локального доступу. Віддалене підключення PostgreSQL потрібне, якщо сайт та база розміщені на різних серверах. Не відкривайте базу в Інтернет без обмежень.

1. В основному файлі вкажіть приватну або іншу конкретну адресу сервера в listen_addresses.

2. У файлі доступу додайте правило для однієї IP-адреси сервера застосунку.

3. У firewall дозвольте PostgreSQL порт 5432 лише для цього джерела.

4. Перезапустіть PostgreSQL або виконайте reload, після чого перевірте підключення з сервера застосунку.

Приклад правила доступу:

host    appdb    appuser    APP_SERVER_IP/32    scram-sha-256

Маска /32 дозволяє одну IPv4-адресу. Не використовуйте 0.0.0.0/0: так будь-який вузол мережі зможе звертатися до порту. Для налаштування PostgreSQL на VPS краще застосувати приватну мережу або SSH-тунель.

Firewall та SSH-тунель

Для PostgreSQL firewall правило має містити конкретну адресу:

sudo ufw allow from APP_SERVER_IP to any port 5432 proto tcp
sudo ufw status numbered

Варіант без публічного порту — тунель через SSH:

ssh -L 5432:127.0.0.1:5432 user@DB_SERVER_IP

Після створення тунелю клієнт підключається до 127.0.0.1:5432 на локальному комп'ютері. Це зменшує поверхню атаки та не скасовує перевірку пароля.

Перезапуск PostgreSQL після зміни конфігурації

Після зміни файла доступу зазвичай достатньо перечитати правила:

sudo systemctl reload postgresql

Після зміни listen_addresses або порту може знадобитися повний перезапуск:

sudo systemctl restart postgresql

reload застосовує доступні безперервно параметри та не має переривати активні сесії. restart зупиняє та запускає процес заново, тому його виконуйте у погоджене вікно робіт. Після операції повторіть перевірку через systemctl та журнал.

Видалення користувача або бази

Команди DROP DATABASE та DROP USER використовуйте лише після перевірки backup та активних підключень:

DROP DATABASE appdb;
DROP USER appuser;

DROP DATABASE безповоротно видаляє дані. Якщо роль володіє об'єктами або має залежності, спочатку перепризначте їх або видаліть у контрольованому середовищі.

Типові проблеми

• connection refused. Перевірте, чи запущений сервіс, чи слухає потрібна адреса та чи відкритий порт.

• PostgreSQL не запущений. Перевірте статус через systemctl, журнал запуску та стан кластера.

• Помилка автентифікації. Перевірте роль, пароль та відповідне правило доступу.

• Користувач не має доступу до бази. Перевірте власника, права на схему та таблиці. Власник бази даних не завжди є власником об'єктів, створених іншою роллю.

• Порт 5432 закритий firewall. Перевірте правило UFW, зовнішній firewall провайдера та IP-адресу клієнта.

• Зміни не діють. Перевірте синтаксис файлів та виконайте reload або restart залежно від параметра.

Для діагностики локального сервера виконайте:

sudo ss -lntp | grep 5432
sudo -u postgres psql -X -P pager=off -c "SHOW listen_addresses; SHOW port;"

Що варто налаштувати на production-сервері

Перед змінами та за розкладом створюйте резервну копію:

pg_dump -h 127.0.0.1 -U appuser -d appdb -Fc -f /var/backups/appdb.dump

Копія на тому самому диску не захищає від його відмови. Передбачте інше сховище та тестове відновлення. Налаштуйте firewall, обмежте віддалений доступ, регулярно оновлюйте Ubuntu, контролюйте вільне місце, RAM та журнали. Моніторинг диска, RAM і PostgreSQL допоможе помітити проблему до зупинки застосунку. SSD або NVMe корисні для навантажених баз, але не замінюють індекси, оптимізацію запитів і backup.

Ресурси для PostgreSQL на VPS

PostgreSQL на VPS може працювати на невеликому тарифі, якщо навантаження помірне. Для великих баз та великої кількості запитів потрібні достатні RAM, процесор і швидкий диск. VPS від FREEhost.UA має запас ресурсів для бази, вебсервера та фонового worker-а. Окремий PostgreSQL сервер зручний для проєкту, де база потребує власних ресурсів та незалежного моніторингу.

Висновок

Після виконання інструкції працює PostgreSQL, створено окремого користувача та базу даних із власником. Підключення через psql перевірено, застосунок можна під'єднати через змінні середовища.

Далі налаштуйте резервне копіювання, перевірте відновлення та оптимізуйте PostgreSQL під навантаження. Віддалений доступ обмежуйте конкретною IP-адресою.

Підписуйтеся на наш Telegram-канал https://t.me/freehostua, щоб бути в курсі нових корисних матеріалів.

Дивіться наш канал YouTube на https://www.youtube.com/freehostua.

Ми у чомусь помилилися, чи щось пропустили?

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

Дата: 15.09.2026 Автор: Алексей Литвинцев #linux#ubuntu

Голосування 1 2 3 4 5