Модуль 8. Эксплуатация двух СУБД одновременно

Цель модуля: перевести систему из режима «работает на стенде» в режим «работает годами»: бэкапы с проверенным восстановлением и PITR для обеих СУБД, миграции схем, которые не роняют CDC-пайплайн, мониторинг с алертами на реальные проблемы (bloat, лаг слота, parts), безопасность — и честный выбор между managed и self-hosted.

Перед началом: Модули 2–7 — пайплайн PG → Debezium → Kafka → CH поднят и живёт. Инструменты: pgBackRest (или WAL-G), clickhouse-backup, Prometheus + Grafana, pg_cron, goose. Трудоёмкость: ~8–10 ч (теория 4 ч · практика 3–4 ч · квиз 0.5 ч).

🔥 Инцидент: бэкап, которого не хватило

В ночь на вторник упал мастер PostgreSQL OrderFlow — заказы, платежи, статусы операторов. Реплика в тот момент как раз пересобиралась после профилактики, и принять роль было некому. Поднялись из единственного бэкапа: pg_dump, снятый в 02:00. К 11:30 утра последняя точка восстановления отстояла на девять с половиной часов назад — весь утренний поток заказов, включая оплаченные, пришлось восстанавливать вручную по логам и письмам операторов. Свежих WAL никто не архивировал, восстанавливать промежуток было нечем. И ещё один холодок: бэкап ClickHouse делался скриптом, но на восстановление его не проверял никто ни разу — сколько данных там реально живёт, мы не знали.

Первая реакция — дампы чаще: перешли на pg_dump каждые три часа. Помогло не сильно: бэкап добавляет нагрузку на диск, а точки за последний час всё равно нет — её даёт непрерывный архив WAL, а не расписание.

Модуль — про эксплуатацию, которая живёт годами: pgBackRest/WAL-G и PITR, clickhouse-backup с проверенными ресторами, миграции, не роняющие CDC-пайплайн, мониторинг с алертами на bloat, лаг слота и parts, и безопасность доступа к обоим хранилищам.

📖 8.1. Бэкапы и PITR: pgBackRest/WAL-G и clickhouse-backup

Бэкап — это процедура восстановления

«Бэкап без теста восстановления — не бэкап». Архив на диске — это надежда; бэкап — это проверенный сценарий восстановления с измеренными RTO (сколько восстанавливаемся) и RPO (сколько данных теряем максимум). Обе цифры должны быть записаны в runbook до инцидента, а не после него.

PostgreSQL: базовый бэкап + архив WAL = PITR

PITR (Point-In-Time Recovery) — восстановление на произвольную секунду. Формула: физический базовый бэкап + непрерывный архив WAL. Требования: wal_level = replica и рабочий механизм архивации; pgBackRest и WAL-G забирают WAL сами, без хрупкого archive_command.

pgBackRest — конфиг и ежедневный цикл:

ini
# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-type=s3
repo1-path=/pgbackrest
repo1-s3-endpoint=storage.yandexcloud.net
repo1-s3-bucket=backup-bucket
repo1-retention-full=2        # два полных цикла
repo1-cipher-type=aes-256-cbc
process-max=4
start-fast=y
log-level-console=info

[main]
pg1-path=/var/lib/postgresql/16/main
pg1-user=postgres

bash
# stanza — описание кластера для pgBackRest
pgbackrest --stanza=main stanza-create

# воскресенье: полный; будни: инкрементальные
pgbackrest --stanza=main --type=full backup
pgbackrest --stanza=main --type=incr backup

# инвентаризация хранилища
pgbackrest --stanza=main info

Восстановление на момент «за три минуты до катастрофы»:

bash
pgbackrest --stanza=main --delta \
  --type=time --target='2026-09-15 02:57:00+03' \
  --target-action=promote \
  restore
# затем systemctl start postgresql@16-main

