数据库源代码控制的实现策略

关于将数据库置于源代码控制下的好处，已经有很多文章论述，但许多文章都明确了“为什么”，却对“如何做”含糊其辞。在我们组织决定采用 Linux 和其他开源技术之前，我们的一个开发团队在使用 Redgate SQL Source Control 进行数据市场项目方面取得了显著成功。通过仔细研究该工具解决问题的方式，我们能够推断出这些技术并将其应用于我们使用的基于云的数据市场平台，即 Amazon RedShift 和 Microfocus（原 HP）Vertica。

本文描述了我们数据库源代码控制解决方案的需求、我们面临的挑战以及我们如何应对这些挑战，以及使用 Redgate SQL Source Control 作为模板所提供的帮助。虽然有些细节特定于 RedShift 和 Vertica，但项目的总体要求以及我们团队所秉持的原则适用于任何数据库源代码控制解决方案。

数据库源代码控制解决方案需要什么？

您现有流程中的摩擦点在哪里？哪些造成了最大的痛苦？就我们而言，问题在于不清楚每个环境中数据库的精确状态，这意味着我们永远无法确定跨不同环境的部署或回滚是否会按预期工作，或者针对数据库的测试是否有效。

识别这个问题使我们能够列出数据库源代码控制采用初始阶段最重要的要求。该解决方案应允许团队做到：

1. 为数据库中所有对象生成任意版本的创建脚本
2. 在任何预生产环境中拆除数据库
3. 在任何预生产环境中重建任意版本的数据库
4. 在任何环境（包括生产环境）中从版本“x”部署到版本“x+1”
5. 在任何环境（包括生产环境）中从版本“x”回滚到版本“x-1”
6. 针对每个环境保护任何部署和回滚机制，以便只有授权人员才能运行它

更普遍地说，我们知道我们实施的任何流程都必须尽可能简单和自动化。我们还希望它能让任何有权访问源代码控制系统的人（无论他们是否是团队成员）高度可见我们的数据库更改。

好高骛远是毒药

如果您的总体目标是实现数据库的持续自动化交付，但您是从“如何做”的基础开始的，那么您需要对范围保持现实。花时间了解需要什么才能使数据库源代码控制成为严格和理解良好的变更控制流程的组成部分；这将需要投入时间、技能和流程，以及可能需要组织和文化变革。

就我们而言，我们决定将初始范围限制在阶段 1-3（如前所述）。我们将把增量部署（如将在生产环境中执行的）视为在第二阶段需要解决的挑战。

我们的第一个目标，在第一阶段，是达到我们可以保证部署起始点的阶段，这样我们就可以拆除现有的开发数据库，并从版本控制中重建它，在任何版本。一旦我们的构建脚本健壮可靠，我们就可以继续使用 Jenkins 请求重建预生产环境。这将允许我们构建一个已知版本的数据库，并从该已知起始点练习部署/回滚。

我们还决定将我们的精力仅限于所需的数据库对象，即仅限于我们目前正在使用的 RedShift 和 Vertica 中的那些对象，而不是我们将来可能使用的那些。我们的理由是，如果我们将时间花在满足所有可能的数据库对象上，而不是只满足我们使用的那些，那么我们将增加采用数据库源代码控制的成本和时间，而不能保证获得额外的收益。请记住，在软件交付并投入使用之前，它只有成本，没有收益。

一切都关乎团队合作

虽然源代码控制为单个数据库开发人员提供了一些好处，但只有当整个团队都采用它时，它才能真正发挥作用，因此成功取决于代码开发人员和数据库开发人员之间的密切协作。这不是个人，甚至不是 DBA 团队可以孤立实现的。

作为一个团队，我们以一种清晰表达我们的担忧或恐惧，以及表达感知到的好处的方式，追求数据库源代码控制的目标。对于每个疑点领域，我们都努力解决问题，直到我们确信我们的方法是可靠的。简而言之，我们更关注人员和流程使其发挥作用，而不是工具和技术。

