176 lines
9.7 KiB
Markdown
176 lines
9.7 KiB
Markdown
# Стандарт баз данных
|
||
|
||
**Создан:** 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. **Проверять результат, а не факт выполнения команды.**
|