apiSQL 迁移 PostgreSQL 实操：数据、方言、配置与回滚全指南

前阵子我把手上的 apiSQL 服务从 SQLite 迁到了一个已经在跑的 PostgreSQL 实例上。整个过程不算复杂但也没想象中那么无脑改连接串只是第一步SQL 方言、自增主键、布尔值、返回字段类型这些坑一个接一个。这篇文章就把我的实操过程完整拆开从数据导出到 apiSQL 配置切换再到对账和回滚全部过一遍。如果你也维护着 apiSQL或者只是想把某个查询服务后端从 MySQL 或 SQLite 迁到 PostgreSQL这篇指南可以直接拿来当参考。1. 动手前先搞清楚apiSQL 到底是什么迁移到底在迁移什么1.1 apiSQL 在架构里的位置apiSQL 这类工具的核心能力是把预定义的 SQL 查询包装成 HTTP 接口。请求进来之后它把参数绑定到写好的 SQL 模板里去后端数据库执行最后把结果集转成 JSON 返回给调用方。所以它本身不存数据数据全部落在后端数据库上。迁移 apiSQL 看起来是在动一个应用实际动的是“数据访问层”的底座原来的数据在 SQLite 或 MySQL 里现在要换成 PostgreSQL还要保证所有暴露出去的 API 行为不变。我见过不少人把迁移理解成“把 apiSQL 装到新服务器上”这不对。apiSQL 进程可以原地不动真正要搬的是数据、表结构、连接配置以及藏在 SQL 模板里的方言。这个认知没摆正后面就会反复在“为什么接口报错”里打转。1.2 为什么要迁到 PostgreSQL不是所有“换库”都叫迁移PostgreSQL 这两年作为业务主库出现的频率越来越高。相比 SQLite它能支撑多实例并发写入事务隔离级别完善支持 JSONB、窗口函数、物化视图、逻辑复制这些 SQLite 给不了的能力。相比 MySQLPG 在复杂查询优化、索引类型、扩展生态上又有一批忠实用户。所以常见的迁移动机有三类第一原来挂在 SQLite 上apiSQL 支撑的业务量涨了需要换一个真正的服务型数据库第二团队想统一技术栈把 MySQL 业务的库统一到 PG第三新功能需要 PG 的 JSONB 或全文检索等特性顺手把 apiSQL 也带过去。不管动机是哪一种都要明白迁移不是“倒数据”那么简单。表结构要重新建SQL 写法要重新验连接参数要重新配连运维监控都要跟着换一套。把这些事当成一次小项目来做才能控制好风险。1.3 迁移范围拆解数据、schema、连接配置、SQL 方言我在动手前习惯把迁移拆成四层每一层单独列清单避免漏项。数据层旧库里的每张表、每条记录要完整落到 PG。schema 层建表语句、字段类型、自增主键、索引、约束要在 PG 里重建。配置层apiSQL 里的数据源驱动、连接串、账号密码、连接池参数要指向 PG。SQL 方言层apiSQL 模板里的 SQL 语句要符合 PG 语法包括分页、布尔值、自增主键、日期函数等。很多人只做了配置层改一个 JDBC 地址就觉得完事了。结果启动之后接口要么报“relation does not exist”要么报语法错误要么分页数据不对。所以我把“SQL 方言”单独拆出来它才是这次迁移里最费时间的地方。2. PostgreSQL 侧准备安装、初始化与基础调优2.1 已有 PostgreSQL 实例检查清单虽然标题说的是“已有 PostgreSQL 数据库”但“已有”不代表可以直接用。我建议先给这个实例做一遍体检至少确认下面几项版本SELECT version(); 尽量用 PG 14 及以上版本太老会影响 JSONB 性能和自增语法。编码SHOW server_encoding; 必须确认是 UTF8。如果数据库装成了 LATIN1 或 SQL_ASCII中文字符很容易变成乱码后面迁数据会非常痛苦。监听地址SHOW listen_addresses; 如果 apiSQL 要跨服务器连接这里不能是 localhost。端口SHOW port; 默认 5432但生产环境常有改动。磁盘空间SELECT pg_size_pretty(pg_database_size(库名)); 至少要有旧库 1.5 倍以上的空闲空间导入过程中还会产生 WAL 日志空间不够会直接卡死。扩展插件如果需要 uuid 主键提前创建扩展 CREATE EXTENSION IF NOT EXISTS uuid-ossp; 如果只依赖 PG 自带的 gen_random_uuid()那 PG 13 之后就不需要额外插件但要确认版本。这些检查我一搬是在业务低峰期做先不开 apiSQL等环境确认无误再进迁移流程。2.2 如果没有现成实例安装 PostgreSQL 的快速要点我理解“已有”的意思是有现成库但很多人实际是边看教程边从零搭。这里简单说一下快速装的几个要点。Linux 上最常见的是用发行版自带的包管理工具比如 Debian/Ubuntu 下执行 apt install postgresql postgresql-contribCentOS/RHEL 用 yum install postgresql-server 然后还要执行 postgresql-setup --initdb 初始化数据目录。Windows 版直接下载安装包按向导点下一步注意安装到非系统盘的路径。关键点有三个第一不要用 root 直接跑 PG安装过程会自动创建 postgres 系统用户日常都切到这个用户下执行命令第二安装完成后默认只监听 localhost改监听要动 postgresql.conf 里的 listen_addresses同时在 pg_hba.conf 里加对端 IP 的访问规则第三如果只是本地给 apiSQL 用就别把端口暴露到公网PG 默认密码策略比较宽松裸奔很危险。如果你正好遇到“PostgreSQL 启动服务失败等待服务器启动时超时”这种问题最常见的原因是数据目录权限不对或者日志目录不可写。可以直接去看数据目录下的 log 文件一般都会有明确提示比如 “could not open log file permission denied”。解决方式是把数据目录所在路径的属主改成 postgres 用户然后重新启动。2.3 创建专用用户与数据库我强烈建议给 apiSQL 单独建账号和库不要拿 postgres 超级用户去对接应用。原因很简单出问题的时候能从 PG 的 pg_stat_activity 里一眼看出哪些连接是 apiSQL 的回收权限也不用动超级用户。命令大概是这样的CREATE USER apisql_app WITH PASSWORD 一个足够复杂的密码; CREATE DATABASE apisql_db OWNER apisql_app; GRANT ALL PRIVILEGES ON DATABASE apisql_db TO apisql_app;PG 的逻辑和 MySQL 有一点不同MySQL 里一个实例可以建多个 database应用连接时通过库名区分。PG 里连接串本身只能指向一个 database如果你想在一个实例里隔离多套数据更常见的做法是在同一个 database 里建多个 schema。apiSQL 如果原来连接 MySQL 时用了多个库迁移时就要规划好是把多个库合并成一个 PG database 下的多个 schema还是拆成多个 database。这直接影响 apiSQL 配置里的表名写法没有提前规划后面改 SQL 模板会改到怀疑人生。3. 数据迁移方案设计从“导出”到“对账”3.1 旧库类型判断与导出策略开始迁移之前必须先确认 apiSQL 原来连的是什么数据库。最常见的两个来源是 MySQL 和 SQLite。方法很简单如果 apiSQL 配置里能看出 jdbc:mysql 就是 MySQLjdbc:sqlite 就是 SQLite拿不准就问一下当时搭环境的人。确认之后选导出工具。我优先推荐 pgloader这个工具能同时处理 MySQL 和 SQLite 到 PostgreSQL 的迁移而且能自动做不少类型转换。用 MySQL 举个例子LOAD DATABASE FROM mysql://root:密码localhost/source_db INTO postgresql://apisql_app:密码localhost/apisql_db WITH include drop, create tables, create indexes, reset sequences SET maintenance_work_mem1GB, work_mem64MB CAST type int with extra auto_increment to bigserial;注意pgloader 不是 PostgreSQL 官方自带的工具需要单独安装但它的稳定程度在社区里口碑不错。如果不方便装也可以退而求其次旧库导出 CSV新库用 COPY 导入。CSV 方案的问题是类型转换基本要自己处理遇到特殊字符、换行、日期格式不对都容易中断只适合表很少、结构很简单的场景。如果是 SQLite其实也建议优先走 pgloader。SQLite 内部对类型的检查非常宽松比如声明成 INTEGER 的字段里可能混着文本直接导出 CSV 再导入 PG 时PG 会严格按照目标表类型校验碰到一个脏数据整张表就失败。3.2 数据结构转换类型映射和字段命名数据迁移里最考验细心的地方是字段类型的映射。我保守地把常见映射列成一张表方便直接对照旧库类型常见PostgreSQL 推荐类型说明MySQL AUTO_INCREMENTINT GENERATED BY DEFAULT AS IDENTITY不要再用 SERIALIDENTITY 更符合 SQL 标准权限管理也方便MySQL TINYINT(1)BOOLEAN 或 SMALLINT如果只存 0/1 就转 BOOLEAN如果还存 2/3 就保留 SMALLINTMySQL DATETIMETIMESTAMP 或 TIMESTAMPTZapiSQL 返回 JSON 时要考虑时区建议统一成 TIMESTAMPTZMySQL TEXT/LONGTEXTTEXTPG 的 TEXT 没有长度限制直接映射没问题MySQL VARCHAR(n)VARCHAR(n)注意 PG 的 VARCHAR 长度单位是字符不是字节MySQL ENUM(a,b)VARCHAR(10) CHECK 约束PG 也有 ENUM但后续加值麻烦建议用 CHECKSQLite 动态类型按实际值手工指定SQLite 太随意必须逐个字段人工确认字段命名也要提前扫一遍。MySQL 的字段名可以用反引号包裹PG 里反引号是非法字符。更麻烦的是 PG 对大小写敏感不加双引号的标识符会被自动折叠成小写。如果旧库里有个字段叫 UserNameMySQL 不敏感没问题到了 PG 如果你写成 SELECT UserName就必须始终带双引号如果写成 UserName它实际查的是 username字段不存在。所以我的原则是迁移时统一把所有字段名转成小写加下划线的风格比如 user_name。虽然改字段名会牵连 apiSQL 的 SQL 模板但这一步做完后面的麻烦会少很多。3.3 增量/全量选择什么时候必须停服迁移方案里必须明确一个问题能不能接受停服。如果 apiSQL 只做只读查询后端数据是从别的地方同步过来的那么可以在不停服的情况下导出快照在低峰期导入 PG最后做一次增量补录。但这种情况“已有 PostgreSQL 实例”通常已经有业务在写最简单的还是设置维护窗口。我的建议是第一次迁移直接停服。把 apiSQL 进程停掉确保旧库不再有写入然后导出、导入、校验、切换。整个过程如果是几百张表的小项目一两个小时足够。如果业务要求不能停服那就要引入增量同步比如 MySQL 开启 binlog 后通过 Debezium 同步到 PG或者 SQLite 场景用定时增量导出。但跨库实时同步状态很难看清尤其是数据回放出现延迟时应用看到的可能是旧库和新库各一半的数据对账压力远大于停服方案。如果不是团队里有专门的 DBA 和同步工具经验我真心不建议第一次迁移就上在线增量方案。老老实实申请一个维护窗口比任何高级方案都稳。4. apiSQL 连接与方言适配让应用层无感切换4.1 连接串改法与驱动配置数据导完之后开始动 apiSQL。第一步是改数据源配置。apiSQL 的配置形式可能不一样有的用 YAML有的用 properties有的直接写在环境变量里但核心字段是固定的驱动类名、连接串、用户名、密码。如果原来连的是 MySQLdrivercom.mysql.cj.jdbc.Driver urljdbc:mysql://127.0.0.1:3306/source_db?useUnicodetruecharacterEncodingutf8 usernameroot passwordxxx改成 PG 后driverorg.postgresql.Driver urljdbc:postgresql://127.0.0.1:5432/apisql_db usernameapisql_app passwordxxx改配置的同时要确认 apiSQL 运行环境里有没有 PostgreSQL 的 JDBC 驱动。常见的坑是jar 包没放进去或者项目依赖里只引入了 MySQL 驱动。启动以后报 “ClassNotFoundException: org.postgresql.Driver”就是驱动缺失。如果是 Python 的 apiSQLPG 驱动一般是 psycopg2 或 asyncpg需要在虚拟环境里重新安装。另外从 MySQL 迁到 PG连接池参数也要调整。MySQL 驱动有 autoReconnect 这样的参数PG 驱动里没有。如果 apiSQL 使用了连接池最好配置一个心跳检测比如验证查询写 SELECT 1否则数据库空闲超时之后连接池里的连接可能已经断开接口会出现偶发超时。4.2 SQL 语句差异踩坑LIMIT、反引号、布尔值、自增主键连接串改完真正的硬骨头才刚开始。apiSQL 里每一个 SQL 模板都要人工过一遍下面是几个最常见的差异点。反引号问题MySQL 里习惯给表名和字段名加反引号PG 完全不认反引号。如果从 MySQL 导出 SQL 模板或者是从旧库里拿表结构直接改第一步就是去掉所有反引号。分页问题MySQL 常用 LIMIT ?, ? 这种两参数分页第一个参数是偏移量第二个是行数。PG 也支持 LIMIT 和 OFFSET但写法是 LIMIT 行数 OFFSET 偏移量。如果 apiSQL 里用了 JDBC 参数绑定参数位置也要对应调整。比如 MySQL 的 LIMIT ?, ? 到 PG 里得改成 LIMIT ? OFFSET ?。布尔值问题MySQL 中 TINYINT(1) 经常被当成布尔用查询时 WHERE is_active 1 很常见。PG 的布尔类型不接受整数 1正确写法是 WHERE is_active TRUE。如果你不想改 SQL可以在迁移时把 TINYINT(1) 映射成 SMALLINT保留整数语义但这会让应用返回类型从布尔变成数字apiSQL 返回 JSON 时可能不符合前端预期。自增主键插入问题MySQL 的 INSERT 语句不需要关心主键数据库自动生成。PG 用 IDENTITY 之后普通 INSERT 也不需要关心。但如果旧库导出的 INSERT 语句里带着自增主键的值导入 PG 时就要注意IDENTITY 默认不接受用户指定值会报错。可以让导入工具把主键列去掉或者临时 SET override 行为。更常见的是导入完成后自增序列没有同步插入新数据会主键冲突。这就是为什么 pgloader 里要开 reset sequences。4.3 apiSQL 内置查询逻辑的针对性调整apiSQL 除了 SQL 模板通常还会定义参数的类型、默认值、缓存策略。迁移后这些也要同步检查。参数类型比如原来定义了一个 int 类型的参数MySQL 里可以用 WHERE create_time ? 传数字PG 对类型更严格如果参数被定义成字符串PG 不会像 MySQL 那样自动做隐式转换查询就会报 “column is of type timestamp but expression is of type character varying”。遇到这种问题要在 apiSQL 的参数配置里改成正确类型或者在 SQL 里写 CAST(? AS TIMESTAMP)。返回字段映射PG 的 NUMERIC 类型在 JDBC 驱动里默认返回 BigDecimalJSON 序列化时可能会变成科学计数法或字符串和 MySQL 返回 double 的时候不一样。apiSQL 如果直接序列化结果前端可能会收到 “1.0E2” 这种格式。解决办法是在 SQL 层面转成浮点类型或者让 apiSQL 对 BigDecimal 自定义序列化。特殊写入语句如果 apiSQL 支持写接口原来用 MySQL 的 REPLACE INTO 或 INSERT ... ON DUPLICATE KEY UPDATE在 PG 里要用 INSERT ... ON CONFLICT (唯一键) DO UPDATE。语法差异很大要专门改。比如-- MySQL 写法 INSERT INTO user (id, name) VALUES (1, aa) ON DUPLICATE KEY UPDATE name VALUES(name); -- PostgreSQL 写法 INSERT INTO user (id, name) VALUES (1, aa) ON CONFLICT (id) DO UPDATE SET name EXCLUDED.name;这种改写一旦出错最容易造成数据异常所以我建议先把 apiSQL 的写接口单列出来逐个做冒烟测试。5. 执行迁移的完整实操过程5.1 停机窗口内的操作顺序讲完准备和适配我把完整执行过程按时间顺序列一遍可以直接当成操作手册用。第一步通知相关方确认维护窗口。把 apiSQL 对应的 API 全部标记为维护中避免调用方在数据搬到一半时发出请求。第二步备份旧库。不管要迁到哪旧库一定要留一个完整备份。MySQL 可以用 mysqldump --single-transactionSQLite 可以直接复制 .db 文件或者用 sqlite3 的 .backup 命令。第三步停掉 apiSQL 进程。如果是 systemd 服务执行 systemctl stop apisql如果是直接跑的 java -jar就 kill 掉进程。第四步执行数据导出。按第 3 章选的工具把 schema 和数据导出来。pgloader 有个好处是它会自己建表省了手工跑 DDL 的时间。第五步导入到 PG。如果导入中途报错先看是不是类型映射问题。pgloader 通常会给详细的错误行和字段不要忽略因为每忽略一条错误代价都是数据对不上。第六步修复序列和索引。导入完成不等于序列是对的。用 setval 把每个自增序列调整到当前最大值SELECT setval(user_id_seq, (SELECT max(id) FROM user));第七步修改 apiSQL 配置按第 4 章的清单改驱动、连接串、SQL 方言。第八步启动 apiSQL跑冒烟测试。先跑只读接口再跑写接口最后跑缓存刷新。第九步切流量。如果是内部系统让上游恢复调用如果是网关转发把网关规则切到新节点。第十步观察一段时间。最少观察一个业务周期确认没有慢查询再宣布迁移完成。5.2 数据校验与一致性检查校验是迁移里最容易糊弄、也最不能糊弄的环节。不要只看行数一致就拍板行数一致但内容错乱的情况我见过太多次。我常用的校验方法有四层。第一层是行数对比逐表执行 count(*) 对比。第二层是抽样内容对比在每张表里随机抽几行把旧库和新库的数据做成 JSON 对比重点看字符编码、精度、时间格式。第三层是自增序列检查确保新插数据不会冲突。第四层是业务层校验直接调用几个 apiSQL 的高频接口看返回结果和迁移前抓到的样本是否一致。时间字段要看时区。如果 PG 用的是 TIMESTAMPTZ而 API 依赖的时区是 UTC展示层可能整体差 8 小时。我建议在切换前写一个接口专门返回当前时间和几条带时间字段的记录跟前端确认显示格式。5.3 回滚预案迁移一定要有回滚预案哪怕你心里觉得“肯定一次成功”。我的做法是旧库和旧配置全部保留不动。数据迁移到 PG 之后旧库的数据仍在原地apiSQL 的旧配置文件也备份一份。如果切换后发现问题直接把 apiSQL 配置改回旧库地址重启服务流量就回到原来的库。麻烦的是如果 PG 上已经开始接收新写入回滚时这些新数据会落在 PG 上旧库没有。所以我的建议是在迁移维护窗口内要么完全禁止写入只做只读验证要么对写入操作单独记日志万一回滚手工把这些新的写入补到旧库里。后者操作成本高一般只有在迁移时间很长、业务无法停写时才用。如果你没有把握一次成功我建议把回滚条件写在迁移方案里哪些接口验证失败必须回滚哪些数据对账不一致必须回滚。提前定义清楚真到出问题时团队不用临时开会吵半天。6. 常见问题与排查技巧实录6.1 服务启动失败、连接超时迁移完第一次启动 apiSQL最常见的报错是连接超时。第一步先测试网络连通性在跑 apiSQL 的机器上执行 telnet 127.0.0.1 5432如果端口不通检查 PostgreSQL 是否启动、listen_addresses 是否包含对应 IP、防火墙是否放行。如果 telnet 通但应用还是报超时继续看 pg_hba.conf白名单里有没有允许 apiSQL 的 IP。如果是 PostgreSQL 本身启动失败报 “waiting for server to start... timed out”多半是数据目录权限和磁盘空间。用 postgres 用户执行 pg_ctl start -D 数据目录日志会告诉你真正原因。不要拿着 root 账号去启动 PG权限错误非常隐蔽有时候明明数据目录权限看着没问题但 log 文件属主不对启动一样失败。6.2 中文乱码与编码问题迁移之后中文变乱码是仅次于连接失败的第二个高频问题。根源通常是旧库字符集不是 UTF8或者导出导入过程中没有明确指定编码。MySQL 导出前先确认 STATUS 里的 Server characterset 和 Db characterset导出 SQL 时加上 --default-character-setutf8。导入 PG 前确认 PG 的数据库编码是 UTF8前面 2.1 节强调过。如果用 COPY 导入 CSV客户端连接时要设置 client_encodingUTF8。如果已经导入乱码不要急着改数据先确认数据在源头是否正常。很多时候旧库的库表字段是 latin1但应用写入时用了 utf8这种情况下导出的字节本身可能已经错位需要结合具体场景处理。6.3 性能变慢的排查思路迁移后 apiSQL 变慢先在 PG 里跑 EXPLAIN ANALYZE看查询计划。很多 MySQL 上跑得好好的 SQL到了 PG 因为统计信息没更新、索引没建对或者写法不符合 PG 习惯会突然变成全表扫描。别急着调 PostgreSQL 参数先看索引。导入数据之后要执行 ANALYZE更新优化器统计信息不然 PG 对表行数估计不准。原来 MySQL 里显式写的 FORCE INDEX 这种优化器提示 PG 不认要删掉。另外一个常见坑是MySQL 里某些 join 查询因为字段隐式类型转换能用上索引PG 对隐式转换更严格转换后的字段类型和索引类型不匹配索引就失效了。如果确认 SQL 没问题才考虑调 work_mem、shared_buffers 这类参数。我个人经验是大部分迁移后的慢查询都是因为少建了索引而不是数据库没调好。7. 迁移后一周我做的事迁移切换完成只是开始。我在随后的一周里会持续盯几件事每天看一次 pg_stat_activity看有没有 apiSQL 产生的慢查询和长时间空闲事务。PostgreSQL 的空闲事务很坑如果 apiSQL 连接池没有开启自动提交事务一直挂着后面再执行写操作会阻塞表现为接口突然卡住。另外我会把备份策略重新梳理一遍。API 迁到 PG 之后如果还用原来的 MySQL 定时任务备份等于白搭。PG 上可以用 pg_dump 做逻辑备份也可以用 pg_basebackup 做物理备份。至少先保证手动备份能跑通再上 cron。最后我会把这次改过的 SQL 模板整理成一份“PostgreSQL 写法对照表”放进团队文档里。迁移工作最怕的是只有脑子记着三个月后没人知道为什么这里要用 ON CONFLICT那里不能用反引号。把迁移踩过的坑记录下来后面再迁其他服务至少能少走一半弯路。如果你手上也有一个 apiSQL 正打算迁到 PostgreSQL我的建议很简单先别急着动数据把第 1 章的迁移范围拆清楚把第 3 章的方案写明白把第 5 章的回滚预案定好然后再动手。数据库迁移这事稳比快重要。