← 返回首页

SQLite 到 PostgreSQL 迁移实战：27,000 条记录迁移的踩坑与修复

2026-06-26

背景

我维护着一个视频处理流水线系统，核心数据存储在一个约 8MB 的 SQLite 数据库中，记录了 27,247 条视频处理记录。SQLite 跑在日常任务上一直很稳定，但它有一个天生局限：只能被同一台机器上的进程访问。

于是有了这次迁移的目标——把 SQLite 搬到 PostgreSQL，让数据库可以被多个系统同时查询，同时也图 PG 的更强并发和更丰富的查询能力。

如果你做过数据库迁移，应该知道这不是简单的「导出→导入」两步走。这次迁移从表面看是一次「直接迁移」，我甚至也觉得半小时就能搞定。但实际上，我在 5 个地方踩了坑，有些坑花了一个多小时来排查。

这篇文章不是 SQLite→PG 的迁移教程（这种文章太多了），而是一次真实迁移中遇到的 5 个坑及怎么修复。如果你打算做类似的事情，希望你能少走我走过的弯路。

迁移前的准备

环境摸底

迁移前需要搞清楚三件事：目标数据库的状态、源数据的结构、影响范围。

目标数据库跑着 PostgreSQL（含 pgvector 插件），已经运作了 fund_tracker、gbrain 等多个数据库。主用户有超级管理员权限，可以直接 CREATE DATABASE。

源数据库是一个 SQLite 文件（videos.db），包含一个主表 videos（27,247 行、18 列）和一个空表。主表结构包含视频文件名、源路径、状态（pending/uploaded/archived）、时间戳等字段，还有两个索引。

影响范围更值得关心。数据库不是被单一应用消费的——7 个业务脚本通过同一个 db.py 模块调用数据库，另有 4 个 CI 脚本不依赖此模块。还有一个脚本比较特殊，它直接把 SQLite 文件拉到本地解析。迁移意味着这些脚本全都需要能切换到新数据库。

方案取舍

评估了三个方案：

  1. 在 db.py 加一个 PG 后端开关，通过环境变量切换后端
  2. 重写整个 db.py 为抽象接口层
  3. 双写到 SQLite 和 PG

方案一的改动量最小——只改 db.py 和一个特殊脚本。所以选了方案一。

迁移执行

核心思路是：db.py 检测环境变量 DATABASE_URL，如果有就用 PostgreSQL，否则回退 SQLite。所有 CRUD 函数（add_video、get_videos_by_status、update_video_status、get_stats 等）内部根据后端选择 SQL 占位符风格和行数据读取方式。

迁移数据本身用了一个批量插入函数，每次 500 条，跑完大约花了 5 秒：

# 使用 execute_values 批量插入
from psycopg2.extras import execute_values

# 每次 500 条
for i in range(0, len(rows), 500):
    batch = rows[i:i+500]
    execute_values(cursor, """
        INSERT INTO videos (filename, source_path, date, status, ...) 
        VALUES %s
    """, batch, template="(%s, %s, %s, %s, ...)")
    conn.commit()

迁移日志输出了一条条进度：

迁移 27247 行...
500/27247
1000/27247
...
27247/27247
PG 验证: 27247 行
迁移完成 ✅

看起来很简单？真正的挑战在后头。

踩坑记录

坑 1：psycopg2 的 connection 没有 .execute()

这是我碰到的第一个坑，也是最容易忽视的一个。

在 SQLite 里，你可以直接写 conn.execute("SELECT ...")——connection 对象有快捷方法，自动创建 cursor 并返回结果。这个写法很方便，但不通用。

psycopg2 的 connection 对象没有 .execute() 方法。你必须：

# ❌ SQLite 写法，psycopg2 不工作
conn.execute("SELECT * FROM videos")

# ✅ 正确写法
cur = conn.cursor()
cur.execute("SELECT * FROM videos")

教训：SQLite → PG 不是简单换一个 import 就行。connection.execute() 是 SQLite 特有的快捷语法，你的查询全要走 cursor()。如果旧代码里很多地方用了这个快捷方式，逐个改一遍是逃不掉的。

坑 2：安全过滤器让我以为自己密码写对了

迁移后调试时，连接数据库反复报「password authentication failed」。检查连接 URL，发现密码字段显示 ***。

问题出在哪？我在多个终端的嵌套 shell 命令里传递 base64 编码的连接字符串，其中一段编码了包含占位符 *** 的字符串，而不是真实密码。因为安全过滤器会在日志输出里把真实的密码脱敏成 ***，我一度以为是过滤器的问题，结果赫然发现：代码里写的就是 *** 字面量。

  • 安全过滤器脱敏的是显示层的密码——你看不到真实密码但代码中传的是正确的
  • 代码中的 *** 字面量是另一个问题——你真的传了个没有意义的字符串给数据库

