如何迁移一个 3TB 的 PostgreSQL 数据库数据

【CSDN 编者按】这篇文章分享了作者作为一位全栈开发者，在三周内将一个 3TB 的 PostgreSQL 数据库从 AWS 迁移到 Azure DevOps 的过程和经验，以及遇到的挑战和解决方案。

原文链接：https://betterprogramming.pub/how-i-migrated-a-3tb-postgresql-transactional-database-ee1cf85c37bf

作者 | Geronimo Velasco译者| 明明如月

责编 | 夏萌

出品 | CSDN（ID：CSDNnews）

在快速发展的创业环境中，小型开发团队经常需要负责整个项目的开发工作。当担任一家交通顾问公司的全栈开发人员时，我亲自体验了这样的情况，并成为了一个关键项目的独立负责人。随着团队成员开始忙于其他任务，项目中的所有挑战都集中在我一人身上。

我们主要负责管理交通系统中的交易流程，并处理传入的数据，以生成关键报告和信息。面对源源不断涌入的新交易，我们需要一套可靠且高效的数据库解决方案。

在忙于日常系统管理的同时，我们收到了一项突发的客户需求：将庞大的 3 TB PostgreSQL 数据库从 AWS 迁移到 Azure DevOps。这个需求背后的目的十分明确，即将整个系统整合到 Azure 生态系统中，从而简化操作流程并挖掘增长潜力。

然而，时间紧迫，我们面临着严峻的挑战。客户设定了一个很短的 Deadline，仅给我们三周的时间来完成迁移。由于我们深知成功依赖于精密的规划、完美的执行和一定程度的创新，任务的迫切性为我们增加了额外的压力。

在本文中，我将分享关于迁移过程的经验和洞察。虽然我不是 PostgreSQL 的专家，但作为承担这一复杂任务的唯一开发人员，整个过程充满了挑战和重要的发现。我相信，这些经验对于那些面临类似情况的人将具有极大的价值。迁移如此庞大的数据库需要精心的规划、战略性的决策，以及深入理解所涉及的复杂性。

迁移准备

迁移的准备阶段无疑是整个过程中最为复杂的部分之一。由于项目过去尝试使用 pg_dump 迁移数据库（当时数据库为 2TB）而失败，我对下一步该如何进行感到不知所措。尽管我对数据库的整体结构和大小有基本了解，还曾针对它进行特性开发和复杂查询编写，但由超过 200 张表格组成的庞大结构使得任务远比我最初设想的复杂。

要解决这个问题，首先要对整个数据库进行全面映射。我详细记录了关键列、索引、关系、约束、触发器、受它们影响的表格和必要的序列。这一详细映射使我们能清晰地理解面临的挑战，并为迁移制定最佳方案。

此外，整个过程耗时近一周才完成，期间新交易不断涌入，使得主数据库的不断增长成为了一个严重的问题。

由于传统的备份和恢复手段可能会导致数据不一致，但是我们的业务非常关键，所以迁移期间停机不可行。

在增量备份方面的不熟悉和时间的紧迫下，我和技术主管共同分析了现状，并设计了一种替代方案。我们的方案包括将新的交易重定向到另一个数据库，同时我集中精力迁移现有的 3 TB 数据库。这种方法要求我们不仅迁移 3 TB 的数据库，还要处理新传入交易的新数据库。

虽然方案复杂，但这个替代方案预计将节省用于迁移较小数据库的时间和精力。尽管遇到了挑战，我们还是成功地完成了迁移，并将对持续运营的干扰降至最低。这次经历让我在高效、负责地管理大规模数据库迁移项目方面积累了宝贵经验。

数据库准备策略

选择迁移方案

在深入分析数据库后，我对不同的迁移方案进行了探讨。首先考虑的是使用 pg_dump/pg_restore方案，虽然此前的尝试并未成功。其次考虑了使用 Pentaho，这是一款专门处理大型数据集，功能强大的数据集成工具。最后，技术负责人提出了一种使用偏移限制的手动分页方案，在演示中引起了我的关注。

下面，我将详细解释这三种迁移方案，包括各自的实施过程和我们所遇到的挑战。尽管每一种方案最初都显得诱人，但我们最终选择了最适合我们独特需求的方案。

Pentaho

项目在使用 pg_dump/pg_restore失败后，Pentaho 方案成了首选。为验证 Pentaho 是否符合迁移需求，我进行了一次演示，试图从一个包含近 7 亿条记录、40 列左右的庞大表中迁移数据。

在当天配置好环境后，我让迁移任务在夜间运行，以便第二天评估其性能。

