MySQLで運用しているシステムをPostgreSQLへ移行したい場合、

「mysqldumpで取得したバックアップをPostgreSQLへそのままリストアできないか？」

と考えることがあります。

結論から言うと、

MySQLからPostgreSQLへのデータベース移行は可能です。

ただし、

mysqldump
 ↓
PostgreSQLへそのままpsqlで投入

という方法は基本的に使えません。

MySQLとPostgreSQLではSQL方言やデータ型、AUTO_INCREMENT、真偽値、日時、インデックスなどの仕様が異なるためです。

そこで実際の移行では、

MySQL
 ↓
pgloader
 ↓
型・DDLを変換
 ↓
PostgreSQL

という方法が便利です。

この記事では、

MySQL 8.0系 → PostgreSQL 16系

を例として、バックアップ取得から移行、検証、PostgreSQL側の再バックアップまで詳しく説明します。

最初に結論：移行は可能。ただし「バックアップファイルの直接リストア」ではない

MySQLの代表的な論理バックアップは、

mysqldump

です。

例えば、

mysqldump -u root -p mydb > mydb.sql

とすると、

mydb.sql

というSQL形式のバックアップファイルができます。

MySQL公式ドキュメントでも、mysqldumpはデータベースオブジェクトとデータを再現するSQL文を出力する「論理バックアップ」の仕組みとして説明されています。

しかし、このファイルを、

psql mydb < mydb.sql

としてPostgreSQLへ直接ロードすることはできません。

理由はMySQL固有のSQLが含まれるからです。

例えばMySQLでは、

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    enabled TINYINT(1)
) ENGINE=InnoDB;

のような定義があります。

PostgreSQLでは、

AUTO_INCREMENT
ENGINE=InnoDB
TINYINT(1)

などをそのまま扱えません。

つまり必要なのは、

リストアではなくマイグレーション

です。

この記事で使用するバージョン

具体例として次の構成を使用します。

移行元：
MySQL 8.0系

移行先：
PostgreSQL 16系

変換ツール：
pgloader 3.6.10

具体的な構築例としては、

MySQL 8.0.36～8.0.46クラス
        ↓
pgloader 3.6.10
        ↓
PostgreSQL 16.x

という構成を想定します。

MySQL 8.0の現行公式ドキュメントは8.0.46までを対象としており、mysqldumpによる論理バックアップを正式にサポートしています。

pgloader 3.6.10の現行ドキュメントでも、MySQLからPostgreSQLへの移行機能が用意されています。

ただし注意があります。

「MySQL 8.0とPostgreSQL 16なら、どんなデータベースでも100%自動移行できる」という意味ではありません。

移行可否は、

テーブル構造
データ型
ビュー
トリガー
ストアドプロシージャ
MySQL固有関数
アプリケーションSQL

によって変わります。

一般的な、

テーブル
主キー
外部キー
インデックス
VARCHAR
INT
BIGINT
DATE
DATETIME
TEXT

を中心としたシステムであれば移行しやすい、という意味での推奨例です。

pgloaderとは

pgloaderは、異なるデータベースからPostgreSQLへデータを移行するためのツールです。

MySQLからPostgreSQLへの移行では、

MySQLのテーブル構造を解析
 ↓
PostgreSQL用DDLへ変換
 ↓
テーブル作成
 ↓
データ転送
 ↓
インデックス作成
 ↓
主キー作成
 ↓
外部キー作成
 ↓
シーケンス調整

などを自動的に行ってくれます。

単純なデータコピーではなく、MySQLとPostgreSQLのデータ型の違いも吸収してくれるのが大きなメリットです。

pgloaderが自動変換してくれる代表例

例えばMySQLの、

id INT AUTO_INCREMENT

はPostgreSQL側でシーケンスを利用できる形へ変換されます。

またMySQLの型についても、pgloaderには標準のキャストルールがあります。

イメージとしては、

MySQL                     PostgreSQL

INT AUTO_INCREMENT   →    serial系
BIGINT AUTO_INCREMENT →   bigserial系

INT                  →    integer / bigint

VARCHAR              →    varchar/text

DATETIME             →    timestamp

TINYINT(1)           →    boolean相当として扱う構成も可能

となります。

実際の型変換は元テーブルの定義によって異なるため、移行後には必ず確認します。

重要：pgloaderで自動移行できないものもある

ここは非常に重要です。

pgloaderの現行ドキュメントでは、MySQL移行について、

ビューは移行されません。

また、

トリガーも移行されません。

そのため、

テーブル
データ
インデックス
主キー
外部キー

はかなり自動化できますが、

VIEW

TRIGGER

