889
從MySQL導出到PostgreSQL
異構數據庫的導入、導出是一個大難題。從MySQL到PG目前沒有特別完善的工具，還需要人工的參與。本文將簡單介紹下如何利用一些工具，手工將MySQL導出數據到PostgreSQL。
導出、轉換、導入
數據導出、導入的思路是：
-
導出結構
-
導出數據
-
對結構進行轉換
-
對數據進行轉換
-
導入結構
-
導入數據
這裏的想法是將結構和數據分開處理，這樣有很多好處。首先DDL的語句的差別相比DML要大一些，比如各種類型的轉換、符號的變化等，分開之後可以針對性的處理；其次，DDL和DML分開更好做批量的處理；其他好處……
當前的開源轉換工具，尚沒有一個完善的。不同的工具分別做了些不同的轉換，因此本文將會利用多個工具互相配合使用，可以達到較好的效果。主要用到的工具有：
-
單個python腳本，依賴較少。對文件內容、格式的轉換，比較好的是enum類型改寫等。
-
單個perl腳本，依賴較少，會對數據類型做不錯的轉換。
-
SED、VIM， 文本編輯神器，你懂得~
導出
導出主要采用的是mysql自帶的mysqldump，導出成sql文件的形式。這裏隻給出直接的例子，更多的參數請參考文檔(mysqldump --help)。
導出結構
/usr/local/mysql/bin/mysqldump --skip-opt --compatible=postgresql --compact --no-data --default-character-set=utf8 --skip-dump-date -u cvusr -p -h mysqlhostaddress cvdb > schm.sql
--no-data，故名思議，就是不帶數據
--compatible=postgresql, 盡量兼容PostgreSQL的語法的方式，至少能減少後麵一部分工作量
--default-character-set=utf8，編碼盡量與目標庫保持一致
導出數據
/usr/local/mysql/bin/mysqldump --skip-opt --compatible=postgresql --compact --no-create-info --default-character-set=utf8 --skip-comments --skip-dump-date -u cvusr -p -h mysqlhost cvdb > data.sql
--no-create-info，去除了結構信息，隻是數據
轉換
數據的導出是比較容易的，麻煩的地方在於將數據文件轉換成另一種數據庫可以采用的方式。在這個過程中，至少需要處理以下幾個問題，這裏有詳細的MySQL與PG的對比。
在結構上
-
類型
如mediumint、char等，其實PG是可以自定義類型，這個應該是細節的處理，現有工具已經能做的不錯。my2pg.pl工具可以完成得較好
-
語義轉換
典型的，在MySQL中可以create table的時候對字段采用enum類型，在PG中就需要抽出來先建個enum數據類型。另外如UK、default value等。 這方麵，mysql-postgresql-converter可以滿足部分需求。
-
其他地方
在數據上：
-
格式
-
'與"
-
默認值
-
其他地方
可以在PG上做如下設置：
backslash_quote = on
escape_string_warning = off
standard_conforming_strings = off
在mysqldump的時候，在建表語句中會有注釋，但發現幾個工具都去不掉，mysqldump的"--skip-comments"選項也沒起作用，百思不得姐之後決定手動處理：
sed -i "s/ COMMENT '.*',/ ,/g" schm.dump
sed -i "s/ COMMENT '.*',/ ,/g" data.dump
自動轉換
經過多次嚐試，發現沒有一個工具能夠將導出的結構處理的比較完善，使得可以直接在PG中導入。開了腦洞後嚐試工具的合作，主要是因為懶，不想手動改：
// 一些數據類型的修改，讀者可以自行作個對比。目前my2pg.pl做得比其他工具要好一些。 （但其將blob類型轉換成了text，需要注意，可以將腳本my2pg.pl中的“s/\w*blob$chareg/text/i; ”改為“s/\w*blob$chareg/bytea/i; ”）
perl my2pg.pl schm.dump > my2pg-schm.dump// enum類型改寫等，這個是上麵的工具沒有處理的，還有一些其他的
python mysql-postgresql-converter/db_converter.py schm.dump > db_cvtr-my2pg-schm.dump
這裏需要注意的是，這兩個工具的順序不能變，變了之後結果就不認識了。如果有興趣，可以嚐試下與其他工具的配合。
這還不是結束！這個時候，並不一定所有的類型都修改完全，可能還有一些需要手工處理，如：
特殊字符的處理：
SHELL$ vim mysql2pgsql-data.dump
:%s /\E'/E\'/g
——這裏是去除一些地方轉換出的問題，在插入語句中，"insert into tbl values(0, E\'0\')"會被處理成了"insert into tbl values(0, \E'0\')"。幸好隻是少數！
日期默認值：
'0000-00-00' 改為CURRENT_DATE，或者某固定日期等
——在MySQL中，日期'0000-00-00'值為空、或者無效，但這個日期不是正確日期，因此在PG中不支持。CURRENT_DATE是指當前時間。
其他的修改，也隻能見招拆招了。
數據導入
在轉換處理完成了之後，剩下的也就簡單了。依次導入結構和數據就好，如果有問題，隻能具體問題具體分析了。
結構導入
~/Workspace/pg94/bin/psql -h pghost -p 3432 -U cvusr -d cvdb -f schm.dump
數據導入
~/Workspace/pg94/bin/psql -h pghost -p 3432 -U cvusr -d cvdb -f data.dump
檢查、驗證
數據導進來了，不代表就結束了。因為不確定中間是否出了什麼幺蛾子。需要做一些驗證：
-
表結構、類型
-
記錄數
-
抽樣記錄
-
存儲過程就算了
-
編碼、亂碼的問題
-
其他差異地方的對比，如blob、clob等
針對這些做過檢查無誤之後，需要應用做相應的全麵的測試。
參考
最後更新：2017-04-01 13:39:23