每个团队成员根据自己的优势处理问题的一部分，因此我们对整体解决方案有一种共同的所有权感。例如，我知道如何在各种数据库中使用系统表来自动化数据库对象的脚本编写，所以我承担了编写自动化脚本解决方案的任务。

另一位团队成员熟悉 Git 源代码控制以及如何使用标签和标记来标记和检索任何版本和补丁级别的软件，他承担了如何将其应用于数据库源代码控制，以及如何最好地处理分支、合并和其他源代码控制规范的挑战。第三位成员拥有 Jenkins 等工具的经验，我们将使用这些工具来帮助构建部署管道。

我们的首席 Vertica DBA 使用 Hashicorp Vagrant 和安装脚本做了大量工作，以便我们每个人都可以构建一个本地 Vertica 开发环境。在开发中，Vertica 是一个由 Vagrant 构建的 Virtual Box 实例，我们的起点是能够通过从命令行运行脚本来执行重建。

第五位团队成员，拥有 RedShift (psql) 和 Vertica (vsql) 命令行工具的经验，致力于研究我们可以用来确定部署操作是否已发生，从而继续下一步或触发适当回滚脚本的机制。

尽管我们最初的目标不包括生产，但开发一个最终不适合该目的的流程是没有意义的。因此，我们还与生产系统上的 DBA 密切合作，以确保我们设计的任何流程都可以在关键的凌晨 3:00 生产问题中发挥作用，需要快速部署。

实施 Redshift/Vertica 数据库源代码控制

即使是难题，当你掌握了解决方法，也会变得简单。我们之前使用 Redgate SQL Source Control 的经验向我们展示了如何解决这个问题。我们特别喜欢以下几个想法：

• 数据库对象作为每个对象一个文件进行脚本化
• 文件以其所代表的对象命名
• 文件夹结构密切模仿 SQL Server Management Studio (SSMS) 中数据库对象的结构。
• 数据拥有自己的目录。数据文件将与它们加载数据的表同名。

我们在 RedShift、Vertica 和 SQL Server 之间在对象名称、对象依赖项的实现方式以及数据库脚本选项方面遇到了一些差异。这给我们带来了一些版本控制挑战，但也带来了一些机遇。

源代码控制架构：文件夹结构

Redgate SQL Source Control 将表约束和 sp_addextendedproperty 命令包含在与 CREATE TABLE 命令相同的文件中。这对于 SQL Server 来说是有意义的，SQL Server 使用主键、唯一键和外键强制执行数据参照完整性，因此决定了表的构建顺序。

在 RedShift 和 Vertica 中，这些项只是元数据，仅为查询优化器提供提示。默认情况下，约束不被强制执行。您可以选择开启约束强制执行，但这会带来 CPU 和内存利用率方面的巨大开销。

尽管未强制执行的约束从数据管理角度来看是糟糕的，但它确实允许我们将数据对象之间的依赖关系分类如下：

依赖类别	描述
自然	模式必须存在才能构建表 表必须存在才能构建视图 对象必须存在才能附加描述。
人工	外键决定了表必须构建的顺序。由于 Vertica 或 RedShift 中不强制执行约束，因此在最后一刻创建这些约束没有任何优势。

为了利用 Vertica 和 Redshift 中宽松的约束机制，它允许我们以任何顺序简单地构建表，我们决定打破 SSC 文件夹结构，而是为每种约束类型创建一个单独的文件夹，并为数据字典描述显式创建一个文件夹。这具有以下优点：

• 对象的描述可以单独维护，而无需表明 CREATE TABLE 脚本需要更改。
• 如果部署需要先删除表约束，然后在部署结束时重新创建，那么我们可以简单地调用该约束的相应 DDL 脚本。如果该表约束是表构建脚本的一部分，则这是不可能的。

源代码控制架构：文件命名

如果您的源代码控制解决方案必须跨多个平台工作，就像我们的一样，那么明智的做法是确保像文件命名这样基础的东西不会让您陷入困境。我们原型源代码控制系统最初在 Ubuntu 和 Mac OS 上运行良好，但第一次在 Windows 上运行就失败了。原因是什么？我们在 Windows 工作站上超出了完全限定路径和文件名 260 个字符的限制。

