1. Главная
  2. Хаб
  3. Сценарии
  4. Отчёты и BI без нагрузки на прод: реплика PostgreSQL и пользователь только на чтение

Сценарий 24

Отчёты и BI без нагрузки на прод: реплика PostgreSQL и пользователь только на чтение

Создаём реплику для чтения рядом с рабочей базой, заводим для аналитиков пользователя, который может только читать, следим за лагом и подключаем Metabase на отдельном сервере. Тяжёлые отчёты больше не тормозят приложение.

40 минСредний5 шагов PostgreSQL, Серверы обновлено

Содержание
  1. 01Создайте реплику для чтения
  2. 02Заведите пользователя только на чтение
  3. 03Следите за лагом
  4. 04Подключите Metabase к реплике
  5. 05Настройте реплику под долгие отчёты
  6. Проверка результата
  7. Как удалить ресурсы
  8. Типичные ошибки
  9. Сколько стоит

Что получится

Реплика app-db-ro в состоянии «Стриминг» с лагом в секундах, пользователь bi с пресетом «Только чтение», Metabase на сервере, подключённый к реплике, и понятная настройка для долгих отчётов.

Что понадобится

  1. Шаг 1. Создайте реплику для чтения

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

    Консоль

    1. Откройте карточку рабочей базы (app-db). В разделе «Реплики для чтения» нажмите Создать реплику.
    2. Имя app-db-ro. vCPU и RAM — под отчёты, можно больше, чем у источника. Диск, ГБ — не меньше диска основной базы.
    3. Публичный адрес — выключите, если Metabase будет в той же сети проекта; включите, если BI-инструмент работает снаружи.
    4. Задержка применения при долгих запросах — 15 мин для отчётов. Обратная связь с основной базой (hot_standby_feedback) пока не включайте.
    5. Нажмите Создать. Окно покажет «Первичная загрузка займёт около … мин.»

    CLI

    Терминал
    h3 db replica create <db-id> --name app-db-ro --cpu 2 --memory 4Gi --size 20
    h3 db replica list <db-id>

    Terraform

    Terraform
    # провайдер h3llo-cloud/h3 0.3+
    resource "h3_database" "app_ro" {
      project_id = var.project_id
      name       = "app-db-ro"
      engine     = "postgres"
      cpu        = 2
      memory     = "4Gi"
      size       = "20Gi"                # не меньше, чем у источника
      replica_of = h3_database.app.id
    
      replica_settings {
        max_standby_streaming_delay_seconds = 900
        max_standby_archive_delay_seconds   = 900
        hot_standby_feedback                = false
      }
    }

    Проверка

    В списке реплик состояние меняется с «Загрузка данных» на «Стриминг», в колонке «Лаг» — несколько секунд. Реплика отмечена в общем списке баз стрелкой ← app-db.

    Если не получилось

    • «Для реплики нужны бэкапы этой базы и хотя бы одна готовая копия.» — нажмите Открыть «Бэкапы», включите копирование и дождитесь первой копии.
    • «Реплику реплики создать нельзя.» — реплика создаётся только от самостоятельной базы. У одной базы — не больше трёх реплик.
  2. Шаг 2. Заведите пользователя только на чтение

    Аналитикам не нужен пароль владельца. Пресет «Только чтение» даёт SELECT на существующие и будущие таблицы владельца базы — новые таблицы приложения станут видны отчётам без повторной выдачи прав. Пользователь создаётся на источнике и сразу появляется на реплике.

    Консоль

    1. В карточке источника (app-db, не реплики) в строке «Пользователи и базы» нажмите Открыть.
    2. Создать пользователя: Имя пользователя bi, Лимит подключений — 20. В «Доступ к базам» выберите базу shop и уровень «Только чтение». Нажмите Создать.
    3. Скопируйте пароль в окне «Пользователь создан» и нажмите Я сохранил пароль.
    4. Если инструменту мониторинга нужна статистика запросов (pg_stat_statements всех пользователей), в меню пользователя выберите Системные роли и отметьте pg_read_all_stats. Доступа к данным таблиц эта роль не даёт.

    CLI

    Терминал
    echo '{"name":"bi","connectionLimit":20,"grants":[{"database":"shop","preset":"readonly"}]}' \
      | h3 db user create <db-id> --from-file -
    h3 db user credentials <db-id> bi
    # системная роль для мониторинга запросов (без доступа к данным)
    h3 api PATCH /api/databases/v1/<db-id>/users/bi -d '{"systemRoles":["pg_read_all_stats"]}'

    Terraform

    Terraform
    # провайдер h3llo-cloud/h3 0.3+
    resource "h3_database_user" "bi" {
      database_id      = h3_database.app.id   # источник, не реплика
      name             = "bi"
      connection_limit = 20
      grants = [
        { database = "shop", preset = "readonly" },
      ]
    }

    Проверка

    На реплике пользователь bi читает, но не пишет:

    Терминал
    psql "host=<адрес реплики> port=5432 dbname=shop user=bi sslmode=require" <<'SQL'
    SELECT count(*) FROM items;
    SELECT pg_is_in_recovery();          -- t: это реплика
    SQL

    Если не получилось

    • «Реплика только для чтения. Измените это на основной базе.» — пользователя пытаются создать на реплике. Создавайте на источнике.
    • Пароль bi на реплике тот же, что на источнике. Если сбросить его на источнике, реплика получит новый пароль вместе с потоком WAL.
  3. Шаг 3. Следите за лагом

    Лаг — на сколько секунд реплика отстаёт от источника. Для отчётов «за вчера» лаг в минуту не важен, для оперативных дашбордов следите, чтобы он оставался в секундах.

    Консоль

    1. Колонка «Лаг» в разделе «Реплики для чтения» источника и строка «Лаг» на странице реплики показывают последнее значение и время замера.

    CLI

    Терминал
    h3 db replica status <replica-id>        # state STREAMING, lag в секундах
    h3 api GET /api/databases/v1/<replica-id>/replication

    Терминал

    Терминал
    # то же изнутри PostgreSQL, на реплике
    psql "host=<адрес реплики> port=5432 dbname=shop user=bi sslmode=require" \
      -c 'SELECT now() - pg_last_xact_replay_timestamp() AS lag;'

    Проверка

    Состояние «Стриминг», лаг — секунды. «Догоняет из архива» после большой загрузки данных — нормально, реплика вернётся в «Стриминг» сама.

  4. Шаг 4. Подключите Metabase к реплике

    Metabase запускаем на отдельном сервере в Docker и открываем через SSH-туннель, порт 3000 наружу не публикуем. Если сервер в той же сети проекта, подключайтесь к адресу реплики «Внутри VPC» из блока «Подключение» на её странице.

    На сервере
    sudo apt update && sudo apt install -y docker.io
    sudo docker run -d --name metabase --restart unless-stopped \
      -p 127.0.0.1:3000:3000 -v metabase-data:/metabase.db metabase/metabase
    На вашем компьютере: туннель к Metabase
    ssh -N -L 3000:127.0.0.1:3000 user@<IP сервера>
    # затем откройте http://localhost:3000

    Проверка

    В мастере Metabase добавьте базу: тип PostgreSQL, Host — адрес реплики, Port 5432, Database name shop, Username bi, пароль из шага 2, включите Use a secure connection (SSL). Metabase показывает таблицы shop, простой вопрос по таблице items строится.

    Если не получилось

    • Metabase не подключается с ошибкой SSL — в дополнительных параметрах соединения укажите sslmode=require: сертификат базы выпущен внутренним центром сертификации платформы, режим verify-full с публичными корнями не пройдёт.
  5. Шаг 5. Настройте реплику под долгие отчёты

    Когда источник меняет строки, которые читает долгий запрос на реплике, реплика ждёт запрос не дольше заданной задержки, а потом отменяет его. Есть два способа жить с долгими отчётами.

    Увеличить задержку — до 3600 секунд: отчёты до часа не прерываются, но лаг на это время может вырасти. Включить обратную связь (hot_standby_feedback) — запросы не прерываются и лаг не растёт, но источник дольше хранит старые версии строк и его таблицы могут разрастись.

    Консоль

    1. На странице реплики в настройках задержек измените «Задержка при потоковой репликации, с» и «Задержка при догоне из архива, с» (0–3600) и нажмите Сохранить. Применяется без перезапуска.

    CLI

    Терминал
    h3 db settings set <replica-id> --max-standby-streaming-delay 3600 --max-standby-archive-delay 3600
    # или: h3 db settings set <replica-id> --hot-standby-feedback

    Проверка

    Уведомление «Задержки изменены». Отчёт, который раньше падал с canceling statement due to conflict with recovery, доходит до конца.