Stored Procedure

Stored Function

などは別途確認・変換する必要があります。

MySQL固有SQLを多用しているシステムほど、移行作業は増えます。

バックアップファイルしかない場合はどうする？

ここが今回のポイントです。

例えば手元に、

mysql_backup.sql

しかないとします。

pgloaderは基本的には、

MySQLサーバー
 ↓
PostgreSQLサーバー

という形でデータベースへ接続して移行する方法が分かりやすいです。

そのため、

mysqldumpファイル
 ↓
一時MySQLへ復元
 ↓
pgloader
 ↓
PostgreSQL

という流れにします。

全体構成

今回の移行は次の流れです。

既存MySQL 8.0
      ↓
 mysqldump
      ↓
mysql_backup.sql
      ↓
一時MySQL 8.0
      ↓
  pgloader
      ↓
PostgreSQL 16
      ↓
データ確認
      ↓
pg_dump
      ↓
PostgreSQL用バックアップ完成

この方法なら、元のMySQLサーバーへ直接pgloaderを接続できない場合でも移行できます。

STEP 1：MySQLのバージョンを確認する

まずMySQL側を確認します。

mysql --version

またはMySQLへログインして、

SELECT VERSION();

を実行します。

例えば、

8.0.36

などと表示されればMySQL 8.0系です。

STEP 2：MySQLの文字コードを確認

特に日本語データを扱っている場合は重要です。

SHOW VARIABLES LIKE 'character_set%';

確認したいのは主に、

character_set_database

character_set_server

などです。

現在のシステムなら、

utf8mb4

になっているケースが多いでしょう。

STEP 3：MySQLをmysqldumpでバックアップ

例としてデータベース名を、

appdb

とします。

バックアップします。

mysqldump \
  -u root \
  -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --default-character-set=utf8mb4 \
  appdb > appdb_mysql.sql

ここでは、

appdb_mysql.sql

というファイルを作成します。

--single-transaction は、主にInnoDBでオンラインの整合性を保ちながら論理バックアップを取得する場合に有用です。

なぜroutinesやtriggersもdumpするのか

pgloaderではトリガーやストアドプログラムをそのままPostgreSQLへ移行できません。

それでも、

--routines
--triggers
--events

を含めてMySQL側のバックアップを取得しておくことをおすすめします。

理由は、

後からPostgreSQL用に書き換える際の資料になるから

です。

つまり、

移行用
→ pgloader

移行元の完全な論理定義保存
→ mysqldump

という位置付けです。

STEP 4：バックアップがMySQLへ戻せるか確認する

PostgreSQLへの移行を始める前に、

そもそもMySQLバックアップが正常か

確認します。

これを省略すると、

移行失敗

原因：
PostgreSQLではなく元のバックアップが壊れていた

ということが起こり得ます。

テスト用MySQL 8.0を用意します。

例えば、

CREATE DATABASE appdb_test
CHARACTER SET utf8mb4;

そして、

mysql -u root -p appdb_test < appdb_mysql.sql

で復元します。

ここでエラーが出ないことを確認します。

STEP 5：件数を記録しておく

移行前にテーブルごとの件数を記録しておくことをおすすめします。

例えば、

SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM products;

結果が、

users       15234
orders      483921
products    3258

だったとします。

これは移行後の比較に使います。

STEP 6：PostgreSQL 16を準備

PostgreSQLをインストールします。

確認します。

psql --version

例えば、

psql (PostgreSQL) 16.x

となればOKです。

STEP 7：PostgreSQLにユーザーを作成

例えば、

sudo -u postgres psql

で接続します。

ユーザーを作成します。

CREATE ROLE appuser
WITH LOGIN PASSWORD '安全なパスワード';

データベースを作成します。

CREATE DATABASE appdb
OWNER appuser
ENCODING 'UTF8';

終了します。

\q

STEP 8：接続確認

pgloaderを実行する前に、普通のクライアントから接続できることを確認します。

MySQL：

mysql \
  -h mysql-server \
  -u migration_user \
  -p \
  appdb

PostgreSQL：

psql \
  -h postgres-server \
  -U appuser \
  -d appdb

両方につながることを確認します。

STEP 9：pgloaderをインストール

Ubuntu/Debian系でパッケージが利用できる環境なら、

sudo apt update
sudo apt install pgloader

などで導入できます。

確認します。

pgloader --version

ただし、OS標準リポジトリに含まれるpgloaderは古い場合があります。

MySQL 8.0を扱う場合は、特に認証方式との相性があるため、できるだけ現行版を使用します。

この記事では、

pgloader 3.6.10

を前提にしています。