SQL Server、Vertica 和 RedShift 中的数据库对象名称可以是 128 个字符长，因此架构限定表的 文件名可以是 261 个字符

• 2 x 128 个字符名称
• 2 x 1 个句点
• 1 x 3 个字符用于 SQL 扩展名

这还没有考虑文件夹结构。因此，我们将源代码控制对象放置在驱动器根目录下的一个简单代码文件夹中，并对数据库对象使用简洁的名称。

260个字符的限制可以通过组策略更改或注册表设置来克服，但这需要推广到整个系统。

自动化数据库脚本

在 RedShift 或 Vertica 数据库没有像 SQL Source Control 甚至 SQL Compare 这样的工具的情况下，我们需要设计一种自动化方法，将现有数据库脚本化为源代码控制中的单个对象脚本，以及开发人员在开发工作中脚本化并提交 DDL 对象脚本。

消除 Git 中“虚假更改”的担忧

我们担忧之一是，重复对数据库进行脚本编写会导致我们的源代码控制库发生巨大变化。似乎每个对象都已更改。如果属实，这将使我们很难知道哪些对象真正发生了更改。如前所述，能够表达疑虑，并设计实验来证实或消除它们，是获得采用的重要组成部分。

我们在 Windows 工作站上使用 Git BASH，以确保我们的脚本解决方案不会在每次提交时都用“虚假”更改淹没 Git。下面的实验说明了这一点。

# Create a simple text file
echo "This is a test" >test.txt

# Does git see this file?
git status

这会产生一个简单的消息，如下所示，表明 git 已经看到了新文件

On branch master
Your branch is ahead of 'origin/master' by 1 commit.
 (use "git push" to publish your local commits)
Untracked files:

(use "git add <file>..." to include in what will be committed)

test.txt

nothing added to commit but untracked files present (use "git add" to track)

所以，我们把文件添加到 git，提交更改，然后询问 git 如何看待这个文件

# add and commit the file
git add .
git commit -m "When applied this records our experimental test.txt file"
# Does git see this file?

git status

现在 git 知道文件已记录在我们的本地存储库中，但尚未推送到我们的中央存储库。

On branch master
Your branch is ahead of 'origin/master' by 1 commit.
 (use "git push" to publish your local commits)
nothing to commit, working tree clean

现在让我们删除文件，看看 git 会怎么做

# Remove our experimental file
rm test.txt

# Does git see this file?
git status

这表明 git 识别出文件已被删除，并希望我们更新/提交或放弃更改

On branch master
Your branch is ahead of 'origin/master' by 1 commit.
  (use "git push" to publish your local commits)
Changes not staged for commit:
  (use "git add/rm <file>..." to update what will be committed)
  (use "git checkout -- <file>..." to discard changes in working directory)
          deleted: test.txt
no changes added to commit (use "git add" and/or "git commit -a")

现在我们重新生成文件，就像我们一开始做的那样

# Create a simple text file
echo "This is a test" >test.txt

# Does git see this file?
git status

下面的消息显示，就 Git 而言，为未提交的删除重新添加相同的文件不会被注册为源代码控制下的额外活动。

On branch master
Your branch is ahead of 'origin/master' by 1 commit.
 (use "git push" to publish your local commits)
noting to commit, working tree clean

脚本化数据库

我们的自动化脚本解决方案由 SQL DML 查询组成，用于编写 SQL DDL 命令。在 Vertica 中，有一个显式的 EXPORT_OBJECTS() 函数，它将用户被授予权限的所有数据库对象脚本到一个文件 (overall_db_script.sql) 中。对于 RedShift，没有显式的脚本函数，但 Amazon 提供了许多 方便的查询，我们可以以此为基础编写 DML 查询。RedShift 查询生成一个文件，其中包含特定类型的所有对象。例如，用于生成所有数据库模式对象的 DDL 的数据将在一个文件中。

例如，在 Vertica 中，结果是一个看起来像以下内容的单个 SQL 脚本

