marco
4ee6de2548
- CpsActivityUpdate schema + repo update_activity(部分更新,非 None 覆盖) - router 按「合并后最终值」校验平台必填项(同新建口径),写审计 cps.activity.update - 可改名/平台/对应字段/落地页图/备注/状态 Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
285 lines
11 KiB
Markdown
285 lines
11 KiB
Markdown
# SQLite → PostgreSQL 迁移指南
|
|
|
|
> 上线前把数据库从 SQLite 切到 PostgreSQL。本服务自始就为这一步预留了路径(`db/session.py` 的 dialect 特判 / `alembic/env.py` 的 batch mode 开关 / SQLAlchemy 2.0 + Alembic),代码改动很小。
|
|
>
|
|
> **相关文档**:
|
|
> - 这份文档讲 **切引擎的完整步骤**(本机装 PG / 建库 / `scripts/init_postgres.py` 用法 / 生产切换)
|
|
> - [数据库迁移.md](./数据库迁移.md) 讲 **alembic 操作**(如何建表/升级/新增迁移/多 head 排查/迁移链 12 条)
|
|
> - [后端技术实现.md §10](../后端技术实现.md) 列待办与已知问题(含 `init_postgres.py` 已知小 bug)
|
|
>
|
|
> **背景假设**:当前生产**无真实用户数据**(MVP 阶段),所以整个迁移本质是"切引擎",不涉及数据搬迁。如果将来已有真实数据,本文档不适用,需补充 `pgloader` 演练 + 钱表金额逐行核对 + 停服窗口 + 回滚预案。
|
|
>
|
|
> 最后更新:2026-05-31
|
|
|
|
---
|
|
|
|
## 0. 为什么切
|
|
|
|
SQLite 三个硬伤决定它不能上线:
|
|
|
|
- **并发写锁库**:整个文件一把锁,多请求并发写会排队,极易超时。当前 uvicorn `--workers 1` 勉强能跑,扩 worker 立刻撞墙。
|
|
- **类型宽松**:整数列能塞字符串、`CHECK` 约束当装饰品。钱表(`wallet` / `withdraw_order` / `cash_transaction`)用 SQLite 等于裸奔。
|
|
- **无 JSONB / 无并发索引 / 无分区**:PG 的核心能力一个都享受不到。`savings.dishes` 现在用 SQLite 的 `JSON`(实际存 TEXT),无索引、无操作符,纯展示用。
|
|
|
|
PG 默认上 16 版(工具链最齐),驱动用 **psycopg3**(SQLAlchemy 2.0 时代默认,不要再装 psycopg2)。
|
|
|
|
---
|
|
|
|
## 1. 本地起 PG + 跑通空库(半天)
|
|
|
|
### 1.1 装 PG
|
|
|
|
macOS:
|
|
```bash
|
|
brew install postgresql@16
|
|
brew services start postgresql@16
|
|
```
|
|
|
|
Linux(Ubuntu/Debian):
|
|
```bash
|
|
sudo apt install postgresql-16
|
|
sudo systemctl enable --now postgresql
|
|
```
|
|
|
|
### 1.2 建库 + 用户 + 授权
|
|
|
|
```bash
|
|
psql postgres <<'EOF'
|
|
CREATE USER shaguabijia_app WITH PASSWORD 'change-me-strong-random';
|
|
CREATE DATABASE shaguabijia OWNER shaguabijia_app ENCODING 'UTF8';
|
|
GRANT ALL PRIVILEGES ON DATABASE shaguabijia TO shaguabijia_app;
|
|
EOF
|
|
```
|
|
|
|
密码用 `python -c "import secrets; print(secrets.token_urlsafe(32))"` 生成,**不要复用 JWT_SECRET_KEY**。
|
|
|
|
### 1.3 装驱动
|
|
|
|
```bash
|
|
pip install "psycopg[binary]>=3.1"
|
|
```
|
|
|
|
`psycopg[binary]` 是预编译版,免装 libpq 头文件。生产同款,无需源码编译版。
|
|
|
|
### 1.4 改 `.env`
|
|
|
|
```ini
|
|
DATABASE_URL=postgresql+psycopg://shaguabijia_app:<password>@localhost:5432/shaguabijia
|
|
```
|
|
|
|
> ⚠️ URL scheme 必须是 `postgresql+psycopg://`(显式声明 psycopg3),不能写成 `postgresql://`——SQLAlchemy 默认会去找 psycopg2,装的不一致就报 ModuleNotFoundError。
|
|
|
|
### 1.5 建表
|
|
|
|
```bash
|
|
alembic upgrade head
|
|
```
|
|
|
|
### 1.6 验证
|
|
|
|
```bash
|
|
psql -U shaguabijia_app -d shaguabijia -c "\dt"
|
|
```
|
|
|
|
应该列出全部 9 张表 + `alembic_version`:
|
|
```
|
|
user, coin_account, coin_transaction, cash_transaction,
|
|
withdraw_order, signin_record, task_claim_record,
|
|
savings_record, ad_reward_record, alembic_version
|
|
```
|
|
|
|
再手动 INSERT 一行 user,确认 `created_at` 默认值正常落:
|
|
```sql
|
|
INSERT INTO "user" (phone, register_channel, status) VALUES ('13800138000', 'sms', 'active');
|
|
SELECT id, phone, created_at, last_login_at FROM "user";
|
|
```
|
|
|
|
`created_at` 应该是当前时间戳带时区,不是 NULL。`user` 表名是 PG 关键字,**必须用双引号**包裹。
|
|
|
|
---
|
|
|
|
## 2. 测试套件全绿(1 小时)
|
|
|
|
### 2.1 测试连 PG
|
|
|
|
测试默认连 SQLite。临时让 `tests/conftest.py` 把 `DATABASE_URL` 也指到本地 PG(可以建个 `shaguabijia_test` 库专门跑测试,免得污染 dev 库)。
|
|
|
|
```bash
|
|
createdb -O shaguabijia_app shaguabijia_test
|
|
DATABASE_URL=postgresql+psycopg://shaguabijia_app:xxx@localhost:5432/shaguabijia_test pytest
|
|
```
|
|
|
|
### 2.2 处理失败用例
|
|
|
|
**所有红色测试都不能放过**——SQLite 类型/约束都很宽松,PG 严格起来暴露的就是真 bug。常见问题:
|
|
|
|
- **字符串和整数比较**:SQLite 允许 `WHERE phone = 13800138000`(自动转字符串),PG 直接报错。改成 `:phone` 参数 + 类型严格
|
|
- **timezone 处理**:SQLite 存 naive datetime,PG 的 `TIMESTAMPTZ` 必须带时区。检查所有 `datetime.utcnow()`,改成 `datetime.now(timezone.utc)`
|
|
- **隐式 commit**:SQLite 某些情况下隐式 commit,PG 严格事务边界。如果某用例报 `current transaction is aborted`,说明业务代码缺 commit/rollback
|
|
|
|
### 2.3 验证
|
|
|
|
`pytest` 全绿,**且本地起 `./run.sh` 后用 Apifox 调通登录 / 美团 feed / 领券透传三个真接口**。
|
|
|
|
---
|
|
|
|
## 3. 代码扫雷(1 天)
|
|
|
|
### 3.1 必改清单
|
|
|
|
| 文件 / 位置 | 改动 | 原因 |
|
|
|---|---|---|
|
|
| `app/models/savings.py` | `from sqlalchemy import JSON` → `from sqlalchemy.dialects.postgresql import JSONB`;`mapped_column(JSON, ...)` → `mapped_column(JSONB, ...)` | JSONB 在 PG 上有 GIN 索引和操作符支持,是 PG 核心优势之一。SQLite 上 `JSON` 实际是 TEXT,切到 PG 后用 `JSON` 类型存的是 `json` 不是 `jsonb`,查询/索引能力差一大截 |
|
|
| `app/db/session.py` 的 `create_engine` 调用 | 增加连接池参数:`pool_size=10, max_overflow=20, pool_recycle=3600` | SQLite 单文件不需要池,PG 必须显式池化。`pool_recycle=3600` 防 PG 主动断开的 idle 连接 |
|
|
| `alembic/versions/` 下旧迁移里的 `sa.text('(CURRENT_TIMESTAMP)')` | **不要改** | PG 能接受这个语法。改了反而搞乱迁移历史 + 让 alembic 觉得 schema drift。新写的迁移统一用 `sa.func.now()` |
|
|
|
|
### 3.2 保留的 dialect 特判
|
|
|
|
下面这些**保留原样**,它们的 SQLite 分支在 PG 下自动绕过、不影响新部署,但本地开发想用 SQLite 临时跑还能用:
|
|
|
|
- `app/db/session.py` 里的 `_ensure_sqlite_dir` 和 `check_same_thread` 特判
|
|
- `alembic/env.py` 里的 `render_as_batch` 特判
|
|
|
|
### 3.3 顺便清掉
|
|
|
|
`app/main.py` 启动日志里如果打 `DATABASE_URL`,确认密码不会进日志(只打 scheme + host,不打完整 URL)。
|
|
|
|
### 3.4 验证
|
|
|
|
本地 PG 起 `./run.sh`,跑一遍下面的烟测脚本:
|
|
|
|
```bash
|
|
# 登录
|
|
curl -X POST http://localhost:8770/api/v1/auth/sms/send -d '{"phone":"13800138000"}'
|
|
curl -X POST http://localhost:8770/api/v1/auth/sms/login -d '{"phone":"13800138000","code":"123456"}'
|
|
# 拿到 access_token 后调 me
|
|
curl -H "Authorization: Bearer <token>" http://localhost:8770/api/v1/auth/me
|
|
# 美团 feed
|
|
curl -X POST http://localhost:8770/api/v1/meituan/feed -d '{"longitude":116.4,"latitude":39.9,"page":0}'
|
|
```
|
|
|
|
三条都 200 即通过。`psql` 看 `user` 表多了一条 `13800138000`。
|
|
|
|
---
|
|
|
|
## 4. 生产切换(半小时,无需停服窗口)
|
|
|
|
**前提**:生产无真实数据。如已有数据,本节不适用。
|
|
|
|
### 4.1 装 PG
|
|
|
|
```bash
|
|
ssh server
|
|
sudo apt install postgresql-16
|
|
sudo systemctl enable --now postgresql
|
|
```
|
|
|
|
### 4.2 建库
|
|
|
|
```bash
|
|
sudo -u postgres psql <<'EOF'
|
|
CREATE USER shaguabijia_app WITH PASSWORD '<strong-random-password>';
|
|
CREATE DATABASE shaguabijia OWNER shaguabijia_app ENCODING 'UTF8';
|
|
GRANT ALL PRIVILEGES ON DATABASE shaguabijia TO shaguabijia_app;
|
|
EOF
|
|
```
|
|
|
|
生产密码**单独生成**,不要复用本地的。
|
|
|
|
### 4.3 装驱动到生产 venv
|
|
|
|
```bash
|
|
cd /opt/shaguabijia-app-server
|
|
.venv/bin/pip install "psycopg[binary]>=3.1"
|
|
```
|
|
|
|
### 4.4 改生产 `.env`
|
|
|
|
```ini
|
|
DATABASE_URL=postgresql+psycopg://shaguabijia_app:<password>@localhost:5432/shaguabijia
|
|
```
|
|
|
|
### 4.5 建表 + 重启
|
|
|
|
```bash
|
|
.venv/bin/alembic upgrade head
|
|
sudo systemctl restart shaguabijia-app-server
|
|
sudo journalctl -u shaguabijia-app-server -f
|
|
```
|
|
|
|
启动日志里看到 `Application startup complete` + 没有 5xx 即通过。
|
|
|
|
### 4.6 验证
|
|
|
|
- 用真账号(手机号)走一遍极光登录或短信登录
|
|
- 调 `GET /api/v1/auth/me`,确认返回当前用户
|
|
- 调 `POST /api/v1/meituan/feed`,确认券列表正常
|
|
- `psql -U shaguabijia_app -d shaguabijia -c 'SELECT id, phone, created_at FROM "user"'` 看到刚登录的用户
|
|
|
|
### 4.7 老 SQLite 文件归档
|
|
|
|
```bash
|
|
cd /opt/shaguabijia-app-server
|
|
mv data/app.db data/app.db.legacy-$(date +%Y%m%d)
|
|
```
|
|
|
|
不要删——万一要回查测试期残留数据(开发期可能塞了一些手工记录),保留几个月。
|
|
|
|
---
|
|
|
|
## 5. 兜底:切换失败如何回滚
|
|
|
|
万一改完起不来:
|
|
|
|
```bash
|
|
# 1. .env 改回 SQLite
|
|
sed -i 's|^DATABASE_URL=postgresql.*|DATABASE_URL=sqlite:///./data/app.db|' /opt/shaguabijia-app-server/.env
|
|
|
|
# 2. 把归档的 sqlite 文件还原(如果已经改名)
|
|
mv data/app.db.legacy-* data/app.db
|
|
|
|
# 3. 重启
|
|
sudo systemctl restart shaguabijia-app-server
|
|
```
|
|
|
|
由于 SQLite 文件没动过,无数据丢失,损失只是切换过程中的几分钟 5xx。
|
|
|
|
---
|
|
|
|
## 6. 顺便建议:同时装上 Redis
|
|
|
|
PG 上线后,Redis 是下一个明确要补的基础设施(已在 [待办与技术债.md](../guides/待办与技术债.md) 里列了用途)。**建议本次维护窗口顺手把 Redis 也装上**,不开始用、只装服务,免得下次还要单独申请部署:
|
|
|
|
```bash
|
|
sudo apt install redis-server
|
|
sudo systemctl enable --now redis-server
|
|
# 改 /etc/redis/redis.conf:bind 127.0.0.1(只本机访问)+ requirepass <strong-password>
|
|
sudo systemctl restart redis-server
|
|
```
|
|
|
|
下面三个用途等接的时候再写代码,**本次只装服务、不接代码**:
|
|
|
|
| 用途 | 当前临时方案 | 何时必须接 Redis |
|
|
|---|---|---|
|
|
| 短信验证码冷却 | 进程内存 dict(`integrations/sms.py`) | `SMS_MOCK=false` 上线时 / 扩 worker 时 |
|
|
| pricebot trace_id session | 进程内存 dict(在 pricebot-backend) | pricebot 扩 worker 时 |
|
|
| JWT 黑名单(logout 真失效) | 当前 logout 占位无失效 | P1 鉴权改造时 |
|
|
|
|
---
|
|
|
|
## 7. 后续优化(不阻塞上线,一周内补)
|
|
|
|
- **每天凌晨 `pg_dump` 备份**:用 systemd timer 或 cron,保留 7 天。备份文件 `pg_dump -Fc shaguabijia > /backup/shaguabijia-$(date +%Y%m%d).dump`
|
|
- **金钱表加 CHECK 约束**:新迁移给 `coin_account.balance`、`withdraw_order.amount_yuan_x100` 加 `CHECK (... >= 0)`。PG 严格执行,SQLite 形同虚设
|
|
- **timezone 统一**:生产 PG 配 `timezone='Asia/Shanghai'` 或维持 `UTC`,**团队定一个**写进本文档,业务代码统一处理。当前模型混用 `func.now()`(server-side)和 `_utcnow()`(`models/user.py` 的 `last_login_at`,Python-side),后者依赖应用层时区,迁完 PG 后建议统一改 `func.now()`
|
|
- **慢查询观察**:`shared_preload_libraries = 'pg_stat_statements'` 打开扩展,每周看一次 Top 10 慢查询,提前优化
|
|
|
|
---
|
|
|
|
## 8. 不要做的事
|
|
|
|
- ❌ 不要搞主从复制 / 读写分离——量级远没到,先用单实例
|
|
- ❌ 不要装 PgBouncer / 连接池中间件——SQLAlchemy 自带池够用
|
|
- ❌ 不要 squash 旧迁移文件——9 个增量保持原样,让 alembic 一次跑过空库
|
|
- ❌ 不要在生产 PG 上裸跑测试套件——用单独的 `shaguabijia_test` 库
|