--type=time с --target — это и есть PITR: pgBackRest разворачивает базовый бэкап и проигрывает WAL до указанного момента; --target-action=promote поднимает узел по достижении цели. --delta позволяет восстанавливать поверх существующего каталога, переписывая только изменённые файлы.

WAL-G (родом из Яндекса) — тот же контракт другим вкусом:

bash
# бэкап + листинг
wal-g backup-push /var/lib/postgresql/16/main
wal-g backup-list

# восстановление последнего бэкапа
wal-g backup-fetch /var/lib/postgresql/16/main LATEST

# затем настройки восстановления (postgresql.auto.conf):
#   restore_command = 'wal-g wal-fetch "%f" "%p"'
#   recovery_target_time = '2026-09-15 02:57:00+03'
#   recovery_target_action = 'promote'
# и пустой файл-сигнал recovery.signal в PGDATA

ClickHouse: clickhouse-backup

CH — производная проекция (Модуль 1), но «восстановим перезаливкой» — не отговорка: пересинк сотен миллионов строк кладёт нагрузку на OLTP-мастер, а DROP TABLE без WHERE реплицируется на все реплики мгновенно. Реплика — не защита от ошибки человека.

yaml
# /etc/clickhouse-backup/config.yml (фрагмент)
general:
  remote_storage: s3
clickhouse:
  username: backup
  password: "${CH_BACKUP_PASSWORD}"
s3:
  access_key: "${S3_KEY}"
  secret_key: "${S3_SECRET}"
  bucket: ch-backups
  endpoint: https://storage.yandexcloud.net

bash
# бэкап таблиц аналитики + выгрузка в S3
clickhouse-backup create --tables='analytics.*' daily_20260915
clickhouse-backup upload daily_20260915
clickhouse-backup list

# восстановление на новом узле
clickhouse-backup download daily_20260915
clickhouse-backup restore --schema --data daily_20260915

Стратегия для нашей связки

Тест восстановления — не разовое упражнение, а автоматический ежемесячный сценарий: restore в отдельный инстанс → сверка контрольных сумм → отчёт. Стал красным — вы узнаете об этом в рабочее время, а не в три часа ночи.

📖 8.2. Миграции схем: goose, мутации CH и совместимость в CDC

PostgreSQL: goose

goose — SQL-файлы с секциями Up/Down, версионирование в таблице goose_db_version, возможность зашить миграции в Go-бинарник (embed) и накатывать при старте сервиса.

sql
-- +goose Up
ALTER TABLE orders ADD COLUMN cancel_reason text;

-- +goose StatementBegin
CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders (status, created_at);
-- +goose StatementEnd

-- +goose Down
DROP INDEX idx_orders_status_created;
ALTER TABLE orders DROP COLUMN cancel_reason;

bash
goose -dir migrations postgres "$DATABASE_URL" up
goose -dir migrations postgres "$DATABASE_URL" status

Альтернатива — Atlas: декларативная схема, автоматический diff, план миграции. Оба инструмента валидны; goose выбран за прозрачность — миграция читается как код и ревьюится как код.

В проде миграции накатывает сам сервис при старте, а не отдельный шаг деплоя: SQL-файлы зашиваются в бинарник через embed, и версия схемы всегда соответствует версии кода.

go
// boilerplate/internal/db/migrate.go
package db

import (
	"context"
	"embed"
	"fmt"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/stdlib"
	"github.com/pressly/goose/v3"
)

//go:embed migrations/*.sql
var migrationFS embed.FS

// Migrate накатывает миграции из бинарника. Вызывается в main() до старта
// HTTP-сервера: схема готова к первому запросу.
func Migrate(ctx context.Context, dsn string) error {
	connCfg, err := pgx.ParseConfig(dsn)
	if err != nil {
		return fmt.Errorf("db: parse dsn: %w", err)
	}
	sqlDB := stdlib.OpenDB(*connCfg) // goose работает через database/sql
	defer sqlDB.Close()

	if err := sqlDB.PingContext(ctx); err != nil {
		return fmt.Errorf("db: ping: %w", err)
	}

	goose.SetBaseFS(migrationFS)
	if err := goose.SetDialect("postgres"); err != nil {
		return fmt.Errorf("db: dialect: %w", err)
	}
	if err := goose.UpContext(ctx, sqlDB, "migrations"); err != nil {
		return fmt.Errorf("db: migrate: %w", err)
	}
	return nil
}

