SQLite迁移PostgreSQL全攻略：脚本写法与踩坑避坑指南

你们有没有遇到过这种局面项目在本地和测试环境一直用 SQLite跑得好好的一上生产或者准备做高并发压测就现原形。我最近就刚把一套跑在单节点上的服务从 SQLite 整体迁到了 PostgreSQL迁移脚本整理完以后踩过的坑也差不多能写一本小册子了。这篇就聊两件事一是那套迁移脚本到底应该怎么写二是从导出、转换、导入到验数这一路有哪些坑值得你提前避掉。如果你是刚准备把 SQLite 换到 PostgreSQL或者正在做类似的数据搬迁这篇文章可以直接拿来当参考。1. 迁移背景与整体方案设计1.1 这次迁移的实际场景先说背景。这套服务以前是一套跑在单节点上的微服务基于若依微服务框架改的容器编排在单节点 k8s 上。开发阶段为了省事数据库直接用的 SQLite单文件、零运维本地联调非常舒服。但是要往云上正式环境迁还得接受高并发压测SQLite 就顶不住了——它本质上是单写者模型并发一上来就频频出现database is locked而且备份、监控、权限管理这些能力都要自己拼。所以目标很明确在不停服或者说尽量少停服的前提下把历史数据完整搬到 PostgreSQL部署到云上 ECS再配合 JMeter 脚本做高并发验证用数据说话看看云上环境到底能扛多少并发。迁移脚本的核心诉求就三点数据不丢、类型不串、跑完能直接接业务流量。1.2 迁移工具的选型思路迁移工具我没有一上来就写脚本而是先试了几条路线最后才定下来。第一条是用 DBeaver 的导入导出功能把 SQLite 的表结构导出成 SQL手工改一改再到 PostgreSQL 里执行。这条路适合表少、结构简单的情况表一多就很痛苦外键、索引、自增主键全都得手工改稍不注意就漏一个。而且 DBeaver 做的是数据搬运不负责类型映射脏数据它一样给你原封不动搬过去。第二条是 pgloader这是 PostgreSQL 生态里最正统的迁移工具一条命令就能把 SQLite 的 schema 和 data 都搬过来内置了类型转换、批量写入、序列重置这些能力比手写脚本省心很多。第三条才是自己写迁移脚本控制力最强适合数据里面有坑、类型有脏情况的场景。最后我实际采用的是 pgloader 跑主流程再配合自研校验脚本做逐表核对特殊情况用补丁脚本修复。两条腿走路既快又稳。1.3 整体流程拆解整个迁移流程我分成了六步盘点存量摸清有哪些表、哪些字段、哪些索引和外键估算数据量。准备目标环境在 ECS 上安装 PostgreSQL规划好用户、库、schema 和连接数。结构迁移把 SQLite 的表结构转换成 PostgreSQL 风格重点处理自增主键、布尔值、时间戳。数据迁移批量灌入数据先不建索引和外键速度会快很多。收尾修复重建索引、外键、序列执行ANALYZE更新统计信息。校验与切换逐表核对行数和抽样数据确认无误后切换应用连接串。这个顺序非常关键。很多人一上来就想着先建全索引再导数据结果一条几百万行的表导得奇慢无比。正确做法是结构先到、数据先灌、索引后建效率能差一个数量级。2. 迁移脚本核心原理与数据类型映射2.1 数据库类型映射是怎么定出来的SQLite 和 PostgreSQL 最大的区别是类型体系。SQLite 是动态类型字段声明成 INTEGER 也不影响你往里存字符串全靠typeof()自己判断PostgreSQL 是静态类型写进表里的每一列都有严格约束类型不对要么报错要么被隐式转换吃掉。所以迁移脚本第一件事就是把类型映射定清楚。下面这张表是我这次项目里实际用到的映射方案可以直接抄走SQLite 类型PostgreSQL 类型说明INTEGER PRIMARY KEYBIGINT GENERATED BY DEFAULT AS IDENTITY注意别直接用 BIGSERIAL后面细说INTEGERINTEGER / BIGINT按数值范围选SQLite 的 INTEGER 上限 2^63-1直接对齐 BIGINT 更稳REAL / DOUBLEDOUBLE PRECISION浮点数对齐双精度NUMERIC(p,s) / DECIMAL(p,s)NUMERIC(p,s)金额类字段必须用这个别用浮点TEXT / VARCHAR(n)TEXT / VARCHAR(n)没有默认长度就上 TEXTPG 的 TEXT 没有性能惩罚BLOBBYTEA二进制大对象DATETIME / TIMESTAMPTIMESTAMPTZ建议统一成带时区类型见踩坑 4.3BOOLEANBOOLEANSQLite 里可能存 0/1 或 true/false需要转换JSON / JSONBJSONBPG 里 JSONB 有索引能力比 JSON 实用其他自定义类型TEXT兜底方案迁移后再业务层处理类型映射看起来简单实际工作量在于 SQLite 里很多字段声明和真实数据并不一致。比如某个字段建表时写的是 VARCHAR但历史数据里混进了整数、空字符串、甚至None。手工写脚本的好处就在这里你可以在读取每条数据的时候做类型清洗而不是等 PG 报错了再回头补。2.2 自增主键与序列重建SQLite 的自增主键靠INTEGER PRIMARY KEY AUTOINCREMENT实现内部维护一张sqlite_sequence表来记录当前序号。PostgreSQL 里对应的是 IDENTITY 列或者序列两者虽然都解决自增需求但数据搬完之后有个非常容易漏的环节sequence 当前值不会跟着数据走。如果导入时保留了原始 id 值但 sequence 还停留在 1下一条新数据就会跟已有主键撞车直接报 duplicate key 错误。正确做法是在迁完后对每一张有自增主键的表重新设置序列终点SELECT setval(pg_get_serial_sequence(app_user, id), (SELECT MAX(id) FROM app_user));pgloader 自带reset sequences这个开关但它在某些带过滤条件的迁移里不一定生效所以我的建议是不管用什么工具搬运迁移后都要跑一遍上面的 SQL 做兜底。我这次还在脚本里加了一步自动校验循环把所有序列当前值和表内 max id 对比不一致就报警。2.3 布尔值、时间戳、大字段的处理布尔值在 SQLite 里通常存成 0 和 1PostgreSQL 的 BOOLEAN 类型虽然也能接受 0/1 的隐式转换但更规范的做法是在迁移脚本里显式转换。pgloader 有现成的转换规则type tinyint to boolean using tinyint-to-boolean但如果 SQLite 表里布尔字段声明成了 VARCHAR并且存的是 true/false 字符串那这条规则就不生效需要自己写 SQL 兜底。时间戳是大坑。SQLite 允许你以 TEXT、INTEGERUnix 时间戳或者 REAL 三种方式存时间迁到 PostgreSQL 之前必须先搞清楚存量数据到底是哪种格式。如果建表规范TEXT 格式一般是YYYY-MM-DD HH:MM:SS直接映射成 TIMESTAMPTZ 没问题如果有大量 INTEGER 格式建议先转成时间再入库UPDATE t SET created_at to_timestamp(created_at) WHERE created_at ~ ^\\d$;BYTEA 字段也值得一提。SQLite 的 BLOB 在 Python 的 sqlite3 驱动里读出来是 bytes直接用 psycopg2 写入 BYTEA 没有任何问题。但如果中间经过了一层 JSON 序列化bytes 会被转成 base64 字符串导入之后再转换就很麻烦。所以迁移脚本里写 BLOB 字段要保证全程走二进制通道不要绕 JSON。2.4 索引、外键、触发器的重建SQLite 默认不强制外键约束只有执行了PRAGMA foreign_keys ON才检查PostgreSQL 则默认就强制外键。这个差异直接决定了导入顺序如果先建外键再导数据遇到历史数据里存在引用断裂的情况导入会直接失败。我这次是先导数据、后建约束让 PG 在收尾阶段做一轮完整的完整性检查。另外要注意pgloader 默认只迁移表、字段、索引和一部分约束触发器基本不带过去。如果你在 SQLite 里写了触发器来维护 update_time 这类字段迁移后需要在 PostgreSQL 里用函数和触发器重建。比如CREATE OR REPLACE FUNCTION set_updated_at() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_app_user_updated_at BEFORE UPDATE ON app_user FOR EACH ROW EXECUTE FUNCTION set_updated_at();索引的迁移也要小心。SQLite 里建索引时指定了名字的迁移后先查一下pg_indexes确认有没有重复没指定名字的PG 会按表名和列名自动生成但顺序可能跟你预期不一致建议在迁移脚本里自己维护一份完整的索引清单最后逐个比对pg_indexes缺哪个补哪个。3. 实操从 SQLite 导出到 PostgreSQL 导入3.1 手工脚本导出需要注意什么虽然最终用 pgloader 跑主流程但我还是先写了一个 Python 手工脚本目的有两个一是验证类型映射逻辑二是处理 pgloader 转不了的脏数据。核心逻辑不复杂sqlite3 读一批psycopg2 写一批关键在批量写入和逐表事务import sqlite3 import psycopg2 from psycopg2.extras import execute_values src sqlite3.connect(legacy.db) dst psycopg2.connect(host127.0.0.1 port5432 dbnameappdb userapp passwordxxx) src.row_factory sqlite3.Row tables [r[name] for r in src.execute( SELECT name FROM sqlite_master WHERE typetable AND name NOT LIKE sqlite_% )] for table in tables: cols [c[name] for c in src.execute(fPRAGMA table_info({table}))] rows src.execute(fSELECT * FROM {table}).fetchall() batch [tuple(r[c] for c in cols) for r in rows] sql fINSERT INTO {table} ({,.join(cols)}) VALUES %s execute_values(dst.cursor(), sql, batch, page_size2000) dst.commit()这个脚本有几个点值得解释。execute_values是 psycopg2 提供的批量插入接口底层会把多条 insert 拼成一条多值语句比逐行 execute 快几十倍。page_size 设为 2000是均衡了内存占用和单条 SQL 的长度如果单行数据有大量 TEXT 字段建议再调小一点避免超出 PG 的 SQL 语句长度限制。PRAGMA table_info拿到的列顺序和 SELECT * 的顺序一致所以 zip 拼 tuple 是安全的。这里没做类型清洗真实场景里我会在拼 batch 之前加一个normalize_value(value, col_type)函数针对布尔、时间戳、空字符串做统一处理。3.2 用 pgloader 做自动迁移的配置写法pgloader 的配置文件写起来很直观我这份 load 文件基本是标准模板LOAD DATABASE FROM sqlite:///data/legacy.db INTO postgresql://app:secret127.0.0.1:5432/appdb WITH include drop, create tables, create indexes, reset sequences, batch rows 5000, batch concurrency 4, prefetch rows 1000 CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null, type tinyint to boolean using tinyint-to-boolean; SET maintenance_work_mem to 128MB, work_mem to 16MB;运行方式很简单一行命令pgloader migrate.loadinclude drop表示目标库里如果有同名表就删掉重建适合反复跑迁移的场景但生产环境慎用。batch rows和batch concurrency控制写入批量和并发数我试过几千行的表用默认值就行几百万行的表把 batch size 调到 5000 以上能明显提速。SET maintenance_work_mem是给创建索引期间用的调大一点能缩短重建索引的时间。pgloader 也不是万能的。它不会迁移 SQLite 里由触发器维护的默认值逻辑也不会处理某些自定义的日期格式。所以 pgloader 跑完后我通常还会把手写脚本当成“补丁工具”针对具体脏数据做二次清洗。3.3 数据一致性校验方法数据搬完不等于迁移完成校验才是重头戏。我这次做了三层校验第一层是行数对比。最简单也最直观逐表数行数-- PostgreSQL 端 SELECT app_user AS tbl, count(*) FROM app_user UNION ALL SELECT order_info, count(*) FROM order_info;SQLite 端用同样逻辑跑一遍两边行数不一致就先定位哪些表有问题。第二层是抽样内容对比。行数一致不代表内容一致我用 Python 脚本对每张表随机抽 50 到 100 条记录取 id 和几个关键字段两边拉出来逐字节比对。这种方法能发现绝大多数类型转换错误。金额、状态、时间这三个字段是比对重点出问题的概率最高。第三层是聚合校验。针对数字字段把最大值、最小值、求和值都算出来做对比。比如订单表的总金额SQLite 端算一次PG 端算一次对不上就说明有数据在迁移过程中被吃了或者被改了。这一层能覆盖抽样碰不到的范围。3.4 不停服或少停服的切换策略SQLite 没有内置复制能力所以“准不停服”实际上做不到绝对零停服但可以把停服窗口压缩到分钟级。我的做法分三步先并行部署。在云上 ECS 把 PostgreSQL 环境搭好数据通过上面说的流程预迁移过去应用代码还是继续跑在旧环境里写 SQLite。接着在业务低谷期开一个极短的写冻结窗口。把应用的写入入口暂时停掉或者切到只读模式趁这个窗口把 SQLite 最后一次增量数据搬过去。这个窗口长则三五分钟短则几十秒取决于增量数据量。最后切换连接串。在 k8s 里其实就是更新 ConfigMap 里数据库地址和驱动配置然后滚动重启 Pod。PostgreSQL 连接串从jdbc:sqlite:...换成jdbc:postgresql://...JVM 驱动换成 PostgreSQL JDBC重启用滚动方式逐批替换容器不会所有实例同时不可用。这里有个小细节应用代码里如果大量使用了 SQLite 专属语法比如insert or replace、limit ? offset ?切换前要做一轮语法兼容改造。这回迁移就遇到不少 MyBatis XML 里写死 SQLite 方言的地方最后是全量搜了一遍sqlite关键词才排干净。4. 踩坑清单这些问题我全都遇到过4.1 自增主键的序列断档这个坑几乎每张带自增主键的表都会踩一遍。迁移之后不重建 sequence业务一写入就报duplicate key value violates unique constraint。我第一次跑完迁移后压测脚本刚发起来用户注册接口就直接刷屏报错排查了半天才发现是序列没重置。最标准的修复办法就是前面写的setval语句但要注意pg_get_serial_sequence只对 SERIAL 或 IDENTITY 列生效。如果你建表时用的是普通 BIGINT 自己维护序列那就得手动同步序列名和目标值这点在自动化脚本里要单独兼容。4.2 布尔值你真的转对了吗SQLite 的布尔值在库里可能存成三种形态0/1 整数、true/false 字符串、或者干脆是 NULL。直接导到 PG 的 BOOLEAN 列0/1 和 true/false 都能被隐式转换但字符串可能有大小写混用的极端情况比如 True、FALSE一旦出现就导入失败。更隐蔽的是那些本应是布尔但 SQLite 里存了其他值的记录比如某条数据的状态位存成了字符串 yes。这种脏数据在 SQLite 里跑得欢到了 PG 就直接报错。手工脚本里我加了一层兜底逻辑把所有非 0/1 的布尔候选值先归一到 0 或 1再交给 PG迁移成功率立刻上去了。4.3 时间戳与时区问题SQLite 存时间基本靠自觉有的表存 ISO 字符串有的存 Unix 时间戳还有的用datetime(now)生成的 UTC 字符串。PostgreSQL 的 TIMESTAMPTZ 类型会把输入的字符串按当前会话时区解释两边时区不一致导进去的时间就整体偏移了。我的建议是迁移目标统一用 TIMESTAMPTZ并且全链路都按 UTC 处理。应用层读出来之后再转本地时区展示。如果你存量数据里已经是带时区的 ISO8601 字符串直接转没问题如果是无时区的字符串先确认业务上它到底是哪个时区再统一补时区转换千万别裸转。这个坑最坑的地方在于数据看起来没问题但所有统计报表的时间都差 8 个小时不仔细对比根本发现不了。4.4 SQLite 动态类型留下的脏数据这个是最难提前发现的问题。SQLite 建表时声明了字段类型但本质上是“推荐”而不是“强制”历史代码里可能混入完全不符合声明类型的值。比如手机号字段声明成 INTEGER有些号码因为前面有 0 被存成了 TEXT同表同列两种类型并存。迁到 PG 后INTEGER 列里出现文本值直接导入必然报错。我的处理方案是在迁移脚本里增加类型诊断步骤先对所有字段跑一遍typeof()统计看看每个字段实际存储的类型有几种再针对异常字段写清洗规则。这一步强烈建议在正式迁移之前做能省下大量返工时间。4.5 LIKE 大小写与排序差异SQLite 的 LIKE 对 ASCII 字符默认不区分大小写PostgreSQL 的 LIKE 是严格区分大小写的。应用迁移后用户搜索模块如果依赖 LIKE 查用户名或邮箱行为会直接变化。以前能查出来的数据迁完突然查不到了。解决方案有两种简单粗暴的是把 LIKE 全改成 ILIKE但 ILIKE 不走普通 b-tree 索引大数据量下性能会崩更推荐的方式是给中文和大小写不敏感的场景设计好索引策略要么用lower()表达式建索引要么引入 PG 的 pg_trgm 扩展做模糊搜索。具体选哪种要看业务量和查询模式。排序差异也很显眼。SQLite 的排序按字节码来中文默认不是按拼音排的PostgreSQL 里排序行为取决于数据库的 collation。如果你在云上建库时没指定zh_CN.utf8中文 ORDER BY 出来的顺序可能跟业务预期不一致。建库时就要把 locale 规划好迁完再改 collation 很麻烦。4.6 大数据量导入慢与参数调整第一次跑迁移一张 800 万行的日志表导入花了一个多小时后来发现全是被默认参数坑的。后来把导入脚本调成一下子导入 5000 行加上临时把synchronous_commit关掉、wal_level保持默认照样提速了四五倍。注意这些参数只是导入期间临时调整导完记得改回去不然异常断电可能丢数据。另外很多 DBA 会建议先删掉目标表的索引再导入导完再重建。工具栏里那些外键、唯一约束也一样导完再开导入速度能上一个台阶但前提是你要有完整的收尾脚本别导完忘建索引那上线后慢查询能查到怀疑人生。5. 迁移后的验证与压测配合5.1 迁移后必做的验证项数据校验通过了不代表应用能直接跑稳。我在切换之后按这个清单逐项过了一遍开启pg_stat_statements扩展看看有没有迁移前没见过的慢 SQL。ANALYZE所有表让 PG 生成最新的统计信息避免走错执行计划。检查所有序列值是否设置正确避免写入撞主键。核对应用账号的权限至少要给到SELECT, INSERT, UPDATE, DELETE有需要再加TRUNCATE。跑一遍功能冒烟用例把登录、菜单、增删改查、报表这几个高频链路全过一遍。查一下pg_stat_activity确认没有残留的死锁和长事务。5.2 配合 JMeter 压测时的 PostgreSQL 调优压测不是简单把人加上去就行。我们压测用的是配套的 JMeter 脚本线程组从 50 并发起跑每 15 秒加 50一直压到接口开始出现超时或者错误率超标为止。这里有一个很容易忽略的连接数问题JMeter 高并发打过来应用层连接池如果不够大请求会先堆积在连接获取上压测结果看起来是接口慢实际瓶颈在数据库连接池。Spring Boot 默认的 HikariCP 最大连接数只有 10如果你们没改过压测一上去瞬间就满。我这次把spring.datasource.hikari.maximum-pool-size调到了跟 PGmax_connections匹配的值比如 PG 允许 200 连接应用侧分给核心服务 50非核心服务 30避免抢连接。PG 侧的基础调优参数可以这样起步shared_buffers 总内存的 25% effective_cache_size 总内存的 50% ~ 70% work_mem 4MB ~ 16MB max_connections 200 ~ 500压测过程中我习惯盯着pg_stat_statements看按总耗时排序找出 TOP 慢查询再用EXPLAIN (ANALYZE, BUFFERS)逐个分析。迁移后最常见的问题就是原来 SQLite 小数据量下走全表扫描没事PG 上数据量一大某些查询没吃上索引压测一高延迟直接飙升。5.3 运行时监控与慢查询定位压测跑到一半除了看 JMeter 的吞吐量和错误率我还开了三个终端分别盯 ECS 的 CPU 内存、PG 的pg_stat_activity和pg_stat_statements。有一个很典型的现象CPU 不高但接口延迟已经很高这时候大概率是等锁在pg_stat_activity里能看到大量wait_event是Lock开头的会话。慢查询定位的经验是不要只盯执行时间要看“执行次数乘以单次时间”的总量。有的查询单次只要 50 毫秒但每分钟执行几千次累计占用的 CPU 和时间比单次 2 秒的查询还多。这类高频短查询往往是连接池配置或者 N1 查询问题而不是 SQL 本身写得多差。第一次做这种迁移时我建议先在测试环境完整跑三遍——第一遍跑通流程第二遍边跑边记时间和坑点第三遍模拟切流演练连 ConfigMap 改动和滚动重启都真刀真枪做一遍。等正式迁移那天你就会发现自己淡定的像个局外人所有的坑都已经在演练里踩过了。