从 MySQL 迁移到 PostgreSQL

将数据库从 mysql 迁移到 postgres 是一个具有挑战性的过程。

虽然 mysql 和 postgres 执行类似的工作，但它们之间存在一些根本差异，这些差异可能会产生需要解决才能成功迁移的问题。

从哪儿开始？

pg loader 是一个可以用来将数据移动到 postgresql 的工具，但是，它并不完美，但在某些情况下可以很好地工作。值得一看，看看这是否是你想要走的方向。

另一种方法是创建自定义脚本。

自定义脚本提供了更大的灵活性和范围来解决特定于您的数据集的问题。

在本文中，构建了自定义脚本来处理迁移过程。

导出数据

数据如何导出对于迁移的顺利进行至关重要。在默认设置中使用 mysqldump 将导致更困难的过程。

使用 --兼容=ansi 选项以 postgresql 需要的格式导出数据。

为了使迁移更容易处理，请将架构和数据转储分开，以便可以单独处理它们。每个文件的处理要求都非常不同，为每个文件创建一个脚本将使其更易于管理。

架构差异

数据类型

mysql 和 postgresql 中可用的数据类型存在差异，这意味着在处理架构时，您需要确定哪些字段数据类型最适合您的数据。

tinyint 字段类型

tinyint 在 postgresql 中不存在。您可以选择使用smallint 或boolean 来替换它。选择与当前数据集最相似的数据类型。

 $line =~ s/\btinyint(?:\(\d+\))?\b/smallint/gi;

枚举字段类型

枚举字段稍微复杂一些，虽然 postgresql 中存在枚举，但它们需要创建自定义类型。

为了避免重复自定义类型，最好规划出需要哪些枚举类型，并创建架构所需的最少数量的自定义类型。自定义类型不是特定于表的，一种自定义类型可以在多个表上使用。

create type color_enum as enum ('blue', 'green');

...
"shirt_color" color_enum not null default 'blue',
"pant_color" color_enum not null default 'green',
...

类型的创建需要在导入 sql 之前完成。然后可以调整脚本以使用已创建的自定义类型。

如果有多个字段使用 enum('blue','green')，这些字段都应该使用相同的 enum 自定义类型。为每个单独的字段创建自定义类型并不是好的数据库设计。

if ( $line =~ /"([^"]+)"\s+enum\(([^)]+)\)/ ) {
    my $column_name = $1;
    my $enum_values = $2;
    if ( $enum_values !~ /''/ ) {
        $enum_values .= ",''";
    }

    my @items = $enum_values =~ /'([^']*)'/g;

    my $sorted_enum_values = join( ',', sort @items );

    my $enum_type_name;
    if ( exists $enum_types{$sorted_enum_values} ) {
        $enum_type_name = $enum_types{$sorted_enum_values};
    }
    else {
        $enum_type_name = create_enum_type_name($sorted_enum_values);
        $enum_types{$sorted_enum_values} = $enum_type_name;

        # add create type statement to post-processing
        push @enum_lines,
        "create type $enum_type_name as enum ($enum_values);\n";
    }

    # replace the line with the new enum type
    $line =~ s/enum\([^)]+\)/$enum_type_name/;
}

索引

索引的创建方式存在差异。索引有两种变体：有字符限制的索引和无字符限制的索引。这两个都需要处理并从 sql 中删除，并放入一个单独的 sql 文件中，以便在导入完成后运行 (run_after.sql)。

if ($line =~ /^\s*key\s+/i) {
    if ($line =~ /key\s+"([^"]+)"\s+\("([^"]+)"\)/) {
        my $index_name = $1;
        my $column_name = $2;
        push @post_process_lines, "create index idx_${current_table}_$index_name on \"$current_table\" (\"$column_name\");\n";
    } elsif ($line =~ /key\s+"([^"]+)"\s+\("([^"]+)"\((\d+)\)\)/i) {
        my $index_name = $1;
        my $column_name = $2;
        my $prefix_length = $3;
        push @post_process_lines, "create index idx_${current_table}_$index_name on \"$current_table\" (left(\"$column_name\", $prefix_length));\n";
    }
    next;
}