第二天一早检查迁移进度时，我发现了IODataFileRead 等待事件，这让我感到不安，因为这暗示了迁移正在费力地从磁盘读取所需的数据。经过 12 个多小时，仅迁移了约 2.5 亿条记录，对此我非常纳闷，于是我开始深入分析背后的原因。

我开始关注表格的庞大大小和众多列数。显然，数据库默认的 8 KB 页面大小无法处理如此庞大的表，可能导致数据库按随机顺序获取大量页面，从而使数据读取卡顿。数据库不得不以这种低效方式检索数千个页面，并将它们存储在 RAM 中再返回。这种方法大大拖慢了迁移速度。

我多次尝试复现问题，并与类似的表格进行对比，寻找一种能使 Pentaho 方案有效的办法。然而，不幸的是，这并没有达到预期效果，随着时间的流逝，找到合适方案的紧迫性也愈发增加。

尽管在这个方案上投入了四天的时间和精力，但显然还有其他更符合我们迁移需求的选择。因此，在截止日期临近和需要探索其他方案的压力下，我决定转向下一种方案。作为此关键项目的负责开发者，快速适应和明智地做出决策成了关键。

手动分页方法与偏移量限制的应用

在 Pentaho 数据迁移演示中，我利用空余时间探索了其他的可能方案。尽管起初对方案的成功抱有疑虑，但在探索过程中，我却偶然发现了一些有趣的现象。

我构建了一个简易的 NodeJS 项目，通过设置偏移量和限制值均为 100,000 条记录的分页方式，从一个拥有 7 亿条记录的表中获取信息。出乎意料的是，当查询量达到大约 2.2 亿条记录时，查询突然变得缓慢并卡顿，触发了引起关注的 IODataFileRead等待事件。对这一突发情况我感到困惑，便决定进行深入分析，以找出根本原因。

在我的研究过程中，我发现了一些关于偏移量的有趣事项。通过执行 EXPLAIN ANALYZE命令深入探讨查询，我进一步了解了 PostgreSQL 的执行流程，以及它在处理如此庞大的数据集时所遇到的挑战。

查询示例

EXPLAINANALYZESELECT* FROMtable_name offset200000limit100000;

使用 OFFSET命令并不直接跳过记录，而是获取所有记录，并由 PostgreSQL 进行筛选。这解释了查询为何减速，因为它不仅获取了预期的 100,000 条记录，还获取了全部的 2.2 亿条记录，然后才进行过滤。这一观察验证了我的最初怀疑。

在探索备选方案时，我们考虑使用 COPY 命令的分页方式进行备份。不幸的是，我们遇到了难题。由于数据库表拥有包含 UUID 值的主索引，这使得通过 COPY 命令高效地进行迁移变得困难，因为 UUID 打乱了表的顺序。

查询示例

COPY (SELECT* FROMyour_table WHEREidBETWEEN1AND100) TO'/table-backup.csv'CSV HEADER;

这一发现促使我们来到了一个关键的决策点，要求我们重新评估方案，并寻找一种哪怕受到 UUID 主索引限制也能有效工作的方法。作为一名独立开发者，找到合适的解决方案需要创造力和技术专长的有机结合。

使用 pg_dump/pg_restore 进行迁移

经过了一周对两种不同方案的探索和测试后，我最终决定使用 pg_dump和 pg_restore命令来执行任务。在这个阶段，我深入研究了这些命令，以便更精确地理解如何充分利用它们以达到最佳性能。

然而，我们在 Azure For PostgreSQL 的灵活服务器配置方面遇到了一个显著的挑战，那就是无法获得超级用户的访问权限。这个问题的限制在于，理想情况下迁移过程中应禁用触发器以提高恢复速度，但由于我们缺乏必要的访问权限，无法执行该操作。这个问题促使我必须寻找其他有效的迁移方法。

在整个过程中，我及时向客户报告了开发的进展，因为任务的复杂性已经超出了原定的时间计划。为了解决这些挑战，客户还特地聘请了一位 PostgreSQL 专家来协助我们，但即使经过两周的共同努力，迁移依然没有成功。因此，客户不得不延长了项目的截止日期以适应我们所遇到的复杂情况。

两周的深入研究和广泛的实验后，我成功地制定了一项全面的迁移计划，以加速 3 TB 和较小数据库的迁移。

1. 使用 pg_dump 的 0 压缩 (-Z 0): 为了提高速度，我在备份阶段选择了不进行压缩。虽然这样做增加了磁盘空间的需求，但却加快了备份过程。
2. 实现并行备份和恢复 (-j <num>): 我通过使用参数 -j <num> 来并行处理表的备份和恢复，以进一步提高性能和减少迁移时间。
3. 在恢复过程中禁用触发器 (--disable-triggers): 为了确保数据一致性和避免触发器冲突，我在恢复阶段使用了该参数来禁用触发器。