Три оговорки, которые спасают прод:

• Таймауты блокировок (lock_timeout) ставят на роль — ALTER ROLE app SET lock_timeout = '2s' (Модуль 2.6), а не SET в коде: SET через пул database/sql действует только на одно соединение из пула и создаёт иллюзию защиты.
• CREATE INDEX CONCURRENTLY не может идти в транзакции, поэтому в goose-файле его оборачивают в -- +goose StatementBegin/End и запускают с -- +goose NO TRANSACTION.
• Миграции в нескольких репликах сервиса: goose берёт advisory-lock на таблицу версий, но при старте десятка подов честнее вынести Migrate в отдельную init-джобу (K8s Job / init-container), а не гонять из каждого пода.

Любое изменение живой таблицы — по паттерну expand/contract:

sql
-- Фаза 1 (expand): nullable или NOT NULL DEFAULT — мгновенно (PG11+)
ALTER TABLE orders ADD COLUMN status_v2 smallint;

-- Фаза 2: бэкфилл БАТЧАМИ по 5–10k, не одним UPDATE по 100 млн строк —
--   каждый батч короткая транзакция, autovacuum дышит

-- Фаза 3: NOT NULL через CHECK: добавить NOT VALID (мгновенно),
--   затем VALIDATE (без долгой блокировки записи)
ALTER TABLE orders ADD CONSTRAINT orders_status_v2_nn
  CHECK (status_v2 IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_v2_nn;

-- Фаза 4 (contract): убрать старую колонку, когда никто её не читает

Цена ошибки «одним ALTER» — блокировка таблицы на всё время бэкфилла и стоп всей записи. CREATE INDEX — только CONCURRENTLY (и помните: она не может идти внутри транзакции — в goose секция помечается -- +goose StatementBegin и запускается с отключённой обёрткой).

ClickHouse: лёгкие ALTER vs мутации

• ALTER TABLE ... ADD COLUMN — metadata-only: мгновенно даже на терабайтах; старые части просто не содержат колонку и отдают значение по умолчанию.
• DROP COLUMN — тоже metadata-only; данные чистятся постепенно при слиянии частей.
• Всё, что переписывает данные — UPDATE, DELETE, MATERIALIZE COLUMN — называется мутацией: асинхронная фоновая перепись всех затронутых частей. На терабайтах это часы дисковой нагрузки и торможение мерджей.

sql
-- посмотреть очередь мутаций
SELECT database, table, mutation_id, command, is_done
FROM system.mutations
WHERE NOT is_done
ORDER BY create_time;

-- разовая чистка допустима; регулярные мутации — сломанный дизайн
ALTER TABLE analytics.events DELETE WHERE user_id = 0;

Правило модуля: мутация — исключительная операция (разовая починка, GDPR-удаление), а не часть бизнес-потока. Регулярные «апдейты в CH» — признак, что эта работа должна жить в PostgreSQL или в синке с версиями (Модуль 6).

Совместимость схем в CDC-пайплайне

Ломающее изменение схемы PG не должно валить Debezium и синк. Политика additive-first:

1. Не переименовываем и не удаляем в один шаг — только добавляем.
2. Порядок: сначала читатели (синк, витрины) умеют работать с новой схемой — и со старой; затем меняется продюсер.
3. Удаление и переименование — последняя фаза contract, когда старую колонку больше никто не читает.
4. Автоматической эволюции схемы на приёмнике Debezium не делает: он честно отдаёт payload со схемой (и историей DDL через schema.history.internal для коннекторов, где она есть). Досоздаёт недостающие колонки на стороне стока свойство sink-коннекторов (auto.evolve у Confluent JDBC Sink, аналоги у Altinity ClickHouse Sink) — и оно не умеет менять primary key и делать даункасты типов. Наш Go-синк (Модуль 6) эволюционирует явно: структура → маппинг → миграция CH.
5. После миграции первые 30 минут смотрим лаг слота и ошибки синка: рассинхрон схем проявляется не в момент ALTER, а на первом сообщении с новой структурой.

📖 8.3. Мониторинг: экспортеры → Prometheus → Grafana

Цепочка: postgres_exporter и clickhouse-exporter отдают метрики, Prometheus собирает, Grafana рисует и будит людей. Инсталляция стандартна; ценность — в том, что вы выводите на главный дашборд и на что настроены алерты.

PostgreSQL: главное

• Подключения по состояниям (active/idle), возраст самых долгих транзакций (pg_stat_activity) — долгая транзакция держит xid-horizon и бьёт по vacuum (Модуль 2).
• Cache hit ratio, WAL-объём в час, lag физических реплик.
• Bloat: n_dead_tup против n_live_tup по таблицам.
• CDC-специфика: репликационные слоты — активность и отставание.

Кастомная метрика слота для postgres_exporter (queries.yaml):

yaml
pg_slot:
  query: |
    SELECT slot_name,
           active::int AS is_active,
           pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)::bigint AS lag_bytes
    FROM pg_replication_slots
  metrics:
    - slot_name: { usage: "LABEL" }
    - is_active:  { usage: "GAUGE" }
    - lag_bytes:  { usage: "GAUGE" }

