Миграция SQLite → Postgres в alatyr-service — runbook (CRM-17)¶

Контекст: сейчас alatyr-service использует SQLite (better-sqlite3 + drizzle-orm/better-sqlite3). CRM-модуль (объекты, клиенты, заявки, счета, акты, интеграции с МД/ЮКассой) — растущая нагрузка, нужен Postgres:

Concurrency (SQLite при > 5 писателях начинает лочить)
Полноценные JSON-типы (JSONB для settings, metadata)
Точный decimal (не float как в SQLite) для денежных сумм
Full-text search (ts_vector) для поиска по клиентам/заявкам
Готовность к горизонтальному масштабированию

Вариант миграции: А — на VPS отдельным контейнером crm-postgres (порт 5433). Не переиспользуем rag-postgres на ragserver — разная граница безопасности.

Разделение работы: - Агент подготовил — compose, .env-template, drizzle-конфиг, db.ts, runbook - Ты выполнишь — pg_dump/restore, обновление кода alatyr-service, ротация DATABASE_URL, тест продакшена

Что уже готово в skeletons/¶

postgres-crm-compose.yml — docker-compose для VPS
postgres-crm-env.example — шаблон .env
drizzle-config-postgres.ts — новый drizzle.config
db-index-postgres.ts — новый server/db.ts

Порядок работы (24 часа с моментами простоя ~15 мин)¶

Фаза 1 · Подготовка Postgres на VPS (агент помог, ты выполняешь)¶

1.1 · Создать директорию¶

# На VPS, от root:
sudo mkdir -p /opt/alatyr-service/db/{data,init}
sudo chown -R root:root /opt/alatyr-service/db
sudo chmod 700 /opt/alatyr-service/db

1.2 · Скопировать compose и .env¶

# Скопируй скелет:
sudo cp /path/to/alatyr-infra-kb/skeletons/postgres-crm-compose.yml \
    /opt/alatyr-service/db/docker-compose.yml
sudo cp /path/to/alatyr-infra-kb/skeletons/postgres-crm-env.example \
    /opt/alatyr-service/db/.env
sudo chmod 600 /opt/alatyr-service/db/.env

# Сгенерировать пароль (32-символьный случайный):
openssl rand -base64 32 | tr -d '/+=' | head -c 32
# Скопируй, положи в /opt/alatyr-service/db/.env → POSTGRES_PASSWORD=
# И сразу в Vaultwarden: запись "alatyr-service · Postgres"
sudo nano /opt/alatyr-service/db/.env

1.3 · Стартануть Postgres¶

cd /opt/alatyr-service/db
sudo docker compose up -d
sleep 15
sudo docker compose ps
# Ожидание: crm-postgres Up X seconds (healthy)

# Проверить подключение:
sudo docker exec -it alatyr-crm-postgres psql -U alatyr_crm -d alatyr_crm -c '\dt'
# Ожидание: пустая БД, "Did not find any relations."

Фаза 2 · Экспорт из SQLite (ты)¶

2.1 · Локально или на VPS — где хостится текущий alatyr-service¶

# Найти путь к SQLite базе:
find / -name "*.db" -path "*/alatyr-service/*" 2>/dev/null
# Обычно: /opt/alatyr-service/data.db или /opt/alatyr-service/dist/data.db
SQLITE_DB=/opt/alatyr-service/data.db

# Бэкап до миграции:
sudo cp "$SQLITE_DB" "$SQLITE_DB.backup-$(date +%Y%m%d)"

# Дамп в SQL:
sudo sqlite3 "$SQLITE_DB" .dump > /tmp/alatyr-service-sqlite-dump.sql

# Размер:
wc -l /tmp/alatyr-service-sqlite-dump.sql
ls -lh /tmp/alatyr-service-sqlite-dump.sql

2.2 · Инспекция SQL перед конвертацией¶

head -50 /tmp/alatyr-service-sqlite-dump.sql
# Смотрим типы, foreign keys, специфику SQLite

Ключевые различия SQLite ↔ Postgres, которые придётся править вручную:

SQLite	Postgres
`INTEGER PRIMARY KEY AUTOINCREMENT`	`SERIAL PRIMARY KEY` или `BIGSERIAL`
`TEXT`	`TEXT` (совместимо)
`REAL`	`DOUBLE PRECISION` или `NUMERIC(precision, scale)`
`BLOB`	`BYTEA`
`INTEGER` (для дат)	`TIMESTAMP WITH TIME ZONE` или `BIGINT` (для unix ms)
`PRAGMA ...`	не поддерживается, удалить
`INSERT OR REPLACE`	`INSERT ... ON CONFLICT DO UPDATE`
Отсутствие `SCHEMA`	добавить `SET search_path TO public;`

Фаза 3 · Схема через drizzle (ты — самый ответственный шаг)¶

3.1 · Обновить `alatyr-service/shared/schema.ts`¶

Твой текущий schema.ts использует sqliteTable. Меняем на pgTable:

// БЫЛО:
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

// СТАЛО:
import { pgTable, text, serial, integer, timestamp, jsonb, decimal } from 'drizzle-orm/pg-core';

// БЫЛО:
export const clients = sqliteTable('clients', {
    id: integer('id').primaryKey({ autoIncrement: true }),
    createdAt: integer('created_at').notNull().$defaultFn(() => Date.now()),
    // ...
});

