MariaDBからPostgreSQLへの移行完全ガイド：プログラミングと一般的な落とし穴

2025-05-27

MariaDB と PostgreSQL はどちらも人気のあるオープンソースのリレーショナルデータベースですが、それぞれ異なる特徴を持っています。そのため、MariaDB から PostgreSQL へ移行する際には、いくつか考慮すべき点があります。

移行の目的と検討事項

なぜ MariaDB から PostgreSQL に移行するのか、その目的を明確にすることが重要です。例えば、以下のような理由が考えられます。

特定のアプリケーション要件: 利用したいアプリケーションが PostgreSQL を推奨または必須としている場合。
並列処理の強化: PostgreSQL はパラレルクエリーなどの並列処理機能が充実しており、大規模データ処理のパフォーマンス向上が期待できます。
高度な機能: PostgreSQL は、地理空間データ、JSONB型、拡張機能（PostGISなど）といった高度な機能を多く持っています。
より厳密なデータ整合性: PostgreSQL はデータ型や制約のチェックがより厳密で、データの整合性を重視するシステムに適しています。

一般的な移行プロセスは、以下のステップで進められます。

本番環境への適用:
- 十分なテストが完了したら、本番環境への切り替えを行います。
パフォーマンスチューニング:
- 移行後の PostgreSQL データベースのパフォーマンスを監視し、必要に応じてインデックスの再構築や設定のチューニングを行います。
アプリケーションの修正:
- アプリケーションがデータベースに接続する際の接続文字列（JDBC URLなど）を PostgreSQL 用に修正します。
- MariaDB 特有のSQL構文や関数を使用していた場合、PostgreSQL の同等な構文に修正します。
データ検証:
- 移行後、データが正しく移行されているか、件数や特定のデータの整合性を確認します。
データのエクスポートとインポート:
- MariaDB からデータのエクスポート: mysqldump などのツールを使って、データをSQLファイルとしてエクスポートします。この際、--compatible=postgresql オプションがある場合は利用を検討します。
- PostgreSQL へのデータのインポート: エクスポートしたデータを PostgreSQL にインポートします。psql コマンドや COPY コマンドなどが利用できます。
- 移行ツールの利用: pgloader は、MariaDB から直接 PostgreSQL へデータをロードする強力なツールです。スキーマ変換とデータ移行を同時に行うことができます。
```
pgloader mysql://user:password@host/database pgsql://user:password@host/database
```
  設定ファイル（例: my.load）を使ってより詳細な設定も可能です。
```
LOAD DATABASE FROM mysql://user:password@localhost/your_mariadb_db
INTO pgsql://user:password@localhost/your_postgresql_db
WITH include drop, create no indexes, create tables, create fkeys, create indexes;
```
  create no indexes オプションは、インデックスを後で作成する場合に有用です。
スキーマの変換:
- MariaDB の CREATE TABLE 文などを PostgreSQL 用に変換します。データ型や制約の調整が必要になる場合があります。
- インデックス、ビュー、ストアドプロシージャ、トリガーなどもPostgreSQLの構文に合わせて修正します。
- ツール利用: pgloader はスキーマの変換も一部サポートしていますが、手動での調整が必要になるケースも多いです。
事前準備と計画:
- 現在のデータベースの調査: MariaDB のスキーマ、データ型、制約、トリガー、ストアドプロシージャ、ビュー、インデックスなどを詳細に把握します。
- PostgreSQL の選定: 移行先の PostgreSQL のバージョンを決定します。
- 互換性の確認: MariaDB と PostgreSQL の構文や機能の互換性を確認し、非互換な部分を特定します。特にデータ型、日付/時刻関数、文字列操作、SQL構文の差異に注意が必要です。
- 移行ツールの選定: pgloader のような専用ツールを使用するか、手動でスクリプトを作成するかを検討します。
- テスト環境の構築: 移行がスムーズに進むかを確認するためのテスト環境を構築します。
- データ移行戦略の決定: 全てのデータを一括で移行するのか、段階的に移行するのかなど、ダウンタイムを考慮した戦略を立てます。

MariaDB と PostgreSQL の主な違いと注意点

