Администрирование базы данных 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

Бэкапы нужно тестировать восстановлением — хотя бы раз в месяц запускать pg_restore в изолированное окружение.

Репликация (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 нельзя запустить после

Для нулевого downtime — logical replication между старой и новой версией, переключение при равенстве LSN.