从 SQLite 迁到 PostgreSQL：采集数据迁移的完整工程实践

数据攒到 800 万行以后，SQLite 的两个经典限制先后来报到：多任务并发写入开始报 database is locked，报表聚合越来越慢。选型结论早就写在存储篇里——该升级到 PostgreSQL 了。这篇记录实际迁移的过程：怎么把数据搬过去、代码怎么改、如何做到零丢失、以及回滚方案。
迁移前先确认：真的是存储的问题

别为了迁移而迁移。对一下症状：

• 并发写入频仍报锁（单写者模式已经扛不住）
  
• 单表几百万行起步，聚合查询肉眼可见地慢
  
• 必要多机器/多进程并发访问
  

三条中了两条，迁移才值得。迁移是有成本的工程动作，不是尝鲜。
第一步：Schema 翻译（类型映射）

SQLite 是「弱类型」（什么都能存 TEXT），PostgreSQL 是强类型。翻译时有张对照表：
SQLitePostgreSQL说明TEXT（存 ISO 日期）date迁移时趁便修正类型TEXTtext原样INTEGER（0/1）smallint 或 boolean二选一，别混用INTEGER（自增主键）bigint GENERATED ALWAYS AS IDENTITY当代化的自增写法PRIMARY KEY (a, b, c)同团结主键照搬普通索引必要显式重修最轻易漏的一项重点提示：索引不会自己跟过来。SQLite 的 file 里索引信息在 dump 里，但假如你是手工建表搬数据，一定要把每个索引重修一遍——否则迁移后「哪都对，就是慢」。
第二步：代码语法翻译

三类改动，逐个搜代码替换：
1. 占位符：? → %s（psycopg）

1. # SQLite
2. con.execute("SELECT * FROM results WHERE keyword = ?", (kw,))
4. # PostgreSQL（psycopg）
5. cur.execute("SELECT * FROM results WHERE keyword = %s", (kw,))

复制代码

2. 幂等写入：INSERT OR IGNORE → ON CONFLICT DO NOTHING

1. -- SQLite: INSERT OR IGNORE INTO results (...) VALUES (...)
2. -- PostgreSQL:
3. INSERT INTO results (keyword, day, rank, link)
4. VALUES (%s, %s, %s, %s)
5. ON CONFLICT (keyword, day, link) DO NOTHING;

复制代码

3. INSERT OR REPLACE → ON CONFLICT ... DO UPDATE

1. INSERT INTO ai_overview (keyword, day, has_ai)
2. VALUES (%s, %s, %s)
3. ON CONFLICT (keyword, day)
4. DO UPDATE SET has_ai = EXCLUDED.has_ai;

复制代码

INSERT OR REPLACE 在 SQLite 里其实是「删了重插」，语义和 PG 的 upsert 不完全一样——顺手同一成 UPSERT 语义，行为更可预期。
第三步：数据搬移

两条路：pgloader 这类现成工具，或自己写批量搬运脚本。数据量在万万行以内，我选了后者——可控、可校验：

1. import sqlite3, psycopg
3. src = sqlite3.connect("serp.db")
4. dst = psycopg.connect("postgresql://user:pass@localhost/serp")
6. BATCH = 5000
7. cur_src = src.execute("SELECT keyword, day, rank, link FROM results")
8. copied = 0
10. with dst.cursor() as cur:
11.     while True:
12.         batch = cur_src.fetchmany(BATCH)     # 分批读，别全量进内存
13.         if not batch:
14.             break
15.         cur.executemany(
16.             "INSERT INTO results (keyword, day, rank, link) VALUES (%s, %s, %s, %s)"
17.             " ON CONFLICT (keyword, day, link) DO NOTHING",
18.             batch,
19.         )
20.         dst.commit()
21.         copied += len(batch)
22.         print(f"已搬 {copied} 行")

复制代码

搬运完必须校验：源和目标「行数对行数」，再抽几张详细查询对比结果。行数对不上别急着切流量，先查是哪批没搬全（重跑该批即可，ON CONFLICT 保证幂等）。
第四步：切换策略（两个方案）

方案 A：冻结窗口 + 一次性切换（个人/小团队推荐）

• 停采集任务（几分钟到几十分钟）
  
• 搬完剩余数据 + 校验
  
• 改设置（DATABASE_URL）指向 PostgreSQL
  
• 重启采集，跑一轮冒烟（采集/查询/报表各一次）
  

方案 B：双写过渡（要求不间断时）
新旧库同时写，跑几天对比一致性，某个低峰期切读。复杂度高一截，独立项目一般用不上 A 就够。
无论哪个方案，旧 SQLite 文件先别删：原样保存一到两周，SQLite 的备份就是复制文件（在线 backup() 更稳），回滚成本极低。
踩坑记录

坑 1：占位符漏改。 ? 和 %s 混用导致运行时错误，或者更糟——隐式行为差异。全库搜一遍 execute( 逐个核对。
坑 2：索引忘了重修。 数据搬迁完查询慢十倍，一查全是 Seq Scan。建表时把索引清单列出来逐条执行。
坑 3：类型不兼容的数据。 空字符串塞进 date 列直接报错。搬运前先跑一次数据体检（空值、格式异常），在导出 SQL 里过滤或转换。
坑 4：标识符大小写。 PostgreSQL 未加引号的标识符都会变小写——Results 和 results 建表时就有坑。同一小写下划线命名，别用引号。
坑 5：校验口径不一致。 对比行数时一边加了过滤条件、一边没加，得到「差 3 万行」的假警报。校验查询两边保持完全一致，最好写成同一个 SQL 分别在两库执行。
坑 6：切完不观察第二天。 迁移的问题往往在「第一个完整日周期」暴露（定时任务、报表、清算）。切完至少盯满 24 小时再宣布成功。
工程清单

• 先确认迁移的必要性（并发锁 + 体量 + 多机访问）
  
• 类型映射表逐列翻译，索引清单显式重修
  
• 语法三件套：占位符、ON CONFLICT、UPSERT 语义
  
• 批量搬运 + 行数校验 + 抽样比对
  
• 冻结切换（或双写），旧库保存 1-2 周可回滚
  
• 切完盯满一个完整日周期
  

迁移这件事，最贵的不是敲命令，而是暗处的不一致：漏掉的索引、没改的占位符、类型不匹配的脏数据。按「翻译 → 搬移 → 校验 → 切换 → 观察」的流程走一遍，把每一步的校验做扎实，迁移就会安静得像什么都没发生——这才是最好的迁移。
接口侧的字段在迁移中保持原样（rank、link、时间戳等），字段语义见 SerpBase 官方文档，对照它可以确认迁移后没有丢字段。你做数据库迁移时踩过什么坑？评论区聊聊

免责声明：如果侵犯了您的权益，请联系站长及时删除侵权内容，谢谢合作！qidao123.com:ToB企服之家，中国第一个企服评测及软件市场,开放入驻,技术点评得现金.