ClickHouse: главное

• Вставки/сек, латентность SELECT, память по запросам.
• Merge pressure: число активных мерджей и частей на партицию (system.parts) — ранний сигнал «too many parts».
• Очередь мутаций (system.mutations), диск, жив ли Keeper, отставание реплик.

sql
-- ранний сигнал перед Too many parts:
-- порог delay ~150 частей на партицию, throw ~300
SELECT partition, count() AS parts
FROM system.parts
WHERE active AND database = 'analytics' AND table = 'events'
GROUP BY partition
ORDER BY parts DESC
LIMIT 5;

Алерты, которые реально спят спокойно

yaml
groups:
  - name: postgres
    rules:
      - alert: PgBloatHigh
        expr: pg_stat_user_tables_n_dead_tup > 0.2 * clamp_min(pg_stat_user_tables_n_live_tup, 1000)
        for: 1h
        labels: { severity: warning }   # bloat > 20% — vacuum не успевает

      - alert: CdcSlotInactive
        expr: pg_slot_is_active == 0
        for: 5m
        labels: { severity: critical }  # слот неактивен — CDC стоит

      - alert: CdcSlotLagHigh
        expr: pg_slot_lag_bytes > 2e9   # >2GB WAL: риск переполнения диска мастера
        for: 15m
        labels: { severity: critical }

  - name: clickhouse
    rules:
      - alert: ChPartsPerPartitionHigh
        expr: clickhouse_system_parts_active > 1000
        for: 10m
        labels: { severity: warning }   # вставки скоро затормозят

      - alert: ChMutationsStuck
        expr: ch_mutations_pending > 10
        for: 30m
        labels: { severity: warning }   # мутации не завершаются

Про имена метрик честно: pg_slot_* — наш кастомный запрос из queries.yaml выше (стабилен), а clickhouse_system_parts_active — имя из конкретного экспортера; в другом форке оно называется иначе, сверяйтесь со своим /metrics. ch_mutations_pending готового экспортера нет: её отдаёт крошечная Go-джоба курса, которая раз в 30 секунд спрашивает SELECT count() FROM system.mutations WHERE NOT is_done и кладёт значение в Prometheus (исходник — boilerplate/internal/metrics/chmut). Урок внутри урока: если метрики нет — её пишут, а не выдумывают имя в алерте.