STEP 10：MySQL 8.0の認証方式に注意

MySQL 8.0では認証方式が原因でpgloaderから接続できないケースがあります。

過去のpgloaderでは、

caching_sha2_password

関連で接続できない問題がありました。

現行pgloaderのドキュメントでは、JDBC形式のURLを使い、

allowPublicKeyRetrieval=true

などMySQL Connector/Jの接続パラメーターを利用する方法も案内されています。

例えば、

jdbc:mysql://user:password@mysql-server:3306/appdb?useSSL=false&allowPublicKeyRetrieval=true

という形式です。

認証エラーが発生した場合は、ここを疑います。

STEP 11：まず単純なpgloaderを試す

最も簡単なのは、

pgloader \
  mysql://migration_user:password@mysql-server/appdb \
  postgresql://appuser:password@postgres-server/appdb

です。

これだけで、

MySQLスキーマ解析
 ↓
PostgreSQLテーブル作成
 ↓
データロード
 ↓
インデックス作成
 ↓
外部キー設定
 ↓
シーケンス調整

が行われます。

STEP 12：本番ではloadファイルを作るのがおすすめ

実運用ではコマンドラインに全部書くより、

migration.load

という設定ファイルを作った方が管理しやすくなります。

例えば、

LOAD DATABASE
     FROM mysql://migration_user:password@mysql-server/appdb
     INTO postgresql://appuser:password@postgres-server/appdb

 WITH include drop,
      create tables,
      create indexes,
      reset sequences,
      foreign keys,
      downcase identifiers;

そして、

pgloader migration.load

を実行します。

これなら、テスト移行を何度でも再実行できます。

STEP 13：移行ログを確認する

pgloaderの終了時には、

table name
read
imported
errors
time

などの情報が表示されます。

重要なのは、

Errors

です。

エラーが0なのか確認します。

ただし、

pgloaderが正常終了した＝移行完了

と判断してはいけません。

ここから検証が必要です。

STEP 14：PostgreSQL側のテーブル一覧を確認

PostgreSQLへ接続します。

psql -U appuser -d appdb

テーブル一覧：

\dt

例えば、

users
orders
products
order_items

など、必要なテーブルが作成されているか確認します。

STEP 15：レコード件数を比較する

MySQL側：

SELECT COUNT(*) FROM users;

PostgreSQL側：

SELECT COUNT(*) FROM users;

例えば、

MySQL

users
15234

に対して、

PostgreSQL

users
15234

となっていることを確認します。

主要テーブルだけではなく、本番移行では全テーブルを比較する方が安全です。

件数一致だけでは不十分

例えば、

100万件

が、

100万件

だったとしても、中身が完全に同じとは限りません。

そのため重要なテーブルでは、

MIN/MAX
SUM
NULL件数
日付範囲
ID範囲

なども比較します。

例えば、

SELECT
    COUNT(*),
    MIN(id),
    MAX(id)
FROM orders;

売上データなら、

SELECT
    COUNT(*),
    SUM(amount)
FROM orders;

といった確認も有効です。

STEP 16：AUTO_INCREMENTの移行確認

これは非常に重要です。

MySQLでは、

AUTO_INCREMENT

が使われます。

PostgreSQLではシーケンスやIdentityが使われます。

移行後、

INSERT INTO users(name)
VALUES ('test');

を実行して、

既存IDと重複せず新しいIDが採番されるか

確認します。

pgloaderにはシーケンスをリセットする機能がありますが、本番前には必ず実データで確認します。

STEP 17：外部キーを確認

PostgreSQLで、

\d users

や、

\d orders

を実行します。

例えば、

Foreign-key constraints:

に期待した外部キーが存在することを確認します。

STEP 18：インデックスを確認

インデックスも重要です。

\d テーブル名

などで確認します。

データが移行できてもインデックスが不足していると、

MySQLでは0.1秒

PostgreSQLでは30秒

ということが起こり得ます。

移行後は必ず性能試験を行います。

STEP 19：ビューは手動移行

例えばMySQLに、

CREATE VIEW active_users AS
SELECT *
FROM users
WHERE enabled = 1;

があったとします。

pgloaderではビューは移行対象外です。

PostgreSQL用に、

CREATE VIEW active_users AS
SELECT *
FROM users
WHERE enabled = true;

などと書き直します。

単純なビューなら簡単ですが、MySQL固有関数を使っている場合は修正が必要です。

STEP 20：トリガーも書き直す

例えばMySQLの、

BEFORE INSERT
AFTER UPDATE

などのトリガーも、そのままpgloaderで移行されません。

PostgreSQLでは一般に、

CREATE FUNCTION
      ↓
CREATE TRIGGER

という構成に書き換えます。

トリガーが大量に存在するシステムでは、この作業量を事前に調査しておく必要があります。

STEP 21：ストアドプロシージャは別物と考えた方がよい

MySQLのStored ProcedureとPostgreSQLのPL/pgSQLは構文が異なります。

例えば、

IF

LOOP

DECLARE

CURSOR

例外処理

関数

などを書き換える必要があります。

そのためストアドプロシージャを大量に使用しているシステムでは、

DB移行

というより、

DB＋アプリケーション移植

に近いプロジェクトになります。

STEP 22：アプリケーションSQLも確認

DBだけ移行しても、アプリケーションがMySQL固有SQLを使っていると動きません。

例えば、

INSERT IGNORE

REPLACE INTO

ON DUPLICATE KEY UPDATE

IFNULL()

GROUP_CONCAT()

DATE_FORMAT()

LIMIT 0, 10

などです。

PostgreSQL用のSQLへ書き換えが必要になる場合があります。

MySQLからPostgreSQLで特に注意する型

代表例をまとめると次のようになります。

MySQL	PostgreSQL側で検討する型
TINYINT(1)	boolean
INT	integer
BIGINT	bigint
AUTO_INCREMENT	identity / sequence
VARCHAR	varchar
TEXT	text
DATETIME	timestamp
TIMESTAMP	timestamp / timestamptz
DECIMAL	numeric
JSON	json / jsonb
BLOB	bytea
ENUM	enum / varchar / CHECK

特に、

UNSIGNED

は注意が必要です。

PostgreSQLの標準整数型にはMySQLのようなUNSIGNED整数がありません。

値域を確認して、場合によっては一段大きい整数型を使用します。

「0000-00-00」に要注意

古いMySQLシステムでは、

0000-00-00

という日付が入っていることがあります。

PostgreSQLでは通常、このような日付をそのまま格納できません。

そのため、

0000-00-00

を、

NULL

などへ変換する必要があります。

移行前に次のようなデータを調査しておくことをおすすめします。

SELECT *
FROM users
WHERE birth_date = '0000-00-00';

MySQLバックアップファイルだけある場合の実践構成

例えば、

appdb_mysql.sql

しかない場合は、Dockerを使うと一時MySQLを作りやすくなります。

構成イメージ：

appdb_mysql.sql
       ↓
MySQL 8.0コンテナ
       ↓
pgloader
       ↓
PostgreSQL 16

本番MySQLへ接続する必要がないため、安全に変換試験できます。

移行完了後はPostgreSQL形式でバックアップ

移行・検証が完了したら、

pg_dump

でPostgreSQLネイティブ形式のバックアップを取得します。

おすすめはcustom形式です。

pg_dump \
  -Fc \
  -U appuser \
  -d appdb \
  -f appdb_postgresql.dump

これで、

appdb_postgresql.dump

ができます。

PostgreSQL公式ドキュメントでも、custom形式は選択的なリストアなどができる柔軟な形式として案内されています。

復元する場合は、

pg_restore \
  -U appuser \
  -d appdb \
  appdb_postgresql.dump

です。

つまり最終的なバックアップ形式も変える

MySQL時代：

mysqldump
 ↓
appdb.sql

移行期間：

appdb.sql
 ↓
一時MySQL
 ↓
pgloader
 ↓
PostgreSQL

移行後：

PostgreSQL
 ↓
pg_dump
 ↓
appdb.dump

となります。

PostgreSQLへ完全移行した後は、mysqldumpではなく pg_dump を使います。

「確実に移行する」ための考え方

MySQLとPostgreSQLは別製品なので、

このバージョンなら無条件で100％移行可能

という保証された組み合わせはありません。

そのため実務では、

バージョンを固定するだけではなく、移行テストを繰り返せる仕組みにする

ことが重要です。

例えば、

本番mysqldump
 ↓
検証MySQL 8.0
 ↓
pgloader
 ↓
検証PostgreSQL 16
 ↓
自動チェック

を何度でも実行できるようにします。

本番切り替え前のチェックリスト

最低でも次を確認します。

全テーブルが存在する
全テーブルの件数が一致する
主要データの内容が一致する
NULLが意図せず変換されていない
日本語が文字化けしていない
主キーが存在する
外部キーが存在する
インデックスが存在する
シーケンスが正しい
新規INSERTできる
UPDATEできる
DELETEできる
ビューを移植した
トリガーを移植した
ストアドプロシージャを移植した
アプリケーションSQLを確認した
性能試験を行った
PostgreSQLバックアップを取得した
PostgreSQLからのリストア試験を行った