CREATE SCHEMA STAGING;
  COMMENT ON SCHEMA STAGING IS 'The landing area for the data warehouse';
  CREATE SCHEMA REF;
  COMMENT ON SCHEMA REF IS 'Primary source of reference data';
  CREATE SEQUENCE STAGING.Seq_UserAgent CACHE 100;
  CREATE TABLE STAGING.UserAgent
  (
    UserAgentId INT NOT NULL DEFAULT nextval('STAGING.Seq_UserAgent'),
    etc
  );
  ALTER TABLE STAGING.UserAgent ADD CONSTRAINT PK_UserAgent PRIMARY KEY(UserAgentID) DISABLED;
  COMMENT ON TABLE STAGING.UserAgent IS 'Captures the web browser user agent';
  …etc

拆分单独的对象脚本

下一个任务是将这个单一脚本分割成单独的 SQL 文件，每个文件放入相应的源代码控制目录，替换任何现有文件。然后可以将这些单独的文件提交到我们的 Git 存储库。

我们使用 bash 工具 grep、awk 和 csplit 来完成此操作。CREATE TABLE、CREATE VIEW 和 CREATE PROJECTION 命令跨多行，而其他所有命令都是每行一个命令。

对于单行命令，我们使用 grep 提取各种命令，然后使用一些 awk 程序将它们分割和格式化为单独的 .sql 文件，并将其保存到源代码控制中的正确目录。例如，对于前面列表中 CREATE SCHEMA 命令，我们最终会在一个名为 /schema/ 的文件夹中为每个模式生成一个文件，每个文件包含相关的 CREATE SCHEMA 命令。对于对细节感兴趣的人，我已提供了一个可下载的 Vertica_ProcessOneLineCommands 工作文件，其中显示了 grep 命令和我们用于拆分的 one_line_create.awk 程序的典型示例。

我们对多行命令（例如 CREATE TABLE、CREATE VIEW 和 CREATE PROJECTION）的处理采用了类似的技术，但使用 Linux csplit 命令将不同的 SQL 命令分离到单独的文件中，并且我们必须处理的额外复杂性是，不同版本的 Linux 对 csplit 等命令的实现方式略有不同。csplit 命令为每个单独的对象生成一个文件，但每个文件的文件名都是通用的 vertica_object_xxx。然后我们使用一个 awk 程序来提取 CREATE TABLE、CREATE VIEW 或 CREATE PROJECTION 命令以及它试图创建的对象的名称。同样，我已将典型示例作为可下载的 Vertica_ProcessMultiLineCommands 工作文件包含在内。

即使在有许多表和视图的数据市场中，这些流程产生的对象数量也微不足道。

自动化数据库脚本的其他好处

自动化脚本还带来一些额外的好处：

• 它标准化了脚本，从而减少了关于格式的激烈但毫无意义的争论
• 它加快了脚本编写速度

为了澄清后者，我可能会编写一个快速脚本，在我的数据市场数据库本地副本中创建一个表，包含所有约束、表和列描述。执行该脚本后，我可以使用自动化脚本方法，以便创建的所有对象都能正确地分离到其正确的源代码控制文件夹中的标准化格式

我不需要担心我的未经修饰的脚本是否符合商定的格式标准，或者是否选择内联脚本约束或在表创建脚本的末尾脚本约束。自动化脚本将为我处理这些。

受源代码控制的数据

Redgate SQL Source Control 允许将表中的“静态”数据置于源代码控制之下。鉴于我们希望启动一个完全可用的数据库实例，这是一个必不可少的功能。它会创建一个与表创建脚本同名的 SQL 文件，但该文件位于“data”文件夹中。这些文件包含每条记录的 INSERT INTO 查询。

同样的方法可以用于 AWS RedShift 和 Vertica，但作为列式存储，它们更倾向于使用各自版本的 COPY 命令批量插入数据。

也许 Redgate SSC 的未来版本可以提供以适合 BULK INSERT 或 bcp 的格式导出文件的选项。这将使将大型表中的数据置于源代码控制之下更具实用性。

个人开发数据库的拆除和重建

在 SQL Server 领域，Redgate 提供了几种机制来提供生产数据库的个人开发副本。