コミュニティとエコシステム:
- MariaDB は MySQL の派生であり、MySQL との互換性が高いです。
- PostgreSQL は独立したプロジェクトであり、独自の拡張機能が豊富です。
文字コードと照合順序:
- 両者でサポートされる文字コードや照合順序の設定が異なる場合があります。UTF-8 で統一することが推奨されます。
トランザクション管理:
- 基本的な ACID 特性は共通していますが、分離レベルやロックの挙動に微妙な違いがある場合があります。
ストアドプロシージャと関数:
- 両者で構文や機能が大きく異なります。MariaDB のストアドプロシージャは、PostgreSQL の PL/pgSQL を使って書き直す必要があります。
SQL構文:
- 関数名（例: NOW() vs CURRENT_TIMESTAMP）。
- 文字列連結演算子（MariaDB: CONCAT(), PostgreSQL: ||）。
- LIMIT と OFFSET の構文。
- SQLモードによる挙動の違い。
インデックス:
- MariaDB と PostgreSQL ではインデックスのタイプやオプションに違いがあります。特に全文検索インデックス (FULLTEXT) などは PostgreSQL の GIN や GIST インデックスなど、異なるアプローチが必要です。
主キーとAUTO_INCREMENT:
- MariaDB の AUTO_INCREMENT は、PostgreSQL では SERIAL または BIGSERIAL 型、あるいは SEQUENCE を使って実装されます。
データ型:
- MariaDB の TINYINT(1) は、PostgreSQL では BOOLEAN として扱われることが多いです。
- 日付/時刻型（DATETIME, TIMESTAMP）の挙動やデフォルト値に違いがある場合があります。
- MariaDB には自動的に更新される TIMESTAMP カラムがありますが、PostgreSQL ではトリガーなどで同様の挙動を実装する必要があります。

DBeaver, SQL Developer: データベース管理ツールで、スキーマの比較やデータのエクスポート/インポートをGUIで行うこともできます。
psql: PostgreSQL にデータをインポートしたり、データベースを操作したりするためのコマンドラインツールです。
mysqldump: MariaDB からデータをエクスポートする際に使用します。
pgloader: MariaDB (MySQL) から PostgreSQL へのデータ移行に非常に強力なツールです。スキーマの変換やデータ型のマッピングも自動で行ってくれる部分が多く、手間を削減できます。

MariaDB から PostgreSQL への移行における一般的なエラーとトラブルシューティング

データ型に関するエラー (Data Type Mismatch)

トラブルシューティング:
- スキーマ変換の確認: 移行スクリプトまたは pgloader の設定で、MariaDB のデータ型が PostgreSQL の適切なデータ型にマッピングされているか確認します。
  - 例: TINYINT(1) -> BOOLEAN、MEDIUMTEXT/LONGTEXT -> TEXT、DECIMAL の精度とスケールを適切に設定。
- データクレンジング: ソースの MariaDB 側で、PostgreSQL のデータ型に合わない不正なデータやサイズオーバーのデータがないか確認し、修正またはクレンジングを行います。
- pgloader のキャストルール: pgloader を使用している場合、.load ファイル内で CAST ルールを定義して、データ型変換を明示的に指定します。
```
CAST TYPE tinyint TO boolean;
CAST TYPE datetime TO timestamp WITHOUT TIME ZONE drop default;
```
- エラーログの詳細確認: どのカラムでどのようなデータが問題になっているか、エラーメッセージから特定します。
原因:
- MariaDB と PostgreSQL で、データ型の名前、サイズ、または厳密性が異なるため。
- MariaDB の TINYINT(1) が PostgreSQL の BOOLEAN と自動的にマッピングされない場合。
- MariaDB の日付/時刻型（DATETIME, TIMESTAMP）が PostgreSQL の厳密な日付/時刻フォーマットに合致しない場合。
- MariaDB の TEXT や VARCHAR が、PostgreSQL で指定されたサイズよりも大きなデータを保持している場合。
エラーの兆候:
- ERROR: column "..." is of type integer but expression is of type character varying
- ERROR: invalid input syntax for type numeric: "..."
- ERROR: value too long for type character varying(...)
- 特定のデータが期待通りにインポートされない、または切り捨てられる。

構文エラー (SQL Syntax Errors)

トラブルシューティング:
- SQL構文の修正: MariaDB 特有の構文を PostgreSQL の構文に手動で書き直します。
  - 例:
    - 文字列連結: MariaDB CONCAT(str1, str2) → PostgreSQL str1 || str2
    - 日付操作関数: NOW() → CURRENT_TIMESTAMP、DATE_ADD() → INTERVAL を使用した加減算
    - LIMIT と OFFSET: MariaDB LIMIT count OFFSET offset → PostgreSQL LIMIT count OFFSET offset (構文は同じだが、挙動に注意)
    - MariaDB の GROUP BY で許可される非集約カラムは、PostgreSQL では厳密に制限されます。
- ストアドプロシージャ/トリガーの再実装: MariaDB のストアドプロシージャやトリガーは、PL/pgSQL を使用して PostgreSQL 用にゼロから書き直す必要がある場合が多いです。
- pgloader の限界: pgloader は基本的なスキーマやデータは移行できますが、複雑なストアドプロシージャやトリガーの自動変換には限界があります。手動での修正が必須となることが多いです。
原因:
- MariaDB と PostgreSQL で、SQL関数名、キーワード、演算子、または特定のSQL機能の構文が異なるため。
- 特にストアドプロシージャ、トリガー、ビュー、イベントなど、高度なデータベースオブジェクトで発生しやすい。
エラーの兆候:
- ERROR: syntax error at or near "..."
- ERROR: function "..." does not exist
- ERROR: column "..." does not exist

