这家欧洲科技公司两年内从MySQL迁移到PostgreSQL

共计 5479 个字符，预计需要花费 14 分钟才能阅读完成。

乍一看，这篇文章看起来像是标题党。可是咱们不能否定一个事实，公司从 MySQL 到 PostgreSQL 迁移现已开端，由于 MySQL 越来越杂乱了。

回想公司刚成立那会儿，我们团队只有三个人，没有太多的资源和精力投入到技术的基础架构上；如果你正处在一个小团队，那么迁移到 PostgreSQL 就是个不错的选择。

不可否认，MySQL 有许多优点，没有它，我们公司就不会发展到今天这一步。但是随着时间的推移，它的缺点慢慢的掩盖了其优点。由于缺乏更深入的专业知识，我们始终依赖于 Percona MySQL 和 Ubuntu LTS 14.04 的机带版本，从未使用过外部工具，因为一旦使用这些，就必须有人学习，了解以及维护这些工作流程，这将是一个巨大的人力成本的投入。

我们对 MySQL 5.6 不满意的最主要原因

无法在线添加新的字段
大表无法在线添加新字段的问题常常迫使我们每隔一个月就要暂停一次数据库服务器。而对于像 pt-online-schema-change 这样的在线 DDL 的第三方工具，我们又不是很了解，这让我们感到很崩溃；
不能用一种可靠的方式在线添加索引
事实上，MySQL 5.6 开始支持在线添加索引了，但是在实际操作中，我们一直很紧张，因为你不知道这样的添加方式是否可靠，错误的操作会引起线上项目的巨大反应；
5.6 版本不支持 json 原生类型的字段（5.7.8 版本开始引入）
缺少 CTE（公用表表达式）和窗口函数等数据库高级功能
在数据库超出最大连接数的地方，我们已经锁住了；但是我们不能在我们的系统中找到问题的根源，最后发现就是几百个连接执行的 select 语句造成的，最终我们手动杀死了这些查询进程；
还有另外几个看似不是问题的小问题，但是它们确实让我们感到不满：
没有原生的布尔类型
取而代之人们以 Tinyint(0)来实现布尔类型，但是这样就需要所有适配器语言执行本地强制类型转换，或者至少配置其 ORM/DataModels；
使用 mysqldump 创建一个完整的数据库备份要花费了几个小时（3- 4 小时）
另外，并行备份数据库也不是很容易
导入该数据库备份比创建备份所花时间更长（通常 8 - 9 小时）
DDL 语句不是事务的一部分
2014 年前后，我们的前合作伙伴 25th floor 建议我们使用 Postgres，实践证明，当我们的应用迁移到 Postgres，并没有增加我们团队的资源消耗。

开启轻量级的 Postgres？我们做到了！

我们遵循了建议，开始更深入的考察 Postgres。为了简单起见，我们打算通过更改驱动程序来达到迁移的目的。但是，同志们，我们错了，2014 年 11 月，我们提出了第一个完全迁移到 Postgres 的愿景的方案，标题为“[数据库]勇敢的新世界”。然而，这个方案有太多的问题，最终在半年后被抛弃了。

但是我们一直没有放弃。经过一番讨论和规划，我们决定将在新的 swat.io 分析引擎项目中引入 Postgres。事实证明，这是一个绝妙的机会：一切从零开始，与现在的系统无关。唯一的缺点就是我们主要的数据仍然在 MySQL 中。要迁移到 Postgres，就需要同步数据。虽然不是很完美，但是从商业角度来讲可以接受，从而为我们迁移的道路迈出了第一步。

这个项目需要花费了几个月时间完成，除了开发新的后端和 UI 代码之外，还给了我们足够的时间来学习新的数据库系统：
– 如何将其集成到我们的开发人员虚拟机堆栈中？
– 数据库角色概念如何运作？
– 在写入密集型的负载下它的表现如何？
– 我们可以多快地对新功能进行原型设计？
– 当我们不满意查询性能，该如何调整它？
– 怎样实现数据库的备份与恢复，以及它的工作原理？
– 如何使用 PHP 和 NodeJS 与现有的系统进行整合？

镜像模式？我们失败了！

公司的分析引擎项目取得了成功，我们非常喜欢 Postgres，但仍然无法迁移整个应用程序，我们尝试了另一种方法：
在我们的 ORM 中创建连接到 Postgres 的镜像模型。在每次保存 MySQL 模型后，我们将触发相同的保存到 Postgres 数据库中。在理论上听起来很好，实践中总是有空白或某些区域没有被正确的更新，我们不得不放弃这种方法。

继续小的尝试？我们做到了！

