一次不成功的MySQL迁移到PostgreSQL案例

数据运维 2024-01-19 捡田螺的小男孩 手机阅读

##

一次不成功的MySQL迁移到PostgreSQL案例

1、开篇废话

1.1、迁移方法概述

将数据库从MySQL迁移到PostgreSQL有几种方法，其中选择取决于需求和环境。以下是一些常见的迁移方法：

  1. 手动迁移：
    • 将表结构和数据从MySQL导出为SQL脚本，然后手动修改脚本以适应PostgreSQL语法。
    • 执行修改后的脚本以在PostgreSQL中创建表和插入数据。
  2. 使用工具进行迁移：
    • 有一些第三方工具可以帮助自动化迁移过程，例如pgLoader和OpenDBCopy。
    • 这些工具可以处理表结构和数据的自动映射，并帮助解决语法和数据类型的差异。
  3. 使用ETL工具：
    • 使用ETL（Extract, Transform, Load）工具，如Talend、Apache NiFi等，可以更灵活地处理数据转换和迁移。
    • ETL工具通常提供图形界面，使迁移任务更直观。
  4. 使用数据库复制：
    • 如果您的MySQL和PostgreSQL数据库需要保持同步，可以考虑使用数据库复制工具，如Londiste或Slony-I。
  5. 使用数据库连接器：
    • 一些数据库连接器（如FEDERATED存储引擎或PostgreSQL的postgres_fdw）允许在PostgreSQL中访问MySQL数据，但这通常不是完整的迁移。

在选择迁移方法之前，确保备份数据库以防止意外数据丢失，并在测试环境中进行迁移以验证结果。此外，考虑到MySQL和PostgreSQL之间的语法和功能差异，可能需要手动调整部分迁移过程中的代码。

1.2、迁移方法比较

当选择数据库迁移方法时，需要考虑多个因素，包括数据规模、迁移时间、数据完整性、可用性和复杂性。以下是对不同迁移方法的比较：

  1. 手动迁移：
    • 优势： 完全控制，适用于小型数据库；可以手动处理特定的数据库差异。
    • 劣势： 对于大型数据库，手动迁移可能非常耗时和容易出错；容易遗漏某些细节。
  2. 使用工具进行迁移：
    • 优势： 自动化程度高，可以处理表结构和数据的映射；适用于中小型数据库。
    • 劣势： 有些工具可能无法处理复杂的数据库结构和特殊语法；可能需要手动调整某些部分。
  3. 使用ETL工具：
    • 优势： 灵活性高，可以处理复杂的数据转换和清理；适用于大型、复杂的数据库。
    • 劣势： 学习曲线较陡，配置可能较为复杂；可能需要额外的硬件资源。
  4. 使用数据库复制：
    • 优势： 实时同步数据库，适用于需要保持源和目标数据库同步的情况。
    • 劣势： 配置和管理可能较为复杂；可能会引入网络延迟。
  5. 使用数据库连接器：
    • 优势： 允许在PostgreSQL中直接访问MySQL数据，无需实际迁移。
    • 劣势： 仅适用于特定场景，可能不支持所有功能；可能会导致性能损失。

在选择方法时，需要综合考虑上述因素，并根据具体情况做出决策。在任何情况下，都建议在迁移之前进行全面的测试，以确保数据的完整性和准确性。此外，建议在生产环境之前备份数据库，以防止任何潜在的问题。

2、pgloader测试案例

2.1、准备测试数据

要在MySQL中造数据，并不是特别方便，像sysbench的数据太单一了，自己写过程又很麻烦。所以我基本都会使用airport-db来进行测试，下面是使用Shell的一个导入过程。

MySQL localhost:3306 ssl JS > util.loadDump('/root/airport-db') Loading DDL and Data from '/root/airport-db' using 4 threads. Opening dump... NOTE: Dump format has version 1.0.2 and was created by an older version of MySQL Shell. If you experience problems loading it, please recreate the dump using the current version of MySQL Shell and try again. Target is MySQL 8.0.35. Dump was produced from MySQL 8.0.26-cloud Scanning metadata - done Checking for pre-existing objects... Executing common preamble SQL Executing DDL - done Executing view DDL - done Starting data load 2 thds loading - 100% (2.03 GB / 2.03 GB), 0.00 B/s, 14 / 14 tables done Recreating indexes - done Executing common postamble SQL 39 chunks (59.50M rows, 2.03 GB) for 14 tables in 1 schemas were loaded in 46 min 54 sec (avg throughput 729.35 KB/s) 0 warnings were reported during the load.