制約違反エラー (Constraint Violation Errors)

トラブルシューティング:
- データクレンジング: 移行前に、ソースの MariaDB 側で制約違反の原因となるデータ（重複データ、NULL値、無効な外部キー参照）がないか確認し、修正します。
- 外部キーの遅延適用: pgloader を使用する場合、WITH create fkeys deferred のようなオプションを使用して、外部キー制約の有効化をデータのロード後に行うように設定します。
- 制約の一時無効化: 移行中に一時的に外部キー制約を無効にし、データロード後に再度有効化して検証する方法もありますが、データ整合性のリスクが伴うため注意が必要です。
```
ALTER TABLE your_table DISABLE TRIGGER ALL; -- データのインポート
ALTER TABLE your_table ENABLE TRIGGER ALL;
```
- 主キー/ユニークキーの確認: AUTO_INCREMENT カラムが正しく SERIAL/BIGSERIAL に変換されているか、またはシーケンスが正しく設定され、最大値が適切に設定されているか確認します。
原因:
- MariaDB のデータに重複、NULL値、または外部キーの参照不整合が含まれているが、PostgreSQL の制約がより厳しいため。
- 移行プロセス中に、データの順序が正しくないために外部キー制約が先にチェックされる。
エラーの兆候:
- ERROR: duplicate key value violates unique constraint "..." (一意制約違反)
- ERROR: insert or update on table "..." violates foreign key constraint "..." (外部キー制約違反)
- ERROR: null value in column "..." violates not-null constraint (NOT NULL制約違反)

接続エラー (Connection Errors)

トラブルシューティング:
- PostgreSQL サーバーの起動確認: sudo systemctl status postgresql などで起動状態を確認します。
- ファイアウォール設定: PostgreSQL が使用するポート（デフォルトは5432）が許可されているか確認します。
- pg_hba.conf の設定: 接続元IPアドレス、データベース、ユーザー、認証方法が許可されているか確認し、必要に応じて修正します。
- ユーザー認証情報の確認: ユーザー名とパスワードが正しいか再確認します。
原因:
- PostgreSQL サーバーが起動していない。
- ファイアウォールによってポートがブロックされている。
- pg_hba.conf の設定が正しくない（接続元IPアドレスや認証方法が許可されていない）。
- ユーザー名やパスワードが間違っている。
エラーの兆候:
- could not connect to server: Connection refused
- FATAL: password authentication failed for user "..."

パフォーマンスとリソースに関する問題 (Performance & Resource Issues)

トラブルシューティング:
- バッチ処理: 大量のデータを一度にインポートするのではなく、チャンクに分割してインポートします。
- COPY コマンドの利用: psql の \copy またはサーバーサイドの COPY コマンドは、INSERT 文よりも高速です。
- pgloader のチューニング: pgloader の --with workers や --with concurrency オプションで並列度を調整し、適切なバッチサイズを設定します。
- インデックスの遅延作成: データのインポート中はインデックスを無効にし、全てのデータがインポートされた後にインデックスを作成します。これは pgloader の create no indexes オプションで実現できます。
- PostgreSQL のチューニング: postgresql.conf で shared_buffers, work_mem, maintenance_mem, wal_buffers などの設定を見直します。
- ディスクI/Oの監視: iostat などのツールでディスクI/Oのボトルネックを特定します。
原因:
- 大量のデータを一度に処理しようとしている。
- PostgreSQL サーバーのリソース（CPU, メモリ, ディスクI/O）が不足している。
- 不適切な移行戦略（例: インデックスを事前に作成してしまい、インポートが遅くなる）。
エラーの兆候:
- データ移行が非常に遅い。
- メモリ不足やディスク容量不足のエラー。

文字コードと照合順序に関する問題 (Character Encoding & Collation Issues)

トラブルシューティング:
- UTF-8 での統一: 移行元の MariaDB も、移行先の PostgreSQL も、すべて UTF-8 (または UTF8) に設定することを強く推奨します。
- データベース作成時の設定: PostgreSQL のデータベース作成時に、文字コードと照合順序を明示的に指定します。
```
CREATE DATABASE your_db ENCODING 'UTF8' LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8' TEMPLATE template0;
```
- クライアントエンコーディング: クライアントツール（psql、pgloader など）のエンコーディング設定も確認します。
原因:
- MariaDB と PostgreSQL で、データベース、テーブル、またはカラムの文字コード設定が異なる。
- 照合順序（Collation）の設定が異なる。
エラーの兆候:
- 文字化けが発生する。
- 文字列の比較やソート順が期待と異なる。

