先交代背景。泰山老父官网原先的整套服务端架构数据库用的是 SQL Server 2019跑在 Windows Server 上主要存文章、车型库、用户评论和线索订单。老实说在纯燃油车内容时代这套组合没什么大毛病最多是 SQL Server 吃内存、授权费用有点肉疼。但当我们决定把四驱混动内容体系全面铺开、同时上线车型能耗对比和智能推荐之后问题就接踵而至了。经过两个月折腾我把这套服务端数据库从 SQL Server 完整迁移到了 PostgreSQL也就是从传统燃油车的“老发动机”换成了四驱混动的“双动力总成”。这篇东西不是理论分析是我和团队在真实业务压力下摸爬滚打出来的迁移记录包含选型理由、家底盘点、工具实测、踩坑排查链路、上线切换和后续运维差异。凡是准备做类似迁移的无论你是技术负责人还是主力开发应该都能从里面捞到一些能直接用的东西。1. 让服务端数据库“换芯”的信号为什么不再续费 SQL Server1.1 业务变了数据库的负载曲线也变了网站原本的内容模型很单纯文章表、图片表、评论表、用户表再加一个线索订单表。这些表的特点是写入不频繁、查询压力平缓SQL Server 处理起来游刃有余。但四驱混动频道上线后业务形态完全变了车型参数表从一个变成了十几个每个车型要关联电机功率、电池容量、能耗实测、四驱扭矩分配等多维数据能耗对比工具要求用户选两台车后实时做参数交叉计算经销商报价模块还要按城市、库存、优惠政策动态拼装结果。这些新需求有一个共同点大量半结构化参数、频繁的嵌套查询、多维度的动态筛选。SQL Server 2019 不是做不了但每做一步都要绕——JSON 函数不够顺手复杂查询的优化器在统计信息更新不及时的时候很容易选错执行计划加上 Windows Server 上的内存策略默认把可用内存当缓存吃运维同事三天两头被“SQL Server Windows NT 占用内存”这种问题折腾。1.2 三个不得不动的理由第一个是授权成本。网站业务量上去之后物理机 CPU 从 8 核准备扩到 16 核SQL Server 商业版按核心数授权价格几乎是线性翻倍。这笔钱放在今天还能咬咬牙但明年再扩一轮呢业务还没赚到那么多钱基础软件先吃掉一大块利润这个账怎么算都不对劲。第二个是功能演进。新版功能里有一块是“驾驶模式推荐”要根据用户上传的路况描述匹配动力分配策略本质上是拿一批标签做布尔组合检索。在 SQL Server 里做这种检索要么建一堆中间表要么写复杂的动态 SQLPostgreSQL 的 JSONB 和 GIN 索引几乎是原生支持查询写起来直白得多。第三个是被低估的运维复杂度。SQL Server 在 Windows 生态里很强但我们的服务端应用已经逐步容器化Linux 环境占比越来越高。让一个 Windows 数据库继续孤悬在外备份、监控、日志采集都要单独维护一套链路恰恰是这种“看起来还能用”的状态最容易让人忽略潜在风险。1.3 为什么选 PostgreSQL而不是 MySQL 或其他很多人第一反应是“换 MySQL 不就行了”。但从一开始我就把 MySQL 排除了我们要的 JSONB 数据模型、丰富扩展生态、对复杂查询的执行计划掌控力MySQL 要么弱一些要么实现方式更别扭。PostgreSQL 和 MySQL 的语句差异网上讨论很多但实际上 SQL Server 到 PostgreSQL 的迁移难度往往比 SQL Server 到 MySQL 更可控因为两者在数据类型、事务模型、窗口函数标准支持上更接近。当然“更接近”不意味着“能自动转换”这一点我们后面被反复教育了。2. 迁移前的家底盘点300多张表里哪些是硬骨头2.1 盘点方法和资产清单迁移最怕的不是表多而是不知道家底有多少。我们动手第一步是写查询把 SQL Server 的元数据系统视图翻了个底朝天。用 sys.tables、sys.procedures、sys.triggers、sys.indexes、sys.foreign_keys 扫了一遍最终得到一个看起来并不吓人、实际暗藏杀机的清单310 张表、87 个视图、63 个存储过程、22 个触发器外加 400 多个索引和外键约束。这个清单出来后要做两件事先按业务模块给表分组标注哪些表是核心交易数据、哪些是日志类数据、哪些是可以重建的缓存表再按复杂度打标凡是涉及大字段、自增列、日期时间运算、全文索引、动态 SQL 的对象全部标记为“高风险”迁移时单独对待。日志类和缓存类表直接考虑只迁结构不迁数据省掉大量不必要的搬运时间。2.2 数据类型映射表先解决“能不能装下”的问题类型映射是迁移的地基。SQL Server 和 PostgreSQL 有很多同名不同类型、不同名同类、甚至语义完全不同的类型稍不注意就会在数据截断、精度丢失、排序错误上翻车。我直接把我们最终采用的映射表摆出来SQL ServerPostgreSQL说明int / bigint / smallintinteger / bigint / smallint基本一一对应nvarchar(n) / varchar(n)varchar(n)库用 UTF8 后无需区分字符集nvarchar(max) / varchar(max) / texttext大文本统一用 textdatetime / datetime2 / smalldatetimetimestamp注意 smalldatetime 精度只有分钟级date / timedate / time直接对应money / smallmoneynumeric(19,4)避免浮点误差统一用定点数bitboolean语义一致但取值一个是 0/1 一个是 true/falseuniqueidentifieruuid注意默认值生成方式要改varbinary(max) / imagebytea二进制大对象rowversion无直接对应需要业务侧改用应用维护版本号ntexttextSQL Server 已废弃的类型迁移时顺手清理这里最容易踩的坑是 nvarchar 到 varchar如果 PostgreSQL 数据库初始化时没有选择 UTF8 编码中文全部变成乱码初始化时保留了 SQL_ASCII 更是灾难。建议建库时明确指定ENCODING UTF8LC_COLLATE 也提前定好后面改起来特别痛苦。2.3 高风险对象的预判盘点完之后我们对高风险对象做了专项预审。最需要警惕的有这么几类第一类是大量使用N...前缀的 SQL 语句。这个前缀在 SQL Server 里代表 Unicode 字符串PostgreSQL 根本不认识但不影响正确性直接全局替换成普通字符串即可。第二类是标识列IDENTITY。310 张表里大概有 40 多张用了自增主键PostgreSQL 有两种写法老式SERIAL和新式GENERATED ... AS IDENTITY。建议用后者它在约束语义上更接近 SQL Server。第三类是全文索引。SQL Server 的全文索引和 PostgreSQL 的 tsvector/tsquery 完全是两套东西迁移不只是语法转换而是索引重建和查询重写。考虑到官网搜索流量占比不高我们决定前期先用LIKE %关键词%兜底后续再用全文索引优化避免拖慢主迁移进度。3. 迁移工具选型实测SSMA、手工脚本与增量同步的取舍3.1 SSMA for PostgreSQL 的实际表现微软官方提供了一个迁移工具叫 SQL Server Migration Assistant for PostgreSQL缩写 SSMA。这个工具能自动连接 SQL Server 源库、评估迁移复杂度、转换 schema 对象并生成带报告的数据同步脚本。听起来很省事但实测下来得给它打个七折。优点是自动化程度确实高310 张表的建表语句全部自动生成类型映射基本准确视图和存储过程的转换也能完成一部分至少把 T-SQL 语法转换成了一版能读的 PL/pgSQL。缺点也很明显自动转换的存储过程只能做到“能看”离“能跑”差得远所有带WITHIN GROUP的STRING_AGG、带临时表的复杂存储过程几乎都要手工重写转换报告里标注的“需人工检查”对象接近一半。我的建议是SSMA 可以用但只把它当“草稿生成器”不要指望一键完成。它最大的价值是帮你快速生成 baseline然后在这个 baseline 上做差异修改比从零写转换脚本省力得多。3.2 手工脚本和导入导出的适配过程对于日志表、缓存表、配置表这类简单对象手工脚本反而是最快路径。我们直接用 BCP 导出 CSV再用 PostgreSQL 的\copy导入全程不碰图形化向导中间少踩很多坑。原因是 SQL Server 自带的导入导出向导在迁移场景下非常容易出幺蛾子比如后面要讲的 ACE.OLEDB 问题这在批量迁移时会拖慢整体节奏。3.3 增量同步停机窗口是不是必须的很多人一上来就问“能不能不停机迁移”。说实话对于官网这类业务系统如果允许一次 30 分钟以内的维护窗口完全没必要为增量同步引入额外复杂度。我们的做法是周中凌晨低峰期停服先用全量迁移把历史数据搬完再做最后一轮增量追平。如果你确实有业务连续性要求可以调研基于日志的增量同步方案。市面上的思路一般是用 Debezium 抓取 SQL Server 的 CDC 日志投递到 Kafka再由消费端写入 PostgreSQL。方案可行但部署和运维成本都不低本质上是建一套数据管道。官网系统没有这个体量直接用维护窗口加脚本追平是最划算的。4. 迁移途中踩过的坑SSL证书链、ACE.OLEDB与SQL方言差异的排查链路4.1 连接阶段的 SSL 报错ODBC Driver 18 的证书信任问题迁移过程中我们顺手新装了一台 SQL Server 2022 实例做测试环境结果客户端一连接就报这么一串错误[08001] [Microsoft][ODBC Driver 18 for SQL Server] SSL Provider: 证书链是由不受信任的机构颁发的。 驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误: 证书链是由不受信任的机构颁发的我当时的第一反应是“服务器证书没配好”。于是打开 SQL Server 配置管理器看了实例的证书配置发现这台测试机的 SQL Server 确实用的是一张自签名证书没有安装到受信任的根证书存储区。问题清楚了但怎么解决还需要一步步验证。排查链路是这样的先用sqlcmd在命令行测试加上-C参数trust server certificate之后连接成功证明问题出在证书信任而不是网络或认证方向对了。去应用服务器上看连接串发现用的是新版 ODBC Driver 18。这个驱动从 18.0 开始默认强制启用加密连接而且默认不信任自签名证书和以前的老驱动行为不同。很多老项目升级驱动后突然连不上就是这个原因。在连接串里显式添加TrustServerCertificateTrue保留加密但跳过证书链校验问题立刻解决。最终连接串类似这样Server192.168.10.20,1433;Databasemaster;User IDsa;Passwordxxx;EncryptYes;TrustServerCertificateTrue;Trusted_ConnectionNo;这里要提醒一句TrustServerCertificateTrue适合内网测试环境生产环境最好还是给 SQL Server 配上正式证书把加密连接打开但信任链也验正避免中间人风险。知道原因之后这就是一行配置的事怕的是不知道驱动行为变化在那里反复重装驱动浪费时间。4.2 导出导入阶段的“ACE.OLEDB.15.0 未注册”另一个高频事故发生在 SQL Server 导入导出向导。我们一开始图省事想用 SSMS 自带的向导把几张迁移表导出成 Excel 再导到 PostgreSQL。结果向导一启动就弹出未在本地计算机上注册“Microsoft.ACE.OLEDB.15.0”提供程序这个问题的根源不在 SQL Server而在 Windows 的 Office 组件生态。SSMS 的导入导出向导默认调用 ACE 驱动来读写 Excel 和 CSV而这台机器既没装 Office也没装 Access Database Engine。而且 64 位的向导必须配 64 位的 ACE 驱动32 位的 Office 和 64 位的驱动冲突还会引发另一套错误。排查过程先确认本机装的是 64 位 SSMS再从官网下载AccessDatabaseEngine_x64.exe安装重启向导后问题消失。诡异的是有些机器装完之后发现磁盘上注册的是 16.0 版本的 ACE Provider这时候要去向导的“数据源”下拉框里改选 Excel 新版类型而不是死盯着 15.0 不放。这事给我最大的教训是图形化向导看着省心实际在迁移场景里是最大的不确定性来源。后来干脆全部改成 BCP 导出 CSVbcp [db].[dbo].[model_params] out model_params.csv -S 192.168.10.20 -U sa -P xxx -c -t , -C 65001再在 PostgreSQL 侧用\copy导入\copy model_params from model_params.csv with (format csv, header true, delimiter ,)这样整套流程在命令行里可重复、可留痕迁移失败也能快速重试比点向导按钮稳太多。4.3 SQL 方言差异的系统性改造清单数据类型解决之后SQL 方言差异就成了最大的工作量来源。这里直接把实际改造中最高频的差异列出来SQL ServerPostgreSQL说明SELECT TOP 10 *SELECT * ... LIMIT 10分页和限量完全重写GETDATE()CURRENT_TIMESTAMP返回当前事务时间NEWID()gen_random_uuid()PG 13 内置旧版本要装 pgcryptoISNULL(a, b)COALESCE(a, b)语义相近但参数展开规则不同N中文中文去掉前缀SCOPE_IDENTITY()INSERT ... RETURNING id获取自增主键的方式变了STRING_AGG(col, ,) WITHIN GROUP (ORDER BY ...)STRING_AGG(col, , ORDER BY ...)语法细节差异OFFSET n ROWS FETCH NEXT m ROWS ONLYLIMIT m OFFSET n分页语法重写DATEPART(year, date)EXTRACT(YEAR FROM date)日期函数体会不同EXEC(sql)EXECUTE format(...) USING ...动态 SQL 机制完全不同其中最坑的是ISNULL到COALESCE的替换。肉眼看起来差不多但COALESCE会依次计算所有参数的数据类型如果第二参数和第一参数类型不一致或者里面混了子查询行为会有微妙差异。我们用脚本全局替换之后花了整整一个下午来处理各种“类型不匹配”报错。另一个值得单独说的是自增主键。原来表里大量使用IDENTITY(1,1)迁移后用这种方式CREATE TABLE model_params ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, model_id integer NOT NULL, param_name text NOT NULL, param_value numeric(10,2) );插入后获取自增 id 的方式也从SELECT SCOPE_IDENTITY()改成了INSERT INTO model_params (model_id, param_name, param_value) VALUES (101, max_torque, 420.00) RETURNING id;这套写法和 T-SQL 差异极大团队里开发同学刚开始非常不适应但养成习惯后反而觉得更直观。4.4 存储过程与触发器的迁移不能指望自动转换前面提到 SSMA 能转换存储过程但那是“能看”的版本。真正跑起来之后我们会发现 T-SQL 和 PL/pgSQL 在流程控制、异常处理、游标语义上差别很大。举一个实际例子原来计算混动车型综合能耗的存储过程内部用了WHILE循环遍历临时表T-SQL 长这样CREATE PROCEDURE calc_energy_consumption batch_id int AS BEGIN CREATE TABLE #tmp (model_id int, energy numeric(10,2)); INSERT INTO #tmp SELECT model_id, energy FROM energy_raw WHERE batch_id batch_id; WHILE EXISTS (SELECT 1 FROM #tmp) BEGIN -- 逐行处理逻辑 END END迁移到 PostgreSQL 后我们用 CTE 和集合操作重写彻底抛掉了循环CREATE OR REPLACE FUNCTION calc_energy_consumption(p_batch_id integer) RETURNS TABLE (model_id integer, energy numeric(10,2)) LANGUAGE plpgsql AS $$ BEGIN RETURN QUERY WITH raw_data AS ( SELECT model_id, energy FROM energy_raw WHERE batch_id p_batch_id ) SELECT model_id, energy FROM raw_data WHERE energy 0; END; $$;这一轮重写让我们意识到存储过程迁移的重点不是语法翻译而是业务逻辑重新建模。T-SQL 里很多用临时表和循环实现的“行级处理”在 PostgreSQL 里往往能用集合查询更优雅地表达性能反而更好。触发器同样如此。SQL Server 的INSERTED和DELETED虚拟表对应到 PostgreSQL 是NEW和OLD行变量但约束触发器和普通触发器的执行时机、可修改性不同迁移时如果不仔细读文档极容易造成数据不一致。5. 上线前的验证与回滚数据校验和灰度切换这样操作5.1 数据一致性校验不能只数行数数据迁移完成之后最怕的是数量对上了但内容不一致。我们的校验分成三层第一层是行数校验。每张表对比源库和目标库的COUNT(*)这一步只能筛掉明显漏数据意义有但不能依赖。第二层是哈希校验。对每一张业务表按主键排序拼出核心字段的字符串在两侧计算哈希值再逐组对比。我们用 Python 脚本连两个库拉取关键字段做 MD5 后比对发现了几处大字段尾部空格不一致的问题。这类型差异在 SQL Server 里不敏感但迁到 PostgreSQL 后会暴露出来。第三层是业务验证。选出网站访问量前 20 的查询语句分别在两个库上执行逐条对比结果集。这一步最容易发现问题比如某个报表查询依赖nvarchar的排序规则迁到 PostgreSQL 后排序结果完全不同。我们为此调整了 3 个查询用COLLATE显式指定排序规则后才对齐。5.2 性能回归测试要看真实执行计划数据校验完成后不能急着切换还得做性能回归。官网首页热点车型的详情页要一次关联 17 张表源库跑 1.2 秒新库刚迁移完一模一样的查询跑了 8 秒团队差点被吓到。后来用EXPLAIN ANALYZE一看问题很清楚迁移后的表没有重新收集统计信息PostgreSQL 的优化器选了一个非常糟糕的嵌套循环。解决办法是执行一次ANALYZE重新统计再建上原来缺失的复合索引。调整之后同样的查询在新库只需要 700 毫秒。这里引出一个重要经验迁移后不要拿旧的索引策略直接复制。SQL Server 的聚集索引和非聚集索引的物理语义和 PostgreSQL 不完全一致复合索引字段顺序、部分索引的适用场景都要重新审视。做性能测试时多看几轮EXPLAIN ANALYZE输出的实际行数和预估行数偏差偏差大就说明统计信息没吃准。5.3 灰度切换和回滚预案官网系统不能接受长时间故障所以切换方案设计成两步第一步切读流量。先把应用的只读连接指向 PostgreSQL 新库线上用户正常浏览写操作仍走老库。这一步能验证新库在真实读写负载下的表现同时因为混合双写还没开启不会造成脏数据。第二步是写流量切换。选择周四凌晨 2 点到 3 点停服维护把写连接也切到新库同时把老库设置为只读保留 48 小时。回滚预案就是一条命令应用配置切回老库连接新库直接下线。因为老库挂了 48 小时的只读即使新库出了问题之前的数据也不会丢。这套方案执行下来整个切换窗口只花了 12 分钟其中大部分时间还是在等缓存预热真正切库的操作不到 3 分钟。6. 迁移后的运维差异checkpointer、共享内存与备份策略6.1 管理习惯从 SSMS 到 psql/pgAdmin迁移完成后运维日常发生了很大变化。以前连 SSMS 图形化界面右键就能看各种报告现在主力工具变成了 pgAdmin 4 加命令行 psql。真正用起来你会发现 psql 的一些能力是 SSMS 没有的比如\d直接看表结构、\timing打开执行计时、EXPLAIN ANALYZE就在手边。做好习惯切换之后操作效率不会下降多少。6.2 内存使用逻辑完全不同别被默认值骗了SQL Server 在 Windows 上的行为是能占多少内存就占多少所以热搜词里会有“SQL Server Windows NT 占用内存”这种问题。PostgreSQL 的默认shared_buffers只有 128MB 左右很多人迁移完一看内存占用不高以为数据库没吃满内存不是好现象其实这就是设计差异。PostgreSQL 依赖三层缓冲shared_buffers、操作系统页缓存、每个会话独立的 work_mem。你不能只盯着shared_buffers调而要把shared_buffers设在物理内存的 25% 左右同时根据查询特征调整work_mem再配合系统级别的页面缓存命中率来判断。这里有一层很容易被忽视操作系统对文件读取的缓存对 PostgreSQL 的读性能提升非常显著所以“内存占用低”不等于“性能差”要综合看缓存命中率和 WAL 写入情况。6.3 checkpointer一个新的后台进程观察对象PostgreSQL 里有一个后台进程叫 checkpointer它的职责是把脏页从 shared_buffers 刷回磁盘并记录检查点位置。SQL Server 也有检查点机制但它没有这么独立、需要显式关注的进程。热搜词里的“postgresql checkpointer”说明这个问题确实困扰过不少人。我们刚迁完的那段时间发现 WAL 目录增长很快看日志发现 checkpointer 频繁触发检查点。查了一圈原因是max_wal_size默认值偏小业务写入一高就不断提前检查点。调整max_wal_size和checkpoint_timeout之后检查点间隔趋于稳定磁盘 I/O 压力也降下来了。另外checkpoint_completion_target这个参数决定了写入散开的时间窗口对机械磁盘是救命的对 NVMe 影响不大调优时要结合存储介质判断。6.4 备份恢复从 .bak 思维切换到 pg_dumpSQL Server 的备份恢复大家都熟BACKUP DATABASE产生.bak文件配合日志备份能做到按时间点恢复。PostgreSQL 的对应方案是逻辑备份pg_dump加物理备份pg_basebackup两者分工不同# 逻辑备份适合单库、跨版本、部分表 pg_dump -h 127.0.0.1 -U app dbname -Fc -f backup.dump # 物理备份适合整个集群级别恢复 pg_basebackup -D /backup/$(date %F) -Fp -Xs -P -U replicator -h 127.0.0.1逻辑备份迁移灵活但是恢复数据量大时慢物理备份适合完整恢复。重要的是先定义好 RPO/RTO别等到出事了才研究命令。官网站点的数据量不算大我们现在的策略是每天pg_dump全库加上 WAL 归档已经能满足需求。这次迁移给我最大的感受是数据库迁移本质上不是技术决策而是业务决策。技术上的坑哪怕是 SSL 证书链这种看着吓人的问题只要有一个清晰的排查链路都能逐个击破真正难的是判断“什么时候该动”“哪些数据可以牺牲”“切换窗口怎么定”。团队在这两个月里最大的成长不是学会了 PostgreSQL 语法而是学会了先把业务风险梳理清楚、再做技术操作。最后分享一个个人觉得很有用的习惯任何迁移项目先别急着做全量挑一个独立的小业务模块走完“备份-迁移-校验-切换-回滚”的闭环把耗时长度和风险点全部摸透再向全量铺开。当初我们就是先拿评论模块做实验才在后来的全量迁移里精准预估了四小时这个时间点。数据库迁移没有银弹但把前戏做足了后期的问题真的会少一大半。