Проверка результата

Отчёт в Metabase строится на реплике, а на графиках нагрузки источника в карточке базы во время отчёта не видно скачка CPU. Пользователь bi не может изменить данные даже на источнике:

Терминал
psql "host=<IP источника> port=5432 dbname=shop user=bi sslmode=require" -c 'DELETE FROM items;'
# ERROR:  permission denied for table items

Как удалить ресурсы

Удаление реплики не трогает источник.

CLI

Терминал
h3 db delete <replica-id>
h3 db user delete <db-id> bi
  1. Базы данных → app-db-ro → меню «…» → Удалить → Подтвердить.
  2. Пользователя bi удалите в «Пользователи и базы» источника: меню пользователя → Удалить.
  3. Удалите сервер Metabase в Инстансы и освободите его IP.

Типичные ошибки

Чем реплика для чтения отличается от экземпляров отказоустойчивости?

Экземпляры HA (2–3) — копии внутри одной базы для автоматического переключения при отказе, их адрес «Только чтение» тоже подходит для отчётов. Реплика для чтения — отдельная база со своими vCPU, RAM и адресом: её можно сделать мощнее источника и не бояться, что отчёты займут ресурсы резервного экземпляра.

Можно ли сделать реплику самостоятельной базой?

Да: «Сделать самостоятельной базой» на странице реплики (или `h3 db replica promote`). Это необратимо: репликация прекращается, у новой базы включаются свои бэкапы. Удобно для тестовой копии прода.