トラブルシューティング:
- シーケンスの現在の値の更新: データ移行後、各 SERIAL/BIGSERIAL カラムのシーケンスの現在の値を、テーブルの最大値に合わせるように手動で更新します。
```
SELECT setval('your_table_id_seq', (SELECT MAX(id) FROM your_table));
```
  your_table_id_seq は、id カラムに関連付けられたシーケンス名です。これは通常、tablename_columnname_seq の形式になります。
- pgloader の利用: pgloader は通常、この処理を自動的に行ってくれますが、念のため確認が必要です。
原因:
- MariaDB の AUTO_INCREMENT 値が正しく PostgreSQL のシーケンスの現在の値に反映されていない。
エラーの兆候:
- インポートされたデータで、SERIAL や BIGSERIAL カラムの値が重複する。
- 新しいレコードを挿入しようとすると、duplicate key value violates unique constraint エラーが発生する。

詳細なログの確認: 移行ツール（pgloader など）やデータベースサーバー（PostgreSQL のログファイル）のログを詳細に確認します。エラーメッセージ、行番号、カラム名など、特定できる情報が多いほど解決が早まります。
小規模なデータでテスト: まずは小さなテーブルや少ないデータ量で移行を試し、問題点を特定します。
段階的な移行: 複雑なデータベースの場合は、テーブルごとに、または機能ごとに段階的に移行を進めます。
バックアップ: 移行作業の前後、および重要な変更を加える前には、必ずMariaDBとPostgreSQLの両方でバックアップを取得します。
ドキュメントの参照: MariaDB と PostgreSQL の公式ドキュメントで、特定の機能や構文について確認します。

スキーマ変換の例

MariaDB の CREATE TABLE 文を PostgreSQL 用に変換する例です。データ型やAUTO_INCREMENTの扱いが主な変更点になります。

MariaDB のテーブル定義例

-- MariaDB のテーブル定義例
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active TINYINT(1) DEFAULT 1,
    profile_data JSON
);

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    created_date DATE
);

-- MariaDB のインデックス例
CREATE INDEX idx_username ON users (username);

PostgreSQL 用の変換例

上記の MariaDB の定義を PostgreSQL 用に変換すると、以下のようになります。

-- PostgreSQL のテーブル定義例

-- users テーブル
CREATE TABLE users (
    id SERIAL PRIMARY KEY, -- INT AUTO_INCREMENT は SERIAL に
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP, -- TIMESTAMP WITHOUT TIME ZONE を推奨
    is_active BOOLEAN DEFAULT TRUE, -- TINYINT(1) は BOOLEAN に
    profile_data JSONB -- JSON は JSONB を推奨 (バイナリ形式で高速)
);

-- products テーブル
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY, -- INT AUTO_INCREMENT は SERIAL に
    product_name VARCHAR(255) NOT NULL,
    price NUMERIC(10, 2) NOT NULL, -- DECIMAL は NUMERIC に
    stock_quantity INTEGER DEFAULT 0, -- INT は INTEGER に
    created_date DATE
);

-- PostgreSQL のインデックス例
-- MariaDB と同様に CREATE INDEX 構文で作成可能
CREATE INDEX idx_username ON users (username);

-- シーケンスの最大値更新（データ移行後に実行）
-- pgloader を使う場合、通常自動で設定されるが、手動で行う場合の例
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));
SELECT setval('products_product_id_seq', (SELECT MAX(product_id) FROM products));

主な変更点

JSON: PostgreSQL の JSON 型は単なるテキストとしてJSONを保存しますが、JSONB はバイナリ形式で保存し、より高速なクエリとインデックスをサポートします。
TIMESTAMP: PostgreSQL の TIMESTAMP はデフォルトでタイムゾーン情報を含みますが、MariaDB の TIMESTAMP は通常含まないため、TIMESTAMP WITHOUT TIME ZONE を使うことが多いです。
INT: PostgreSQL では INTEGER が一般的です。
DECIMAL: PostgreSQL では NUMERIC が同等です。
TINYINT(1): MariaDB で TINYINT(1) は boolean として使われることが多いですが、PostgreSQL では明示的に BOOLEAN 型を使います。
AUTO_INCREMENT: MariaDB の INT AUTO_INCREMENT は、PostgreSQL では SERIAL (または BIGSERIAL for BIGINT) に置き換えられます。これは自動的にシーケンスを作成し、主キーとして設定します。

データ移行の例

データ移行には、pgloader のような専用ツールを使用するのが最も効率的です。手動で行う場合は、MariaDB からデータをエクスポートし、PostgreSQL にインポートするプロセスになります。

