SQLite迁移PostgreSQL：准不停服方案与高并发踩坑实录

最近刚把一套单节点 k8s 上跑的若依微服务整库从 SQLite 迁到了 PostgreSQL整个过程要求准不停服、不丢数据迁移完还要交给压测组用联调好的 jmeter 脚本做高并发验证。这一趟折腾下来踩的坑比预想的多得多。趁着记忆还热我把迁移脚本的思路、切换方案和完整的踩坑清单整理出来给后面要做同样事的人当个参考。先说一个反直觉的结论SQLite 到 PostgreSQL 的迁移最难的部分不是写迁移脚本而是把 SQLite 那种宽松到随意的数据模型翻译成 PostgreSQL 这种严格到较真的类型系统。大部分报错都发生在你以为数据很干净、结果导入中途崩掉的那一刻。1. 为什么迁单节点 K8s 上那套业务的瓶颈扛不住了1.1 我这次的迁移背景从单节点 k8s 到阿里云 ECS背景是这样的这套若依微服务环境最初部署在单节点 k8s 上数据库直接用了 SQLite挂在 PVC 里图的就是轻量、免运维、备份就是一个文件拷走完事。前期内部使用完全没问题毕竟 SQLite 单文件架构在低并发下确实舒服。但随着接入方变多、业务数据增长问题开始冒头。更直接的压力来自一个硬性指标迁移完成后压测组要用配套的 jmeter 脚本在云上做高并发测试验证这套环境能不能扛住线上流量。SQLite 这种嵌入式数据库在高并发写场景下基本处于劣势——它的锁机制注定了不适合作为微服务架构的统一数据存储。所以迁移到部署在阿里云 ECS 上的 PostgreSQL属于业务发展的必然选择。1.2 SQLite 在微服务场景下的三大痛点第一痛是并发写。SQLite 本质上是一个单写者模型虽然 WAL 模式能让读操作和写操作并行但写事务仍然是串行的。多个服务实例同时写同一张表很快就会出现 database is locked微服务架构天然多实例并发这个矛盾是结构性的。第二痛是网络访问。SQLite 是嵌入式数据库它可以被同一个进程内的多个连接打开但它不是 C/S 架构。跨服务通过网络去访问另一个 Pod 里的 SQLite 文件不仅性能差而且文件锁在分布式环境下本来就是伪命题。微服务拆分之后每个服务直连一个 SQLite 文件的做法很快就会失控。第三痛是运维和可观测性。SQLite 没有用户权限体系没有网络端口没有慢查询日志做监控和备份都比较原始。而 PostgreSQL 有成熟的权限模型、WAL 归档、流复制、pg_stat_statements 这些运维手段对于要上云、要压测、要长期维护的场景来说这是刚需。针对准不停服、不丢数据这个要求第 4 章会详细展开讲。你如果只是测试环境直接停服慢慢迁就行但如果是业务已经在跑的状态切换窗口的设计直接决定这次迁移是平滑的还是痛苦的。2. 迁移前必须先搞懂的类型差异SQLite 的随性与 PostgreSQL 的较真2.1 SQLite 类型亲和性到底是怎么回事SQLite 的文档里有一个关键词叫 type affinity中文一般翻译成类型亲和性。它的意思是你声明一个列为 INTEGER但如果你往里面插入一个字符串只要这个字符串可以被无损转换成整数SQLite 就收下了如果转换不了它也会存成 TEXT而不是报错。这跟 PostgreSQL 完全不同。PG 是严格类型系统你往 integer 列里插入 abc直接报错 invalid input syntax for type integer。所以在迁移前你必须对源库的数据做一次全面的体检看看哪些列表面上声明为 INTEGER实际数据里混着文本或空字符串。我这次遇到的一个真实例子某张配置表有个字段声明为 INTEGER但历史数据里有一条记录的字段值是空字符串 。在 SQLite 里一切正常但导入 PG 时崩了。解决办法是写脚本做清洗把 转成 NULL再把所有脏数据列出来人工确认一遍。迁移前的数据体检不是可选项而是必选项。2.2 自增主键最容易翻车的第一站SQLite 的自增主键是 INTEGER PRIMARY KEY它是 rowid 的别名。如果你声明了 AUTOINCREMENTSQLite 会额外维护一个 sqlite_sequence 表来防止 rowid 复用。但这个机制和 PostgreSQL 的序列完全是两码事。PostgreSQL 里最常用的自增方式是 SERIAL 或 IDENTITY本质上是创建一个序列sequence然后列的默认值取 nextval。迁移时如果只建表、导数据不设置序列的当前值那么新插入的数据会从 1 开始取序列号跟已有的主键冲突应用一启动就报主键重复。解决办法是在数据导入完成后对每张有自增主键的表执行一次序列重置SELECT setval( pg_get_serial_sequence(sys_user, user_id), (SELECT COALESCE(MAX(user_id), 1) FROM sys_user) );如果表很多用脚本批量生成这类 SQL而不是一张一张手写。这也是为什么迁移脚本要用程序自动产出的原因之一光靠手工根本忙不过来。2.3 布尔值、时间字段、JSON 与特殊类型的处理布尔值这块的坑比较隐蔽。SQLite 没有独立的布尔类型通常用 INTEGER 0 和 1 表示。而 PostgreSQL 的 boolean 类型接受 true/false也接受 t/f、yes/no、1/0但在批量导入时如果列类型是 boolean你直接喂数字是没问题的问题出在有些历史数据写入了 true 或 True 这种大小写混杂的字符串。稳妥做法是统一转成整数 0/1再让 PG 自己转换。时间字段是另一大坑。SQLite 里大家习惯用 TEXT 存 2024-01-01 12:30:00这没问题。但有些表里会出现 2024-01-01 12:30:00.123 或 2024-01-01T12:30:00Z 这种不统一格式。导入 PG 的 timestamp 列时格式不标准就直接报错。预处理阶段统一用 Python 的 datetime 解析输出成 PG 能识别的标准格式。如果应用层对时区有要求建议直接使用 timestamp with time zone避免后续排查时区偏移。JSON 字段也需要规划。SQLite 可以通过 json1 扩展存储 JSON 文本本质上还是 TEXT。PG 里建议用 jsonb因为它在索引、查询上的能力远超纯文本存储。迁移时用 json.dumps 把 Python 对象序列化后再导入避免格式问题。BLOB 对应的是 BYTEA这个直接映射但要注意大字段的导入性能后面第 3 章会讲。3. 迁移脚本的设计从 sqlite3 到 psycopg2 的全量搬迁3.1 为什么不用现成工具而是手写脚本市面上的迁移工具不算少DBeaver 自带数据迁移功能SQLite 的 .dump 命令也能输出 SQL 文本再交给 psql 执行。但实际跑下来这些方案在我们的场景里都有不太顺手的地方。.dump 导出的 SQL 是 SQLite 方言里面包含很多 PG 不认识的语法比如 AUTOINCREMENT 关键字、双引号作为字符串字面量、PRAGMA 语句等。你仍然需要写一套文本处理脚本去清洗工作量不小。DBeaver 的迁移功能适合表结构比较规范的场景但遇到自定义类型、触发器、特殊约束时就容易翻车。而 SQLite 到 PG 的映射逻辑虽然复杂但它本质上是确定性的规则。用 Python 脚本 sqlite3 psycopg2 可以精确控制每一步遇到类型问题当场修改映射规则不用担心工具黑盒。对于几十张表的业务系统来说这是最平衡的方案。还有一点微服务通常会拆多个库每个服务一张 SQLite 文件。手动挨个导不现实脚本可以做成循环批量处理。这也是选脚本的核心理由。3.2 全量导出脚本的骨架下面这个脚本骨架是根据我这次迁移的实际流程简化出来的核心思路是读 SQLite 元数据建表、转类型、导数据。import sqlite3 import psycopg2 from psycopg2.extras import execute_values SRC_DB app.db PG_DSN hostpg-host dbnameappdb userapp password*** # SQLite 类型 - PG 类型的映射按需扩充 TYPE_MAP { INTEGER: INTEGER, INT: INTEGER, BIGINT: BIGINT, TEXT: TEXT, VARCHAR: VARCHAR, CHAR: CHAR, REAL: DOUBLE PRECISION, FLOAT: DOUBLE PRECISION, NUMERIC: NUMERIC, BLOB: BYTEA, BOOLEAN: BOOLEAN, DATETIME: TIMESTAMP, TIMESTAMP: TIMESTAMP, JSON: JSONB, } def convert_col_type(sqlite_type: str) - str: SQLite 列类型 - PG 列类型 upper (sqlite_type or TEXT).upper() # 处理 VARCHAR(255) 这种带长度的类型 for prefix, pg_type in TYPE_MAP.items(): if upper.startswith(prefix): return pg_type return TEXT def build_pg_table(src_conn, table: str): 根据 PRAGMA table_info 生成 PG 建表语句 cols src_conn.execute(fPRAGMA table_info({table})).fetchall() col_defs [] for cid, name, ctype, notnull, dflt, pk in cols: col_defs.append(f{name} {convert_col_type(ctype)}) return fCREATE TABLE IF NOT EXISTS {table} (\n ,\n .join(col_defs) \n);这里的 TYPE_MAP 是整个迁移的关键配置。不同业务的 SQLite 建表语句风格差异很大有的喜欢写 INTEGER有的写 INT有的直接不写类型脚本必须做兜底处理。在正式迁移前我建议先跑一遍建表语句生成人工过目所有 DDL确认没有类型明显不对的地方再执行。3.3 分批插入与性能优化数据导入层面要特别注意两个性能问题。第一个是单条 INSERT 的性能损耗逐行插入几百万行数据光网络往返就够喝一壶。第二个是索引维护的开销边插数据边维护索引比全部数据插入后再统一建索引慢很多。我的做法是先建表不含索引然后用 execute_values 批量插入每批 1000 行左右最后再统一创建索引和约束。execute_values 是 psycopg2 提供的高效批量插入方法它会把多条 INSERT 合并成一条多 VALUES 的语句性能比逐行 executemany 好不少。def copy_table(src_conn, dst_conn, table: str, batch_size: int 1000): 分批全量复制一张表 columns [row[1] for row in src_conn.execute(fPRAGMA table_info({table}))] col_sql , .join(columns) placeholders , .join([%s] * len(columns)) rows src_conn.execute(fSELECT {col_sql} FROM {table}) batch [] for row in rows: # 对脏数据做清洗比如空字符串转 NoneJSON 序列化 cleaned [clean_value(v, col) for v, col in zip(row, columns)] batch.append(cleaned) if len(batch) batch_size: sql fINSERT INTO {table} ({col_sql}) VALUES %s execute_values(dst_conn.cursor(), sql, batch, page_sizebatch_size) dst_conn.commit() batch [] if batch: execute_values(dst_conn.cursor(), sql, batch, page_sizebatch_size) dst_conn.commit()分批大小不是越大越好。我测试下来 1000 行一批是比较稳定的值再大容易让 PG 的 WAL 和内存产生压力再小又显现不出性能优势。你如果导的数据里有比较大的 BYTEA 字段建议把批次调小避免单条 SQL 过大触发 PostgreSQL 的 max_allowed_packet 一类限制。4. 准不停服的迁移流程全量快照 增量回放 短时切换4.1 为什么是准不停服而不是零停机零停机听起来很美好但代价往往是应用层必须做双写改造业务代码里每个写操作都要同步写 SQLite 和 PG还要处理一致性、失败补偿。对于一个正在运行的微服务系统来说这个改造周期和风险都不小。准不停服的思路是迁移过程中正常服务照跑不中断用户访问但在最终切换的短暂时间窗内需要把业务置为只读或短暂停写等增量数据追上、切换完成后再恢复。对于后台管理系统这类场景在凌晨低峰期停写 1-5 分钟业务上完全可接受。相比之下零停机的开发和验证成本却要高出一个量级。所以准不停服是应对这类场景更务实的方案。4.2 增量日志与回放机制的具体实现增量日志的思路是这样的在全量导出开始之前给需要迁移的表加一组迁移触发器业务后续的每次写入都会同步记录一条操作日志到 migration_log 表。这样全量导入完成之后我们可以按照日志顺序把这段时间新增的数据回放到 PG实现数据追赶。-- SQLite 侧迁移日志表 CREATE TABLE migration_log ( id INTEGER PRIMARY KEY AUTOINCREMENT, table_name TEXT NOT NULL, row_id INTEGER NOT NULL, action TEXT NOT NULL, -- INSERT / UPDATE / DELETE payload TEXT NOT NULL, -- 变更后的整行 JSON created_at TEXT DEFAULT (datetime(now)) ); -- 以 sys_user 表为例插入触发器 CREATE TRIGGER trg_sys_user_insert AFTER INSERT ON sys_user BEGIN INSERT INTO migration_log (table_name, row_id, action, payload) VALUES (sys_user, NEW.user_id, INSERT, json_object( user_id, NEW.user_id, user_name, NEW.user_name )); END;回放的时候按 id 顺序逐个处理INSERT 就 insert or updateDELETE 就按主键删除。这个触发器方案比盲目双写要轻量很多而且不需要改业务代码风险可控得很好。一个值得注意的点是SQLite 的触发器本身也属于要迁移的对象在完成切换后要记得清理掉否则老库还在继续记日志时间长了会占空间。4.3 切换窗口的操作顺序与回滚预案这是我这次迁移里最核心的编排步骤实际执行顺序如下在业务低峰期提前部署迁移触发器并启动全量导出。全量导出导入完成后记录当前 migration_log 的最大 id 为 last_synced_id。应用进入只读维护模式或者如果业务上能接受直接停写。这里需要和运维、产品提前打招呼最好选在凌晨。回放 migration_log 里 id 大于 last_synced_id 的增量变更。执行数据校验第 6 章会详细讲确认无差异。修改配置中心或环境变量中的数据库连接串指向 PostgreSQL。重启相关微服务做一轮冒烟测试。确认稳定后保留 SQLite 备份 24 小时以上再清理触发器。回滚预案同样要在切换前写好如果步骤 7 冒烟测试发现严重问题直接把连接串改回 SQLite、重启服务即可migration_log 不会丢失任何增量变更。这里有个细节步骤 4 回放和步骤 5 校验之间业务随时可能再产生新变更如果切换期间业务还在持续写入校验时要把这个时间点纳入考虑记录一个明确的切换时间点前后各查一次确保一致。5. 十七个坑我一个个排过来的5.1 数据与类型层面的坑坑 1INTEGER 列里的空字符串导致导入中断。这个前面提过SQLite 对类型的宽容让脏数据存活多年PG 一上来就严格校验。解决思路是清洗脚本里把数字列的 统一转成 None但更要紧的是把这些问题数据导出一份 CSV 交给业务方确认而不是悄悄替他们做决定。坑 2自增主键序列未重置。数据是导进去了但序列还停在 1。不重置序列的后果是应用第一次插入数据就报 duplicate key。这也是迁移后第一个晚上最容易炸的雷。坑 3布尔值存成了字符串 true 或 True。PG 的 boolean 类型能接受大小写不一的 true/false但如果数据里混了 yes、on、1 这种带空格的脏值批量导入也会报错。psycopg2 在处理 bool 类型时比较严格统一转成 Python 的 True/False 最省事。坑 4时间格式不统一导致 timestamp 解析失败。有的行是 2024-01-01 12:00:00有的行是 2024-01-01T12:00:00Z有的直接存了时间戳整数。统一用 dateutil.parser 解析兜底解析失败的单独列出来人工处理。5.2 SQL 行为差异导致的坑坑 5GROUP BY 行为不同。SQLite 允许 SELECT 非聚合列会取该组的第一行勉强能用但不严谨。PG 对此直接报错强制你写清楚聚合逻辑。若依这种框架里的查询其实比较规整但某些历史统计 SQL 可能依赖 SQLite 的宽松行为迁移后应用启动报错就得一条条改 SQL。这个坑的排查成本最高。坑 6字符串比较 collation 的差异。SQLite 默认按二进制字节比较PG 默认按数据库 collation 比较。如果库里有中文排序或大小写不敏感查询行为会不一样。最典型的是用户名查询SQLite 里 Admin 和 admin 是不同的但 PG 在某些 collation 下不区分大小写结果集可能和预期不一致。解决方案是建表时显式指定 collation比如对于需要严格比较的列加 COLLATE C或者查询时用配合显式类型转换。坑 7正则表达式函数不兼容。SQLite 默认没有内置 REGEXP有些项目会通过自定义函数实现简单的正则匹配。PG 里有~和~*运算符虽然能力更强但语法完全不同。迁移后如果业务代码里有正则查询必须逐条重写。而且要注意 PG 正则默认区分大小写想要不区分要用~*。5.3 环境与工具的坑坑 8psql 导入时中文乱码。SQLite 数据文件一般是 UTF-8但导出时的环境变量会影响客户端编码。用 Python 导出时要把字符串显式编码成 UTF-8不要依赖终端环境。否则导到 PG 里的中文显示乱码排查起来很费劲。坑 9连接池配置参数不兼容。若依微服务里的连接池用的是 HikariCP原来连 SQLite 时的配置字段和 PG 不一样。比如 SQLite 不需要 validationQuery但 PG 建议配置SELECT 1来保证连接可用。迁移后如果发现数据库连接频繁失效优先检查连接池的 test-while-idle 配置。坑 10事务隔离级别的理解偏差。SQLite 的默认事务行为是串行化的写锁PG 默认是 read committed。并发高的场景下PG 可能出现同一条记录被多个事务修改的冲突部分老代码依赖 SQLite 的串行写缺少乐观锁重试压测时会出现 unexpected error。排查方法是把报错堆栈里的 SQL 拿出来在 PG 里手动跑并发事务复现。坑 11schema 权限问题。PG 的对象是挂在 schema 下的。导数据时如果不指定 search_path或者用非超级用户导数据可能遇到 permission denied for schema public。如果不想纠结权限让 DBA 给应用账号授权对应 schema 的 USAGE 和 CREATE 权限同时连接串里加上options-csearch_pathpublic。坑 12dbeaver 对比数据时的坑。图形工具很好用但用 dbeaver 对比大表时它默认只取前几百条预览容易造成数据一致的错觉。数据校验不能完全依赖 GUI 工具必须用脚本做全量对比。坑 13SQLite 双引号字符串导致 PG 语法错误。SQLite 默认可以把双引号里的内容当作字符串字面量虽然不规范但很多人这么写。PG 双引号是标识符。如果历史 SQL 里有WHERE name admin在 PG 里会变成查找列名为 admin 的值报 column does not exist。这个坑很隐蔽因为直接看 SQL 根本发现不了。坑 14LIKE 模糊查询大小写差异。若依框架里很多查询默认用 LIKE %关键字%。SQLite 对 ASCII 字符的 LIKE 默认不区分大小写PG 则区分。对于账号、邮箱这类字段同一 SQL 在两个库里的查询结果可能不同导致登录或搜索功能表现不一致。坑 15数据库时区设置不一致。阿里云 ECS 上的 PG 如果没设置 timezone默认可能用 UTC。而业务日志或创建时间字段如果在 SQLite 里存的是本地时间切到 PG 后显示会差 8 个小时。建议初始化 PG 时就把timezone Asia/Shanghai写进 postgresql.conf。坑 16serial 类型不会自动更新序列起始值。这个和第 2 章说的一致但值得再强调一次。哪怕你建表用的是 GENERATED ALWAYS AS IDENTITY导入数据后序列也不会自动跟随现有数据必须手动 setval。坑 17大表迁移时 WAL 膨胀导致磁盘告急。PG 导入大量数据时会产生大量 WAL 日志如果 ECS 数据盘只剩一半空间可能导入到一半就磁盘满了。解决办法是迁移期间临时调大max_wal_size或者用pg_dump的 single transaction 思路控制 checkpoint。对于自建 PG 来说这是一项很有必要的事前检查。6. 迁移完成后的验证与 jmeter 高并发压测6.1 数据一致性校验不能只查行数迁移完成后的第一件事不是上压测而是做数据一致性校验。只对比行数是远远不够的因为行数相同不代表数据内容相同。我的校验脚本做了三层第一层逐表统计行数这层最基础只能发现明显的漏导或重复导入。第二层对每张表做字段级抽样比对随机抽 1000 条记录的多个关键字段计算 MD5 后对比两边的值。第三层针对关键业务表做汇总校验比如金额字段的 SUM、状态字段的分布确保迁移前后整体数据没有偏移。对于自增主键还要单独确认序列的 nextval 值大于当前表里最大主键值。这个可以用一条 SQL 查出来SELECT c.relname, last_value FROM pg_sequences s JOIN pg_class c ON c.relname s.seqname ORDER BY c.relname;6.2 PG 侧的关键参数调优压测前不做任何参数调整是很多团队常犯的错。PG 的默认配置偏向保守直接上 jmeter 高并发连接数、共享内存很容易成为瓶颈。ECS 机器如果是 8C16G 的规格我会先把这几个参数调一遍max_connections默认 100配合连接池后按业务预估调到 300 左右太高会导致系统过度切换反而性能下降。shared_buffers设置为内存的 1/4比如 4GB 内存给 1GB不是越大越好。work_mem8MB 默认值对排序、哈希聚合非常不友好压测阶段调到 64MB但要观察内存压力。effective_cache_size这个参数告诉优化器系统有多少内存可被用作文件缓存一般设为物理内存的一半。改完参数记得重启 PG 或者SELECT pg_reload_conf();部分参数需要重启。同时可以在压测前安装并打开 pg_stat_statements 扩展这样压测完成后能直观看哪些 SQL 消耗时间最多。6.3 jmeter 压测的观察点压测人员用的是配套的 jmeter 脚本我的职责是配合确认数据库侧没有成为瓶颈。压测过程中重点观察三样东西第一个是数据库连接数是否打满连接池排队等待时间是否持续上涨第二个是慢查询日志里有没有突然出现执行时间超过 1 秒的 SQL第三个是磁盘 IO 的等待时间如果持续在 20% 以上考虑提高 shared_buffers 或优化查询。压测过程中如果有报错优先从响应时间的 95th 和 99th 分位看起。通常数据库迁移本身不会导致性能断崖式下降真正的问题是 SQL 语法虽然能跑但原来的查询方式在 PG 里的执行计划不理想比如嵌套循环、顺序扫描。这时候用 EXPLAIN ANALYZE 逐条分析找到执行计划变化较大的 SQL该加索引加索引该改写改写。7. 最后补几句针对同样场景的真心建议7.1 演练至少跑两次一次也别省迁移最大的风险从来不是技术细节而是第一次做的不确定性。我在正式迁移前做了两次全流程演练第一次专门用来暴露问题比如某个表的数据量比预想大十倍、某个时间字段格式比预想更乱第二次验证问题是否修干净。这两次演练帮我在正式切换时做到了心里有底。演练时建议走完全的流程部署触发器、全量导出、增量回放、切换、校验、回滚一步都不能省。只测导出不测切换等于没做。7.2 切换后的 24 小时是最关键的观察期切换完成不等于迁移完成。我在切换后保留了 24 小时的 SQLite 备份同时每天定时跑一次行数对比和关键字段校验。如果这 24 小时内没有出现异常基本可以宣告迁移成功。另外切换后的第一个业务高峰要有专人盯着 PG 的慢查询和错误日志因为有些隐藏问题在压测环境未必能完全复现。7.3 迁移脚本本身也是资产这次迁移写的脚本我整理之后放进了内部的运维工具库以后再有项目要从 SQLite 迁过来直接复用映射关系和校验逻辑省掉很多重复工作。这也算是我对这次折腾的一个交代——踩过的坑如果只是记录在文档里价值会大打折扣沉淀成可复用的脚本才是这笔成本真正换来的回报。