全文索引在 postgresql 中的工作方式完全不同。要创建全文索引，索引必须将数据转换为向量。

然后可以对向量进行索引。索引向量时有两种索引类型可供选择。 gin 和 gist。两者都有优点和缺点。一般来说，gin 优于 gist。虽然 gin 构建索引的速度较慢，但​​查找速度更快。

if ( $line =~ /^\s*fulltext\s+key\s+"([^"]+)"\s+\("([^"]+)"\)/i ) {
    my $index_name  = $1;
    my $column_name = $2;
    push @post_process_lines,
    "create index idx_fts_${current_table}_$index_name on \"$current_table\" using gin (to_tsvector('english', \"$column_name\"));\n";
    next;
}

自动递增

postgresql 不使用 autoincrment 关键字，而是使用 generated always as identity。

导入数据时使用 generated always as identity 有一个问题。 generated always as identity不是为导入id而设计的，当向表中插入行时，不能指定id字段。 id 值将自动生成。尝试将您自己的 id 插入该行将会产生错误。

要解决此问题，可以将 id 字段设置为 serial 类型，而不是 int generated always as identity。 serial 对于导入来说更加灵活，但不建议将该字段保留为 serial。

使用此方法的另一种方法是将 overriding system value 添加到插入查询中。

insert into table (id, name)
overriding system value
values (100, 'a name');

如果您使用 serial，则需要将一些查询写入 run_after.sql，以将 serial 更改为 generated always as identity，并在创建 schema 并插入数据后重置内部计数器。

if ( $line =~ /^\s*"(\w+)"\s+(int|bigint)\s+not\s+null\s+auto_increment\s*,/i ) {
    my $column_name = $1;
    $line =~ s/^\s*"$column_name"\s+(int|bigint)\s+not\s+null\s+auto_increment\s*,/"$column_name" serial,/;

    push @post_process_lines, "alter table \"$current_table\" alter column \"$column_name\" drop default;\n";

    push @post_process_lines, "drop sequence ${current_table}_${column_name}_seq;\n";

    push @post_process_lines, "alter table \"$current_table\" alter column \"$column_name\" add generated always as identity;\n";

    push @post_process_lines, "select setval('${current_table}_${column_name}_seq', (select coalesce(max(\"$column_name\"), 1) from \"$current_table\"));\n\n";

}

架构结果

从mysql导出后的原始模式

drop table if exists "address_book";
/*!40101 set @saved_cs_client     = @@character_set_client */;
/*!40101 set character_set_client = utf8 */;
create table "address_book" (
  "id" int not null auto_increment,
  "user_id" varchar(50) not null,
  "common_name" varchar(50) not null,
  "display_name" varchar(50) not null,
  primary key ("id"),
  key "user_id" ("user_id")
);

处理的主要 sql 文件

drop table if exists "address_book";
create table "address_book" (
  "id" serial,
  "user_id" varchar(85) not null,
  "common_name" varchar(85) not null,
  "display_name" varchar(85) not null,
  primary key ("id")
);

运行后.sql

alter table "address_book" alter column "id" drop default;
drop sequence address_book_id_seq;
alter table "address_book" alter column "id" add generated always as identity;
select setval('address_book_id_seq', (select coalesce(max("id"), 1) from "address_book"));
create index idx_address_book_user_id on "address_book" ("user_id");

值得注意的是迁移中使用的索引命名约定。索引名称包括表名和字段名。 索引名称必须是唯一的，不仅在添加索引的表中，而且在整个数据库中，添加表名称和列名称可以减少脚本中出现重复的机会。

数据处理

迁移数据库的最大障碍是将数据转换为 postgresql 接受的格式。 postgresql 存储数据的方式存在一些差异，需要额外注意。

字符集

本文使用的数据集早于utf8mb4，并使用旧的默认latin1，该字符集与postgresql默认字符集utf8不兼容，需要注意的是，postgresql utf8也与mysql的utf8mb4不同。