我的具体迁移计划包括：

1. 备份数据库模式、迁移序列，并在新服务器上创建必要的数据库角色。
2. 删除关键约束，以降低不必要的 IO 操作，并加速迁移过程。
3. 移除大型索引，进一步优化迁移中的 IO 操作。
4. 按表或相关表组进行逐项迁移。由于没有超级用户权限，我仔细选择了需要移除的关键关系以促进迁移。
5. 在迁移信息后恢复约束，并注意表之间的关联关系。
6. 最后，成功迁移数据后恢复大型索引。

迁移计划完成后，我与技术负责人一同审查了方案，并开始配置环境，准备启动实际的数据库迁移过程，从而解决了由于缺乏超级用户权限所带来的限制问题。

配置迁移环境

为了搭建迁移环境，我们计划利用虚拟机（VM）来完成数据库的备份，并在最终的数据库中执行恢复操作。该虚拟机将与目的数据库在同一区域内创建。为确保 Azure 资源间的安全高效通信，我们将调整必要的权限设置，使虚拟机能够通过 Azure .NET（虚拟网络）与数据库连接。

相比与公共互联网通信，使用 Azure VNET 连接资源不仅提高了通信性能，还增强了安全保障，因此更具优势。

以下是我们准备虚拟机以进行迁移所需采取的具体步骤：

1. 创建虚拟机：我们在与最终数据库同一 Azure 区域内创建虚拟机，确保了数据传输的高效性和速度提升。
2. 安装 PostgreSQL：在虚拟机配置完毕后，我们对其安装了 PostgreSQL，作为存储数据库备份文件和执行恢复的迁移服务器。
3. 权限和网络配置：为构建虚拟机与目的数据库之间的安全连接，我们精心配置了相应的权限和网络设置。通过使用 Azure VNET，我们将通信限制在虚拟网络内部，从而降低了公共互联网的风险，整体提高了安全性。
4. 连接测试：完成 PostgreSQL 的安装和网络设置后，我们执行了连接测试，确认虚拟机能够与源数据库和目的数据库正常通信。这一环节对确保迁移流程的顺利进行是必不可少的，可以有效避免连接相关的问题。

至此，虚拟机已做好作为迁移服务器的准备，我们可以继续推进数据库迁移的下一步。下一阶段将涉及从源数据库提取备份、安全地传输至虚拟机，并在最终数据库中执行数据恢复。

执行迁移

面对 3TB 数据的迁移任务，我按照预定计划精心策划并准备。在启动任何备份或恢复操作之前，我对 postgresql.conf 的参数进行了精细调整，以提高性能并规避一些可能的问题，如自动清理。此外，我还参考了Azure 资源的推荐设置，以确保在使用 pg_dump和 pg_restore时符合最佳实践。

在配置优化完毕后，我编写了备份和恢复脚本，以简化流程并消除手动输入的需求。执行任何命令前，我反复审查脚本以确保无误。

准备工作完成后，我通知了团队和客户，即将启动迁移流程，并依据计划逐步推进。整个迁移期间，我不断监控进展，确保各阶段均按预期进行，并通过定期与团队沟通进展，保持各方的信息同步。

凭借周密的筹备和严格的计划执行，3TB 数据库的迁移得以平稳、高效地完成。通过配置的优化、脚本的自动化以及持续的沟通，我们共同促成了此次迁移的成功。

在迁移较小数据库的过程中，我们通知客户按队列处理即将到来的交易，有效地控制了这些交易，确保数据完整无损。小型数据库迁移结束后，我们整理了所有信息，恢复了交易接收，并在新环境中顺利完成了迁移。

系统在新环境中无缝运行，这得益于所有信息的成功迁移和交易接收的恢复。我们还进行了全面的测试和验证，确保数据完整性和一致的性能。

客户对迁移效果非常满意。通过战略性的迁移管理，我们最大限度地减少了停机时间，避免了对客户操作的重大干扰。

监控与数据完整性

在数据迁移中，数据完整性的保障和进度的实时监控是至关重要的任务。为了实现这一目标，我采用了以下三种监控手段：

1. 网络监控：通过网络监控工具，我全面观察了虚拟机（VM）与数据库服务器之间的数据流动，确保信息能按照计划迁移，并能及时找出可能阻碍迁移的网络问题。
2. PgAdmin 仪表板监控：PgAdmin 的仪表板可以提供关于 pg_restore 流程进展的实时洞见。通过对仪表板的监控，我能够跟踪恢复任务的执行状况，发现任何等待事件，并实时观察数据库的整体健康状态。
3. 数据库元数据查询监控：我还定期执行了多种查询，以检查数据库的元数据，确保成功迁移并核实数据完整性。其中一些关键的查询包括：

• 活动和死亡元组（Live and Dead Tuples）监控：我监测表中的实时和死亡元组数量，以了解空间使用和数据分布情况。这有助于及时发现迁移过程中的数据分布和空间使用问题。

SELECTrelname, n_live_tup, n_dead_tup FROMpg_stat_all_tables WHEREn_live_tup > 0ORn_dead_tup > 0;

-- TO SEARCH FOR A SPECIFIC TABLE

SELECTrelname, n_live_tup, n_dead_tup FROMpg_stat_all_tables WHERErelname = 'table_name';

• 表大小查询：通过定期检查表的大小，我能够跟踪数据迁移进度，确保表数据按预期填充，并及时发现可能暗示迁移问题的任何异常。

SELECTrelname AS"relation",pg_size_pretty (pg_total_relation_size (C .oid)) AS"total_size"FROMpg_class CLEFTJOINpg_namespace N ON(N.oid = C .relnamespace)WHEREnspname NOTIN('pg_catalog','information_schema')ANDC .relkind <> 'i'ANDNSpname !~ '^pg_toast'andrelname = 'table_name'ORDERBYpg_total_relation_size (C .oid) DESC

• 数据库大小查询：此外，我还监控了数据库的整体大小。

SELECTpg_size_pretty(pg_database_size( current_database )) ASdatabase_size;

通过这些精心选择的监控方法和查询执行，我确信迁移过程能够顺利进行，数据完整性得到保障，并能及时发现和解决迁移过程中可能出现的任何问题。

迁移后的性能优化

通过对 postgresql.conf服务器设置进行微调，我调整了 PostgreSQL 配置的参数，从而提升了数据库性能。虽然 Azure 会根据服务器资源自动调整 PostgreSQL 的参数，但我还是通过细致的手动优化，进一步优化了数据库配置。

个人感悟与体验

起初，我觉得执行迁移任务似乎是不可能完成的挑战。尽管有团队中的高级开发人员和软件架构师的支持，我们过去备份数据库的过程也充满了困难。当客户提出此项要求时，由于原团队中的其他人员都已离开，而我是唯一留下的人，我知道这个责任将全部落在我身上。

坦白说，这个任务不仅让我感到担忧，甚至连其他团队成员和我的技术领导也为之胆战心惊。然而，当我想起了一位杰出的内容创作者 midudev 的建议时，我意识到这是一个挑战自我、增长知识、提升工程师素质的绝佳机会。他指出，如果有一个项目被所有人回避，这可能正是你所需的挑战。

因此，我决定用积极的态度迎接这一挑战。虽然我并不是 PostgreSQL 的专家，但我曾有过迁移小型数据库（例如 5 GB 或 20 GB）的经验。但毫无疑问，处理一个庞大的 3 TB 事务数据库将是一次全新的经历。

尽管一开始我感到恐惧，但我还是决定勇敢地领导迁移工作。我欣然发现，这个挑战激发了我最好的潜能，并成功地完成了迁移，甚至找到了优化数据库某些方面的方法。

整个过程让我学到了许多宝贵的知识，特别是加深了对数据库基础运作机制的理解。回首整个经历，这对我的专业成长来说，是一段难忘的旅程。

现在，我对 PostgreSQL 的理解更深入了。未来我可能会考虑使用像 Barman 或 pgcopydb 这样的专业迁移工具来实现增量备份和更高级的迁移任务。通过这些工具，能够增加自动化和可靠性，从而减少手动迁移可能出现的错误。

我写下这篇文章的目的是分享我的迁移经验。我坚信，它可能对那些发现自己处于类似挑战的人有所启发和鼓励，我的旅程和所学经验或许能为他们提供有价值的见解。

结论

总体来说，迁移一个庞大的 3TB 数据库既充满挑战性，又异常复杂。这一过程不仅要求深入掌握数据库基础知识，还涉及一系列精细且反复的实验操作。尽管遭遇了一些难题，但我从中学到了许多宝贵的经验，并对 PostgreSQL 有了更深入的理解。

对迁移过程的精确监控对于确保数据的完整性和性能来说是至关重要的一环。这一经历不仅促进了我的专业成长，还使我成功地优化了数据库。

展望未来，我急切希望探索其他适用于将来项目的迁移工具。我希望通过本文的分享，能鼓舞和帮助那些面临类似挑战的人。总之，这一迁移经历极大地提升了我作为软件工程师的技能，并增强了我迎接未来技术挑战的信心与决心。

你是否也搞过类似的迁移任务？对于这种庞大的迁移任务，你有没有更好的建议或经验？请发表你的看法。