从 SQLite 到 PostgreSQL

Danny Bytebase 2022-05-27

此文英语原文作于 Bytebase 1.0 发布之前，在 Hacker News 上引发热烈讨论。可点击文末阅读原文进行阅读，也可访问 https://news.ycombinator.com/item?id=31038614 围观讨论。

前情提要

迁移过程

制定计划对于任何工程项目都很重要。以下是谷歌迁移存储系统的常用配方，我写下了一个简单的计划（所幸的是这次我们不用迁移数据）：

启动 Bytebase 时安装 PostgreSQL；
创建一个 PostgreSQL 数据库 schema，与 SQLite 的相当；
同时向 SQLite 和 PostgreSQL 写入数据；
将读操作从 SQLite 切换到 PostgreSQL；
清理双重写入的代码。

我们用 Go embed (https://pkg.go.dev/embed) 打包了 PostgreSQL Linux 和 Darwin 两个软件包。Bytebase 启动时，我们就解压软件包，运行 initdb 来设置数据库，最后启动 PostgreSQL Server。同时，我们也进行了一系列优化，包括：

使得 PostgreSQL 的安装和设置成幂等，以应对中间可能产生的错误；

关掉监听端口，用 Unix 域套接字进行连接。不暴露端口可以提高安全性，也改善了主机冲突端口的情况；

使用 Go build 标签 (https://pkg.go.dev/go/build)，仅为对应平台嵌入相应 PostgreSQL 包，以减少 Bytebase 二进制文件大小；
支持以 Linux root 用户运行 Bytebase，同时以非 root 用户运行 PostgreSQL，因为 PostgreSQL 不能以 root 用户运行。

我们原来有一套复杂的 SQLite schema (https://github.com/bytebase/bytebase/blob/0.13.0/store/migration/10001__init_schema.sql)，包含 1K+ 行代码。为了找到适合 PostgreSQL 的 schema (https://github.com/bytebase/bytebase/blob/release/v1.0.2/store/migration/10001__init_schema.sql)，我们对 PostgreSQL 执行了 SQLite DDL 语句，并逐一解决了出现的问题。它们间一些明显的区别包括：

AUTOINCREMENT 变成了 SERIAL；

关键词：一些列名，如 "GRANT" 在 PostgreSQL 是关键词，应该给它加上双引号；

行更新时间触发的 DDL 不同；

改变序列起始值的方式不同；
Now() 的时间戳。

SQLite	PostgreSQL
CREATE TABLE principal ( id INTEGER PRIMARY KEY AUTOINCREMENT, grant TEXT NOT NULL )	CREATE TABLE principal ( id SERIAL PRIMARY KEY, "grant" TEXT NOT NULL;
CREATE TRIGGER IF NOT EXISTS `trigger_update_principal_modification_time` AFTER UPDATE ON `principal` FOR EACH ROW BEGIN UPDATE `principal` SET updated_ts = (strftime('%s', 'now')) WHERE rowid = old.rowid;	CREATE OR REPLACE FUNCTION trigger_update_updated_ts() RETURNS TRIGGER AS $$ BEGIN NEW.updated_ts = extract(epoch from now()); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER update_principal_updated_ts BEFORE UPDATE ON principal FOR EACH ROW EXECUTE FUNCTION trigger_update_updated_ts();
INSERT INTO sqlite_sequence (name, seq) VALUES ('principal', 100);	ALTER SEQUENCE principal_id_seq RESTART WITH 101;
strftime('%s', 'now')	extract(epoch from now())

随后就是浩大的工程，来更新现有的 SQL 语句，SQLite 语句（类似于 MySQL）使用问号参数，而 PostgreSQL 使用定位参数进行查询。因为我们把大工程分解成了多个小变化，所以如果有外键，写入表时的变化应该以拓扑排序的方式实现。例如，如果 author table 被 book table 引用，我们应该在一个变化中首先对 author table 进行双重写入，然后作为后续变化对 book table 进行双重写入。

SQLite

PostgreSQL

SELECT name FROM principal

WHERE age > ? AND city = ?;

SELECT name FROM principal

WHERE age > $1 AND city =$2;

任务完成！真实世界的「迁移」感觉就不太一样了。在酒店的房间里呆了几天后，我决定和朋友一起去上海市中心逛逛，但是在没有任何计划的情况下，我们走丢了好几回。

上海市中心某处

总结

SQLite 能够满足大多数本地数据存储的需求，而 PostgreSQL 则更为普遍。值得一提的是，迁移后，我们收到来自社区的一个贡献 (https://github.com/bytebase/bytebase/issues/1027)：Bytebase 现已支持连接外部数据库，用户能在 K8s 集群中运行 Bytebase 并持久性存储。

在迁移过程中，我们当然也没忘记受到 Google 最佳实践的影响，而在 Bytebase 建立的 Life of a Feature (https://github.com/bytebase/bytebase/blob/main/docs/life-of-a-feature.md)，正是这些最佳实践使得我们这次数据库迁移能够顺利进行。

写下你的设计。它可以帮助你想出一个清晰、可执行的计划，也可让你的同事看看是否合理，甚至提出更好的替代方案。
把大问题分解，并对每个小问题进行 review，这对于做出快速坚决的决策非常重要。
测试。我们非常幸运，因为在迁移数据库项目前不久，我们增加了 Go E2E 测试。测试会启动一个真正的 Bytebase 服务器，通过调用 API 来模拟用户的行为，并覆盖了大部分关键的用户旅程。我们可以通过运行 "go test" 命令而不是点击 Web UI 上的数百个按钮来了解某个变化是否有效。

Bytebase 提供了基于 Web 的协同工作空间，帮助 DBA 和开发人员管理数据库 schema。大家应该很好奇 Bytebase 是如何管理自己的数据库 schema 的，请听下回分解。

康威定律的边界 - Project (项目) 的设计脉络

解读 Retool 团队升级 4TB PostgreSQL 踩坑

远程办公参与开源项目如何协作？

如何为复杂项目做贡献