Junior: питання на співбесіді з теми «Експлуатація БД»
Питання з реальних співбесід з відповідями: Laravel і PHP, бази даних, JavaScript і фронтенд, Git, Docker, API, безпека й архітектура. Тими самими темами, що й тести.
5 питань
Найпростіший спосіб - логічний дамп:
# PostgreSQL: власний стиснений формат, зручний для вибіркового відновлення
pg_dump -Fc -d app > app.dump
pg_restore -d app_restored app.dump
# MySQL
mysqldump --single-transaction --routines app > app.sql
pg_dump бере узгоджений знімок і не блокує роботу застосунку. Для MySQL на InnoDB той самий ефект дає --single-transaction.
Бекап, який ніхто не пробував відновити, - не бекап. Мінімум:
- Регулярно відновлювати копію на окремому сервері й перевіряти, що застосунок з нею працює. Автоматично, за розкладом.
- Зберігати копії не там, де база: інший сервер, інший дата-центр, об'єктне сховище. Пожежа чи видалений акаунт не мають забрати і базу, і бекапи.
- Кілька поколінь: щоденні за тиждень, щотижневі за місяць. Помилку в даних часто помічають не одразу.
- Моніторинг: сповіщення, якщо бекап не створився чи раптом став набагато меншим.
Правило 3-2-1: три копії, на двох різних носіях, одна - поза основним майданчиком.
Обмеження дампу: відновлення - лише на момент створення копії. Усе, що сталося після, втрачено. Для відновлення на довільний момент потрібне безперервне архівування журналу (WAL, binlog).
Докладніше в документації: Резервне копіювання і відновлення
Репліка - копія бази на іншому сервері, яка безперервно отримує зміни з основного сервера (primary) і застосовує їх у себе.
Як це працює: primary записує всі зміни в журнал (WAL у PostgreSQL, binlog у MySQL), а репліка отримує цей журнал по мережі й програє його. Зазвичай реплікація асинхронна: primary не чекає підтвердження від репліки.
Навіщо:
- Відмовостійкість. Якщо primary впав, репліку можна підвищити до нового primary і продовжити роботу за хвилини, а не за години відновлення з бекапу.
- Масштабування читання. Важкі звіти, аналітику й частину запитів на читання відправляють на репліки, розвантажуючи primary.
- Обслуговування без простою. Оновлення чи перевірки можна спершу робити на репліці.
Чого репліка НЕ замінює - бекапів. DELETE FROM users без WHERE за мілісекунди реплікується на всі копії. Від логічних помилок рятує лише бекап чи відновлення на момент у часі.
Головний нюанс для розробника - затримка реплікації: щойно записані дані на репліці можуть з'явитися із запізненням. У Laravel є опція sticky: після запису в межах того самого запиту читання йде на primary.
psql - консольний клієнт PostgreSQL. Окрім SQL, у ньому є метакоманди з \, що швидко показують структуру бази.
Підключення:
psql -h localhost -U app -d app_production
psql "postgresql://app@localhost:5432/app_production"
Структура бази:
\l список баз
\c app_test підключитися до іншої бази
\dt таблиці
\d orders структура таблиці: колонки, індекси, обмеження, тригери
\di індекси
\dn схеми
\du ролі
\df функції
\dx встановлені розширення
\dt+ таблиці з розміром
Робота із запитами:
\x auto розгорнутий вивід для широких рядків
\timing on показувати час виконання кожного запиту
\e редагувати останній запит у $EDITOR
\i file.sql виконати файл
\copy orders TO 'orders.csv' CSV HEADER експорт з клієнтського боку
\watch 2 повторювати запит кожні 2 секунди
\q вийти
Корисні прийоми:
\set ON_ERROR_STOP onу скриптах - зупинитися на першій помилці замість виконання решти.BEGIN;перед ризикованими змінами на проді - подивитися результат і вирішити:COMMITчиROLLBACK.\set AUTOCOMMIT off- кожна команда автоматично в транзакції, доки не зробитиCOMMIT.~/.psqlrc- налаштування за замовчуванням (\timing,\x auto, кольоровий промпт для продакшену).~/.pgpass- паролі для підключень, щоб не вводити їх щоразу й не передавати в командному рядку.
На проді - обережно: psql на продакшн-базі - повний доступ. Безпечніше підключатися до репліки для читання і мати окрему роль лише з правами читання для розслідувань.
Поширена практика «застосунок підключається як postgres» означає, що SQL-ін'єкція чи помилка в коді може видалити будь-яку таблицю, прочитати будь-які дані, змінити налаштування сервера. Принцип найменших привілеїв: кожна роль має лише ті права, які їй потрібні.
Типова схема ролей:
-- Власник схеми: від його імені ганяють міграції
CREATE ROLE app_owner LOGIN PASSWORD '...';
CREATE SCHEMA app AUTHORIZATION app_owner;
-- Застосунок: лише робота з даними
CREATE ROLE app_user LOGIN PASSWORD '...';
GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO app_user;
-- Права на таблиці, які створять пізніше міграції
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT USAGE ON SEQUENCES TO app_user;
-- Аналітика й розслідування: лише читання
CREATE ROLE readonly LOGIN PASSWORD '...';
GRANT pg_read_all_data TO readonly; -- PostgreSQL 14+
Що це дає:
- Застосунок не може
DROP TABLE,ALTER, створювати ролі чи читати системні налаштування. - Міграції виконуються окремою роллю - її пароль не живе в змінних середовища вебсерверів.
- Аналітики й розробники на проді працюють від ролі лише для читання.
Деталі, про які забувають:
- Права за замовчуванням (
ALTER DEFAULT PRIVILEGES) - без них нова таблиця з міграції буде недоступна застосунку. - Схема
public: з PostgreSQL 15 звичайні ролі за замовчуванням не можуть створювати в ній об'єкти - на старіших версіях це право варто забрати (REVOKE CREATE ON SCHEMA public FROM PUBLIC). - Послідовності потребують окремого
USAGE, інакше вставка з автоінкрементом впаде. - Підключення обмежують ще й на рівні
pg_hba.conf: з яких адрес, до яких баз, яким методом автентифікації (scram-sha-256).
Мінорні оновлення (17.4 → 17.6) - лише виправлення помилок: досить оновити пакет і перезапустити сервер. Формат даних не змінюється.
Мажорні (16 → 17 → 18) змінюють внутрішній формат зберігання, тож файли даних старої версії нова прочитати не може. Є три способи:
1. pg_dump + відновлення - найпростіше й найнадійніше:
pg_dump -Fc -d app > app.dump
# новий сервер
pg_restore -d app app.dump
Простій - на весь час дампу й відновлення з перебудовою індексів. Для бази в кілька гігабайтів - нормально, для терабайтів - години.
2. pg_upgrade - перетворює каталог даних «на місці»:
pg_upgrade --old-datadir ... --new-datadir ... --old-bindir ... --new-bindir ... --link
З --link файли даних не копіюються, а використовуються жорсткі посилання - оновлення займає хвилини навіть для великих баз. Обов'язково спершу --check (сухий прогін). Після оновлення потрібна статистика для планувальника (vacuumdb --analyze-in-stages); з PostgreSQL 18 pg_upgrade уміє переносити її сам.
3. Логічна реплікація - майже без простою: новий сервер на новій версії підписується на зміни старого, наздоганяє його, і застосунок перемикається за секунди. Найскладніше в налаштуванні: послідовності й зміни схеми не реплікуються, їх переносять окремо.
Як підготуватися:
- Прочитати release notes усіх мажорних версій між поточною й цільовою - розділи про несумісності.
- Перевірити, що розширення (PostGIS, pgvector тощо) доступні для нової версії.
- Прогнати тести застосунку на новій версії, відрепетирувати оновлення на копії продакшн-даних, виміряти час.
- Мати свіжий бекап і план відкату.
На керованих сервісах (RDS, Cloud SQL) мажорне оновлення - кнопка, але простій і перевірки сумісності ті самі - про них варто подбати заздалегідь.