// СТАЛО:
export const clients = pgTable('clients', {
    id: serial('id').primaryKey(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    // ...
});

Замены типов — таблица выше.

Обрати внимание — если ты уже включил skeletons/schema-crm.ts (задача 39a), он уже написан под pg-core, дополнительно ничего делать не надо.

3.2 · Обновить `drizzle.config.ts`¶

# В корне alatyr-service:
cp /path/to/alatyr-infra-kb/skeletons/drizzle-config-postgres.ts drizzle.config.ts

3.3 · Обновить `server/db.ts` (или где ты держишь drizzle-клиент)¶

# В alatyr-service/server/db.ts:
cp /path/to/alatyr-infra-kb/skeletons/db-index-postgres.ts server/db.ts

3.4 · Package.json — новые зависимости¶

cd /path/to/alatyr-service
npm uninstall better-sqlite3 @types/better-sqlite3
npm install pg drizzle-orm
npm install -D @types/pg

3.5 · Сгенерировать миграцию¶

# Установи DATABASE_URL временно для локальной генерации:
# Только для db:generate — миграцию можно сгенерить и не подключаясь к БД
export DATABASE_URL='postgresql://alatyr_crm:PASSWORD@127.0.0.1:5433/alatyr_crm'
npm run db:generate

# Изучи что drizzle-kit сгенерил:
ls drizzle/*.sql
head -100 drizzle/0000_*.sql

⚠️ Внимательно прочти сгенерированный SQL. Drizzle может пропустить нюансы: - Индексы (UNIQUE, GIN для JSONB) - CHECK constraints - Trigger'ы (например, updated_at через plpgsql)

Дополни файл миграции руками, где нужно.

3.6 · Применить миграцию на VPS Postgres¶

Через VPN с ноутбука:

export DATABASE_URL='postgresql://alatyr_crm:PASSWORD@127.0.0.1:5433/alatyr_crm'
# ⚠️ 127.0.0.1 работает только если ты внутри VPS. Иначе — SSH-туннель:
# ssh -L 5433:127.0.0.1:5433 root@46.17.99.183

npm run db:migrate
# Проверка:
psql "$DATABASE_URL" -c '\dt'
# Ожидание: все таблицы созданы

Фаза 4 · Перенос данных (ты)¶

4.1 · Стратегия¶

Вариант А — pgloader:

sudo apt install pgloader
pgloader sqlite:///opt/alatyr-service/data.db postgresql://alatyr_crm:PASSWORD@127.0.0.1:5433/alatyr_crm

pgloader сам конвертит типы. Но может не понять кастомные схемы.

Вариант Б — ручной sed по SQL-дампу:

# Убрать SQLite-специфичные PRAGMA и BEGIN TRANSACTION:
sed -i '/^PRAGMA /d; /^BEGIN TRANSACTION;/d; /^COMMIT;/d' /tmp/dump.sql
# Заменить AUTOINCREMENT:
sed -i 's/INTEGER PRIMARY KEY AUTOINCREMENT/BIGINT/g' /tmp/dump.sql
# ... и так далее
# Затем: psql "$DATABASE_URL" < /tmp/dump.sql

Вариант В — TypeScript-скрипт scripts/migrate-data.ts: - Читает SQLite напрямую через better-sqlite3 (одноразово) - Пишет в Postgres через drizzle - Явно контролирует конверсию типов - Логирует прогресс, retry-логика

Для мелкой БД (< 10 MB) — вариант Б проще. Для крупной или сложной — В.

4.2 · Проверка целостности¶

После миграции — сравнить количество строк:

# В SQLite:
for table in clients orders invoices; do
    echo -n "SQLite $table: "
    sudo sqlite3 /opt/alatyr-service/data.db "SELECT COUNT(*) FROM $table;"
done

# В Postgres:
for table in clients orders invoices; do
    echo -n "Postgres $table: "
    psql "$DATABASE_URL" -Atc "SELECT COUNT(*) FROM $table;"
done

Числа должны совпасть до строки.

Фаза 5 · Переключение alatyr-service (downtime ~5 минут)¶

5.1 · Обновить `.env` на VPS¶

sudo nano /opt/alatyr-service/.env
# Заменить (или добавить):
DATABASE_URL='postgresql://alatyr_crm:PASSWORD@127.0.0.1:5433/alatyr_crm?sslmode=disable'

⚠️ Записать в Vaultwarden — запись "alatyr-service · DATABASE_URL".

5.2 · Задеплоить новый код¶

# Локально:
git add -A
git commit -m "Миграция SQLite → Postgres: drizzle-kit config, server/db, package.json"
git push origin main
# GitHub Actions задеплоит на VPS

5.3 · Проверить работу¶

# На VPS:
sudo systemctl status alatyr-service
sudo journalctl -u alatyr-service -n 100
# Ожидание: приложение стартовало, коннект к Postgres OK

# HTTP-тест:
curl -sSI https://alatyr-service.ru/health
# Ожидание: HTTP/2 200

# Функциональный тест — открыть UI, залогиниться, посмотреть список клиентов

5.4 · Если сломалось — откат¶

# На VPS быстро откатить DATABASE_URL:
sudo sed -i 's|^DATABASE_URL=postgresql|DATABASE_URL=sqlite:///opt/alatyr-service/data.db\n#OLD-POSTGRES-DATABASE_URL=postgresql|' /opt/alatyr-service/.env
sudo systemctl restart alatyr-service
# И откатить git:
git revert HEAD
git push

Фаза 6 · Cleanup (после 1 недели стабильной работы)¶

6.1 · Удалить SQLite¶

# ТОЛЬКО после того как убедился что Postgres работает 7+ дней:
sudo mv /opt/alatyr-service/data.db /opt/alatyr-service/data.db.legacy-$(date +%Y%m%d)
# Оставь ещё месяц как страховку, потом удали.

6.2 · Убрать SQLite зависимости¶

Уже сделано в 3.4. Проверь package.json — не осталось ли better-sqlite3.

6.3 · Обновить бэкап-логику¶

⚠️ Важно: локальный backup.sh alatyr-service (если такой был для data.db) больше не работает. Настрой:

Postgres bэкап на VPS: pg_dump через docker exec
Куда: /opt/alatyr-service/db/backups/
Частота: ежедневно 04:30 (после RAG-бэкапа 04:00)
Retention: 30 дней локально + off-site на ragserver

Заведи отдельную задачу в TODO — "Задача 47: Бэкапы crm-postgres".

Диагностика¶

Симптом	Причина	Действия
`connection refused 127.0.0.1:5433`	Postgres не стартовал	`docker compose logs crm-postgres`
`password authentication failed`	Пароль в приложении не совпадает	Проверить `.env` в /opt/alatyr-service/ и /opt/alatyr-service/db/
`relation "clients" does not exist`	Миграция не применилась	`npm run db:migrate` вручную
Приложение работает, но пусто	Данные не перенесены	Проверить COUNT(*) — фаза 4.2
`too many connections`	Пул не закрывается	`SELECT COUNT(*) FROM pg_stat_activity;`, проверить `max: 5` в db.ts

Что дальше — после успешной миграции¶

Разблокирует: - Задачи 41b/41c/41d (МД sync — им нужен Postgres для транзакционности) - Возможность использовать ts_vector для поиска клиентов - JSONB для гибких настроек (тарифы, метаданные)

Возможно — переиспользовать этот compose для второго сервиса (если появится). Но лучше отдельный инстанс Postgres на сервис — разные жизненные циклы, разные пароли, изоляция.

Автор: агент, 08.08.2026