2015 年夏季时，我们决定把一些记录表迁移到 Postgres；但是这些数据只是些无关紧要的临时数据，所以放弃了数据迁移工作，直接迁移到新的数据库，并停用删除了相关的老数据库。

紧接着的尝试

在 2015 年 8 月左右，我们的团队再次对 MySQL 的使用表示无奈，并且尝试迁移应用程序代码库。这次尝试比第一次更加先进，一次真正“使用”几乎完整的应用程序。
只有一个“轻微”缺点：性能落后于 MySQL！这在我们开发人员 VM 中甚至可以很容易地观测到。为此，我们进行了微型优化（例如禁用 SSL 连接），并开始大修查询。但是我们得出的主要问题是：我们的应用程序在页面加载期间（有时）会有成百上千个小型 SQL 语句的操作，而且每一个在 Postgres 中都有一点点慢，但是这一切还是在可容忍的范围内。
请注意，这个结论是未来数据库优化的主攻点：
– 在数据库端执行更多操作（诸如：使用更合理的的复合查询等）
– 更多的使用数据库（很多业务能够在客户端完成，但并不能表明它一定要在客户端完成）。

令人尴尬的是，直到一年以后，我们才发现了这个罪魁祸首（CakePHP2 Postgres 驱动程序开销问题），给我们带来如此多的需要优化的地方，我们深知，还有许多帮助改进的事情可以做，但是这在当时超出了我们的能力范围。

不幸的是，这次尝试在几个月内都没啥进展，最后几乎被遗忘。

挑战一切，永不放弃！

不过，随着时间的推移，新内部项目中就出现了一个问题：

我们是要在 MySQL 还是在 Postgres 中创建新的表？

使用 InnoDB 数据库引擎的外键功能来保持数据一致并不总是很容易，往往在不同数据库间同步数据时容易出错。但是感到庆幸的是，这些 MySQL 中的主要痛点都不复存在了：
– 不管你的表有多大，100MB 也好，100GB 也好，一旦新字段允许为 NULL，并且没有默认值的情况下，数据库默认添加一个空操作；
– 添加新索引变动轻而易举，只要将它们设置成 CONCURRENTLY，然后你就完事了；
由于我们不仅扩展了数据存储，而且还扩展了代码库，2016 年前后，在引入新后台 (基于基础 API 的 JSONAPI) 之后，写测试达到了一个峰值，对于在测试中写各种 DML 语句和 DDL 事务语句，我们有着丰富的经验，但是最终不得不放弃这些，因为存储了主要数据的 MySQL 不支持这些功能；

迁移的黎明

Postgres 的流行趋势仍在继续，MySQL 的问题越来越多，夜间的维护时间越来越长，因为我们想在大表中添加几个字段，开始时需要 2 个小时，到最后变动完全不可测了，导致超出了我们宣布的停机维护时长。

2016 春夏以后，我们再次尝试将整个应用迁移到 Postgres。基于前一年提出的目标和一些新尝试，管理层经过仔细考虑，一直给我们开绿灯。

我们在 2016 年的几次尝试为我们带来了大量的性能优化经验，并且帮助我们解锁了一些发现 bug 隐藏属性，这在之前我们团队闻所未闻的。经过几个月的努力奋斗以及大量测试之后，我们拟定在 2017 年 2 月份，准备迁移工作。

虽然数据本身的迁移是成功的，但我们的应用程序的关键部分存在性能问题。这导致我们不得不回滚，然而我们并没有放弃，一直渴望找到问题根本原因。

在我们通往 Postgres 的道路上，我们得到了 Markus Winand 的 use-the-index-luke 和 modern-sql 框架的完美支持，他们对多种数据库的全面深入了解令我们惊叹不已。在我们团队的共同努力下，我们攻克了性能问题，最终被证明是由于数据库的“冷却”对性能造成了影响。迁移的最后一步，我们增加 pg_prewarm 上选择的关系，因而提高了对面向用户的关键部件的首次体验。

2017 年 3 月的第二次尝试终于成功了。虽然数据迁移时间比预期的长，但是当流量到 Postgres 时，所有的一切都运行正常。

我最终迁移数量