ここまで確認して初めて「移行できた」と考えた方が安全です。

小規模DBならかなり現実的

例えば、

テーブル：30個

データ量：5GB

VIEW：数個

Trigger：なし

Stored Procedure：なし

文字コード：utf8mb4

InnoDB

といった比較的シンプルなWebアプリケーションなら、

MySQL 8.0
 ↓
pgloader
 ↓
PostgreSQL 16

はかなり現実的な移行方法です。

一方、

Stored Procedure：300個

Trigger：100個

VIEW：200個

MySQL固有関数を大量使用

というシステムなら、pgloaderだけで完了させるのは難しくなります。

pgloaderが向いているケース

向いているのは、

テーブル中心

一般的なデータ型

主キー・外部キー中心

インデックス中心

ストアドプロシージャが少ない

MySQL固有SQLが少ない

システムです。

pgloaderだけでは難しいケース

逆に、

大量のVIEW

大量のTrigger

大量のStored Procedure

MySQL固有関数

複雑なENUM

Geometry

特殊な文字コード

古いシステム特有の日付データ

大量のUNSIGNED型

などがある場合は、個別の移行設計が必要です。

まとめ

MySQLのデータベースバックアップをPostgreSQLへマイグレーションすることは可能です。

ただし、

MySQL dump
 ↓
PostgreSQLへ直接restore

ではありません。

基本的には、

MySQL 8.0
      ↓
 mysqldump
      ↓
バックアップ
      ↓
必要なら一時MySQLへ復元
      ↓
 pgloader
      ↓
PostgreSQL 16
      ↓
移行検証
      ↓
 pg_dump

という流れになります。

バージョンの具体例としては、

MySQL 8.0系
      ↓
pgloader 3.6.10
      ↓
PostgreSQL 16系

が、一般的なテーブル中心のデータベースを移行する際に検討しやすい構成です。

ただし、

「このバージョン同士ならすべて自動的に100％移行できる」という保証はありません。

特に、

VIEW
TRIGGER
Stored Procedure
Stored Function
MySQL独自SQL

については手動変換が必要になる可能性があります。

逆に、

一般的なテーブル
＋
主キー
＋
外部キー
＋
インデックス
＋
一般的なデータ型

を中心としたデータベースなら、pgloaderを使うことでかなりの部分を自動化できます。

MySQLからPostgreSQLへの移行で最も大切なのは、

「データをコピーできたか」ではなく、「移行前後で同じシステムとして正しく動作するか」を検証すること

です。

そのため、本番環境では必ず、

バックアップ
 ↓
検証環境への移行
 ↓
データ比較
 ↓
アプリケーション試験
 ↓
本番移行

という段階を踏むことをおすすめします。

最初に結論：移行は可能。ただし「バックアップファイルの直接リストア」ではない

この記事で使用するバージョン

pgloaderとは

pgloaderが自動変換してくれる代表例

重要：pgloaderで自動移行できないものもある

バックアップファイルしかない場合はどうする？

全体構成

STEP 1：MySQLのバージョンを確認する

STEP 2：MySQLの文字コードを確認

STEP 3：MySQLをmysqldumpでバックアップ

なぜroutinesやtriggersもdumpするのか

STEP 4：バックアップがMySQLへ戻せるか確認する

STEP 5：件数を記録しておく

STEP 6：PostgreSQL 16を準備

STEP 7：PostgreSQLにユーザーを作成

STEP 8：接続確認

STEP 9：pgloaderをインストール

STEP 10：MySQL 8.0の認証方式に注意

STEP 11：まず単純なpgloaderを試す

STEP 12：本番ではloadファイルを作るのがおすすめ

STEP 13：移行ログを確認する

STEP 14：PostgreSQL側のテーブル一覧を確認

STEP 15：レコード件数を比較する

件数一致だけでは不十分

STEP 16：AUTO_INCREMENTの移行確認

STEP 17：外部キーを確認

STEP 18：インデックスを確認

STEP 19：ビューは手動移行

STEP 20：トリガーも書き直す

STEP 21：ストアドプロシージャは別物と考えた方がよい

STEP 22：アプリケーションSQLも確認

MySQLからPostgreSQLで特に注意する型

「0000-00-00」に要注意

MySQLバックアップファイルだけある場合の実践構成

移行完了後はPostgreSQL形式でバックアップ

つまり最終的なバックアップ形式も変える

「確実に移行する」ための考え方

本番切り替え前のチェックリスト

小規模DBならかなり現実的

pgloaderが向いているケース

pgloaderだけでは難しいケース

おすすめの移行方式

まとめ