Як керувати базами даних у PostgreSQL: Повний посібник для користувачів Linux VPS

PostgreSQL (часто називають Postgres) — один з найпотужніших, багатофункціональних, відкритих реляційних систем управління базами даних, доступних сьогодні. Довіреній стартапами та корпоративними командами, він відмінно справляється зі складними запитами, великими наборами даних та масштабованою архітектурою додатків. Незалежно від того, чи ви розробляєте платформу SaaS, інтернет-магазин або API-бекенд з інтенсивною обробкою даних, PostgreSQL забезпечує надійність та продуктивність, які потребує ваш проект.

Запуск PostgreSQL у високопродуктивному середовищі VPS Hosting — з накопичувачами NVMe SSD, повним доступом root та вбудованим захистом від DDoS — дає вам повний контроль над інфраструктурою вашої бази даних без навантаження керованих хмарних рішень. Цей посібник проведе вас через кожне важливе завдання управління PostgreSQL, від початкового доступу та створення бази даних до дозволів користувачів, резервних копій та посилення безпеки.

Передумови

Перед тим як продовжити, переконайтеся, що у вас є:

• Linux VPS з Ubuntu, Debian або CentOS з встановленим PostgreSQL
• Доступ root або sudo до вашого сервера
• Базове знайомство з командним рядком Linux

Якщо ви ще не налаштували своє серверне середовище, VPS Control Panels AlexHost дозволяють легко налаштувати ваш стек швидко.

1. Доступ до інтерфейсу командного рядка PostgreSQL

Усі завдання управління PostgreSQL починаються з інтерфейсу командного рядка (CLI), також відомого як psql. За замовчуванням PostgreSQL створює системного користувача postgres під час встановлення, і цей обліковий запис використовується для автентифікації з механізмом бази даних.

Крок 1: Перейти на системного користувача PostgreSQL

sudo -i -u postgres

Крок 2: Запустити CLI PostgreSQL

psql

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

postgres=#

Це підтверджує, що ви знаходитесь у середовищі PostgreSQL і готові виконувати SQL-команди та мета-команди.

> Професійна порада: Ви також можете підключитися безпосередньо без переходу користувачів, запустивши sudo -u postgres psql з вашої звичайної сеансу оболонки.

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

Бази даних — це контейнери верхнього рівня для всіх ваших таблиць, індексів та збережених даних. Використовуйте оператор CREATE DATABASE для підготування нової бази даних.

Синтаксис

CREATE DATABASE database_name;

Приклад

CREATE DATABASE my_database;

Це створює базу даних з назвою my_database у власності поточної активної ролі PostgreSQL.

Перевірте, що база даних була створена

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

l

Ви побачите таблицю, що показує назви баз даних, власників, кодування та привілеї доступу.

3. Створення та управління користувачами бази даних

Правильне управління користувачами критично важливо для безпеки бази даних. Замість надання всім додаткам доступу під обліковим записом суперкористувача postgres, ви повинні створити спеціалізованих користувачів з обмеженими дозволами.

Створити нового користувача

CREATE USER username WITH PASSWORD 'your_secure_password';

Приклад

CREATE USER dbuser WITH PASSWORD 'StrongP@ssword123';

Надати повний доступ до конкретної бази даних

GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

Приклад

GRANT ALL PRIVILEGES ON DATABASE my_database TO dbuser;

Це надає dbuser повний доступ для читання та запису до my_database, включаючи можливість створювати та видаляти таблиці.

Список всіх користувачів

Для перегляду всіх існуючих ролей та користувачів:

du

4. Підключення до бази даних

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

Перейти до бази даних

c my_database

Запит буде оновлено, щоб відобразити активну базу даних:

my_database=#

Ви тепер працюєте в межах my_database і можете виконувати всі DDL та DML оператори проти неї.

5. Створення та управління таблицями

Таблиці — це основна структурна одиниця будь-якої реляційної бази даних. Кожна таблиця визначає схему — набір іменованих стовпців з конкретними типами даних та обмеженнями.

Створити таблицю

CREATE TABLE table_name (
    column1 data_type PRIMARY KEY,
    column2 data_type,
    column3 data_type
);

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

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    department  VARCHAR(50),
    salary      NUMERIC(10, 2)
);

