亚洲视频二区_亚洲欧洲日本天天堂在线观看_日韩一区二区在线观看_中文字幕不卡一区

將數(shù)據(jù)庫從 mysql 遷移到 postgres 是一個具有挑戰(zhàn)性的過程。

雖然 mysql 和 postgres 執(zhí)行類似的工作，但它們之間存在一些根本差異，這些差異可能會產生需要解決才能成功遷移的問題。

從哪兒開始？

pg loader 是一個可以用來將數(shù)據(jù)移動到 postgresql 的工具，但是，它并不完美，但在某些情況下可以很好地工作。值得一看，看看這是否是你想要走的方向。

另一種方法是創(chuàng)建自定義腳本。

自定義腳本提供了更大的靈活性和范圍來解決特定于您的數(shù)據(jù)集的問題。

在本文中，構建了自定義腳本來處理遷移過程。

導出數(shù)據(jù)

數(shù)據(jù)如何導出對于遷移的順利進行至關重要。在默認設置中使用 mysqldump 將導致更困難的過程。

使用 –兼容=ansi 選項以 postgresql 需要的格式導出數(shù)據(jù)。

為了使遷移更容易處理，請將架構和數(shù)據(jù)轉儲分開，以便可以單獨處理它們。每個文件的處理要求都非常不同，為每個文件創(chuàng)建一個腳本將使其更易于管理。

架構差異

數(shù)據(jù)類型

mysql 和 postgresql 中可用的數(shù)據(jù)類型存在差異，這意味著在處理架構時，您需要確定哪些字段數(shù)據(jù)類型最適合您的數(shù)據(jù)。

tinyint 字段類型

tinyint 在 postgresql 中不存在。您可以選擇使用smallint 或boolean 來替換它。選擇與當前數(shù)據(jù)集最相似的數(shù)據(jù)類型。

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

登錄后復制

枚舉字段類型

枚舉字段稍微復雜一些，雖然 postgresql 中存在枚舉，但它們需要創(chuàng)建自定義類型。

為了避免重復自定義類型，最好規(guī)劃出需要哪些枚舉類型，并創(chuàng)建架構所需的最少數(shù)量的自定義類型。自定義類型不是特定于表的，一種自定義類型可以在多個表上使用。

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

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

登錄后復制

類型的創(chuàng)建需要在導入 sql 之前完成。然后可以調整腳本以使用已創(chuàng)建的自定義類型。

如果有多個字段使用 enum(‘blue’,’green’)，這些字段都應該使用相同的 enum 自定義類型。為每個單獨的字段創(chuàng)建自定義類型并不是好的數(shù)據(jù)庫設計。

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/;
}

登錄后復制

索引

索引的創(chuàng)建方式存在差異。索引有兩種變體：有字符限制的索引和無字符限制的索引。這兩個都需要處理并從 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 中的工作方式完全不同。要創(chuàng)建全文索引，索引必須將數(shù)據(jù)轉換為向量。

然后可以對向量進行索引。索引向量時有兩種索引類型可供選擇。 gin 和 gist。兩者都有優(yōu)點和缺點。一般來說，gin 優(yōu)于 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。

導入數(shù)據(jù)時使用 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，并在創(chuàng)建 schema 并插入數(shù)據(jù)后重置內部計數(shù)器。

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");

登錄后復制

值得注意的是遷移中使用的索引命名約定。索引名稱包括表名和字段名。 索引名稱必須是唯一的，不僅在添加索引的表中，而且在整個數(shù)據(jù)庫中，添加表名稱和列名稱可以減少腳本中出現(xiàn)重復的機會。

數(shù)據(jù)處理

遷移數(shù)據(jù)庫的最大障礙是將數(shù)據(jù)轉換為 postgresql 接受的格式。 postgresql 存儲數(shù)據(jù)的方式存在一些差異，需要額外注意。

字符集

本文使用的數(shù)據(jù)集早于utf8mb4，并使用舊的默認latin1，該字符集與postgresql默認字符集utf8不兼容，需要注意的是，postgresql utf8也與mysql的utf8mb4不同。

從 latin1 遷移到 utf8 的問題是數(shù)據(jù)的存儲方式。在 latin1 中每個字符都是一個字節(jié)，而在 utf8 中字符可以是多字節(jié)，最多 4 個字節(jié)。