Логика критичности — по последствиям. CdcSlotInactive — critical: пайплайн остановлен и WAL копится на мастере. Части — warning: до беды ещё 10–20 минут и можно спокойно настроить батчинг. И правило вкуса: алерт на тренд, а не на абсолют — стабильно 50MB лага слота это не инцидент, а рабочая точка.

📖 8.4. Безопасность: TLS, SCRAM, RLS в PG; users/quotas/profiles в CH

PostgreSQL: транспорт и аутентификация

ini
# postgresql.conf
ssl = on
ssl_cert_file = '/etc/ssl/pg/server.crt'
ssl_min_protocol_version = 'TLSv1.2'
password_encryption = scram-sha-256

conf
# pg_hba.conf: только TLS, только SCRAM, только сеть кластера
hostssl  app         app          10.0.0.0/8  scram-sha-256
hostssl  replicator  replication  10.0.0.0/8  scram-sha-256
hostssl  debezium    app          10.0.0.0/8  scram-sha-256

md5-аутентификация передаёт серверу хэш, эквивалентный паролю: перехвативший может войти с ним повторно. SCRAM-SHA-256 этой дыры не имеет. TLS — всегда, внутри VPC тоже: это гигиена, а не паранойя.

RLS: изоляция тенантов строками

Row Level Security — когда один кластер обслуживает несколько тенантов, БД сама не даст увидеть чужие строки, даже если WHERE забыли в коде:

sql
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;   -- действует и на владельца

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::bigint);

-- приложение (pgx) — на каждый коннект из пула, внутри транзакции:
-- SET LOCAL app.tenant_id = '42';

Честно: RLS — второй рубеж обороны, а не замена фильтрам в коде. С transaction-pooling (Модуль 2.7) дисциплина обязательна: SET LOCAL внутри транзакции — иначе настройка «прилипнет» к следующему чужому запросу.

ClickHouse: пользователи, квоты, профили

sql
-- синк: только вставка; Grafana: только чтение; люди: интерактив
CREATE USER sink     IDENTIFIED WITH sha256_password BY '***';
CREATE USER grafana  IDENTIFIED WITH sha256_password BY '***';
GRANT INSERT, SELECT ON analytics.* TO sink;
GRANT SELECT ON analytics.* TO grafana;

-- квота: защита кластера от «случайного SELECT *» на миллиарды строк
CREATE QUOTA grafana_q
  FOR INTERVAL 1 HOUR MAX queries 2000, MAX read_rows 1e12
  TO grafana;

-- профиль настроек: не дадим себе OOM
CREATE SETTINGS PROFILE analyst_profile
  SETTINGS max_memory_usage = 20e9, max_execution_time = 300
  TO grafana;

Отдельные пользователи для синка (insert-only), Grafana (read-only) и людей — это ещё и наблюдаемость: в system.query_log видно, кто именно выполнил тяжёлый запрос.

Секреты: Lockbox

Секреты не живут в git, в образах и в .env на диске. В Yandex Cloud — Lockbox: версионируемое хранилище секретов; в K8s их синхронизирует External Secrets Operator (ExternalSecret → secretStoreRef: yc-lockbox) в обычный Secret, монтируемый в поды. Ротация пароля подхватывается при следующем refresh; приложение читает креды из окружения, собранного платформой на старте. В git — только ссылки на имена секретов.

📖 8.5. Периодические задачи: pg_cron + pg_partman

pg_cron — cron внутри PostgreSQL. Сам по себе — мелочь; без него скучает pg_partman, которому нужно регулярно создавать партиции наперёд и убирать старые по retention:

sql
CREATE EXTENSION pg_cron;

-- обёртка с защитой от запуска на реплике (см. нюанс ниже)
CREATE OR REPLACE FUNCTION cron_maintenance() RETURNS void
LANGUAGE plpgsql AS $$
BEGIN
  IF pg_is_in_recovery() THEN RETURN; END IF;
  CALL partman.run_maintenance_proc();
END $$;