`pgloader` を使った移行例（推奨）

pgloader は、MariaDB から PostgreSQL への移行を自動化してくれる非常に強力なツールです。

インストール: まず、pgloader をインストールします。Homebrew (macOS) や apt (Debian/Ubuntu) で利用可能です。
```
# Debian/Ubuntu
sudo apt install pgloader
# macOS (Homebrew)
brew install pgloader
```

実行コマンド: MariaDB から PostgreSQL へ直接データをロードする基本的なコマンドです。

pgloader mysql://<maria_user>:<maria_password>@<maria_host>:<maria_port>/<maria_db_name> pgsql://<pg_user>:<pg_password>@<pg_host>:<pg_port>/<pg_db_name>

例

pgloader mysql://myuser:mypass@localhost/mariadb_db pgsql://pguser:pgpass@localhost/postgres_db

設定ファイル (.load ファイル) を使った詳細な制御: より複雑な移行（データ型のマッピング、特定のテーブルのスキップ、変換ルールの適用など）には、.load ファイルを使用します。

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

LOAD DATABASE
     FROM mysql://myuser:mypass@localhost:3306/mariadb_db
     INTO pgsql://pguser:pgpass@localhost:5432/postgres_db

WITH include drop,          -- 移行前にPostgreSQL側の既存テーブルをドロップする
     create no indexes,     -- 最初にインデックスを作成しない（データロードを高速化）
     create tables,
     create fkeys,          -- 外部キーを作成する
     create indexes,        -- データロード後にインデックスを作成する
     reset sequences,       -- シーケンスを最大値にリセットする
     workers = 8,           -- 並列処理のワーカー数
     concurrency = 1,       -- 同時接続数
     batch size = 10000;    -- バッチサイズ

-- データ型のマッピング例（必要に応じて追加）
-- CAST TYPE tinyint TO boolean USING (CASE WHEN "colname" = 0 THEN FALSE ELSE TRUE END);
-- CAST COLUMN users.is_active TO boolean;
-- CAST TYPE datetime TO timestamp WITHOUT TIME ZONE;

-- 特定のテーブルをスキップする場合
-- SKIP TABLE users;

実行コマンド:

pgloader migrate_db.load

手動でのデータ移行例（SQLファイル経由）

pgloader が使えない、または特定の理由で手動で制御したい場合の例です。

MariaDB からデータのエクスポート: MariaDB のデータを mysqldump でSQLファイルとしてエクスポートします。この際、--compatible=postgresql オプションは限定的に役立つ場合もありますが、完全な互換性があるわけではないので注意が必要です。通常は、--no-create-info でデータのみをエクスポートし、別途PostgreSQL用のスキーマを作成するのが安全です。

# スキーマなしでデータのみをエクスポート
mysqldump --user=myuser --password=mypass --host=localhost --port=3306 mariadb_db --no-create-info --compact --skip-extended-insert --default-character-set=utf8mb4 --hex-blob > data.sql

# 注意点:
# --skip-extended-insert は1行ずつINSERT文を生成し、デバッグしやすいが遅い。大量データには不向き。
# --compact はコメントなどを省略。
# --hex-blob はバイナリデータ（BLOBなど）を16進数で出力し、PostgreSQLで扱いやすくする。
# --default-character-set=utf8mb4 は文字コードを指定。

PostgreSQL へのデータのインポート: エクスポートした data.sql ファイルを psql コマンドでPostgreSQLにインポートします。
```
psql -U pguser -d postgres_db -h localhost -p 5432 -f data.sql
```
より高速なインポート方法 (COPY コマンドを使用): mysqldump でCSV形式などでエクスポートし、PostgreSQL の COPY コマンドでインポートする方が、INSERT 文の羅列よりもはるかに高速です。

MariaDB から CSV エクスポート
```
-- MariaDB クライアントから実行
SELECT id, username, email, created_at, is_active, profile_data
FROM users
INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
```
PostgreSQL への CSV インポート
```
-- psql クライアントから実行
COPY users FROM '/tmp/users.csv' WITH (FORMAT CSV, HEADER FALSE, ENCODING 'UTF8');
-- HEADER TRUE はCSVの1行目がヘッダーの場合
```
これはサーバーサイドの COPY なので、ファイルはPostgreSQLサーバーがアクセスできる場所に置く必要があります。クライアントからインポートする場合は \copy を使います。

アプリケーションがデータベースに接続する際の接続文字列（URL）と、データベース固有のSQL構文や関数を修正する必要があります。

接続文字列の変更 (Python/SQLAlchemy の例)

MariaDB (MySQL) 接続例

# MariaDB (MySQL) 接続文字列例
# pymysql ドライバーを使用
DATABASE_URL = "mysql+pymysql://myuser:mypass@localhost:3306/mariadb_db"

