Адміністрування бази даних PostgreSQL для веб-застосунку

Адміністрування базі даних PostgreSQL для веб-додатку

PostgreSQL — не «встановив та забув». За замовчуванням налаштований консервативно для запуску на найбільш широкому спектрі залізо. Без регулярного обслуговування: таблиці роздуваються від dead tuples, індекси фрагментуються, bloat займає гігабайти, повільні запити тягнуть вниз весь додаток. Системна адміністрація — це сукупність регулярних завдань та постійного моніторингу.

Аудит інсталяції на початку

Перше, що робиться при підключенні до нової бази:

-- Версія та конфігурація
SELECT version();
SHOW config_file;
SHOW data_directory;

-- Розмір баз даних
SELECT datname,
       pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- Топ-10 таблиць по розміру з урахуванням bloat
SELECT schemaname,
       relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid))       AS table,
       pg_size_pretty(pg_indexes_size(relid))        AS indexes
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- Таблиці з великою кількістю dead tuples (кандидати на VACUUM)
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

VACUUM та ANALYZE

Автовакуум працює в фоні, але іноді його налаштувань недостатньо для високонавантажених таблиць:

-- Ручний vacuum для конкретної таблиці
VACUUM (VERBOSE, ANALYZE) orders;

-- Для таблиці з величезним bloat — FULL (експлюзивна блокировка!)
-- Виконувати в окно обслуговування
VACUUM FULL orders;

VACUUM FULL блокує таблицю все час виконання. Для продакшну без окна — використовуємо pg_repack:

# Дефрагментація без блокировки
pg_repack -h localhost -U postgres -d mydb -t orders

Налаштування автовакуума для активно обновляемих таблиць:

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,  -- vacuum при 1% dead tuples (замість 20%)
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_cost_delay = 2        -- ms, агресивніше
);

Управління індексами

-- Невикористовувані індекси (витрачають місце та сповільнюють INSERT/UPDATE)
SELECT schemaname, relname, indexrelname,
       idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS idx_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Дублюючі індекси
SELECT a.indexrelid::regclass AS index1,
       b.indexrelid::regclass AS index2,
       a.indrelid::regclass   AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
               AND a.indexrelid < b.indexrelid
               AND a.indkey = b.indkey;

-- Створення індексу без блокировки запису
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

CONCURRENTLY обов'язковий на продакшені — стандартний CREATE INDEX блокує запис.

Резервні копії

# pg_dump — логічний бекап конкретної бази
pg_dump -h localhost -U postgres -Fc -Z 9 mydb > /backups/mydb_$(date +%Y%m%d_%H%M).dump

# Восстановлення
pg_restore -h localhost -U postgres -d mydb_restored /backups/mydb_20250101_0300.dump

# pg_basebackup — фізичний бекап для PITR
pg_basebackup -h localhost -U replication -D /backups/base -Ft -z -P

Розклад через cron:

# /etc/cron.d/postgres-backup
0 3 * * * postgres pg_dump -Fc mydb | gzip > /backups/mydb_$(date +\%Y\%m\%d).dump.gz
# Ротація: зберігати 30 днів
0 4 * * * find /backups/ -name "*.dump.gz" -mtime +30 -delete

Тестуйте восстановлення регулярно — щонайменше раз на місяць в ізольованому окружені.

Репліка (streaming replication)

# postgresql.conf на primary
wal_level = replica
max_wal_senders = 3
wal_keep_size = 1GB

# pg_hba.conf
host replication replication 10.0.0.2/32 scram-sha-256

# Ініціалізація реплік
pg_basebackup -h 10.0.0.1 -U replication -D /var/lib/postgresql/14/main \
  -P -Xs -R
# -R створює standby.signal та recovery.conf автоматично

Мониторинг лага репліки:

-- На primary
SELECT client_addr,
       state,
       sent_lsn,
       write_lsn,
       flush_lsn,
       replay_lsn,
       (sent_lsn - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

Управління підключеннями: PgBouncer

PostgreSQL погано масштабується при тисячах одночасних підключень — кожне займає ~5–10 МБ пам'яті. PgBouncer вирішує це через пулинг:

# /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode        = transaction  # transaction pooling — найбільш ефективний
max_client_conn  = 1000
default_pool_size = 25
reserve_pool_size = 5
server_idle_timeout = 600

Додаток підключається до PgBouncer на порту 6432, не напрямо до PostgreSQL.

Регулярні завдання

Ролі та дозволи

-- Принцип найменших привілегій
CREATE ROLE app_user LOGIN PASSWORD 'strong_password';
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Для майбутніх таблиць
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

-- Read-only роль для аналітики/звітів
CREATE ROLE readonly_user LOGIN PASSWORD 'password';
GRANT CONNECT ON DATABASE mydb TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

Оновлення мажорної версії

PostgreSQL не обновляется через apt upgrade між мажорними версіями. Використовується pg_upgrade:

# Приклад: 14 -> 16
pg_upgrade \
  -b /usr/lib/postgresql/14/bin \
  -B /usr/lib/postgresql/16/bin \
  -d /var/lib/postgresql/14/main \
  -D /var/lib/postgresql/16/main \
  --link  # hard links замість копіювання — швидше, але 14 не може запуститися після

Для zero downtime — logical replication між старою та новою версією, переключення при рівності LSN.