尧图网页设计 
这几年做数据类项目跟各种数据库打交道多了几乎每个从轻量级应用起步的团队都会在某个节点遇到同一个问题SQLite顶不住了要换PostgreSQL。尤其是像若依这类框架的单节点部署开发阶段用SQLite跑得飞快一到生产环境、来了并发、来了压测SQLite的单写锁和并发瓶颈立刻暴露无遗。我也在这个节骨眼上接过一次迁移任务目标是把一套跑在单节点K8s上的微服务环境、带着全部历史数据从SQLite平滑迁到云上的PostgreSQL期间还要尽量不停服、不丢数据。这篇博文就是那次迁移的脚本实现和踩坑记录。我不会去讲“为什么要用PGSQLite哪里不好”这种空泛的道理而是把你真正会遇到的、文档里查不到的细节全部摊开类型映射怎么设计、自增主键怎么接续、数据校验怎么核对、迁移期间业务还在写入怎么办以及那些让你半夜挠头的诡异报错到底怎么解决。适合正在做同类迁移的人也适合准备从零规划一套数据库迁移方案的后端工程师。1. 迁移方案选型为什么不是直接 dump 一把梭先说结论SQLite导出成SQL文件、再导进PG这种“一把梭”的做法只适合纯离线、无并发、数据量几百条的小玩具项目。只要你的业务是真实在用的直接全量导出导入基本等于给自己埋雷。1.1 迁移方案的三个核心约束我在开始写脚本之前先列了三件事也建议你动手前先想清楚这三件事。第一可用窗口。业务能不能停能停多久我当时接到的条件是不能长时间停服只允许在凌晨低峰段短暂切换所以全量迁移必须能跑完且中途如果失败要能快速回滚。如果允许你停机一个小时那方案可以简单很多如果不允许停就要考虑增量同步。第二数据体积。SQLite单文件10 MB、100 MB和10 GB迁移策略完全不一样。10 MB可以随便搞脚本里直接循环读都行到了GB级别逐行insert就是灾难必须批量导入。我那次迁移的数据量在30 GB左右逐条插入跑到天亮都跑不完必须用COPY这类批量通道。第三历史包袱。SQLite是弱类型数据库字段里什么乱七八糟的类型都能存进去int、float、文本混在一个字段里是常态。到了PG这种强类型数据库每个列必须有明确类型所以“脏数据”必须在迁移前清洗干净否则导入时直接报错。这三件事没有想清楚后面每踩一个坑都要回头改方案成本极高。1.2 为什么我最终选定“读SQLite 写PG”双脚本结构原始的迁移脚本目标是“SQLite → PostgreSQL”看起来是单向一次性操作但我在设计时把脚本拆成了两段一段负责从SQLite导出数据一段负责将数据写入PG。中间用CSV或自定义文本格式做中转。这样拆有三个好处可断点续跑。导出写完一批CSV导入的时候如果崩了可以从最近一批接着导不用重新读SQLite。便于校验。导出的中间文件可以直接做行数、哈希比对这是验证“没丢数据”最直观的手段。解耦环境。导出脚本在旧机器上跑导入脚本在新机器上跑中间通过文件传输不需要两台机器网络互通。有人可能会问为什么不直接用pgloaderpgloader确实是一个成熟的SQLite到PG迁移工具我在调研时也试用过。但用它有两个问题一是它对SQLite的弱类型字段容忍度不够碰到类型混乱的列就容易挂二是它不好嵌入到我们的发布流程里出了问题也不方便定制恢复策略。所以我最终选择了自己写脚本pgloader作为参考对照工具但主链路完全可控。在设计阶段你还要特别留心“字符编码”这个隐藏坑。SQLite默认存储UTF-8但很多windows环境下导出的SQL或CSV中文会变成GBK。我在导出脚本里统一转成UTF-8然后设置CSV的分隔符时避开了数据中可能出现的逗号、引号、换行符用的方案是制表符做分隔每条记录的分隔符用换行符并且对字段内部出现的制表符、换行符做转义处理。这一套做完导入时的脏数据问题大幅度减少。2. 类型映射与DDL生成最容易翻车的地方SQLite的存储类型只有NULL、INTEGER、REAL、TEXT、BLOB这五种而PostgreSQL的类型体系丰富得多映射关系必须逐列确认。只看名字照搬十有八九会踩坑。我专门整理了一张映射表贴出来供参考。2.1 常用类型的映射对照表SQLite类型PostgreSQL类型说明INTEGERBIGINT / INTEGER优先用BIGINTSQLite的INTEGER可能存64位整数INTEGER可能溢出INTEGER PRIMARY KEYBIGSERIAL / BIGINT GENERATED ALWAYS AS IDENTITY自增主键的特殊处理见2.2REALDOUBLE PRECISIONSQLite的REAL是8字节浮点对应PG的DOUBLE PRECISIONNUMERIC / DECIMAL(p,s)NUMERIC(p,s)注意SQLite可能不校验精度原样定义即可TEXTTEXT / VARCHAR(n) / CITEXT不强制长度就用TEXT需要索引控制长度时用VARCHARBLOBBYTEASQLite的BLOB刚好对应PG的BYTEA注意十六进制格式转换BOOLEANBOOLEANSQLite没有原生布尔见坑点4.1DATETIME / TIMESTAMPTIMESTAMPTZ / TIMESTAMP坑最多见坑点4.2JSONJSONBPG的JSONB有更丰富的查询能力但需要先清洗非法JSONNULLNULL这个没啥歧义这里面每个映射我都在脚本里做了显式转换而不是交给数据库隐式转换。原因很简单隐式转换在数据量小的时候看不出问题数据量一大某一行类型对不上整个脚本卡死你根本不知道是哪一行。2.2 自增主键迁移SERIAL 还是 IDENTITYSQLite里自动递增的写法是INTEGER PRIMARY KEY AUTOINCREMENT底层由sqlite_sequence表记录下一次生成的序号。迁移到PG后你要考虑新插入数据的主键不能和旧数据冲突。PG里有两个主流方案SERIAL伪类型id BIGSERIAL PRIMARY KEY历史用法但它是先建序列再用序列默认值逻辑上有些绕。IDENTITY语法id BIGINT GENERATED ALWAYS AS IDENTITYSQL标准写法更清晰控制更严格。我使用的是GENERATED ALWAYS AS IDENTITY并在迁移完数据后手动把序列下一位置到MAX(id)1。这一步非常关键否则你只导入了数据序列还停在1新数据插入直接主键冲突。源码如下-- 先导入全部数据 -- 然后重建序列起点 SELECT setval( pg_get_serial_sequence(public.your_table_name, id), (SELECT MAX(id) FROM public.your_table_name) );pg_get_serial_sequence()函数可以自动找到该表id列绑定的序列名省去手动查pg_class的麻烦。注意如果表里数据为空这个语句会把序列设成NULL所以最好加个空值保护。2.3 DDL生成脚本的写法我写了一个Python脚本先用SQLite的PRAGMA table_info(表名)拿到所有列的定义再根据列名映射表把SQLite类型翻译成PG类型最后拼出CREATE TABLE语句。这里不能直接复制SQLite原生的建表SQL因为SQLite的CREATE TABLE语法里可以附带一些PG不认的属性例如sqlite_sequence、WITHOUT ROWID等关键词直接执行会报错。核心代码逻辑类似这样import sqlite3 def convert_type(sqlite_type, is_primary_key): t sqlite_type.upper() if is_primary_key and INT in t: return BIGINT GENERATED ALWAYS AS IDENTITY if INT in t: return BIGINT if REAL in t or FLOA in t or DOUB in t: return DOUBLE PRECISION if NUMERIC in t or DECIMAL in t: return NUMERIC if BOOL in t: return BOOLEAN if DATE in t or TIME in t: return TIMESTAMPTZ if JSON in t: return JSONB if BLOB in t or BYTE in t: return BYTEA return TEXT # 遍历所有表生成CREATE TABLE语句 conn sqlite3.connect(app.db) tables [r[0] for r in conn.execute(SELECT name FROM sqlite_master WHERE typetable)] for table in tables: cols conn.execute(fPRAGMA table_info({table})).fetchall() pg_cols [] for cid, name, ctype, notnull, dflt, pk in cols: pg_type convert_type(ctype, pk) col_def f{name.lower()} {pg_type} if notnull and not pk: col_def NOT NULL pg_cols.append(col_def) create_sql fCREATE TABLE IF NOT EXISTS public.{table} (\n ,\n .join(pg_cols) \n); # 输出或直接连接PG执行这里我把列名统一转成了小写原因后面坑点4.3会说PG会默认把不带引号的标识符转成小写这个行为在迁移阶段会带来很多麻烦。3. 核心实现从导出到校验的完整链路这一节我直接给出可复用的实现步骤每一步的目的和细节都会讲清楚。你不需要照抄我的代码但建议照着这个链路设计自己的脚本。3.1 第一步从SQLite导出数据到中转文件导出时我采用“按表导出、分批写出”的策略。一张表可能几百万行全部load进内存再写文件机器内存直接爆掉所以必须流式读取用fetchmany()边取边写。核心要点统一输出UTF-8编码避免中文乱码。统一使用制表符作为列分隔符并对字段内部的制表符、换行符、反斜杠做转义防止错位。每张表独立生成一个文件方便断点续导和后续校验。import sqlite3, csv, os def export_table(sqlite_conn, table_name, out_dir): cur sqlite_conn.cursor() cur.execute(fSELECT COUNT(*) FROM {table_name}) total cur.fetchone()[0] # 分批读取 cur.execute(fSELECT * FROM {table_name}) out_path os.path.join(out_dir, f{table_name}.tsv) # 拿到列名 col_names [d[0] for d in cur.description] with open(out_path, w, encodingutf-8, newline) as f: writer csv.writer(f, delimiter\t, quotingcsv.QUOTE_MINIMAL, lineterminator\n) writer.writerow(col_names) while True: rows cur.fetchmany(5000) if not rows: break cleaned_rows [] for row in rows: cleaned [] for val in row: if val is None: cleaned.append() elif isinstance(val, str): # 对特殊字符做转义 cleaned.append(val.replace(\\, \\\\).replace(\t, \\t).replace(\n, \\n)) else: cleaned.append(str(val)) cleaned_rows.append(cleaned) writer.writerows(cleaned_rows) return total这里有个容易忽略的点SQLite的NULL和空字符串存储上是有区别的但很多老代码会把空字符串和NULL混用。我导出时统一把None转成空字符串导入时再根据业务规则决定转成NULL还是保留空字符串。你也可以用类似\N的占位符表示NULL很多数据库工具都认这个标识。我结合自己这次业务场景最后选择了空字符串转NULL的策略因为业务查询都是IS NULL从没出现过需要区分空字符串和NULL的需求。3.2 第二步在PG中创建表结构并准备导入拿到导出的中间文件后在PG里执行第一步生成好的DDL然后对每张表做一次预检查包括确认表存在、列数量正确。确认主键是唯一且无重复。确认没有外键依赖倒挂见坑点4.10。确认自增列被正确设置为IDENTITY。准备工作做完再进入正式的数据导入环节。导入过程中为了提速我会先临时禁用索引和外键约束等数据全导入完再重建。这样做的原因是每插入一行PG都要维护索引和检查外键成本非常高数据导入到一半如果失败重建索引反而简单回滚也快。-- 导入前 ALTER TABLE public.your_table DISABLE TRIGGER ALL; -- 导入完成后 ALTER TABLE public.your_table ENABLE TRIGGER ALL;注意DISABLE TRIGGER ALL需要超级用户或表owner权限普通业务账号可能执行不了迁移时建议直接用超级用户操作。3.3 第三步批量数据导入不用逐条 insert逐条INSERT INTO ... VALUES对于数据量小的表无所谓但到了百万级这条路的性能就是灾难。我在脚本里用了PG的COPY FROM命令它底层走的是二进制或文本协议比insert快一到两个数量级。Python的psycopg2提供了copy_expert()方法可以直接将CSV/TSV文件流式COPY进表。代码简化如下import psycopg2 def load_table(pg_conn, table_name, tsv_path): cur pg_conn.cursor() with open(tsv_path, r, encodingutf-8) as f: cur.copy_expert( fCOPY public.\{table_name}\ FROM STDIN WITH DELIMITER E\\t NULL CSV HEADER, f ) pg_conn.commit()这里NULL 表示空字符串当成NULL。如果业务上确实需要区分空字符串和NULL这两者就要重新设计不能简单统一转换。还有一个细节COPY命令的HEADER行。我在导出的TSV第一行写了列名所以COPY时加了HEADER选项否则第一行会被当成数据插入导致类型转换直接报错。如果中间文件没有表头就需要在COPY时显式指明列顺序否则两边列对不上也会报错。3.4 第四步重建索引、约束和序列数据导完需要按顺序做三件收尾工作重新创建索引或启用之前禁用的索引。重新启用外键触发器。重置自增序列的起点。第二步和第三步的代码我前面已经给过这里补充一个建议先做约束再重置序列。因为有些表存在父子关系父表数据导入完、子表数据还没导完时外键检查必然会失败所以约束校验要放在所有表都导入完成后再做顺序不能反。我自己遇到过一个场景有张订单表和订单明细表明细表有外键指向订单表导入时我先导明细后导订单结果外键校验直接挂掉。排查半天发现不是数据问题是导入顺序问题。后来我调整为先导主表、再导子表问题就消失了。如果你导数据时禁用了触发器这个顺序问题就不会暴露但导入完成后重建约束时只要存在一个孤儿记录整个过程就会功亏一篑。所以迁移完后千万记得抽查外键完整性。3.5 数据校验脚本如何证明你没有丢数据“不丢数据”不是一个口号是要有数据支撑的。我做了三层校验行数校验对每张表分别执行SELECT COUNT(*)对比SQLite和PG的行数。关键字段汇总值校验取数值列做SUM或者对文本列做长度汇总确保数据内容没有错位。抽样哈希校验对主键ID取模抽取5%的行把整行所有字段拼成一个字符串用MD5哈希后对比两边的值。第一层最简单但也能挡住大部分漏导问题。第二层能发现类型转换的错误比如某个数字字段被截断或四舍五入。第三层最严格能定位到具体某行的某一列是否出了问题。抽样哈希的Python逻辑大致如下import hashlib def row_hash(row): raw |.join(str(v) for v in row) return hashlib.md5(raw.encode(utf-8)).hexdigest() # 从SQLite采样 sqlite_cur.execute(SELECT * FROM orders WHERE id % 20 0) sample_sqlite [(row, row_hash(row)) for row in sqlite_cur.fetchall()] # 从PG采样 pg_cur.execute(SELECT * FROM public.orders WHERE id % 20 0) sample_pg [(row, row_hash(row)) for row in pg_cur.fetchall()] # 比对两侧哈希集合 sqlite_hashes {h for _, h in sample_sqlite} pg_hashes {h for _, h in sample_pg} missing sqlite_hashes - pg_hashes if missing: print(f不一致的哈希数量: {len(missing)})注意两点一是采样条件要相同二是两边都要按主键排序防止行序差异导致哈希对不上。这个脚本我实际跑完之后还真发现过一个字段错位的问题所以强烈建议不要跳过这一步。4. 踩坑清单这些坑我替你们提前踩了这一部分是我最想写的。很多东西网上没有现成答案全靠读源码、试错、Log分析一点点磨出来的。我按照从“最隐蔽”到“最明显”的顺序列出来每一条都配上问题现象、原因分析和解决方式。4.1 布尔值SQLite没有布尔但你不该用0/1硬塞SQLite没有专门的BOOLEAN类型通常用INTEGER 0/1表示但有些人会往里面存true、false字符串有些人存1、0文本有些甚至存yes、no。PostgreSQL的BOOLEAN类型只认true/false/1/0/yes/no/on/off关键是字符串和数字在COPY时不能混用。我的处理方式是在导出阶段就把所有布尔语义的字段统一转成t/f两个字母PG的BOOLEAN类型能直接识别。如果某些历史数据里插入的是TRUE或True这种大小写混合COPY时大概率报错invalid input syntax for type boolean这时候你就要先去清洗数据而不是在PG端处理。排查这个问题的办法很简单导出前先执行一遍SELECT DISTINCT 字段 FROM 表看看真实存的都是哪些值手动改掉异常值再导。4.2 时间戳SQLite存的是文本PG认的是时间类型SQLite支持的时间类型本质是TEXT存的值形如2024-06-01 12:00:00或2024-06-01T12:00:00Z。PostgreSQL的TIMESTAMPTZ在解析时依赖会话的TimeZone参数如果两端时区不一样导入后时间会产生偏移。我踩坑的场景是这样的SQLite里存的是2024-06-01 12:00:00我直接当成字符串COPY进PG的TIMESTAMPTZ列结果PG按当前会话时区Asia/ShanghaiUTC8解析最终存进去的值变成了UTC时间2024-06-01 04:00:0000。查询时如果客户端时区跟服务端不一致显示出来的时间就莫名其妙少了8小时。这个问题非常隐蔽因为看单个值你可能看不出对错只有跟业务记录的时间对比才能发现。解决方法如果业务上存的就是本地时间PG列使用TIMESTAMP WITHOUT TIME ZONE避免时区转换。如果业务需要统一UTC时间SQLite导出时就要把本地时间转成带时区偏移的字符串再导入TIMESTAMPTZ。还有一个取巧的办法保留原文本作为字符串导入再在应用层转换。我这次的选择是保留本地时间语义PG列定义为TIMESTAMP WITHOUT TIME ZONE应用层代码把读取到的值当作本地时间处理这样与原来的SQLite行为完全一致避免整个业务链路的时区改造。4.3 大小写双引号为什么建好的表突然没了PostgreSQL对标识符大小写的处理规则是不带双引号的标识符统一转成小写带双引号的标识符则严格保持大小写。SQLite在这点上相对宽松大小写混用也能查到表。迁移时如果原SQLite表名是UserOrder你生成的DDL里写CREATE TABLE UserOrder那么查询时也必须写成UserOrder写成userorder会直接报错relation userorder does not exist。我的建议是迁移过程中统一转为小写表名和列名省掉所有双引号的坑。如果你有外部系统直接查库写SQL这个改动会影响它们需要提前通知如果所有访问都走应用层的ORM大部分ORM对大小写不敏感基本没有影响。我这次的表名和列名在导出脚本里统一做了lower()后续所有代码都按小写处理再没出现过找不到表的问题。4.4 保留字你的表可能叫 order、user 或者 groupSQLite和PostgreSQL的保留字列表并不完全一致。一个表名或列名在SQLite里好好的到了PG就成了保留字直接报语法错误。最常见的就是order、user、group、index、references等。规避办法不是让你把表改名而是在DDL和SQL语句中一律加双引号。比如CREATE TABLE public.order ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user TEXT, group TEXT );但正如4.3说的加双引号又牵扯大小写问题。所以你需要在“小写所有标识符”和“对保留字加双引号”之间做好平衡。我当时的策略是所有标识符转小写对PG保留字列表里的单词自动加双引号。这样既不会有大小写问题也不会触发语法错误。4.5 JSON字段SQLite存文本PG的JSONB会严格校验SQLite中JSON只是一个普通的TEXT字符串你可以往里塞任何内容PG不会校验。但PostgreSQL的JSONB类型要求存储值必须是合法JSON否则COPY或者insert直接报错。如果你的业务有脏JSON数据同样需要在导出前清洗。我的清洗经验是导出前先执行SELECT id, json_field FROM table WHERE json_valid(json_field) 0检查哪些行有问题。对于无法解析的JSON选择设置为NULL并在迁移报告里列出行ID方便业务侧修复。如果业务场景经常做JSON查询优先使用JSONB如果只是存起来偶尔展示用JSON类型更轻量。有一类特殊数据要注意JSON文本里的Unicode转义\uXXXX可能跟PG的JSON解析冲突虽然PG支持这种写法但如果你是从CSV里读入双反斜杠的处理就很容易出错。最好写个函数统一解析一遍再写入。4.6 字符串里的单引号COPY 不背这个锅很多人用INSERT语句写数据时被字符串里的单引号坑过于是理所当然觉得COPY也需要注意转义。其实COPY命令的文本格式对单引号不敏感单引号就是普通字符不需要特殊处理。真正需要小心的是分隔符制表符和换行符我前面给的导出函数里已经把这两类字符做了转义。但如果你选择了CSV格式逗号分隔那引号的处理规则就完全不一样了CSV里字段如果包含分隔符、引号、换行符必须用双引号包裹字段内部的引号需要双写。处理起来比TSV麻烦得多。这也是我坚持用TSV的原因之一。总结一句话用TSV时防制表符和换行符用CSV时防逗号、引号和换行符。4.7 大批量插入时所有索引都先撤掉这条前面已经提了一句我再用实际数据说明一下我迁移一张500万行的流水表时第一次带着索引跑COPY跑了40多分钟还没结束后来先把索引全部删除COPY只用了3分钟然后重建索引花了8分钟加起来11分钟速度提升非常明显。具体操作是-- 迁移前记录索引定义 SELECT indexdef FROM pg_indexes WHERE tablename your_table; -- 删除索引保留主键约束也可以先删 DROP INDEX IF EXISTS idx_your_table_col1; DROP INDEX IF EXISTS idx_your_table_col2; -- 导入数据 COPY ... -- 重建索引 CREATE INDEX idx_your_table_col1 ON public.your_table (col1);这里有个注意事项如果表上有唯一索引删掉重建后要再次检查重复数据避免COPY期间出现重复值没被拦截。我在实际处理时是先查出重复组再决定是否过滤不是直接重建就完事。4.8 停机窗口内的增量数据简单但必须做的收尾如果你的迁移允许一个短暂停机窗口那流程是这样的在低峰期开始全量导出SQLite快照。导出期间业务会继续写入这些新增数据不会出现在快照里。到达停机窗口后停掉业务写入再导出一次变化的数据追加同步到PG。校验完毕将业务切到PG。难点在于第二步到第三步之间怎么识别“变化的数据”。简单做法是给SQLite表增加一个updated_at字段利用这个字段导出增量。如果原表没有这个字段可以退而求其次在首次快照里记录每张表的MAX(主键)停机时再导出主键大于这个值的记录作为增量补导。我这次因为SQLite库里主键是自增的就用这个“主键窗口”方案写起来很简单-- 增量导出条件 WHERE id {last_synced_id}但要注意如果业务有大量更新旧数据的行为这种方案会漏掉被修改的行。稳妥做法是加updated_at字段并在应用层维护这属于长期方案短期应急就只能接受“新增不丢、修改可能有轻微延迟”的妥协。4.9 外键依赖和孤儿数据的检查如果你在PG里保留了外键约束导入数据时就要特别仔细。外键约束检查是严谨的任何一条子表记录找不到对应主表记录整个导入就会失败。常见原因是老系统里数据本来就有问题比如外键指向的主记录被物理删除过。我在迁移前写了一个检查脚本遍历所有外键关系在SQLite里先执行等价查询找出“孤儿记录”数量。如果数量为0直接销毁再重建外键约束如果不为0就要先把孤儿记录筛选出来跟业务确认是修复还是丢弃。最省事的办法居然是先不要外键约束等数据导入完毕再单独跑一遍孤儿检查这时发现问题只会影响单张表而不会让整个导入全部回滚。处理方法比预想中简单得多趁停机窗口内对异常数据进行修正然后再开启约束。4.10 权限与角色问题给迁移账号最低配置PG的权限模型比SQLite复杂得多。SQLite就是一个文件谁有文件权限谁就能读写PG区分了登录权限、库权限、模式权限、表权限、序列权限少一个都会导致业务运行时报错。迁移时用超级用户操作没问题但迁移完成后业务账号要能正常读写就需要注意表要有SELECT/INSERT/UPDATE/DELETE权限。序列IDENTITY对应序列要有USAGE权限。需要能连接数据库、使用schema的usage权限。如果你是通过GRANT ALL ON ALL TABLES IN SCHEMA public TO business_user这种方式授权别忘了还有默认权限ALTER DEFAULT PRIVILEGES的问题否则之后新建的表业务账号依然没有权限。我这次就在这个坑上浪费了大半天表数据都迁移完了应用一启动报permission denied for table xxx翻日志才发现新表的权限没给全。后来把默认权限补上才恢复正常。你可以理解为不配默认权限就像是你给同事开了办公室的门但没告诉他未来新房间的钥匙也都得给他。彻底解决是执行ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO business_user;5. 迁移后的验证与性能压测准备脚本跑完、数据校验通过不代表迁移交付完毕。还要做业务功能验证和性能摸底尤其是你后面还跟着压测任务这个环节做扎实了压测才能顺利通过。5.1 业务层面验证的用例清单业务验证我列了一个清单逐项签字确认登录链路账号密码校验是否正常对应SQL是否用到新库的新索引。主流程CRUD最核心的增删改查逐一过一遍确认insert能取到新主键、update能命中行、delete能执行成功。历史数据查询拿几个用户反馈过的历史订单号、历史流水号验证能够查得出来且字段内容没有乱码。关联查询订单表关联明细表、用户表关联角色表典型的JOIN查询要跑一遍确认外键关系没有丢。定时任务如果有定时任务试跑一次看是否出现连接池不够、事务冲突等新库特有的问题。这里有一个容易忽略的细节连接字符串。SQLite的连接就是jdbc:sqlite:app.dbPG的连接串是jdbc:postgresql://host:5432/dbname。很多应用改了数据库驱动和连接串但配置里可能遗漏了currentSchema、timezone、stringtypeunspecified等参数而这些参数会影响查询行为和类型返回。我在迁移时专门对照过两边的JDBC/驱动版本确保连接参数一致。5.2 压测前要准备好的数据基线与指标你提到迁移完成后由压测人员使用jmeter脚本做高并发测试验证云上环境承载能力。在一上来压测之前我们至少要准备两样东西数据基线将迁移后的行数、关键表数据量、索引大小记录下来作为压测后对比的基线。压测如果产生大量脏数据也要能一键清理避免影响后续业务。指标采集方案PG侧开启pg_stat_statements扩展监控制定SQL的执行计划变化同时部署node_exporter或类似工具采集系统CPU、内存、IO用来分析瓶颈。连接池配置应用侧连接池最大连接数要跟PG的max_connections匹配否则高并发压测时直接报too many clients already。压测过程中如果发现某些查询慢第一件事不是调SQL而是执行EXPLAIN ANALYZE看执行计划。很多从SQLite迁过来的SQL在PG上因为没有合适的索引而走全表扫描这时候补上对应索引压测指标会立刻好很多。这个坑我提醒过压测同事他们也反馈确实有几个慢查询是索引缺失导致的补完索引后吞吐量翻了一倍。6. 常见问题与排查思路速查表把前面所有坑浓缩成一张表方便你迁移时随时翻查。可以当作Checklist用。问题现象可能原因解决方案COPY导入报invalid input syntax for type booleanSQLite布尔字段存了非0/1/true/false的脏值先SELECT DISTINCT检查数据清洗后再导入时间查询结果与原来差8小时SQLite存本地时间PG的TIMESTAMPTZ做了UTC转换列改用TIMESTAMP WITHOUT TIME ZONE保持原语义表名找不到PG大小写规则导致大小写混用表名被转小写统一小写表名保留字加双引号插入新数据主键冲突自增序列起点没有重置使用setval(pg_get_serial_sequence(...), MAX(id))大量行插入非常慢COPY没开启或索引外键没有临时禁用用COPY先删索引、禁用触发器导入后重建JSON字段导入报错SQLite存了非法JSON文本导出前清洗无法修复的置为NULL业务账号查询报权限不足只授了库级权限没有表级、序列权限补GRANT和ALTER DEFAULT PRIVILEGES外键约束重建失败SQLite中存在孤儿记录先查孤儿数据迁移后修正或清理COPY时报extra data after last expected column导出的TSV里换行符没转义导致一行数据被拆成两行导出时对字段内部的换行符做转义统一改为\n字符串中文乱码SQLite中的UTF-8文本被错误转码统一使用UTF-8编码CSV/TSV都声明编码排查问题的习惯也很重要。我每次迁移脚本跑崩第一反应不是看报错堆栈而是截取报错上下文前后的数据行先定位是导出的问题还是导入的问题。比如COPY报错时PG会提示类似COPY your_table, line 12345, column ...这就是定位的线索。拿这条记录去SQLite里查原始值一看就明白是哪类转义或类型问题。千万别说“数据量太大没法查”真实生产环境靠的就是这样一行一行揪出问题。写在最后的一点个人体会数据库迁移这种活听起来就是把数据从一个地方搬到另一个地方真正做起来才知道最花时间的不是搬数据本身而是处理那些历史遗留的“脏”数据、类型混乱的字段、和各种数据库方言之间的行为差异。SQLite到PG还好至少两者都有成熟生态你要是碰到过某些老商业数据库的私有类型那才是真正的折磨。我个人的建议是迁移脚本不要一味追求写得花哨稳定和执行速度才是第一位的。数据导出用简单的流式读写PG导入用COPY两步之间留出可检查的中间文件出了问题能快速定位、断点续跑。这套思路不仅适用SQLite到PG其他关系型数据库之间互相迁移逻辑上也完全说得通。最后再分享一个小技巧迁移正式执行之前一定要在一个全量备份的副本上演练至少一遍。演练过程中记录每一步耗时尤其是COPY导入和索引重建的时间这样正式停机切换时你心里有数不会出现“以为半小时跑完结果三小时还没结束”的尴尬局面。数据无小事多演习一次生产环境就多一分从容。 SQLite到PostgreSQL数据迁移实战：类型映射、增量同步与踩坑记录
这几年做数据类项目跟各种数据库打交道多了几乎每个从轻量级应用起步的团队都会在某个节点遇到同一个问题SQLite顶不住了要换PostgreSQL。尤其是像若依这类框架的单节点部署开发阶段用SQLite跑得飞快一到生产环境、来了并发、来了压测SQLite的单写锁和并发瓶颈立刻暴露无遗。我也在这个节骨眼上接过一次迁移任务目标是把一套跑在单节点K8s上的微服务环境、带着全部历史数据从SQLite平滑迁到云上的PostgreSQL期间还要尽量不停服、不丢数据。这篇博文就是那次迁移的脚本实现和踩坑记录。我不会去讲“为什么要用PGSQLite哪里不好”这种空泛的道理而是把你真正会遇到的、文档里查不到的细节全部摊开类型映射怎么设计、自增主键怎么接续、数据校验怎么核对、迁移期间业务还在写入怎么办以及那些让你半夜挠头的诡异报错到底怎么解决。适合正在做同类迁移的人也适合准备从零规划一套数据库迁移方案的后端工程师。1. 迁移方案选型为什么不是直接 dump 一把梭先说结论SQLite导出成SQL文件、再导进PG这种“一把梭”的做法只适合纯离线、无并发、数据量几百条的小玩具项目。只要你的业务是真实在用的直接全量导出导入基本等于给自己埋雷。1.1 迁移方案的三个核心约束我在开始写脚本之前先列了三件事也建议你动手前先想清楚这三件事。第一可用窗口。业务能不能停能停多久我当时接到的条件是不能长时间停服只允许在凌晨低峰段短暂切换所以全量迁移必须能跑完且中途如果失败要能快速回滚。如果允许你停机一个小时那方案可以简单很多如果不允许停就要考虑增量同步。第二数据体积。SQLite单文件10 MB、100 MB和10 GB迁移策略完全不一样。10 MB可以随便搞脚本里直接循环读都行到了GB级别逐行insert就是灾难必须批量导入。我那次迁移的数据量在30 GB左右逐条插入跑到天亮都跑不完必须用COPY这类批量通道。第三历史包袱。SQLite是弱类型数据库字段里什么乱七八糟的类型都能存进去int、float、文本混在一个字段里是常态。到了PG这种强类型数据库每个列必须有明确类型所以“脏数据”必须在迁移前清洗干净否则导入时直接报错。这三件事没有想清楚后面每踩一个坑都要回头改方案成本极高。1.2 为什么我最终选定“读SQLite 写PG”双脚本结构原始的迁移脚本目标是“SQLite → PostgreSQL”看起来是单向一次性操作但我在设计时把脚本拆成了两段一段负责从SQLite导出数据一段负责将数据写入PG。中间用CSV或自定义文本格式做中转。这样拆有三个好处可断点续跑。导出写完一批CSV导入的时候如果崩了可以从最近一批接着导不用重新读SQLite。便于校验。导出的中间文件可以直接做行数、哈希比对这是验证“没丢数据”最直观的手段。解耦环境。导出脚本在旧机器上跑导入脚本在新机器上跑中间通过文件传输不需要两台机器网络互通。有人可能会问为什么不直接用pgloaderpgloader确实是一个成熟的SQLite到PG迁移工具我在调研时也试用过。但用它有两个问题一是它对SQLite的弱类型字段容忍度不够碰到类型混乱的列就容易挂二是它不好嵌入到我们的发布流程里出了问题也不方便定制恢复策略。所以我最终选择了自己写脚本pgloader作为参考对照工具但主链路完全可控。在设计阶段你还要特别留心“字符编码”这个隐藏坑。SQLite默认存储UTF-8但很多windows环境下导出的SQL或CSV中文会变成GBK。我在导出脚本里统一转成UTF-8然后设置CSV的分隔符时避开了数据中可能出现的逗号、引号、换行符用的方案是制表符做分隔每条记录的分隔符用换行符并且对字段内部出现的制表符、换行符做转义处理。这一套做完导入时的脏数据问题大幅度减少。2. 类型映射与DDL生成最容易翻车的地方SQLite的存储类型只有NULL、INTEGER、REAL、TEXT、BLOB这五种而PostgreSQL的类型体系丰富得多映射关系必须逐列确认。只看名字照搬十有八九会踩坑。我专门整理了一张映射表贴出来供参考。2.1 常用类型的映射对照表SQLite类型PostgreSQL类型说明INTEGERBIGINT / INTEGER优先用BIGINTSQLite的INTEGER可能存64位整数INTEGER可能溢出INTEGER PRIMARY KEYBIGSERIAL / BIGINT GENERATED ALWAYS AS IDENTITY自增主键的特殊处理见2.2REALDOUBLE PRECISIONSQLite的REAL是8字节浮点对应PG的DOUBLE PRECISIONNUMERIC / DECIMAL(p,s)NUMERIC(p,s)注意SQLite可能不校验精度原样定义即可TEXTTEXT / VARCHAR(n) / CITEXT不强制长度就用TEXT需要索引控制长度时用VARCHARBLOBBYTEASQLite的BLOB刚好对应PG的BYTEA注意十六进制格式转换BOOLEANBOOLEANSQLite没有原生布尔见坑点4.1DATETIME / TIMESTAMPTIMESTAMPTZ / TIMESTAMP坑最多见坑点4.2JSONJSONBPG的JSONB有更丰富的查询能力但需要先清洗非法JSONNULLNULL这个没啥歧义这里面每个映射我都在脚本里做了显式转换而不是交给数据库隐式转换。原因很简单隐式转换在数据量小的时候看不出问题数据量一大某一行类型对不上整个脚本卡死你根本不知道是哪一行。2.2 自增主键迁移SERIAL 还是 IDENTITYSQLite里自动递增的写法是INTEGER PRIMARY KEY AUTOINCREMENT底层由sqlite_sequence表记录下一次生成的序号。迁移到PG后你要考虑新插入数据的主键不能和旧数据冲突。PG里有两个主流方案SERIAL伪类型id BIGSERIAL PRIMARY KEY历史用法但它是先建序列再用序列默认值逻辑上有些绕。IDENTITY语法id BIGINT GENERATED ALWAYS AS IDENTITYSQL标准写法更清晰控制更严格。我使用的是GENERATED ALWAYS AS IDENTITY并在迁移完数据后手动把序列下一位置到MAX(id)1。这一步非常关键否则你只导入了数据序列还停在1新数据插入直接主键冲突。源码如下-- 先导入全部数据 -- 然后重建序列起点 SELECT setval( pg_get_serial_sequence(public.your_table_name, id), (SELECT MAX(id) FROM public.your_table_name) );pg_get_serial_sequence()函数可以自动找到该表id列绑定的序列名省去手动查pg_class的麻烦。注意如果表里数据为空这个语句会把序列设成NULL所以最好加个空值保护。2.3 DDL生成脚本的写法我写了一个Python脚本先用SQLite的PRAGMA table_info(表名)拿到所有列的定义再根据列名映射表把SQLite类型翻译成PG类型最后拼出CREATE TABLE语句。这里不能直接复制SQLite原生的建表SQL因为SQLite的CREATE TABLE语法里可以附带一些PG不认的属性例如sqlite_sequence、WITHOUT ROWID等关键词直接执行会报错。核心代码逻辑类似这样import sqlite3 def convert_type(sqlite_type, is_primary_key): t sqlite_type.upper() if is_primary_key and INT in t: return BIGINT GENERATED ALWAYS AS IDENTITY if INT in t: return BIGINT if REAL in t or FLOA in t or DOUB in t: return DOUBLE PRECISION if NUMERIC in t or DECIMAL in t: return NUMERIC if BOOL in t: return BOOLEAN if DATE in t or TIME in t: return TIMESTAMPTZ if JSON in t: return JSONB if BLOB in t or BYTE in t: return BYTEA return TEXT # 遍历所有表生成CREATE TABLE语句 conn sqlite3.connect(app.db) tables [r[0] for r in conn.execute(SELECT name FROM sqlite_master WHERE typetable)] for table in tables: cols conn.execute(fPRAGMA table_info({table})).fetchall() pg_cols [] for cid, name, ctype, notnull, dflt, pk in cols: pg_type convert_type(ctype, pk) col_def f{name.lower()} {pg_type} if notnull and not pk: col_def NOT NULL pg_cols.append(col_def) create_sql fCREATE TABLE IF NOT EXISTS public.{table} (\n ,\n .join(pg_cols) \n); # 输出或直接连接PG执行这里我把列名统一转成了小写原因后面坑点4.3会说PG会默认把不带引号的标识符转成小写这个行为在迁移阶段会带来很多麻烦。3. 核心实现从导出到校验的完整链路这一节我直接给出可复用的实现步骤每一步的目的和细节都会讲清楚。你不需要照抄我的代码但建议照着这个链路设计自己的脚本。3.1 第一步从SQLite导出数据到中转文件导出时我采用“按表导出、分批写出”的策略。一张表可能几百万行全部load进内存再写文件机器内存直接爆掉所以必须流式读取用fetchmany()边取边写。核心要点统一输出UTF-8编码避免中文乱码。统一使用制表符作为列分隔符并对字段内部的制表符、换行符、反斜杠做转义防止错位。每张表独立生成一个文件方便断点续导和后续校验。import sqlite3, csv, os def export_table(sqlite_conn, table_name, out_dir): cur sqlite_conn.cursor() cur.execute(fSELECT COUNT(*) FROM {table_name}) total cur.fetchone()[0] # 分批读取 cur.execute(fSELECT * FROM {table_name}) out_path os.path.join(out_dir, f{table_name}.tsv) # 拿到列名 col_names [d[0] for d in cur.description] with open(out_path, w, encodingutf-8, newline) as f: writer csv.writer(f, delimiter\t, quotingcsv.QUOTE_MINIMAL, lineterminator\n) writer.writerow(col_names) while True: rows cur.fetchmany(5000) if not rows: break cleaned_rows [] for row in rows: cleaned [] for val in row: if val is None: cleaned.append() elif isinstance(val, str): # 对特殊字符做转义 cleaned.append(val.replace(\\, \\\\).replace(\t, \\t).replace(\n, \\n)) else: cleaned.append(str(val)) cleaned_rows.append(cleaned) writer.writerows(cleaned_rows) return total这里有个容易忽略的点SQLite的NULL和空字符串存储上是有区别的但很多老代码会把空字符串和NULL混用。我导出时统一把None转成空字符串导入时再根据业务规则决定转成NULL还是保留空字符串。你也可以用类似\N的占位符表示NULL很多数据库工具都认这个标识。我结合自己这次业务场景最后选择了空字符串转NULL的策略因为业务查询都是IS NULL从没出现过需要区分空字符串和NULL的需求。3.2 第二步在PG中创建表结构并准备导入拿到导出的中间文件后在PG里执行第一步生成好的DDL然后对每张表做一次预检查包括确认表存在、列数量正确。确认主键是唯一且无重复。确认没有外键依赖倒挂见坑点4.10。确认自增列被正确设置为IDENTITY。准备工作做完再进入正式的数据导入环节。导入过程中为了提速我会先临时禁用索引和外键约束等数据全导入完再重建。这样做的原因是每插入一行PG都要维护索引和检查外键成本非常高数据导入到一半如果失败重建索引反而简单回滚也快。-- 导入前 ALTER TABLE public.your_table DISABLE TRIGGER ALL; -- 导入完成后 ALTER TABLE public.your_table ENABLE TRIGGER ALL;注意DISABLE TRIGGER ALL需要超级用户或表owner权限普通业务账号可能执行不了迁移时建议直接用超级用户操作。3.3 第三步批量数据导入不用逐条 insert逐条INSERT INTO ... VALUES对于数据量小的表无所谓但到了百万级这条路的性能就是灾难。我在脚本里用了PG的COPY FROM命令它底层走的是二进制或文本协议比insert快一到两个数量级。Python的psycopg2提供了copy_expert()方法可以直接将CSV/TSV文件流式COPY进表。代码简化如下import psycopg2 def load_table(pg_conn, table_name, tsv_path): cur pg_conn.cursor() with open(tsv_path, r, encodingutf-8) as f: cur.copy_expert( fCOPY public.\{table_name}\ FROM STDIN WITH DELIMITER E\\t NULL CSV HEADER, f ) pg_conn.commit()这里NULL 表示空字符串当成NULL。如果业务上确实需要区分空字符串和NULL这两者就要重新设计不能简单统一转换。还有一个细节COPY命令的HEADER行。我在导出的TSV第一行写了列名所以COPY时加了HEADER选项否则第一行会被当成数据插入导致类型转换直接报错。如果中间文件没有表头就需要在COPY时显式指明列顺序否则两边列对不上也会报错。3.4 第四步重建索引、约束和序列数据导完需要按顺序做三件收尾工作重新创建索引或启用之前禁用的索引。重新启用外键触发器。重置自增序列的起点。第二步和第三步的代码我前面已经给过这里补充一个建议先做约束再重置序列。因为有些表存在父子关系父表数据导入完、子表数据还没导完时外键检查必然会失败所以约束校验要放在所有表都导入完成后再做顺序不能反。我自己遇到过一个场景有张订单表和订单明细表明细表有外键指向订单表导入时我先导明细后导订单结果外键校验直接挂掉。排查半天发现不是数据问题是导入顺序问题。后来我调整为先导主表、再导子表问题就消失了。如果你导数据时禁用了触发器这个顺序问题就不会暴露但导入完成后重建约束时只要存在一个孤儿记录整个过程就会功亏一篑。所以迁移完后千万记得抽查外键完整性。3.5 数据校验脚本如何证明你没有丢数据“不丢数据”不是一个口号是要有数据支撑的。我做了三层校验行数校验对每张表分别执行SELECT COUNT(*)对比SQLite和PG的行数。关键字段汇总值校验取数值列做SUM或者对文本列做长度汇总确保数据内容没有错位。抽样哈希校验对主键ID取模抽取5%的行把整行所有字段拼成一个字符串用MD5哈希后对比两边的值。第一层最简单但也能挡住大部分漏导问题。第二层能发现类型转换的错误比如某个数字字段被截断或四舍五入。第三层最严格能定位到具体某行的某一列是否出了问题。抽样哈希的Python逻辑大致如下import hashlib def row_hash(row): raw |.join(str(v) for v in row) return hashlib.md5(raw.encode(utf-8)).hexdigest() # 从SQLite采样 sqlite_cur.execute(SELECT * FROM orders WHERE id % 20 0) sample_sqlite [(row, row_hash(row)) for row in sqlite_cur.fetchall()] # 从PG采样 pg_cur.execute(SELECT * FROM public.orders WHERE id % 20 0) sample_pg [(row, row_hash(row)) for row in pg_cur.fetchall()] # 比对两侧哈希集合 sqlite_hashes {h for _, h in sample_sqlite} pg_hashes {h for _, h in sample_pg} missing sqlite_hashes - pg_hashes if missing: print(f不一致的哈希数量: {len(missing)})注意两点一是采样条件要相同二是两边都要按主键排序防止行序差异导致哈希对不上。这个脚本我实际跑完之后还真发现过一个字段错位的问题所以强烈建议不要跳过这一步。4. 踩坑清单这些坑我替你们提前踩了这一部分是我最想写的。很多东西网上没有现成答案全靠读源码、试错、Log分析一点点磨出来的。我按照从“最隐蔽”到“最明显”的顺序列出来每一条都配上问题现象、原因分析和解决方式。4.1 布尔值SQLite没有布尔但你不该用0/1硬塞SQLite没有专门的BOOLEAN类型通常用INTEGER 0/1表示但有些人会往里面存true、false字符串有些人存1、0文本有些甚至存yes、no。PostgreSQL的BOOLEAN类型只认true/false/1/0/yes/no/on/off关键是字符串和数字在COPY时不能混用。我的处理方式是在导出阶段就把所有布尔语义的字段统一转成t/f两个字母PG的BOOLEAN类型能直接识别。如果某些历史数据里插入的是TRUE或True这种大小写混合COPY时大概率报错invalid input syntax for type boolean这时候你就要先去清洗数据而不是在PG端处理。排查这个问题的办法很简单导出前先执行一遍SELECT DISTINCT 字段 FROM 表看看真实存的都是哪些值手动改掉异常值再导。4.2 时间戳SQLite存的是文本PG认的是时间类型SQLite支持的时间类型本质是TEXT存的值形如2024-06-01 12:00:00或2024-06-01T12:00:00Z。PostgreSQL的TIMESTAMPTZ在解析时依赖会话的TimeZone参数如果两端时区不一样导入后时间会产生偏移。我踩坑的场景是这样的SQLite里存的是2024-06-01 12:00:00我直接当成字符串COPY进PG的TIMESTAMPTZ列结果PG按当前会话时区Asia/ShanghaiUTC8解析最终存进去的值变成了UTC时间2024-06-01 04:00:0000。查询时如果客户端时区跟服务端不一致显示出来的时间就莫名其妙少了8小时。这个问题非常隐蔽因为看单个值你可能看不出对错只有跟业务记录的时间对比才能发现。解决方法如果业务上存的就是本地时间PG列使用TIMESTAMP WITHOUT TIME ZONE避免时区转换。如果业务需要统一UTC时间SQLite导出时就要把本地时间转成带时区偏移的字符串再导入TIMESTAMPTZ。还有一个取巧的办法保留原文本作为字符串导入再在应用层转换。我这次的选择是保留本地时间语义PG列定义为TIMESTAMP WITHOUT TIME ZONE应用层代码把读取到的值当作本地时间处理这样与原来的SQLite行为完全一致避免整个业务链路的时区改造。4.3 大小写双引号为什么建好的表突然没了PostgreSQL对标识符大小写的处理规则是不带双引号的标识符统一转成小写带双引号的标识符则严格保持大小写。SQLite在这点上相对宽松大小写混用也能查到表。迁移时如果原SQLite表名是UserOrder你生成的DDL里写CREATE TABLE UserOrder那么查询时也必须写成UserOrder写成userorder会直接报错relation userorder does not exist。我的建议是迁移过程中统一转为小写表名和列名省掉所有双引号的坑。如果你有外部系统直接查库写SQL这个改动会影响它们需要提前通知如果所有访问都走应用层的ORM大部分ORM对大小写不敏感基本没有影响。我这次的表名和列名在导出脚本里统一做了lower()后续所有代码都按小写处理再没出现过找不到表的问题。4.4 保留字你的表可能叫 order、user 或者 groupSQLite和PostgreSQL的保留字列表并不完全一致。一个表名或列名在SQLite里好好的到了PG就成了保留字直接报语法错误。最常见的就是order、user、group、index、references等。规避办法不是让你把表改名而是在DDL和SQL语句中一律加双引号。比如CREATE TABLE public.order ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user TEXT, group TEXT );但正如4.3说的加双引号又牵扯大小写问题。所以你需要在“小写所有标识符”和“对保留字加双引号”之间做好平衡。我当时的策略是所有标识符转小写对PG保留字列表里的单词自动加双引号。这样既不会有大小写问题也不会触发语法错误。4.5 JSON字段SQLite存文本PG的JSONB会严格校验SQLite中JSON只是一个普通的TEXT字符串你可以往里塞任何内容PG不会校验。但PostgreSQL的JSONB类型要求存储值必须是合法JSON否则COPY或者insert直接报错。如果你的业务有脏JSON数据同样需要在导出前清洗。我的清洗经验是导出前先执行SELECT id, json_field FROM table WHERE json_valid(json_field) 0检查哪些行有问题。对于无法解析的JSON选择设置为NULL并在迁移报告里列出行ID方便业务侧修复。如果业务场景经常做JSON查询优先使用JSONB如果只是存起来偶尔展示用JSON类型更轻量。有一类特殊数据要注意JSON文本里的Unicode转义\uXXXX可能跟PG的JSON解析冲突虽然PG支持这种写法但如果你是从CSV里读入双反斜杠的处理就很容易出错。最好写个函数统一解析一遍再写入。4.6 字符串里的单引号COPY 不背这个锅很多人用INSERT语句写数据时被字符串里的单引号坑过于是理所当然觉得COPY也需要注意转义。其实COPY命令的文本格式对单引号不敏感单引号就是普通字符不需要特殊处理。真正需要小心的是分隔符制表符和换行符我前面给的导出函数里已经把这两类字符做了转义。但如果你选择了CSV格式逗号分隔那引号的处理规则就完全不一样了CSV里字段如果包含分隔符、引号、换行符必须用双引号包裹字段内部的引号需要双写。处理起来比TSV麻烦得多。这也是我坚持用TSV的原因之一。总结一句话用TSV时防制表符和换行符用CSV时防逗号、引号和换行符。4.7 大批量插入时所有索引都先撤掉这条前面已经提了一句我再用实际数据说明一下我迁移一张500万行的流水表时第一次带着索引跑COPY跑了40多分钟还没结束后来先把索引全部删除COPY只用了3分钟然后重建索引花了8分钟加起来11分钟速度提升非常明显。具体操作是-- 迁移前记录索引定义 SELECT indexdef FROM pg_indexes WHERE tablename your_table; -- 删除索引保留主键约束也可以先删 DROP INDEX IF EXISTS idx_your_table_col1; DROP INDEX IF EXISTS idx_your_table_col2; -- 导入数据 COPY ... -- 重建索引 CREATE INDEX idx_your_table_col1 ON public.your_table (col1);这里有个注意事项如果表上有唯一索引删掉重建后要再次检查重复数据避免COPY期间出现重复值没被拦截。我在实际处理时是先查出重复组再决定是否过滤不是直接重建就完事。4.8 停机窗口内的增量数据简单但必须做的收尾如果你的迁移允许一个短暂停机窗口那流程是这样的在低峰期开始全量导出SQLite快照。导出期间业务会继续写入这些新增数据不会出现在快照里。到达停机窗口后停掉业务写入再导出一次变化的数据追加同步到PG。校验完毕将业务切到PG。难点在于第二步到第三步之间怎么识别“变化的数据”。简单做法是给SQLite表增加一个updated_at字段利用这个字段导出增量。如果原表没有这个字段可以退而求其次在首次快照里记录每张表的MAX(主键)停机时再导出主键大于这个值的记录作为增量补导。我这次因为SQLite库里主键是自增的就用这个“主键窗口”方案写起来很简单-- 增量导出条件 WHERE id {last_synced_id}但要注意如果业务有大量更新旧数据的行为这种方案会漏掉被修改的行。稳妥做法是加updated_at字段并在应用层维护这属于长期方案短期应急就只能接受“新增不丢、修改可能有轻微延迟”的妥协。4.9 外键依赖和孤儿数据的检查如果你在PG里保留了外键约束导入数据时就要特别仔细。外键约束检查是严谨的任何一条子表记录找不到对应主表记录整个导入就会失败。常见原因是老系统里数据本来就有问题比如外键指向的主记录被物理删除过。我在迁移前写了一个检查脚本遍历所有外键关系在SQLite里先执行等价查询找出“孤儿记录”数量。如果数量为0直接销毁再重建外键约束如果不为0就要先把孤儿记录筛选出来跟业务确认是修复还是丢弃。最省事的办法居然是先不要外键约束等数据导入完毕再单独跑一遍孤儿检查这时发现问题只会影响单张表而不会让整个导入全部回滚。处理方法比预想中简单得多趁停机窗口内对异常数据进行修正然后再开启约束。4.10 权限与角色问题给迁移账号最低配置PG的权限模型比SQLite复杂得多。SQLite就是一个文件谁有文件权限谁就能读写PG区分了登录权限、库权限、模式权限、表权限、序列权限少一个都会导致业务运行时报错。迁移时用超级用户操作没问题但迁移完成后业务账号要能正常读写就需要注意表要有SELECT/INSERT/UPDATE/DELETE权限。序列IDENTITY对应序列要有USAGE权限。需要能连接数据库、使用schema的usage权限。如果你是通过GRANT ALL ON ALL TABLES IN SCHEMA public TO business_user这种方式授权别忘了还有默认权限ALTER DEFAULT PRIVILEGES的问题否则之后新建的表业务账号依然没有权限。我这次就在这个坑上浪费了大半天表数据都迁移完了应用一启动报permission denied for table xxx翻日志才发现新表的权限没给全。后来把默认权限补上才恢复正常。你可以理解为不配默认权限就像是你给同事开了办公室的门但没告诉他未来新房间的钥匙也都得给他。彻底解决是执行ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO business_user;5. 迁移后的验证与性能压测准备脚本跑完、数据校验通过不代表迁移交付完毕。还要做业务功能验证和性能摸底尤其是你后面还跟着压测任务这个环节做扎实了压测才能顺利通过。5.1 业务层面验证的用例清单业务验证我列了一个清单逐项签字确认登录链路账号密码校验是否正常对应SQL是否用到新库的新索引。主流程CRUD最核心的增删改查逐一过一遍确认insert能取到新主键、update能命中行、delete能执行成功。历史数据查询拿几个用户反馈过的历史订单号、历史流水号验证能够查得出来且字段内容没有乱码。关联查询订单表关联明细表、用户表关联角色表典型的JOIN查询要跑一遍确认外键关系没有丢。定时任务如果有定时任务试跑一次看是否出现连接池不够、事务冲突等新库特有的问题。这里有一个容易忽略的细节连接字符串。SQLite的连接就是jdbc:sqlite:app.dbPG的连接串是jdbc:postgresql://host:5432/dbname。很多应用改了数据库驱动和连接串但配置里可能遗漏了currentSchema、timezone、stringtypeunspecified等参数而这些参数会影响查询行为和类型返回。我在迁移时专门对照过两边的JDBC/驱动版本确保连接参数一致。5.2 压测前要准备好的数据基线与指标你提到迁移完成后由压测人员使用jmeter脚本做高并发测试验证云上环境承载能力。在一上来压测之前我们至少要准备两样东西数据基线将迁移后的行数、关键表数据量、索引大小记录下来作为压测后对比的基线。压测如果产生大量脏数据也要能一键清理避免影响后续业务。指标采集方案PG侧开启pg_stat_statements扩展监控制定SQL的执行计划变化同时部署node_exporter或类似工具采集系统CPU、内存、IO用来分析瓶颈。连接池配置应用侧连接池最大连接数要跟PG的max_connections匹配否则高并发压测时直接报too many clients already。压测过程中如果发现某些查询慢第一件事不是调SQL而是执行EXPLAIN ANALYZE看执行计划。很多从SQLite迁过来的SQL在PG上因为没有合适的索引而走全表扫描这时候补上对应索引压测指标会立刻好很多。这个坑我提醒过压测同事他们也反馈确实有几个慢查询是索引缺失导致的补完索引后吞吐量翻了一倍。6. 常见问题与排查思路速查表把前面所有坑浓缩成一张表方便你迁移时随时翻查。可以当作Checklist用。问题现象可能原因解决方案COPY导入报invalid input syntax for type booleanSQLite布尔字段存了非0/1/true/false的脏值先SELECT DISTINCT检查数据清洗后再导入时间查询结果与原来差8小时SQLite存本地时间PG的TIMESTAMPTZ做了UTC转换列改用TIMESTAMP WITHOUT TIME ZONE保持原语义表名找不到PG大小写规则导致大小写混用表名被转小写统一小写表名保留字加双引号插入新数据主键冲突自增序列起点没有重置使用setval(pg_get_serial_sequence(...), MAX(id))大量行插入非常慢COPY没开启或索引外键没有临时禁用用COPY先删索引、禁用触发器导入后重建JSON字段导入报错SQLite存了非法JSON文本导出前清洗无法修复的置为NULL业务账号查询报权限不足只授了库级权限没有表级、序列权限补GRANT和ALTER DEFAULT PRIVILEGES外键约束重建失败SQLite中存在孤儿记录先查孤儿数据迁移后修正或清理COPY时报extra data after last expected column导出的TSV里换行符没转义导致一行数据被拆成两行导出时对字段内部的换行符做转义统一改为\n字符串中文乱码SQLite中的UTF-8文本被错误转码统一使用UTF-8编码CSV/TSV都声明编码排查问题的习惯也很重要。我每次迁移脚本跑崩第一反应不是看报错堆栈而是截取报错上下文前后的数据行先定位是导出的问题还是导入的问题。比如COPY报错时PG会提示类似COPY your_table, line 12345, column ...这就是定位的线索。拿这条记录去SQLite里查原始值一看就明白是哪类转义或类型问题。千万别说“数据量太大没法查”真实生产环境靠的就是这样一行一行揪出问题。写在最后的一点个人体会数据库迁移这种活听起来就是把数据从一个地方搬到另一个地方真正做起来才知道最花时间的不是搬数据本身而是处理那些历史遗留的“脏”数据、类型混乱的字段、和各种数据库方言之间的行为差异。SQLite到PG还好至少两者都有成熟生态你要是碰到过某些老商业数据库的私有类型那才是真正的折磨。我个人的建议是迁移脚本不要一味追求写得花哨稳定和执行速度才是第一位的。数据导出用简单的流式读写PG导入用COPY两步之间留出可检查的中间文件出了问题能快速定位、断点续跑。这套思路不仅适用SQLite到PG其他关系型数据库之间互相迁移逻辑上也完全说得通。最后再分享一个小技巧迁移正式执行之前一定要在一个全量备份的副本上演练至少一遍。演练过程中记录每一步耗时尤其是COPY导入和索引重建的时间这样正式停机切换时你心里有数不会出现“以为半小时跑完结果三小时还没结束”的尴尬局面。数据无小事多演习一次生产环境就多一分从容。