从 latin1 迁移到 utf8 的问题是数据的存储方式。在 latin1 中每个字符都是一个字节，而在 utf8 中字符可以是多字节，最多 4 个字节。

咖啡馆这个词就是一个例子

在 latin1 中数据存储为 4 个字节，在 utf8 中存储为 5 个字节。在字符集迁移期间，会考虑字节值，并且可能会导致 utf8 中的数据被截断。 postgresql 将在此截断时出错。

Beautiful.ai

AI在线创建幻灯片

下载

为避免截断，请向受影响的 varchar 字段添加填充。

值得注意的是，如果您更改 mysql 中的字符集，也可能会发生同样的截断问题。

字符转义

在数据库中看到反斜杠转义单引号的情况并不少见。

但是，postgresql 默认不支持这一点。相反，使用使用双单引号的 ansi sql 标准方法。

如果 varchar 字段包含 it's 则需要更改为 it's

 $line =~ s/\\'/\'\'/g;

表锁定

在 sql 转储中，每次插入之前都会有表锁定调用。

lock tables "address_book" write;

postgresql 中一般不需要手动锁定表。

postgresql 使用多版本并发控制（mvcc）来处理事务。当更新一行时，它会创建一个新版本。一旦旧版本不再使用，它​​将被删除。这意味着通常不需要表锁定。 postgresql 将与 mvcc 一起使用锁来提高并发性。手动设置锁会对并发性产生负面影响。

因此，从 sql 转储中删除手动锁并让 postgresql 根据需要处理锁是更好的选择。

导入数据

迁移过程的下一步是运行脚本生成的 sql 文件。如果前面的步骤正确完成，这部分应该是一个顺利的动作。实际发生的情况是，导入发现了前面步骤中未发现的问题，需要返回并调整脚本并重试。

要运行 sql 文件，请使用 psql 登录 postgres 数据库并运行导入功能

\i /path/to/converted_schema.sql

需要注意的两个主要错误：

错误：对于类型字符变化来说值太长（50）

这可以通过增加前面提到的 varchar 字段字符长度来解决。

错误：无效命令 n

此错误可能是由杂散转义单引号或其他不兼容的数据值引起的。要修复这些问题，可能需要将正则表达式添加到数据处理脚本中以针对特定问题区域。

其中一些错误需要更仔细地查看插入语句以找到问题所在。这在大型 sql 文件中可能具有挑战性。为了解决这个问题，请将出错的 insert 语句写到一个单独的、更小的 sql 文件中，这样可以更轻松地研究该文件以找到问题。

my %lines_to_debug = map { $_ => 1 } (1148, 1195); 
 ...
if (exists $lines_to_debug{$current_line_number}) {
    print $debug_data "$line";  
}

数据分块

无论您选择使用哪种脚本语言进行迁移，分块数据对于大型 sql 文件都非常重要。

对于此脚本，数据被分成 1mb 的块，这有助于保持脚本的效率。您应该选择对您的数据集有意义的块大小。

my $bytes_read = read( $original_data, $chunk, $chunk_size );

验证数据

有几种验证数据的方法

行数

进行行计数是确保至少插入所有行的简单方法。计算旧数据库中的行数并将其与新数据库中的行进行比较。

select count(*) from address_book

校验和

跨列运行校验和可能会有所帮助，但请记住，某些字段，尤其是 varchar 字段，可能已更改为 ansi 标准格式。因此，虽然这适用于某些领域，但它不会在所有领域都准确。

对于mysql

select md5(group_concat(coalesce(user_id, '') order by id)) from address_book

对于 postgresql

SELECT MD5(STRING_AGG(COALESCE(user_id, ''), '' ORDER BY id)) FROM address_book

手动数据检查

您还需要通过手动过程验证数据。运行一些有意义的查询，这些查询可能会发现导入问题。

最后的想法

迁移数据库是一项艰巨的任务，但只要仔细规划并充分了解您的数据集以及两个数据库系统之间的差异，就可以成功完成。

迁移到新数据库不仅仅是导入，但是可靠的数据集迁移将使您在其余的过渡过程中处于有利位置。

为此迁移创建的脚本可以在 git hub 上找到。