MYSQL資料庫遷移到POSTGRESQL資料庫（人工遷移）

標籤：資料庫   mysql   遷移   資料   postgresql   

產品即將上線，可專案經理說要換資料庫，要把mysql資料庫的內容全部搬到Postgresql中去。

有一個python的工具可以實現遷移（但是預存程序無法遷移、資料類型也無法靈活對應，還要改程式），為了節約時間採用人工遷移的方式，閑話少說，以下是遷移過程，沒圖，對不起。

1. 利用Navicat for Mysql工具（其它工具也可以，比如mysqldump)將資料庫模式匯出（只導表結構不含資料）方法略，產生的檔案名稱為mysql.sql

2. 利用linux sed工具把匯出的指令碼中的所有COMMENT及mysql的專屬原素替換掉，並產生新檔案table_new.sql，見以下指令碼
   cat mytab.sql  | sed ‘s/^.*ENGINE=.*$/);/g‘ | sed ‘s/COMMENT.*$/,/g‘ | sed ‘s/`//g‘ > table_new.sql 
   

3. 將所有的INDEX語句單獨產生一個檔案（此步也可以省略，後面有其它方法）
   cat table_new.sql | egrep ‘\-|^\s*KEY|^\s*UNIQUE‘ > key.sql
   

4. 將所有的外鍵語句單獨產生一個檔案 （此步可以省略，後面有其它方法）
   cat table_new.sql | egrep ‘\-|^\s*CONSTRAINT‘ > constraint.sql
   

5. 去掉KEY和CONSTRAINT的語句，並產生新檔案 table_last.sql

   cat table_new.sql | sed  ‘s/^\s*KEY.*$//‘ | sed ‘s/^\s*CONSTRAINT.*$//‘ | sed ‘s/^\s*UNIQUE.*$//‘  | sed ‘/^$/d‘> table_last.sql
   

6. 轉換分行符號

   unix2dos table_last.sql
   

7. 把檔案COPY到WINDOWS中，用記事本工具（或其它工具）對一些特殊值進行尋找、替換操作（此步的意思就是把mysql中的類型對應到postgresql中的類型），比如：

   尋找 ID int(12) NOT NULL AUTO_INCREMENT 替換 ID bigserial NOT NULL （自增長類型欄位）

   尋找 datetime 替換 timestamp

   ……

8. 重新整理一些特殊表，因為第5步中去掉了所有KEY和CONSTRAINT的語句，所以如果表欄位中包括KEY開頭或是CONSTRAINT開頭的欄位時（比如欄位 KEYCD，KEYTTL等）要把這些特殊的表的建表語句手工製作一變，並在table_last.sql中替換掉原來的語句。

9. 在Postgresql中執行table_last.sql語句（方法略），如果有錯誤進行微調，直到所有表建立成功。

10. 匯入資料（先在mysql中匯出，匯出後的檔案要替換分行符號不然報錯，然後再匯入到postgresql)

    1. 匯出語句（在MYSQL中執行以下語句，並複製結果後再次執行，便會在伺服器的目錄下產生以每個表為單位的資料檔案,我用的工具是Navicat for Mysql。注意替換table_schema，這裡產生資料檔案的目錄為：/usr/local/mysql/outfile/，也可換成其它目錄）
       

    2. SELECT CONCAT(‘select * from ‘,table_name," into outfile ‘/usr/local/mysql/outfile/",table_name ,‘.dat‘ ,"‘"" fields terminated by ‘|‘ ;")FROM information_schema.tablesWHERE table_schema=‘DEVELOP‘;

    3. 替換分行符號後將產生的檔案COPY到postgresql伺服器的目錄下
       sed -i ‘s/\r//g‘ outfile/*
       

    4. 匯入語句（在postgresql中執行，得到查詢結果後複製查詢結果，並再次執行,注意一定要用有superuser許可權的使用者執行，這裡用的是pgadmin工具，目錄是/tmp/data,注意替換table_catalog中的值，我的資料名為DEVELOP）

       
       

    5. select ‘copy necsl.‘||table_name|| ‘ from ‘ || chr(39)||‘/tmp/data/‘||upper(table_name)||‘.dat‘ || chr(39) ||‘ with DELIMITER ‘ || chr(39) || ‘|‘ || chr(39) ||‘;‘ from information_schema.tableswhere table_schema=‘necsl‘ andtable_catalog=‘DEVELOP‘ ;

11. 資料匯入後可以用以下方法驗證是否匯入正確

    
    

12. --在mysql及PG中分別建立下表create table table_count(tblname varchar(100) primary key not null,tblrecorder integer);

13. /*在mysql中執行以下語句，並將結果複製後再次執行，將會產生在表table_count中產生每張表有多少記錄*/SELECT CONCAT(‘insert into table_count select ‘ ,"‘",table_name,"‘",‘ ,count(*) from ‘,table_name ,";")FROM information_schema.tablesWHERE table_schema=‘DEVELOP‘;--在postgresql中執行以下語句，並將結果複製後再次執行，將會產生在表table_count中產生每張表有多少記錄,然後與mysql中table_count的表比較即可select ‘insert into necsl.table_count select ‘|| quote_literal(table_name) ||‘,count(*) from   necsl.‘||table_name|| ‘;‘  from information_schema.tableswhere table_schema=‘necsl‘ andtable_catalog=‘DEVELOP‘ ;

14. --如果匯入有問題，可以在PG中執行以下句語產生清空所有表的語句，將執行結果複製再執行即可清空所有表PG truncateselect ‘truncate table  necsl.‘||table_name|| ‘;‘  from information_schema.tableswhere table_schema=‘necsl‘ andtable_catalog=‘DEVELOP‘ ;

15. /*產生索引資訊及外鍵，在mysql中分別執行以下語句，並將結果複製到POSTGRESQL中執行*//*產生唯一索引*/select    CONCAT (     ‘CREATE UNIQUE INDEX ‘ ,                table_name,‘_IDX_‘,index_name,     ‘ ON ‘,      table_name,     ‘(‘,      GROUP_CONCAT(column_name order by seq_in_index),      ‘);‘)from statisticswhere table_schema=‘DEVELOP‘AND INDEX_NAME <>‘PRIMARY‘AND INDEX_SCHEMA=‘DEVELOP‘AND NON_UNIQUE=0GROUP BY index_name,table_nameORDER BY TABLE_NAME,INDEX_NAME,seq_in_index asc/*產生btree索引*/select    CONCAT (     ‘CREATE INDEX ‘ ,                table_name,‘_IDX_‘,index_name,     ‘ ON ‘,      table_name,     ‘(‘,      GROUP_CONCAT(column_name order by seq_in_index),      ‘);‘)from statisticswhere table_schema=‘DEVELOP‘AND INDEX_NAME <>‘PRIMARY‘AND INDEX_SCHEMA=‘DEVELOP‘AND NON_UNIQUE=1GROUP BY index_name,table_nameORDER BY TABLE_NAME,INDEX_NAME,seq_in_index asc/*產生外鍵 */selectconcat(‘alter table ‘,c.TABLE_NAME,‘ add constraint ‘,c.CONSTRAINT_NAME,‘ foreign key(‘,c.COLUMN_NAME,‘) references ‘,c.REFERENCED_TABLE_NAME,‘( ‘,c.REFERENCED_COLUMN_NAME,‘);‘)from TABLE_CONSTRAINTS t,KEY_COLUMN_USAGE cwhere t.CONSTRAINT_SCHEMA=‘DEVELOP‘AND t.CONSTRAINT_TYPE=‘FOREIGN KEY‘AND t.TABLE_SCHEMA=‘DEVELOP‘AND c.REFERENCED_TABLE_SCHEMA=‘DEVELOP‘AND t.CONSTRAINT_NAME =c.CONSTRAINT_NAMEand t.table_name=c.table_name;

17.產生以上資訊後，在PG中建立以下函數，用於重設所有的sequence（如果沒有自增長類型可忽略此步），建立放在public模式下

CREATE OR REPLACE FUNCTION pro_resetallseq()  RETURNS void AS$BODY$ DECLARE           tmp VARCHAR(512);           maxval bigint;           stmt  record; BEGIN       FOR stmt IN   select sequence_name,REPLACE(sequence_name,‘_id_seq‘,‘‘) as tnm from information_schema.sequences where sequence_catalog=‘DEVELOP‘ and sequence_schema=‘necsl‘  LOOP                    tmp := ‘SELECT MAX(ID) FROM ‘|| quote_ident(stmt.tnm) || ‘;‘;  EXECUTE tmp into maxval;                  if not (maxval  is NULL or maxval = 0) then                                         EXECUTE ‘SELECT SETVAL(‘|| quote_literal(stmt.sequence_name) || ‘,‘ || maxval || ‘);‘;                  end if;                                           END LOOP;       RAISE NOTICE ‘finished .....‘;       END;$BODY$  LANGUAGE plpgsql

18.建立成功後可以用以下語句重設所有的sequence

select pro_resetallseq();

19.關於mysql中的函數（預存程序）因為文法差別較大，只能在PG中重寫。我沒有找到太好的方法。

因為時間緊，所以沒有寫工具，過程也比較粗糙，僅供參考，如果有好的建議歡迎留言，謝謝閱讀。

本文出自 “鏡子” 部落格，請務必保留此出處http://383133430.blog.51cto.com/454215/1686169

MYSQL資料庫遷移到POSTGRESQL資料庫（人工遷移）