日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

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

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

從哪兒開(kāi)始？

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

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

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

在本文中，構(gòu)建了自定義腳本來(lái)處理遷移過(guò)程。

導(dǎo)出數(shù)據(jù)

數(shù)據(jù)如何導(dǎo)出對(duì)于遷移的順利進(jìn)行至關(guān)重要。在默認(rèn)設(shè)置中使用 mysqldump 將導(dǎo)致更困難的過(guò)程。

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

為了使遷移更容易處理，請(qǐng)將架構(gòu)和數(shù)據(jù)轉(zhuǎn)儲(chǔ)分開(kāi)，以便可以單獨(dú)處理它們。每個(gè)文件的處理要求都非常不同，為每個(gè)文件創(chuàng)建一個(gè)腳本將使其更易于管理。

架構(gòu)差異

數(shù)據(jù)類(lèi)型

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

tinyint 字段類(lèi)型

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

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

登錄后復(fù)制

枚舉字段類(lèi)型

枚舉字段稍微復(fù)雜一些，雖然 postgresql 中存在枚舉，但它們需要?jiǎng)?chuàng)建自定義類(lèi)型。

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

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

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

登錄后復(fù)制

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

如果有多個(gè)字段使用 enum(‘blue’,’green’)，這些字段都應(yīng)該使用相同的 enum 自定義類(lèi)型。為每個(gè)單獨(dú)的字段創(chuàng)建自定義類(lèi)型并不是好的數(shù)據(jù)庫(kù)設(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/;
}

登錄后復(fù)制

索引

索引的創(chuàng)建方式存在差異。索引有兩種變體：有字符限制的索引和無(wú)字符限制的索引。這兩個(gè)都需要處理并從 sql 中刪除，并放入一個(gè)單獨(dú)的 sql 文件中，以便在導(dǎo)入完成后運(yùn)行 (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;
}

登錄后復(fù)制

全文索引在 postgresql 中的工作方式完全不同。要?jiǎng)?chuàng)建全文索引，索引必須將數(shù)據(jù)轉(zhuǎn)換為向量。

然后可以對(duì)向量進(jìn)行索引。索引向量時(shí)有兩種索引類(lèi)型可供選擇。 gin 和 gist。兩者都有優(yōu)點(diǎn)和缺點(diǎn)。一般來(lái)說(shuō)，gin 優(yōu)于 gist。雖然 gin 構(gòu)建索引的速度較慢，但??查找速度更快。

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

登錄后復(fù)制

自動(dòng)遞增

postgresql 不使用 autoincrment 關(guān)鍵字，而是使用 generated always as identity。

導(dǎo)入數(shù)據(jù)時(shí)使用 generated always as identity 有一個(gè)問(wèn)題。 generated always as identity不是為導(dǎo)入id而設(shè)計(jì)的，當(dāng)向表中插入行時(shí)，不能指定id字段。 id 值將自動(dòng)生成。嘗試將您自己的 id 插入該行將會(huì)產(chǎn)生錯(cuò)誤。

要解決此問(wèn)題，可以將 id 字段設(shè)置為 serial 類(lèi)型，而不是 int generated always as identity。 serial 對(duì)于導(dǎo)入來(lái)說(shuō)更加靈活，但不建議將該字段保留為 serial。

使用此方法的另一種方法是將 overriding system value 添加到插入查詢(xún)中。

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

登錄后復(fù)制

如果您使用 serial，則需要將一些查詢(xún)寫(xiě)入 run_after.sql，以將 serial 更改為 generated always as identity，并在創(chuàng)建 schema 并插入數(shù)據(jù)后重置內(nèi)部計(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";

}

登錄后復(fù)制

架構(gòu)結(jié)果

從mysql導(dǎo)出后的原始模式

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

登錄后復(fù)制

處理的主要 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")
);

登錄后復(fù)制

運(yùn)行后.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");

登錄后復(fù)制

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

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

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

字符集

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

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

咖啡館這個(gè)詞就是一個(gè)例子

在 latin1 中數(shù)據(jù)存儲(chǔ)為 4 個(gè)字節(jié)，在 utf8 中存儲(chǔ)為 5 個(gè)字節(jié)。在字符集遷移期間，會(huì)考慮字節(jié)值，并且可能會(huì)導(dǎo)致 utf8 中的數(shù)據(jù)被截?cái)唷?postgresql 將在此截?cái)鄷r(shí)出錯(cuò)。

為避免截?cái)啵?qǐng)向受影響的 varchar 字段添加填充。

值得注意的是，如果您更改 mysql 中的字符集，也可能會(huì)發(fā)生同樣的截?cái)鄦?wèn)題。

字符轉(zhuǎn)義

在數(shù)據(jù)庫(kù)中看到反斜杠轉(zhuǎn)義單引號(hào)的情況并不少見(jiàn)。

但是，postgresql 默認(rèn)不支持這一點(diǎn)。相反，使用使用雙單引號(hào)的 ansi sql 標(biāo)準(zhǔn)方法。

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

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

登錄后復(fù)制

表鎖定

在 sql 轉(zhuǎn)儲(chǔ)中，每次插入之前都會(huì)有表鎖定調(diào)用。

lock tables "address_book" write;

登錄后復(fù)制

postgresql 中一般不需要手動(dòng)鎖定表。

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

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

導(dǎo)入數(shù)據(jù)

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

要運(yùn)行 sql 文件，請(qǐng)使用 psql 登錄 postgres 數(shù)據(jù)庫(kù)并運(yùn)行導(dǎo)入功能

\i /path/to/converted_schema.sql

登錄后復(fù)制

需要注意的兩個(gè)主要錯(cuò)誤：

錯(cuò)誤：對(duì)于類(lèi)型字符變化來(lái)說(shuō)值太長(zhǎng)（50）

這可以通過(guò)增加前面提到的 varchar 字段字符長(zhǎng)度來(lái)解決。

錯(cuò)誤：無(wú)效命令 n

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

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

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

登錄后復(fù)制

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

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

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

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

登錄后復(fù)制

驗(yàn)證數(shù)據(jù)

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

行數(shù)

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

select count(*) from address_book

登錄后復(fù)制

校驗(yàn)和

跨列運(yùn)行校驗(yàn)和可能會(huì)有所幫助，但請(qǐng)記住，某些字段，尤其是 varchar 字段，可能已更改為 ansi 標(biāo)準(zhǔn)格式。因此，雖然這適用于某些領(lǐng)域，但它不會(huì)在所有領(lǐng)域都準(zhǔn)確。

對(duì)于mysql

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

登錄后復(fù)制

對(duì)于 postgresql

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

登錄后復(fù)制

手動(dòng)數(shù)據(jù)檢查

您還需要通過(guò)手動(dòng)過(guò)程驗(yàn)證數(shù)據(jù)。運(yùn)行一些有意義的查詢(xún)，這些查詢(xún)可能會(huì)發(fā)現(xiàn)導(dǎo)入問(wèn)題。

最后的想法

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

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

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