從 MySQL 遷移到 PostgreSQL 完整實戰指南：資料遷移、PHP 程式碼改造、工具鏈

遷移動機：為什麼選 PostgreSQL？

• ACID 合規：完整支援序列化隔離、外鍵、檢查約束、觸發器
• 進階資料類型：JSONB、陣列、UUID、幾何、網路地址、全文檢索、範圍類型
• 擴展生態：PostGIS、TimescaleDB、Citus、pg_partman、pg_stat_statements
• 平行查詢：9.6+ 平行 Seq Scan/Join/Aggregate，大幅加速分析負載
• 邏輯複寫：10+ 發布/訂閱、雙向同步、跨版本升級
• 社群驅動、無廠商鎖定：釋出週期可預期、功能決策透明

第一階段：資料遷移 — 結構與資料轉換

1. 導出 MySQL 結構與資料

# 結構 (含觸發器、程序、檢視表)
mysqldump -u root -p --no-data --routines --triggers --events --single-transaction mydb > mysql_schema.sql

# 資料 (分表導出利於平行匯入)
mysqldump -u root -p --no-create-info --single-transaction --quick --max_allowed_packet=1G mydb > mysql_data.sql

# 或壓縮傳輸
mysqldump -u root -p --single-transaction mydb | gzip > mydb_$(date +%F).sql.gz

2. 結構轉換關鍵對照表

3. 半自動轉換工具鏈

• pgloader：`pgloader mysql://user@host/mydb postgresql://user@host/mydb` — 直接線上遷移、支援類型映射、平行載入
• mysql2pgsql (Python/Ruby 腳本) — 針對特定版本調優
• 手工修正：預存程序、觸發器、動態 SQL、**必須人工重寫** (PL/pgSQL vs MySQL SP 差異極大)

4. 序列值同步 (關鍵！)

MySQL `AUTO_INCREMENT` 最大值 ≠ PG 序列當前值。遷移後必須同步：

-- 對每個有序列的表
SELECT setval(pg_get_serial_sequence('tablename', 'id'), COALESCE(max(id), 1)) FROM tablename;

第二階段：PHP 程式碼改造 — PDO 抽象層平滑過渡

1. 連線字串差異

-- MySQL
$dsn = "mysql:host=localhost;dbname=mydb;charset=utf8mb4";

-- PostgreSQL
$dsn = "pgsql:host=localhost;dbname=mydb;port=5432";

2. 統一資料庫存取層 (推薦模式)

<?php
// Database.php - 統一介面
class Database {
    private static $pdo = null;
    
    public static function getConnection(): PDO {
        if (self::$pdo === null) {
            $driver = getenv('DB_DRIVER') ?: 'pgsql'; // pgsql | mysql
            $host = getenv('DB_HOST');
            $db = getenv('DB_NAME');
            $user = getenv('DB_USER');
            $pass = getenv('DB_PASS');
            $port = getenv('DB_PORT') ?: ($driver === 'pgsql' ? 5432 : 3306);
            
            $dsn = "$driver:host=$host;dbname=$db;port=$port";
            if ($driver === 'mysql') $dsn .= ';charset=utf8mb4';
            
            self::$pdo = new PDO($dsn, $user, $pass, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false, // 真實預處理
            ]);
        }
        return self::$pdo;
    }
    
    // 通用查詢方法
    public static function query(string $sql, array $params = []): array {
        $stmt = self::getConnection()->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }
    
    public static function execute(string $sql, array $params = []): int {
        $stmt = self::getConnection()->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount();
    }
    
    public static function lastInsertId(string $seq = null): string {
        return self::getConnection()->lastInsertId($seq); // PG 需序列名
    }
}

// 使用範例
$users = Database::query("SELECT * FROM users WHERE status = ? AND created_at > ?", ['active', '2024-01-01']);
Database::execute("INSERT INTO logs (msg, level) VALUES (?, ?)", ['User login', 'info']);
$id = Database::lastInsertId('users_id_seq'); // PG 指定序列名

3. SQL 方言差異重寫重點

4. `lastInsertId()` 陷阱與解法

PG `lastInsertId()` 需指定序列名。解法：

• 在 `INSERT` 使用 RETURNING id：INSERT ... VALUES (...) RETURNING id → `$stmt->fetchColumn()`
• 或維護對應表：'users' => 'users_id_seq'

第三階段：管理工具與驗證

• pgAdmin 4 / DBeaver / DataGrip：圖形化管理
• pg_dump / pg_restore：邏輯備份還原 (平行 `-j 4`)
• pg_stat_statements：啟用後查詢 `SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 20;` 找慢查詢
• EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)：深度執行計畫分析