Тут SERIAL автоматично збільшує employee_id для кожного нового рядка, а NOT NULL забезпечує, що кожен працівник повинен мати ім’я.

6. Вставлення, запит, оновлення та видалення даних

Вставити запис

INSERT INTO employees (name, department, salary)
VALUES ('Jane Smith', 'Engineering', 72000);

Запит усіх записів

SELECT * FROM employees;

Фільтрування результатів за допомогою пункту WHERE

SELECT name, salary FROM employees WHERE department = 'Engineering';

Оновити існуючий запис

UPDATE employees
SET salary = 78000
WHERE name = 'Jane Smith';

Видалити конкретний запис

DELETE FROM employees
WHERE name = 'Jane Smith';

> Найкраща практика: Завжди використовуйте пункт WHERE з операторами UPDATE та DELETE. Його пропуск вплине на кожен рядок у таблиці.

7. Управління доступом до бази даних та безпекою

Безпека — це першорядна проблема в будь-якому виробничому розгортанні PostgreSQL. Принцип найменшого привілею — надання користувачам лише дозволів, які їм дійсно потрібні — значно зменшує вашу поверхню атаки.

Відкликати всі привілеї від користувача

REVOKE ALL PRIVILEGES ON DATABASE my_database FROM dbuser;

Надати доступ лише для читання до конкретної таблиці

GRANT SELECT ON TABLE employees TO dbuser;

Це дозволяє dbuser запитувати таблицю employees, але запобігає будь-яким вставленням, оновленням або видаленням.

Надати конкретні дозволи DML

GRANT SELECT, INSERT, UPDATE ON TABLE employees TO dbuser;

Додаткові рекомендації щодо безпеки

• Використовуйте сильні, унікальні паролі для кожного користувача бази даних
• Вимкніть віддалений доступ для суперкористувача postgres у pg_hba.conf
• Увімкніть SSL-з’єднання для шифрування даних під час передачі — поєднайте це з надійним SSL сертифікатом на вашому сервері
• Регулярно перевіряйте привілеї користувачів за допомогою du та dp
• Тримайте PostgreSQL оновленим, щоб виправити відомі вразливості

8. Резервне копіювання та відновлення баз даних

Регулярні резервні копії є обов’язковими для будь-якої виробничої бази даних. PostgreSQL надає утиліти pg_dump та psql для простих робочих процесів резервного копіювання та відновлення.

Резервна копія бази даних у файл SQL

pg_dump my_database > my_database_backup.sql

Це експортує всю схему бази даних та дані як звичайний текстовий SQL-скрипт.

Резервна копія у стисненому форматі (рекомендується для великих баз даних)

pg_dump -Fc my_database > my_database_backup.dump

Користувацький формат (-Fc) створює стиснений двійковий файл і підтримує паралельне відновлення.

Відновити базу даних з резервної копії SQL

psql my_database < my_database_backup.sql

Відновити з резервної копії у користувацькому форматі

pg_restore -d my_database my_database_backup.dump

Автоматизувати резервні копії за допомогою завдання Cron

Заплануйте щоденні резервні копії, додавши запис cron:

crontab -e

0 2 * * * pg_dump my_database > /var/backups/postgres/my_database_$(date +%F).sql

Це запускає резервну копію щодня о 2:00 ранку та додає дату до імені файлу для легкого контролю версій.

9. Видалення таблиць та баз даних

Коли таблиця або база даних більше не потрібна, використовуйте команду DROP для постійного видалення.

Видалити таблицю

DROP TABLE table_name;

Приклад

DROP TABLE employees;

Видалити таблицю лише якщо вона існує (безпечніший синтаксис)

DROP TABLE IF EXISTS employees;

Видалити базу даних

Ви повинні відключитися від цільової бази даних перед її видаленням. Спочатку перейдіть до стандартної бази даних postgres:

c postgres

Потім видаліть ціль:

DROP DATABASE my_database;

> Попередження: DROP DATABASE незворотна. Завжди перевіряйте, що у вас є поточна резервна копія перед виконанням цієї команди у виробництві.

10. Довідник корисних мета-команд PostgreSQL

CLI psql включає багатий набір мета-команд (з префіксом ), які спрощують навігацію та перевірку: