##
一次不成功的MySQL迁移到PostgreSQL案例
1、开篇废话
1.1、迁移方法概述
将数据库从MySQL迁移到PostgreSQL有几种方法，其中选择取决于需求和环境。以下是一些常见的迁移方法：
- 手动迁移：
- 将表结构和数据从MySQL导出为SQL脚本，然后手动修改脚本以适应PostgreSQL语法。
- 执行修改后的脚本以在PostgreSQL中创建表和插入数据。
- 使用工具进行迁移：
- 有一些第三方工具可以帮助自动化迁移过程，例如pgLoader和OpenDBCopy。
- 这些工具可以处理表结构和数据的自动映射，并帮助解决语法和数据类型的差异。
- 使用ETL工具：
- 使用ETL（Extract, Transform, Load）工具，如Talend、Apache NiFi等，可以更灵活地处理数据转换和迁移。
- ETL工具通常提供图形界面，使迁移任务更直观。
- 使用数据库复制：
- 如果您的MySQL和PostgreSQL数据库需要保持同步，可以考虑使用数据库复制工具，如Londiste或Slony-I。
- 使用数据库连接器：
- 一些数据库连接器（如FEDERATED存储引擎或PostgreSQL的postgres_fdw）允许在PostgreSQL中访问MySQL数据，但这通常不是完整的迁移。
在选择迁移方法之前，确保备份数据库以防止意外数据丢失，并在测试环境中进行迁移以验证结果。此外，考虑到MySQL和PostgreSQL之间的语法和功能差异，可能需要手动调整部分迁移过程中的代码。
1.2、迁移方法比较
当选择数据库迁移方法时，需要考虑多个因素，包括数据规模、迁移时间、数据完整性、可用性和复杂性。以下是对不同迁移方法的比较：
- 手动迁移：
- 优势： 完全控制，适用于小型数据库；可以手动处理特定的数据库差异。
- 劣势： 对于大型数据库，手动迁移可能非常耗时和容易出错；容易遗漏某些细节。
- 使用工具进行迁移：
- 优势： 自动化程度高，可以处理表结构和数据的映射；适用于中小型数据库。
- 劣势： 有些工具可能无法处理复杂的数据库结构和特殊语法；可能需要手动调整某些部分。
- 使用ETL工具：
- 优势： 灵活性高，可以处理复杂的数据转换和清理；适用于大型、复杂的数据库。
- 劣势： 学习曲线较陡，配置可能较为复杂；可能需要额外的硬件资源。
- 使用数据库复制：
- 优势： 实时同步数据库，适用于需要保持源和目标数据库同步的情况。
- 劣势： 配置和管理可能较为复杂；可能会引入网络延迟。
- 使用数据库连接器：
- 优势： 允许在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.
#默认4线程，且不配参数的情况下，导入是真的慢。(洋垃圾改改参数可以做到5分钟以内导入)
2.2、pgloader配置
如何安装pgloader这个在网站上有很详细的方法，快速安装的话就yum了。如果我们使用命令行来迁移，实际是不需要这个配置文件，但是配置文件给了很多参数和选项以帮助我们进行迁移。
这里测试的参数如下：
参数的意思很简单，数据从哪里来到哪里去，迁移过程中的一些参数，是否要做什么。
[postgres@pgdb01:/postgresql/backup]$cat my.load
LOAD DATABASE
FROM mysql://root:123456@192.168.5.130:3306/airportdb
INTO postgresql://postgres:postgres@localhost:5432/pgdb
WITH
create indexes, preserve index names, foreign keys,
reset sequences, multiple readers per thread,
workers = 8, concurrency = 2, rows per range = 50000
SET PostgreSQL PARAMETERS
maintenance_work_mem to '1GB'
SET MySQL PARAMETERS
net_read_timeout = '120',
net_write_timeout = '120'
;
这里因为忘记符号;会有大坑，报错信息如下
KABOOM!
ESRAP-PARSE-ERROR: At
SET MySQL PARAMETERS
net_read_timeout = '120',
net_write_timeout = '120'
^ (Line 14, Column 51, Position 417)
2.3、迁移
迁移的过程会发现CPU使用率暴增
检查导入过程中产生的日志
[postgres@pgdb01:/postgresql/backup]$cat mir_m_to_pg.log
2024-01-16T14:45:17.012000Z LOG pgloader version "3.6.7~devel"
2024-01-16T14:45:17.016001Z LOG Data errors in '/tmp/pgloader/'
2024-01-16T14:45:17.016001Z LOG Parsing commands from file #P"/postgresql/backup/my.load"
2024-01-16T14:45:17.160002Z LOG Migrating from #
2024-01-16T14:45:17.160002Z LOG Migrating into #
2024-01-16T14:52:50.802837Z ERROR PostgreSQL Database error 54000: index row requires 10104 bytes, maximum size is 8191
QUERY: CREATE INDEX description_full ON airportdb.airplane_type (identifier, description);
2024-01-16T14:52:51.771846Z ERROR PostgreSQL Database error 42P07: relation "to_idx" already exists
QUERY: CREATE INDEX to_idx ON airportdb.flightschedule ("to");
2024-01-16T14:52:52.005847Z ERROR PostgreSQL Database error 42P07: relation "from_idx" already exists
QUERY: CREATE INDEX from_idx ON airportdb.flightschedule ("from");
2024-01-16T14:52:52.044848Z ERROR PostgreSQL Database error 42P07: relation "airline_idx" already exists
QUERY: CREATE INDEX airline_idx ON airportdb.flightschedule (airline_id);
2024-01-16T14:55:30.373186Z ERROR PostgreSQL Database error 23503: insert or update on table "airline" violates foreign key constraint "airline_ibfk_1"
DETAIL: Key (base_airport)=(813) is not present in table "airport".
QUERY: ALTER TABLE airportdb.airline ADD CONSTRAINT airline_ibfk_1 FOREIGN KEY(base_airport) REFERENCES airportdb.airport(airport_id) ON UPDATE NO ACTION ON DELETE NO ACTION
2024-01-16T14:55:54.025386Z LOG report summary reset
table name errors rows bytes total time
--------------------------- --------- --------- --------- --------------
fetch meta data 0 68 0.178s
Create Schemas 0 0 0.002s
Create SQL Types 0 2 0.014s
Create tables 0 28 0.055s
Set Table OIDs 0 14 0.005s
Check Concurrency Support 0 2 0.305s
--------------------------- --------- --------- --------- --------------
airportdb.booking 0 54304619 1.6 GB 7m32.486s
airportdb.weatherdata 0 4626432 248.1 MB 3m3.809s
airportdb.flight 0 462553 31.8 MB 5.826s
airportdb.flightschedule 0 9881 505.8 kB 4.851s
airportdb.passengerdetails 0 36095 3.6 MB 3.041s
airportdb.airport_geo 0 9854 794.8 kB 0.226s
airportdb.airplane_type 0 342 450.6 kB 0.707s
airportdb.flight_log 0 0 1.968s
airportdb.passenger 0 36095 976.3 kB 1.000s
airportdb.airplane 0 5583 80.3 kB 1.606s
airportdb.airport 0 9854 244.2 kB 0.432s
airportdb.airport_reachable 0 0 0.033s
airportdb.employee 0 1000 173.5 kB 0.213s
airportdb.airline 0 113 3.1 kB 0.474s
--------------------------- --------- --------- --------- --------------
COPY Threads Completion 0 8 7m34.033s
Create Indexes 4 34 10m19.506s
Index Build Completion 0 38 2m38.041s
Reset Sequences 0 9 0.105s
Primary Keys 0 14 0.025s
Create Foreign Keys 1 15 23.653s
Create Triggers 0 0 0.000s
Set Search Path 0 1 0.002s
Install Comments 0 14 0.002s
--------------------------- --------- --------- --------- --------------
Total import time ✓ 59502421 1.9 GB 20m55.368s
[postgres@pgdb01:/postgresql/backup]$
这个日志我觉得是非常不错的，很详细.一个5千万的表导入用了7分32秒，这和我使用的洋垃圾有关系，生产应该是更快的。
错误的地方无奈还是需要手工处理，这里需要注意的是：mysql的数据库迁移到PG之后，并不是一个单独的库，而是一个schema。
3、总结
数据库迁移是一个复杂的任务，选择适当的迁移方法至关重要。尽管迁移工具提供了自动化的便利，但并不是万能的，原因如下：
- 复杂性和特殊性： 数据库结构和语法在MySQL和PostgreSQL之间可能存在差异。一些数据库包含复杂的特殊情况，这可能使得迁移工具难以正确处理。
- 性能和规模： 对于大型数据库，迁移工具可能面临性能瓶颈，导致迁移时间较长。某些工具可能不适用于处理大规模数据的情况。
- 定制需求： 组织可能有特殊的定制需求，例如复杂的数据转换、清理或定制的映射规则。这些需求可能超出迁移工具的能力。
- 学习曲线： 使用某些工具或ETL（Extract, Transform, Load）工具可能需要一定的学习曲线，特别是对于初次使用者。这可能增加迁移过程的复杂性。
- 实时同步需求： 对于需要实时同步的情况，简单的迁移工具可能无法满足要求，而需要考虑使用数据库复制等其他方法。
综上所述，尽管迁移工具在许多情况下提供了便利，但在某些特殊和复杂的情况下，手动迁移或使用更灵活的方法（如ETL工具）可能是更合适的选择。在选择迁移方法时，了解数据库结构和数据的复杂性，以及对于特殊需求的适应性，是确保成功迁移的关键。在任何情况下，测试和备份都是不可或缺的步骤，以确保数据完整性和可用性。
