從 MySQL 轉移到 PostgreSQL

底下列出我把資料從 MySQL 轉移到 PostgreSQL 的步驟。

1. 把資料庫從 MySQL 中備份出來
mysqldump -a $databasename > dump.txt

如果你有設定帳號密碼的話:
mysqldump -u $username -p $password -a $databasename > dump.txt

若有好幾個 database 就重複上面的步驟

2. 修改 dump.txt

  1. create table 語法先剪下來貼到另一個新檔案 schema.txt 上,把語法和資料分開處理。
  2. 把所有的 ‘\\’ 改成 ‘\’,如果遇到 ‘許\’ 這種情況時,’\’ 也要去掉,這裡可以用文字編編器一次全部取代,會比較方便。
  3. '0000-00-00''0000-00-00 00:00:00' 之類的值改成 null,也就是 date, time, timestamp 若有不合法的值時,就改成 null
  4. 注意一下 reference 關連性的問題,PostgreSQL 對它的檢查比 MySQL 嚴格。
  5. 修改 schema.txt 中的 SQL 語法,使其符合 PostgreSQL 的規格,這裡不熟的就要查文件了。
  6. schema.txtdump.txt 都拷貝到 /usr/local/pgsql/data/

3. 初始化 PostgreSQL
先啟動 PostgreSQL,若已啟動先跳過這步(在 postgres 權限下)
postmaster -i -S -D $dataPath

通常 $dataPath/usr/local/pgsql/data 你可以自己更改。接著初始化新的資料庫路徑,這是當你第一次啟動 PostgreSQL 所需要執行的步驟
initdb --pgdata=$dataPath --encoding=UNICODE

4. 匯入語法
如果之前曾經匯入失敗的話,可以先 drop 資料庫後再重建(如果 $databasename 存在的話)
dropdb $databasename

接著要新增資料庫
createdb -U $username -E UNICODE $databasename

把語法先匯進去
psql -f /usr/local/pgsql/data/schema.txt -U $username

注意這裡的 $username 指的是 PostgreSQL 中所設定的 user,不是 linux 上的 user。

5. 匯入資料內容
先以 $username 身份進入 PostgreSQL
psql -U $username $databasename

先轉換 PostgreSQL 的前端編碼,如果你的資料都是中文的話,可以用 big5
> \encoding big5

如果你的檔案可以存成 unicode 編碼的話,那麼把 big5 也可改成 UNICODE。接著開始匯入
> \i /usr/local/pgsql/data/dump.txt

然後就可以開始跑了,如果中間有任何錯誤訊息出現的話,可以 Ctrl-C/Ctrl-D 停止,記下行號之後再重新上一行 \i 的匯入。如果遇到 utf-8 之類的轉換問題的話可以不用理它,因為有幾個中文字會轉不成功,像是墻、碁等等。如果有奇奇怪怪的問題的話,最好跳出來,查看修改 dump.txt 一下,然後再到第四步去重新開始。

附註

底下是 MySQL dump 出來的語法:

CREATE TABLE member (
  MemberIndex int(10) NOT NULL auto_increment,
  Name varchar(10) NOT NULL default '''',
  IdColor int(11) NOT NULL default ''1'',
  Birthdate date default NULL,
  PRIMARY KEY  (MemberIndex),
) TYPE=MyISAM;

底下是 PostgreSQL 語法:

CREATE TABLE customer (
  CID varchar(13) PRIMARY KEY default '''',
  Name varchar(25) NOT NULL default '''',
  JoinTime date default NULL,
  RecNode int2 NOT NULL default 0,
  ParentNode int NOT NULL default 0,
);

Leave a Comment

Your email address will not be published. Required fields are marked *

sixteen − nine =

Scroll to Top