from sqlalchemy import create_engine
engine = create_engine(DATABASE_URL)
# ... データベース操作

PostgreSQL 接続例

# PostgreSQL 接続文字列例
# psycopg2 ドライバーを使用
DATABASE_URL = "postgresql+psycopg2://pguser:pgpass@localhost:5432/postgres_db"

from sqlalchemy import create_engine
engine = create_engine(DATABASE_URL)
# ... データベース操作

主な変更点

データベース名: 移行先のデータベース名へ。
ポート番号: デフォルトの MariaDB (3306) から PostgreSQL (5432) へ。
スキーム: mysql+pymysql から postgresql+psycopg2 へ。

SQL構文・関数の修正例

アプリケーション内で直接SQLクエリを記述している場合、MariaDB特有の関数や構文をPostgreSQLの同等なものに修正する必要があります。

MariaDB のクエリ例

-- MariaDB: 現在の日時をフォーマット
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS current_time;

-- MariaDB: 文字列連結
SELECT CONCAT('Hello, ', username, '!') AS greeting FROM users WHERE id = 1;

-- MariaDB: 条件付き集計
SELECT
    SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END) AS active_users_count
FROM users;

PostgreSQL 用の修正例

-- PostgreSQL: 現在の日時をフォーマット
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS') AS current_time;
-- あるいは単に `NOW()` や `CURRENT_TIMESTAMP` を利用

-- PostgreSQL: 文字列連結
SELECT 'Hello, ' || username || '!' AS greeting FROM users WHERE id = 1;

-- PostgreSQL: 条件付き集計 (BOOLEAN型を直接利用)
SELECT
    COUNT(*) FILTER (WHERE is_active = TRUE) AS active_users_count
FROM users;
-- または
SELECT
    SUM(CASE WHEN is_active THEN 1 ELSE 0 END) AS active_users_count
FROM users;

主な変更点

条件付き集計: CASE WHEN is_active = 1 の部分が is_active (BOOLEAN型) を直接利用する形に変わります。PostgreSQL の FILTER (WHERE ...) 句も便利です。
文字列連結: CONCAT() は || 演算子に。
日付/時刻関数: DATE_FORMAT は TO_CHAR に。NOW() は両方で使えますが、PostgreSQL の CURRENT_TIMESTAMP もよく使われます。

MariaDB から PostgreSQL への移行におけるプログラミングは、主に以下の点に集約されます。

アプリケーションコードの修正: データベース接続文字列の変更と、DB固有のSQL構文・関数の修正が必須。
データの移行: pgloader のような専用ツールが最も効率的。手動の場合は COPY コマンドの利用を検討。
スキーマの変換: データ型のマッピングと AUTO_INCREMENT から SERIAL への変更が中心。

前回の説明では pgloader を主軸とした一般的な移行方法と、手動でのSQL変換・データエクスポート/インポートの例を挙げました。今回は、それら以外の代替手段や、特定のシナリオで役立つアプローチについて詳しく見ていきましょう。

ORM (Object-Relational Mapping) を活用した移行

多くのモダンなアプリケーションは、データベース操作にORMライブラリ（PythonのSQLAlchemy、JavaのHibernate、Ruby on RailsのActive Recordなど）を使用しています。ORMはデータベースの抽象化レイヤーを提供するため、データベースの変更に強いメリットがあります。

アプローチ

既存のORM設定の確認: 現在のMariaDBに接続しているORMの設定を確認します。
PostgreSQLドライバーの追加: アプリケーションの依存関係にPostgreSQL用のデータベースドライバー（例: Pythonのpsycopg2、JavaのPostgreSQL JDBC Driver）を追加します。
接続文字列の変更: アプリケーションコード内で、MariaDBへの接続文字列をPostgreSQLへの接続文字列に変更します。
スキーマの再生成/マイグレーション:
- ORMによる自動生成: ORMによっては、定義されたモデルから新しいデータベース（PostgreSQL）にスキーマを自動生成する機能があります。これは、手動でのCREATE TABLE文の変換手間を省きます。
- マイグレーションツールの利用: Alembic (Python), Flyway (Java), Liquibase (Java) などのデータベースマイグレーションツールを使用している場合、PostgreSQL用のマイグレーションスクリプトを作成し、スキーマを適用します。
データ移行: これは依然として課題となります。ORM自体が直接データ移行機能を提供することは稀です。このステップでは、結局 pgloader や mysqldump -> COPY など、別のツールを併用することになります。ただし、データ移行後のデータの検証や、新しいレコードの挿入テストはORM経由で行いやすくなります。
アプリケーションのテスト: ORMを使用している場合でも、データベース固有の機能や、ORMが生成するSQLの微妙な違いによって問題が発生する可能性があります。徹底的なテストが必要です。