使用 pgloader 迁移 120GB 的数据，使用自定义脚本在 tmux 中手动并行使用每个可用内核（无法使单个 pgloader 实例可靠地工作，因此为每个表使用了专用的）
迁移本身花了大约 4 个小时：
最终，我们并行运行了 32 个 SQL 脚本来加快速度（虽然不是最好的办法，但是确实很好用）
1. 迁移数据
2. 在 Postgres 中添加需要的新索引，充分发挥 Postgres 的性能潜力
3. 创建新外键
4. 在所有表上使用 VACUUM ANALYZE
为了确保迁移成功，能够正常工作且应对一定的并发量，在准备期间可能执行了约 20-30 次，跨时间约为两个月。
不得不转换 8 个代码库（其中有三个很大的代码库）
所有 pull requests 中凡是超过 250 次 comments，我们就要定位问题，跟踪状态
超过 150 次的代码提交，并花费了很长时间仔细审查所有代码更改
我们没有开辟很多的 git 分支，但是猜想下，应该有 50 左右
大约有 400 个文件被改动
添加了大约 7000 行代码，大约删除了 4000 行
做了 3 次主要的攻关尝试
需要进行两次迁移尝试;
在第一次失败和第二次成功迁移的过程中，我们只在代码库中发现了两个真正意义上的 bug。我们假定我们在某些极端情况下仍然存在 bug，但这些经验给了解决问题的信心，我们正变得越来越好。

经验教训和收获

批量导入数据后，使用 PostgreSQL 的 vacuum analyze，能够更新统计信息，让 PG 的 planner 能够算出更准确的执行计划。
datetime with time zone 类型显然返回一个带时区的字符串格式，但是这个类型常常带来一些不必要的麻烦（建议使用这个）
一些占主导地位的代码库仍然基于 CakePHP2，并且有以下一些问题：
1. Postgres 驱动程序对数据库进行了额外的调用，以便 为每个收到的结果 获取列元数据。这导致了许多即使小的开销查询的延迟。不幸的是，我们只是发现这个罪魁祸首在开发的晚期，但是，最终做到了！以下是我们如何解决这个问题。请注意，启用应用程序内 SQL 记录并未显示此开销。必须更深入地激活 log_statement。
2. 我们遇到了迁移后几天才发现的某些数据的问题。我们已经有一个专门的 Postgres 驱动程序，用来更好的兼容数据泄露问题；
3. 在某些地方使用了 CakePHP ORM 来查询已经有很长列名称的模型的 63 个字符的标识符限制。由于 ORMs 内部的混叠，字段将被截断，因此在返回应用程序后会相互覆盖。该部分必须在纯 SQL 中重写，并将结果转换为与 CakePHP 兼容的结构。
切换到 Laravel 仓库是件令人快乐的事：
1. 它允许我们摆脱大量的手工布尔类型转换，因为它的原生类型的支持布尔型。
2. 一旦遇到一个异常，为了解决问题不得不改框架代码。现在所有更改的都是在配置文件中体现，数据库迁移和测试（主要支持适用的原生类型）。
能够优化我们的“线程注释”– 将代码从“n * m”查询转换为单个查询（尽管非常大和复杂），即使在具有十万条注释的线程上也能获得很好的性能（使用 CTE 和窗体函数）
与 MySQL 相比，PostgreSQL 中的 EXPLAIN（ANALYZE，BUFFERS）的输出更加实用性
MySQL 与 Postgres 的结果字母顺序是不同的。MySQL 使用自己的机制排列，Postgres 则取决于操作系统的特性。（提示：PostgreSQL 10 中有变化）
在 MySQL 中，要使用 UTC（为了在整个系统中使用没有异常），首先必须导入时区；而在 Postgres，它是开箱即用的；
文本搜索主要由我们的 ElasticSearch 服务器驱动，但有时您不需要它的全部功能，Postgres 全文搜索能力也是足够好的，这一切还得益于 GIN 索引的高可用性；
部分索引（使用 WHERE -clauses）并且能够在索引中使用表达式，这种新方法使得我们的生活变得更容易；
MySQL 中也有触发器这个功能，但对我们来说，它在具有 WHEN 功能的 Postgres 中更有用，能够简化触发函数本身并将其与应用它的条件分离；
通过使用可用的新功能（部分索引，窗口函数），我们可以将系统平均负载减少一半（从 8 降低到 4）。我们知道，这就像比较苹果和橘子，因为即使在应用程序中也需要调整许多部分; 但是衡量这种变化过程本身是非常有趣的；
WAL 归档可能会让你感到惊讶。我们有一些看似平常的更新语句突然产生 100GB 的 WAL 文件的情况。你最好需要一个可以在线扩展的文件系统；
重构是我们团队的灵魂，特别是命名。为了快速理解所命名字段的意思和加强系统命名的规范和一致性。Postgres 使我们可以轻松重命名数百个索引和外键，而不用让服务器停机。
与世间万物一样，调试复杂的系统也是一门艺术。相比之下，我们发现 Postgres 的文档是非常通俗易懂的。根据我们的经验，正是由于 Postgres 充分解释每个复杂概念和技术细节才使得 MySQL 的一些技术信息变得更加容易理解。