修复：重新用正确密码生成 base64 编码的 URL，确保编码的 payload 正确。验证方式是先在本地用短脚本测试连接，确认 URL 有效再部署到生产容器。

坑 3：RealDictCursor 让归档脚本崩溃，产生孤儿记录

这是最严重的一个坑。迁移后的归档脚本执行到一半崩溃了，产生了 15 条「孤儿记录」——云端文件已经删除，但数据库状态没有更新为 archived。

排查后发现，归档脚本的某个 update_video_status 调用，在 PG 端返回的结果对象和 SQLite 的 Row 对象行为不一致。函数执行失败时，一部分文件已经上传完毕、本地已删，但状态没更新。这就产生了「文件不在了，但记录还挂在非终结态」的孤儿。

根本原因是 db.py 里的 update_video_status 在 PG 模式下返回值处理方式与 SQLite 不同。具体来说，psycopg2.extras.RealDictCursor 与 sqlite3.Row 在索引行为和迭代行为上有微妙差异，导致某一处的条件判断走了错误的分支。

修复分两步：

  1. 修正 update_video_status 的统一 cursor + commit 模式，确保 PG 端返回值处理与 SQLite 一致
  2. 对已产生的孤儿记录，在 PG 中直接 UPDATE 状态为 archived：
    sql UPDATE videos SET status='archived', archived_at=NOW(), updated_at=NOW() WHERE id IN (...);

教训：sqlite3.Row 和 RealDictCursor 都支持字典式访问 row["col"]，但它们在边界情况下的行为不同。如果你在代码中依赖 row 的某种隐式行为（比如 bool(row) 的判断、in 操作符等），迁移后一定会暴露。迁移后必须做完整的端到端测试，不能只看「查询正常」就收工。

坑 4：task 命令自动注入环境变量 vs docker exec 不注入

这是一个容易被忽略的环境差异问题。

在容器环境里，有一个专门的 task 命令来执行脚本——它会自动从面板的 Envs 表中读取环境变量并注入到子进程。所以生产环境的定时任务是用 task 命令跑的，能正确读取 DATABASE_URL。

但我调试的时候直接用了 docker exec python3 script.py，这样做不会自动注入 Envs 表的环境变量。结果 DATABASE_URL 不存在，脚本回退到了 SQLite 模式。

# ✅ 生产环境：用 task 命令会自动注入环境变量
docker exec container-name task scripts/script.py

# ❌ 测试环境：直接 python3 不会注入
docker exec container-name python3 script.py  # DATABASE_URL 不存在

修复：测试时手动 export DATABASE_URL=...。同时确保生产环境的 Envs 表中的变量名与 db.py 读取的一致。

如果你用的也是基于定时任务/计划任务的容器管理工具，一定要搞清楚「面板执行」和「手动执行」之间的环境变量差异。否则你本地测试全绿，上线就挂。

坑 5：docker cp 路径覆盖错误

把新版的 db.py 拷贝到容器里时，看起来命令执行成功了，没有报错。但容器里还是旧版代码——没有 PG 支持的那个版本。

原因是混淆了宿主机路径和容器内目标路径。看似正确的 docker cp /tmp/db.py mycontainer:/target/path 实际上覆盖了一个不存在的路径，而真正的旧文件还在另一个目录位置。

修复：docker cp 之后立即用 head -30 确认内容：

docker cp /tmp/db.py mycontainer:/target/scripts/db.py
docker exec mycontainer head -30 /target/scripts/db.py | grep -i postgresql

如果看不到新代码的特征（比如 PostgreSQL 相关的 import 或函数名），说明路径有问题。

从坑中学到的

这次迁移从表面看是「27,247 行 SQLite → PostgreSQL」的简单数据搬运，但实际操作中暴露了 5 个隐藏陷阱：

  1. API 兼容性 — connection.execute() 快捷语法在 psycopg2 中不存在，所有查询必走 cursor()
  2. 安全过滤器干扰 — 「日志里看到 」和「代码里写 」是两回事，容易混淆排查方向
  3. 游标差异 — RealDictCursor 与 sqlite3.Row 在边界行为上不一致，直接导致生产异常
  4. 环境变量注入 — docker exec 和 task 命令的环境变量可见性不同，测试路径和生产路径的差异会坑你
  5. 异常链断裂 — 归档过程 crash 导致云端文件已删但 DB 状态未更新，形成孤儿记录

那个 5 秒的批量迁移不是重点。真正的价值在于：当你以为「直接迁移」可以一步到位时，每一层抽象都在掩盖数据库差异——直到你碰到它。

如果你要做类似的迁移，我的建议是：先花两小时写一组完全覆盖查改删的端到端测试用例，在两套后端上都跑通。这个过程会提前暴露 80% 的兼容性问题，节省后续的排错时间。

标签： PostgreSQL SQLite 数据库 踩坑
© 2026 Ruite的AI实践分享