咖啡館這個詞就是一個例子

在 latin1 中數(shù)據(jù)存儲為 4 個字節(jié)，在 utf8 中存儲為 5 個字節(jié)。在字符集遷移期間，會考慮字節(jié)值，并且可能會導致 utf8 中的數(shù)據(jù)被截斷。 postgresql 將在此截斷時出錯。

為避免截斷，請向受影響的 varchar 字段添加填充。

值得注意的是，如果您更改 mysql 中的字符集，也可能會發(fā)生同樣的截斷問題。

字符轉義

在數(shù)據(jù)庫中看到反斜杠轉義單引號的情況并不少見。

但是，postgresql 默認不支持這一點。相反，使用使用雙單引號的 ansi sql 標準方法。

如果 varchar 字段包含 it’s 則需要更改為 it’s

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

登錄后復制

表鎖定

在 sql 轉儲中，每次插入之前都會有表鎖定調用。

lock tables "address_book" write;

登錄后復制

postgresql 中一般不需要手動鎖定表。

postgresql 使用多版本并發(fā)控制（mvcc）來處理事務。當更新一行時，它會創(chuàng)建一個新版本。一旦舊版本不再使用，它??將被刪除。這意味著通常不需要表鎖定。 postgresql 將與 mvcc 一起使用鎖來提高并發(fā)性。手動設置鎖會對并發(fā)性產生負面影響。

因此，從 sql 轉儲中刪除手動鎖并讓 postgresql 根據(jù)需要處理鎖是更好的選擇。

導入數(shù)據(jù)

遷移過程的下一步是運行腳本生成的 sql 文件。如果前面的步驟正確完成，這部分應該是一個順利的動作。實際發(fā)生的情況是，導入發(fā)現(xiàn)了前面步驟中未發(fā)現(xiàn)的問題，需要返回并調整腳本并重試。

要運行 sql 文件，請使用 psql 登錄 postgres 數(shù)據(jù)庫并運行導入功能

\i /path/to/converted_schema.sql

登錄后復制

需要注意的兩個主要錯誤：

錯誤：對于類型字符變化來說值太長（50）

這可以通過增加前面提到的 varchar 字段字符長度來解決。

錯誤：無效命令 n

此錯誤可能是由雜散轉義單引號或其他不兼容的數(shù)據(jù)值引起的。要修復這些問題，可能需要將正則表達式添加到數(shù)據(jù)處理腳本中以針對特定問題區(qū)域。

其中一些錯誤需要更仔細地查看插入語句以找到問題所在。這在大型 sql 文件中可能具有挑戰(zhàn)性。為了解決這個問題，請將出錯的 insert 語句寫到一個單獨的、更小的 sql 文件中，這樣可以更輕松地研究該文件以找到問題。

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

登錄后復制

數(shù)據(jù)分塊

無論您選擇使用哪種腳本語言進行遷移，分塊數(shù)據(jù)對于大型 sql 文件都非常重要。

對于此腳本，數(shù)據(jù)被分成 1mb 的塊，這有助于保持腳本的效率。您應該選擇對您的數(shù)據(jù)集有意義的塊大小。

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

登錄后復制

驗證數(shù)據(jù)

有幾種驗證數(shù)據(jù)的方法

行數(shù)

進行行計數(shù)是確保至少插入所有行的簡單方法。計算舊數(shù)據(jù)庫中的行數(shù)并將其與新數(shù)據(jù)庫中的行進行比較。

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

登錄后復制

手動數(shù)據(jù)檢查

您還需要通過手動過程驗證數(shù)據(jù)。運行一些有意義的查詢，這些查詢可能會發(fā)現(xiàn)導入問題。

最后的想法

遷移數(shù)據(jù)庫是一項艱巨的任務，但只要仔細規(guī)劃并充分了解您的數(shù)據(jù)集以及兩個數(shù)據(jù)庫系統(tǒng)之間的差異，就可以成功完成。

遷移到新數(shù)據(jù)庫不僅僅是導入，但是可靠的數(shù)據(jù)集遷移將使您在其余的過渡過程中處于有利位置。

為此遷移創(chuàng)建的腳本可以在 git hub 上找到。