• Redgate SQL Provision 代表了一个全面的解决方案，不仅提供数据，还提供生产数据掩码以缓解合规风险。
• Redgate SQL Compare 快照允许快速构建生产数据库的结构
• Redgate SQL Source Control 在某种程度上介于两者之间，因为它除了允许部署结构和较小的数据集外，我们还可以部署到特定版本。

在 Vertica 和 RedShift 没有此类工具的情况下，我们必须提供自己的机制，该机制要么基于迭代单个 SQL 文件，要么将它们连接成一个大型部署脚本。通过实验，我们发现连接单个脚本并执行生成的多对象脚本性能更好，也更可靠。

我们使用 Linux cat 命令来完成此操作，例如

cat tables/*.sql > all_objects.sql
  cat views/*.sql >> all_objects.sql
  cat data_dictionary/*.sql >> all_objects.sql
  …etc
   psql -f all_objects.sql

对于 Vertica，它要简单一些，因为我们仍然拥有包含我们需要部署的所有对象的 overall_db_script.sql，但我们必须考虑一些陷阱。

Vertica 有明确的命令来描述数据在多节点集群上如何分布。由于我们的开发机器是单节点 VirtualBox 实例，因此我们需要能够去除这些子句的功能。

Vertica 有点类似于 SQL Server 索引优化向导，只是在 Vertica 上建议使用它。它会导致新的系统模式和这些模式中的对象被 EXPORT_OBJECTS() 函数获取。幸运的是，EXPORT_OBJECTS 将接受一个逗号分隔的模式列表，我们可以通过运行下面显示的简单查询来选择相关的模式

SELECT schema_name
  FROM v_catalog.schemata
  WHERE is_system_schema=false
  AND schema_name NOT LIKE ‘v_%’;

到目前为止，我们发现在我们的生产环境中存在两个我们未意识到的具体问题领域

• 就像 SQL Server 2000 一样，Vertica 依赖项跟踪有点碰运气。当构建脚本尝试使用不存在的序列创建表时，我们发现了这一点。
• 分区函数可能以一种实现所需行为的方式进行脚本编写，但具有其自己的 SQL 代码解释。我们必须编写发生这种情况的分区函数。

重建性能

我们发现 RedShift 和 Vertica 在部署大量对象时表现截然不同。对于 RedShift 上大约 15,000 个对象，我们看到了大约 2 小时的构建时间。Vertica 始终低于 10 分钟。我们发现 RedShift 和 Vertica 都相对较快地摄取数据。

增量部署和差异

一旦数据库拆卸和重建的过程变得健壮可靠，我们就可以进入下一阶段并解决增量部署的挑战。

我们究竟如何选择实现对现有数据库对象的更改取决于几个因素：–

• 数据量
• 访问频率
• 访问方法
• 服务级别协议。我们是否必须在系统使用期间进行更改？
• 执行/回滚时间
• 回滚方法。完全恢复到部署前状态真的可能吗？
• 数据库和应用程序依赖项

此外，每个对象的脚本意味着增量部署可能涉及许多脚本。这表明我们需要类似清单的东西来指定部署或回滚需要运行哪些脚本。清单概念是一个有用的概念，其实现表明我们需要在源代码控制文件夹结构中创建两个新文件夹：

• 一个清单文件夹，用于存放我们的部署/回滚清单
• 一个增量文件夹，以便实现对象的更改和对象的删除

在 SQL Server 中，我们可以使用 SQLCMD 模式并使用 :r 命令指向我们希望运行的文件。这允许执行一个主脚本，该脚本又会调用构成该部署或回滚的所有依赖脚本。

我们必须使用 bash、psql 和 vsql 构建一个等效的机制。

清单文件将类似于下面示例所示

# JIRA Ticket number 32768
# ====================
# Purpose:
# ======
# To do stuff
# Deployment scripts
# ==============
+ tables/REF.PostCodeGeography.sql
+ tables/STAGING.PostCodeGeography.sql
+ data/PostCodeGeography.sql
+ constraint/PK/REF.PostCodeGeographyPK.sql
+ constraint/PK/STAGING.PostCodeGeographyPK.sql
+ delta/JIRA32768/ValidatePostCodeGeography.sql

# Rollback scripts
# ============
~ /delta/JIRA32768/rollback/DropPostCodeGeography.sql
# END OF FILE

我们的机制只是查找带有相应前缀符号的行

• # = 忽略。这些行只包含注释
• + = 部署脚本
• ~ = 回滚脚本。我们使用波浪号符号，因为 bash 和 Linux 命令行实用程序可能会混淆如何处理减号

清单和增量脚本内容的寿命只需要持续到部署被认为是成功的并且不会回滚。此时，由于运行我们的数据库脚本进程，数据库模式的所有更改将反映在主源代码控制文件和文件夹中。

鉴于我们的开发、集成、测试和其他预生产数据库可能处于不同级别，将已执行的清单及其脚本日志存储在数据库本身中非常有用。这有助于决定哪些清单已部署或仍待部署。

总结：采用数据库源代码控制的关键原则

源代码控制是一个不断发展的话题，即使在开发社区中也是如此。尽管代码开发人员的源代码控制技术远远领先于他们的数据库开发人员同行，但技术和实践仍在不断涌现，而另一些则正在被淘汰。

2018 年数据库 DevOps 现状 显示，只有 52% 的受访者使用版本控制。随着这个百分比的增加，我预计这将推动源代码控制技术和实践的进一步改变，因为数据库开发人员的需求变得越来越重要。以下是我们目前学到的一些关键点：

保持纪律

如果源代码控制作为纪律流程的一部分使用，那么搞砸它的最快方法就是跳出这个纪律流程。

假设您遵循一个部署管道，该管道包括在开发、集成、测试等环境中执行清单，一直到生产环境。如果您决定在此管道之外部署某些内容，那么尽管您可能解决了立即的局部问题，但您现在已经破坏了管道。您现在将不得不花费大量精力使一切恢复同步。如果您的流程有一些粗糙的边缘，那么请修复这些粗糙的边缘，不要试图规避流程。

少量多次部署

几年前，部署到生产环境是一个紧张而正式的过程。部署规模庞大且复杂，脚本的复杂性也很高。更好的做法是进行许多较小的部署，而不是较大的部署。部署脚本中的大量复杂性旨在减轻部署可能出错的各种组合。较小的部署意味着可能出错的组合更少。

频繁提交，短期分支

18个月前流行的工作方式已经不再受欢迎。一个例子就是源代码分支的使用方式。尽管一些观点仍在争论，但某些实践已形成共识：

• 新的工作代码应频繁提交
• 源代码分支的生命周期越长，合并回主干时出现问题的可能性越大
• 在开发过程中，尽可能频繁地从代表生产环境的分支中拉取代码。

使用第三方工具取代复杂流程

除非您从事构建和销售数据库工具的业务，否则一个价格合理的第三方工具是构建您自己的工具来满足固有复杂流程的诱人替代方案。

尽管 Vertica 通过提供显式的 EXPORT_OBJECTS() 函数给了我们一个开端，但当我们添加脚本以使用清单系统实现增量部署时，这一优势就减弱了。我们的解决方案突然发展到有很多活动部件的地步。我们有一个可行的原型，但它有很多活动部件，并且对维护解决方案所需的技能存在一些担忧。

RedShift 需要更多的查询、bash 和 awk 脚本来生成适合源代码控制的脚本。其复杂性如此之高，以至于负责 RedShift 集群的团队已决定评估其他工具，例如最近开源的 Goldman Sachs Obevo。由于 RedShift 基于 Postgres 8，因此许多 Postgres 工具都可以很好地与 RedShift 配合使用。

Vertica 有许多优点。尽管它最初是 Postgres 的一个分支，但它与 Postgres 的兼容性很低，并且是一个小众产品。因此，第三方工具很少。

延伸阅读

• https://www.red-gate.com/simple-talk/sql/sql-tools/simple-talk-source-control/
• https://www.red-gate.com/simple-talk/sql/database-delivery/database-version-control/