將網站從 MySQL 遷移至 PostgreSQL 完整實戰記錄

遷移動機

原架構：PHP + MySQL。2001 年夏因主機商機房故障更換供應商，新商強力推薦 PostgreSQL，強調其穩定性優於 MySQL。遷移過程中 MySQL 多次導入大量資料崩潰、資料損壞，最終決定全面轉向 PostgreSQL。

第一階段：資料遷移

1. 匯出 MySQL 資料

mysqldump -u username -p databasename > sqldump.txt

2. 轉換 CREATE TABLE 語法

將 dump 中的 CREATE TABLE 獨立出來，按 PostgreSQL 語法重寫：

MySQL 原語法

CREATE TABLE practicetable (
    someID INT AUTO_INCREMENT PRIMARY KEY,
    time TIMESTAMP DEFAULT NOW(),
    name VARCHAR(50),
    address VARCHAR(50),
    state VARCHAR(2),
    country VARCHAR(3) DEFAULT 'USA',
    postlcode VARCHAR(15),
    age SMALLINT,
    lattitude REAL,
    longitude REAL,
    somebool BOOLEAN,
    message TEXT
);

PostgreSQL 目標語法

CREATE TABLE practicetable (
    someID SERIAL PRIMARY KEY,
    time TIMESTAMP DEFAULT NOW(),
    name VARCHAR(50),
    address VARCHAR(50),
    state VARCHAR(2),
    country VARCHAR(3) DEFAULT 'USA',
    postlcode VARCHAR(15),
    age SMALLINT,
    lattitude REAL,
    longitude REAL,
    somebool BOOLEAN,
    message TEXT
);

關鍵差異對照

3. 匯入資料

使用 COPY 指令或 psql 匯入純資料 SQL（INSERT 語句）。

第二階段：PHP 代碼適配

資料庫抽象層修改

• 連線函數：mysql_connect → pg_connect
• 查詢執行：mysql_query → pg_query
• 結果集處理：mysql_fetch_array → pg_fetch_array
• 錯誤處理：mysql_error → pg_last_error

SQL 語法調整重點

1. LIMIT 子句：MySQL LIMIT 10 OFFSET 20 → PG LIMIT 10 OFFSET 20（9.0+ 相容）
2. 字串連接：MySQL CONCAT(a,b) / a || b → PG 僅支援 a || b
3. 日期運算：MySQL DATE_ADD() → PG INTERVAL 語法
4. 自增值獲取：MySQL LAST_INSERT_ID() → PG currval('seq_name') 或 RETURNING id
5. 保留字處理：PG 保留字更多，識別符建議雙引號 "user"

遷移後收益

• 資料庫穩定性大幅提升，不再頻繁崩潰
• 複雜查詢效能優於 MySQL 同期版本
• 獲得完整事務支援、外鍵、檢查約束等進階功能