メリット

スキーマ管理: ORMのマイグレーション機能と組み合わせることで、スキーマの変更履歴を管理しやすくなります。
データベースの抽象化: アプリケーションコードの大部分でデータベース固有のSQLを記述する必要がないため、変更が容易です。

デメリット

パフォーマンスのボトルネック: ORMが生成するSQLが、データベースの移行によって最適化されない場合があります。特定の複雑なクエリでは、手動でSQLを記述し直す必要があるかもしれません。
データ移行は依然として外部ツールに依存: ORMはスキーマ生成やデータ操作には強いですが、既存の大規模なデータの移行機能は持ちません。

データ変換スクリプトの手動作成 (Python/Node.jsなど)

pgloader のような汎用ツールでは対応しきれない複雑なデータ変換ロジックが必要な場合や、ごく小規模なデータ移行の場合に有効な方法です。

データベース接続: Python (mysql-connector-python, psycopg2) や Node.js (mysql2, pg) など、お好みのプログラミング言語で両データベースへの接続を確立します。
データの読み込み: MariaDBからテーブルごとにデータを読み込みます。
データ変換ロジックの実装:
- データ型変換（例: MariaDBのTINYINT(1)をPythonでboolに変換し、PostgreSQLに挿入）。
- 特殊な値の処理（例: MariaDBの特定の文字列値をPostgreSQLのENUM型にマッピング）。
- 複合データの分解/結合。
PostgreSQLへの書き込み: 変換したデータをPostgreSQLの対応するテーブルに挿入します。この際、効率化のためにバッチ挿入（複数の行を一度のSQLで挿入）や、PostgreSQLのCOPYコマンドを使用するライブラリ機能（例: Pythonのpsycopg2.extras.execute_values）を活用します。
シーケンスの更新: SERIAL型のカラムを使用している場合、データ挿入後にシーケンスの現在値を手動で更新します。

Pythonでのバッチ挿入例

import mysql.connector
import psycopg2
from psycopg2 import extras # バッチ挿入用

# MariaDB 接続設定
maria_config = {
    'host': 'localhost',
    'user': 'myuser',
    'password': 'mypass',
    'database': 'mariadb_db'
}

# PostgreSQL 接続設定
pg_config = {
    'host': 'localhost',
    'user': 'pguser',
    'password': 'pgpass',
    'database': 'postgres_db'
}

try:
    maria_conn = mysql.connector.connect(**maria_config)
    maria_cursor = maria_conn.cursor(dictionary=True) # 辞書形式で取得

    pg_conn = psycopg2.connect(**pg_config)
    pg_cursor = pg_conn.cursor()

    # users テーブルのデータを移行する例
    maria_cursor.execute("SELECT id, username, email, created_at, is_active, profile_data FROM users")
    rows = maria_cursor.fetchall()

    # データを変換してバッチ挿入用に準備
    insert_data = []
    for row in rows:
        # MariaDBのTINYINT(1)をPostgreSQLのBOOLEANに変換
        is_active_pg = bool(row['is_active']) if row['is_active'] is not None else None
        # JSONデータはそのまま利用可能だが、念のためダンプしてロード
        profile_data_pg = row['profile_data'] # psycopg2は辞書をJSONBに自動変換できる

        insert_data.append((
            row['id'],
            row['username'],
            row['email'],
            row['created_at'], # TIMESTAMP型は通常自動変換される
            is_active_pg,
            profile_data_pg
        ))

    # PostgreSQLへのバッチ挿入
    # ON CONFLICT は、PostgreSQLで既に存在する主キーがあった場合の処理（例: 何もしない）
    insert_query = """
    INSERT INTO users (id, username, email, created_at, is_active, profile_data)
    VALUES %s
    ON CONFLICT (id) DO NOTHING;
    """
    extras.execute_values(pg_cursor, insert_query, insert_data, page_size=1000)

    # シーケンスの最大値を更新 (idカラムがSERIAL型の場合)
    pg_cursor.execute(f"SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));")

    pg_conn.commit()
    print("データ移行が完了しました。")

except mysql.connector.Error as err:
    print(f"MariaDB エラー: {err}")
except psycopg2.Error as err:
    print(f"PostgreSQL エラー: {err}")
finally:
    if 'maria_conn' in locals() and maria_conn.is_connected():
        maria_cursor.close()
        maria_conn.close()
    if 'pg_conn' in locals() and not pg_conn.closed:
        pg_cursor.close()
        pg_conn.close()

小規模なデータに最適: 移行するデータ量が少ない場合や、複雑な変換ロエリロジックをテストしやすいです。
詳細な制御: 移行プロセス全体をコードで完全に制御できます。
高い柔軟性: 複雑なデータ変換、クレンジング、加工ロジックを自由に実装できます。

