Files
buryan-infra/docs/DATABASE-STANDARD.md

176 lines
9.7 KiB
Markdown
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
# Стандарт баз данных
**Создан:** 2026-10-14
**Относится к:** централизация данных в CT 151 (задача №128)
**Статус:** действует. Все новые базы создаются по этому стандарту.
---
## 1. Зачем стандарт
Сегодня данные проекта разбросаны: часть сервисов работает с центральной
PostgreSQL, часть держит собственные файловые базы. У центральной базы
кодировка `SQL_ASCII`, из-за чего русский текст сравнивается и сортируется
неправильно. Копия базы хранится в единственном экземпляре.
Цель — **одно место хранения, одна схема именования, одна модель
обслуживания**. Сервисы остаются разными, но работа с данными у них
устроена одинаково.
---
## 2. Кластер
| Параметр | Значение |
|---|---|
| Расположение | CT 151, `postgres-rbac-lxc`, 192.168.1.151 |
| Версия | PostgreSQL **17** |
| Порт | 5432 |
| Кодировка | **UTF8** (обязательно) |
| Сравнение | `C` |
**Один кластер.** Второй, версии 14, остановлен и подлежит разбору: он
остался от обновления версии и только путает.
---
## 3. Именование
| Что | Правило | Пример |
|---|---|---|
| База | `<сервис>_db` | `gitea_db`, `kuma_db` |
| Роль | `<сервис>_user` | `gitea_user`, `kuma_user` |
| Схема | стандартная `public`, отдельные схемы не создаём | — |
| Таблицы | отдаются приложению, мы их не переименовываем | — |
**Одна база — один сервис.** Двух сервисов в одной базе быть не должно:
иначе нельзя ни восстановить по отдельности, ни выдать права точечно.
**Исключения** (уже существуют, переименование дороже пользы):
`n8n`, `n8n_central`, `n8n_prod`, `gitea`, `headscale`, `authentik`,
`buryan_monitoring`, `buryan_monitor`, `buryan_hub`, `postgres`.
---
## 4. Владелец и права
1. Владелец базы — роль сервиса, **не `postgres`**. Иначе сервис не сможет
обслуживать свои таблицы, а `postgres` обрастает лишними правами.
2. Пароль хранится только в двух местах: в `.env` сервиса с правами 600
и в Vaultwarden. В репозитории — никогда.
3. Проверка пароля — `scram-sha-256`. Устаревший `md5` выводим из употребления.
4. Доступ — **по адресу контейнера**, а не по всей подсети. Правило
`host all all 192.168.1.0/24 md5` будет заменено на точечные записи.
5. **Новая база — сразу права.** Бот и n8n ходят под ролью `n8n`, и таблицу,
созданную другим владельцем, они не увидят. Эта ошибка уже стоила вечера
14.10.2026: кнопка одобрения обновлений молча не работала.
```sql
-- шаблон создания базы сервиса
CREATE ROLE <сервис>_user LOGIN PASSWORD '<пароль>';
CREATE DATABASE <сервис>_db OWNER <сервис>_user ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;
```
---
## 5. Кодировка
**Только UTF8.** Сейчас 15 баз из 18 имеют `SQL_ASCII`. Это не «почти то же
самое» — проверено на живой базе:
```
длина строки «Привет» = 12 (а должна быть 6)
upper('Привет') = 'Привет' (регистр не меняется)
```
При `SQL_ASCII` база хранит байты как есть: не проверяет кодировку, не
переводит регистр для кириллицы, неправильно сортирует и сравнивает
русский текст. Для проекта, где данные на русском, это дефект.
**Порядок исправления:** выгрузка базы → создание новой с UTF8 → загрузка →
проверка сервиса → удаление старой. По одной базе, с проверкой.
---
## 6. Резервное копирование
| Требование | Как выполняется |
|---|---|
| Ежедневная выгрузка всех баз | `pg-backup-ct151.sh`, 02:00 |
| **Несколько копий, а не одна** | хранить 7 ежедневных и 4 недельных |
| Проверка годности копии | проверять целостность архива и наличие баз внутри |
| **Проверка восстановления** | раз в месяц разворачивать копию в отдельную базу и сверять число таблиц |
| Копия вне этого сервера | внешнее копирование + съёмный носитель |
**Сегодня хранится одна копия.** Если она испортится, восстанавливать будет
не из чего. Это исправляется в первую очередь.
Проверка копии обязательна: 14.10.2026 выяснилось, что «успешно созданные»
копии контейнеров две недели были пустыми, и никто этого не замечал.
---
## 7. Что переносим и в каком порядке
### Уже в центральной базе (18 баз)
`immich_db`, `n8n`, `matrix_db`, `gitea`, `authentik_db`, `paperless_db`,
`nextcloud_db`, `n8n_central`, `buryan_monitoring`, `n8n_prod`, `mealie_db`,
`firefly_db`, `matrix_telegram_db`, `buryan_monitor`, `buryan_hub`,
`authentik`, `headscale`, `postgres`
### Осталось на файлах
| Сервис | Файл | Размер | Поддержка PostgreSQL |
|---|---|---|---|
| Uptime Kuma | `kuma.db` | 160 МБ | да, во второй версии |
| Gitea | `gitea.db` | 15 МБ | да — и база в PG уже есть |
| Sonarr | `sonarr.db` | 3.2 МБ | да |
| Radarr | `radarr.db` | 2.0 МБ | да |
| Grafana | `grafana.db` | 1.5 МБ | да |
| Mealie | `mealie.db` | 1.3 МБ | Перенесён, файл — остаток |
| n8n | `database.sqlite` | 99 МБ | Перенесён, файл — остаток |
| Linkding | `db.sqlite3` | 264 КБ | нет |
| Vaultwarden | `db.sqlite3` | 288 КБ | да |
| Prowlarr | `prowlarr.db` | 372 КБ | да |
| Bazarr | `bazarr.db` | 368 КБ | да |
| Tautulli | `tautulli.db` | 280 КБ | нет |
| Navidrome | `navidrome.db` | 596 КБ | нет |
| Jellyfin | `jellyfin.db` | 452 КБ | нет |
| NPM | `database.sqlite` | 124 КБ | нет |
| FreshRSS | `app.db` | 120 КБ | да |
| OpenClaw | `openclaw.sqlite` | 1.1 МБ | нет |
**Порядок:**
1. **Остатки после прошлых переносов** — убрать (n8n 99 МБ, mealie, paperless).
Сначала убедиться, что сервис действительно работает с центральной базой.
2. **Gitea** — главный сервис, база в PostgreSQL уже создана и ждёт.
3. **Vaultwarden** — хранилище паролей, ценность высокая.
4. **Grafana** — простая, безопасная проверка схемы переноса.
5. **Медиа-сервисы** (Sonarr, Radarr, Prowlarr, Bazarr) — однотипные.
6. **Остальные по возможности** — часть сервисов PostgreSQL не поддерживает,
для них честно фиксируем: остаются на файлах, но с копированием.
---
## 8. Реестр баз
Каждая база описана в одном месте — таблица `db_registry` в
`buryan_monitoring`. Там видно: сервис, движок, где лежит, размер, владелец,
кодировка, состояние переноса и когда проверялась копия.
Без реестра через месяц никто не вспомнит, что где лежит и что уже перенесено.
---
## 9. Правила, которые дорого дались
1. **Копия без проверки — не копия.** Две недели «успешных» копий оказались
пустыми.
2. **Новая таблица или база — сразу права** тому, кто с ней работает.
3. **Одна копия — не резерв.** Нужны несколько и на разных носителях.
4. **Переносить по одной базе с проверкой сервиса**, а не всё разом.
5. **Кодировку задавать при создании.** Сменить её потом — это перенос.
6. **Проверять результат, а не факт выполнения команды.**