Нужна ли аналитику роль pg_read_all_data?

Нет: пресет «Только чтение» на нужную базу безопаснее — он не открывает другие базы сервера. Системные роли нужны мониторингу: `pg_monitor` или `pg_read_all_stats`.

Отказоустойчивость для реплики

У реплики один экземпляр. Если реплика недоступна, переключите отчёты на адрес «Только чтение» источника (при 2–3 экземплярах) или на сам источник.

Связанные продукты и материалы

Сколько стоит

≈ 9,93 ₽ в час · 7 146 ₽ в месяц при работе 24/7

vCPU реплики2 × 900 ₽
RAM реплики, ГБ4 × 360 ₽
Диск реплики до 20 000 IOPS*, ГБ20 × 14,40 ₽
vCPU сервера Metabase2 × 900 ₽
RAM сервера Metabase, ГБ4 × 360 ₽
Диск сервера до 20 000 IOPS*, ГБ20 × 14,40 ₽
Публичный IPv4 сервера1 × 90 ₽

Реплика оплачивается как обычная база. Диск реплики не может быть меньше диска основной базы (в примере 20 ГБ).

Цены из прайс-листа, по которому работает биллинг; списание поминутное, месяц — 720 часов. Если удалить ресурсы после проверки, сценарий обойдётся в рубли.

Новым пользователям — 4 000 ₽ на 30 дней на любые сервисы: бонуса хватит, чтобы попробовать этот сценарий целиком. Бонус начисляется после подтверждения телефона и привязки карты.

* IOPS растут с объёмом диска; точные значения для вашего объёма видны в консоли при выборе.