エラーハンドリング: スクリプト内で堅牢なエラーハンドリングを実装する必要があります。
パフォーマンス: 大量のデータ移行には、最適化された汎用ツール（pgloader や COPY）に劣る可能性があります。
開発コスト: 汎用ツールに比べて、スクリプトの開発・テストに時間がかかります。

PostgreSQLの**Foreign Data Wrapper (FDW)**機能を利用すると、外部データベース（MariaDBを含む）にPostgreSQLから直接アクセスできます。これは、特に段階的な移行や、移行中のデータ比較・検証に役立ちます。

MariaDB FDW のインストール: PostgreSQLサーバーにMariaDB用のFDW（例: mysql_fdw）をインストールします。
```
-- PostgreSQL の psql で実行
CREATE EXTENSION mysql_fdw;
```

外部サーバーの定義: MariaDBへの接続情報を定義します。

CREATE SERVER mariadb_server
    FOREIGN DATA WRAPPER mysql_fdw
    OPTIONS (host 'localhost', port '3306');

ユーザーマッピングの定義: MariaDBのユーザー認証情報をPostgreSQLにマッピングします。

CREATE USER MAPPING FOR pguser -- PostgreSQLのユーザー
    SERVER mariadb_server
    OPTIONS (username 'myuser', password 'mypass');

外部テーブルの作成: MariaDBのテーブルに対応する外部テーブルをPostgreSQL内に作成します。これにより、MariaDBのテーブルをPostgreSQLの通常のテーブルのように扱えるようになります。
```
CREATE FOREIGN TABLE mariadb_users (
    id INTEGER,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP WITHOUT TIME ZONE,
    is_active BOOLEAN,
    profile_data JSONB
)
SERVER mariadb_server
OPTIONS (dbname 'mariadb_db', table_name 'users');
```
注意: ここでデータ型をPostgreSQLの型にマッピングする必要があります。

データ移行: INSERT INTO ... SELECT FROM ... 構文を使用して、外部テーブルからPostgreSQLのローカルテーブルにデータを直接コピーします。

INSERT INTO users (id, username, email, created_at, is_active, profile_data)
SELECT id, username, email, created_at, is_active, profile_data FROM mariadb_users;

-- idがSERIALの場合、id列は含めず、代わりにCOALESCEとSETVALでシーケンスを設定する
INSERT INTO users (username, email, created_at, is_active, profile_data)
SELECT username, email, created_at, is_active, profile_data FROM mariadb_users;

-- シーケンスの更新
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));

段階的移行: 移行期間中に両方のデータベースを並行して利用する場合に、FDWを介してMariaDBのデータにアクセスできます（ただしパフォーマンスはローカルテーブルより劣ります）。
データ検証の容易さ: 移行中にMariaDBとPostgreSQLのデータを直接比較・検証するのに便利です。
SQLによるデータ移行: 複雑なプログラミングなしに、SQLクエリだけでデータ移行が可能です。

設定の複雑さ: FDWのインストールと設定が必要です。
スキーマの変換: FDWはデータ型の自動変換をあまり行いません。外部テーブル作成時に手動でPostgreSQLのデータ型にマッピングする必要があります。
パフォーマンス: FDW経由のデータアクセスは、直接データにアクセスするよりもオーバーヘッドがあり、大規模データ移行には向かない場合があります。

MariaDB から PostgreSQL への移行において、pgloader は最も推奨される汎用ツールですが、特定の要件や状況に応じて上記のような代替手段も検討できます。

FDW: SQLのみでデータ移行を試みたい場合や、移行中のデータ比較・検証に便利ですが、大規模データには向かない可能性があります。
カスタムスクリプト: 複雑なデータ変換ロジックや特定のデータクレンジングが必要な場合に高い柔軟性を提供しますが、開発コストがかかります。
ORMを活用: アプリケーションがORMを使用している場合、スキーマの管理はORMに任せつつ、データ移行は別のツール（pgloaderなど）を併用します。

MariaDBからPostgreSQLへの移行完全ガイド：プログラミングと一般的な落とし穴

MariaDB から PostgreSQL への移行における一般的なエラーとトラブルシューティング

スキーマ変換の例

MariaDB のテーブル定義例

PostgreSQL 用の変換例

データ移行の例

pgloader を使った移行例（推奨）

手動でのデータ移行例（SQLファイル経由）

接続文字列の変更 (Python/SQLAlchemy の例)

SQL構文・関数の修正例

ORM (Object-Relational Mapping) を活用した移行

アプローチ

メリット

デメリット

データ変換スクリプトの手動作成 (Python/Node.jsなど)

Pythonでのバッチ挿入例

`pgloader` を使った移行例（推奨）