SELECT cron.schedule('partman-maintenance', '10 3 * * *',
  'SELECT cron_maintenance()');

-- очистка старого аудит-лога (для таблиц БЕЗ партиций)
SELECT cron.schedule('audit-cleanup', '40 3 * * *',
  $$DELETE FROM audit_log WHERE created_at < now() - interval '180 days'$$);

-- что запланировано
SELECT jobid, schedule, command, active FROM cron.job;

Нюансы, о которых вспоминают в три часа ночи:

• pg_cron живёт на том узле, где запущен его воркер. В Patroni-кластере при failover задача не переедет сама. Рабочая схема — выше: pg_cron установлен на всех узлах, а джоб начинается проверкой pg_is_in_recovery() и на реплике тихо выходит.
• DELETE по retention — для непартиционированных таблиц; всё, что можно партиционировать, чистим DROP PARTITION — мгновенно, без bloat (pg_partman делает это сам внутри run_maintenance).
• Не ставьте тяжёлое в окно бэкапа и не запускайте задачи параллельно: pg_cron не знает про вашу матрицу конфликтов.

📖 8.6. Managed vs self-hosted: таблица решений

Правила выбора — без религии:

• Нет выделенных DBA/SRE → managed. Свои кластеры — вторая профессия; «прочитал статью про Patroni» не равно «умею эксплуатировать».
• Проект зависит от экзотики → сначала проверьте список расширений. Нет нужного (свежий pgvector, Citus) — self-hosted или пересмотр архитектуры, но не «возьмём managed и попробуем».
• CH до единиц терабайт и умеренного QPS managed закрывает честно; дальше — свои узлы и тюнинг мерджей.
• Гибрид — норма индустрии: PG managed + CH managed, CDC между ними — ваш код.

И главное: managed снимает эксплуатацию СУБД, но не эксплуатацию данных — бэкап-тесты, миграции без остановки пайплайна и алерты на CDC остаются вашими при любой модели. Весь этот модуль — про то, что остаётся.

💻 Практика

1. Бэкапы PG с PITR-тестом. MinIO/S3 + pgBackRest: stanza, full + incr. Уроните таблицу (DROP TABLE payments), восстановите кластер на момент за минуту до падения, запишите фактический RTO в runbook.
2. Бэкап CH. clickhouse-backup: create + upload + restore на второй узел; сверьте счётчики строк.
3. Алерты. Добавьте три алерта из 8.3 (bloat, slot inactive, parts). Остановите синк — CdcSlotInactive должен сработать; почините — должен resolved.
4. Миграция под живым CDC. goose-миграция ADD COLUMN при работающем Debezium: лаг слота вернулся в норму, синк не упал. Затем contract-фаза — и снова проверка.
5. pg_cron + pg_partman. Retention order_events = 90 дней; старые партиции должны уйти через DROP, а не через DELETE.

🧩 Quiz "Проверь себя"

✅ Итоги модуля

• Настроить pgBackRest: full/incr + PITR-восстановление с --type=time — и проверить его на стенде.
• Снять и восстановить бэкап ClickHouse через clickhouse-backup.
• Проводить миграции по expand/contract и объяснять, почему мутации CH — исключение, а не операция.
• Настроить алерты: bloat, активность и лаг слота, parts на партицию, мутации.
• Включить TLS + SCRAM, RLS для тенантов; развести пользователей/квоты/профили в CH.
• Держать секреты в Lockbox, а не в git.
• Объяснить, когда брать managed, а когда self-hosted — и что managed не снимает.

Шпаргалка модуля

🔗 Мостик к Модулю 9

Система переживает сбои — теперь заставим её переживать рост. Дальше: read-replicas и роутинг чтения, переезд на Citus по шагам, добавление шарда и backfill в ClickHouse, профилирование обеих СУБД (pg_stat_statements против system.query_log), нагрузочные прогоны pgbench и clickhouse-benchmark — и цена трёх классических антипаттернов.