開発

2017.07.20

pgloader 3.4.1でMySQLからPostgreSQLへスマートに移行しよう（翻訳）

hachi8833

シェア
ツイート
ブックマーク
LINE

こんにちは、hachi8833です。今回はPostgreSQL向けの強力な移行ツールであるpgloader 3.4.1の紹介記事をお送りいたします。
pgloaderはHomebrewやappでビルドインストールしたりソースからビルドしたりできますが、できればライセンスを購入して作者のDimitri Fontaineを応援しましょう。

概要

原著者の許諾を得て翻訳・公開いたします。

元記事: from MySQL to PostgreSQL
元記事公開日: 2017/07/06
pgloader公式サイト: http://pgloader.io/
GitHubリポジトリ: dimitri/pgloader
原著者: Dimitri Fontaine: PostgreSQLのエキスパートであり、本記事のpgloaderの作者でもあります。

pgloader 3.4.1でMySQLからPostgreSQLへスマートに移行しよう（翻訳）

本日pgloader v3.4.1をリリースいたしました。GitHub commitでご覧いただけるとおり、v3.3.2から100件ものcommitが行われました。

今回リリースのpgloaderでは、シンプルにすることで使いやすさを向上させるという伝統に則っています。かのAlan Kayの有名な言葉になぞらえれば、「シンプルなものがシンプルであるべき」なら「複雑なことも可能になるべき」と信じています。

原注: 本記事ではpgloader 3.4.1の機能に言及していますので、pgloader --versionでご利用のバージョンをご確認ください。pgloaderはソースから容易にビルドでき、debianやRPMベースのディストリビューション向けにもパッケージ化されています。
現在ご利用のディストリビューションやPostgreSQLバージョンで利用可能な最新のパッケージオプションについては、http://wiki.postgresql.org/wiki/Aptやhttps://yum.postgresql.org/をご覧ください。

MySQLからPostgreSQLへの移行

pgloaderがいかにシンプルにできているかを示すひとつの例として、MySQLデータベースをまるごと移行する機能があります。テーブルのスキーマ定義、インデックス、主キー、外部キー制約、コメント、デフォルト値はもちろん、PostgreSQLにトリガをインストールする必要がある場合でも、以下のコマンド一発で完了します。

訳注: --dry-runオプションももちろんあります。

$ pgloader mysql://root@localhost/f1db pgsql:///f1db
2017-07-06T17:57:04.679000+02:00 LOG report summary reset
               table name       read   imported     errors      total time
-------------------------  ---------  ---------  ---------  --------------
          fetch meta data         33         33          0          0.206s 
           Create Schemas          0          0          0          0.026s 
         Create SQL Types          0          0          0          0.007s 
            Create tables         26         26          0          0.057s 
           Set Table OIDs         13         13          0          0.005s 
-------------------------  ---------  ---------  ---------  --------------
            f1db.circuits         73         73          0          0.093s 
  f1db.constructorresults      11011      11011          0          0.139s 
        f1db.constructors        208        208          0          0.047s 
             f1db.drivers        841        841          0          0.073s 
f1db.constructorstandings      11766      11766          0          0.237s 
            f1db.laptimes     413578     413578          0          2.643s 
     f1db.driverstandings      31420      31420          0          0.392s 
            f1db.pitstops       5796       5796          0          0.132s 
               f1db.races        976        976          0          0.076s 
             f1db.seasons         68         68          0          0.053s 
          f1db.qualifying       7257       7257          0          0.091s 
             f1db.results      23514      23514          0          0.431s 
              f1db.status        133        133          0          0.137s 
-------------------------  ---------  ---------  ---------  --------------
  COPY Threads Completion          4          4          0          3.265s 
           Create Indexes         20         20          0          1.760s 
   Index Build Completion         20         20          0          0.715s 
          Reset Sequences         10         10          0          0.029s 
             Primary Keys         13         13          0          0.010s 
      Create Foreign Keys          0          0          0          0.000s 
          Create Triggers          0          0          0          0.000s 
         Install Comments          0          0          0          0.000s 
-------------------------  ---------  ---------  ---------  --------------
        Total import time     506641     506641          0          4.888s

Ergastデータベース（（非商用目的のモーターレース結果の履歴データ）をサンプルデータに使って、上の作業をご家庭でも簡単に再現できます。このサイトからf1db.sql.gzファイル（MySQL 5.1データベースダンプ）をダウンロードし、適当なMySQLインスタンスにインストールして上のコマンドを実行するだけで終わります。

補足: pgloaderはターゲットデータベースの作成までは行いませんので、これは自分で行う必要があります。また、現行のpgloaderはターゲットデータベースのsearch_pathを変更しないので、必要であればこれも設定する必要があります。こうした作業も含めると次の3つのコマンドが必要になります。なお、移行そのものはpgloaderの行でしか行われません。

$ createdb f1db
$ pgloader mysql://root@localhost/f1db pgsql:///f1db
$ psql -d f1db -c 'ALTER DATABASE f1db SET search_path TO f1db, public;'

pgloaderでこの魔法のような操作を行うには、稼働中のMySQLデータベースが必要です。あらゆるSQL方言の解析方法を今から学ぶよりも、システムのカタログにクエリをかけるほうがエラーは少なくなります。

お気づきの方もいらっしゃると思いますが、pgloaderの自動移行は、MySQLデータベースと同じ名前のPostgreSQLスキーマを対象としています。というのも、データベース業界ではSQL標準の「カタログ」をデータベースと呼ぶのがほとんどなのですが、このコンセプトがMySQLにないためです。MySQLでデータベースと呼ばれているものは、実は標準（およびPostgreSQL)でいうところの「スキーマ」に相当します。

MySQLの空間インデックス（spatial index）の地理情報への変換

今回のpgloaderから、MySQLの空間キーを自動的にPostgreSQLのGiSTインデックスに変換するようになりました。従来のpgloaderだと、Sakila Sample Databaseを使って試しに動かすと以下のエラーが発生することがありました。

ERROR PostgreSQL Database error 42704: data type point has no default operator class for access method "btree"
HINT: You must specify an operator class for the index or define a default operator class for the data type.
QUERY: CREATE INDEX idx_188012_idx_location ON pagila.address (location);

この場合、代わりに以下のCREATE INDEXコマンドをPostgreSQLに対して発行することで対応します。

CREATE INDEX idx_188012_idx_location ON pagila.address USING gist(location);

このコマンドが何だかおわかりでしょうか。種明かしすると、実は現行のpgloaderの実行結果からコピペしたものです。この機能についてはユーザーが意識しなくてもよいように実装してあります。またpgloaderは、PostgreSQLのどの型でどのような特殊な取り扱いが必要かをハードコードしなくても、PostgreSQLカタログに対して以下のSQLクエリを実行します（このクエリはsrc/pgsql/sql/list-typenames-without-btree-support.sqlのソースツリーそのままです）。

select typname,
       array_agg(amname order by amname <> 'gist', amname <> 'gin')
  from pg_type
       join pg_opclass on pg_opclass.opcintype = pg_type.oid
       join pg_am on pg_am.oid = pg_opclass.opcmethod
 where substring(typname from 1 for 1) <> '_'
       and not exists
       (
         select amname
           from pg_am am
                join pg_opclass c on am.oid = c.opcmethod
                join pg_type t on c.opcintype = t.oid
          where amname = 'btree' and t.oid = pg_type.oid
       )
group by typname;

通常、クエリの実行結果は以下のようになります。pgloaderが実行時にbtreeサポートのないデータ型を検出すると、ローカルにインストールされているPostgreSQL拡張をサポートすることで未知の型に対応する点が重要です。また、以下のようなユーザー定義の型変換ルールも扱えます。

  typname   │      array_agg       
════════════╪══════════════════════
 aclitem    │ {hash}
 box        │ {gist,brin,spgist}
 cid        │ {hash}
 circle     │ {gist}
 int2vector │ {hash}
 point      │ {gist,spgist,spgist}
 polygon    │ {gist}
 xid        │ {hash}
(8 rows)

この機能は、pgloader 3.4.1では単一カラムインデックスに限定されます。マルチカラムインデックスのサポートが必要な場合はissueを開いて私までお知らせください。

SQLiteからPostgreSQLへの移行

SQLiteデータベース全体をPostgreSQLに移行する場合も、おおむね同じ要領で1コマンドで完了できます。SQLiteデータベースは1つのファイル内で複数に分散していることもありますが、今回リリースのpgloaderではHTTP経由で1個のファイルとしてダウンロードすることもできます。以下はchinook databaseをサンプルに使ったSQLデータベース全体の移行例です。ここでもpgloaderがスキーマ定義、インデックス、主キー、外部キー、デフォルト値、コメントなど諸々の面倒を見てくれます。

$ pgloader https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite_AutoIncrementPKs.sqlite pgsql:///chinook
2017-07-06T18:16:52.256000+02:00 LOG Fetching 'https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite_AutoIncrementPKs.sqlite'
2017-07-06T18:16:54.118000+02:00 ERROR PostgreSQL Database error 42P16: multiple primary keys for table "playlisttrack" are not allowed
QUERY: ALTER TABLE playlisttrack ADD PRIMARY KEY USING INDEX idx_189226_sqlite_autoindex_playlisttrack_1;
2017-07-06T18:16:54.119000+02:00 LOG report summary reset
             table name       read   imported     errors      total time
-----------------------  ---------  ---------  ---------  --------------
                  fetch          0          0          0          1.305s 
        fetch meta data         33         33          0          0.034s 
         Create Schemas          0          0          0          0.001s 
       Create SQL Types          0          0          0          0.006s 
          Create tables         22         22          0          0.096s 
         Set Table OIDs         11         11          0          0.007s 
-----------------------  ---------  ---------  ---------  --------------
                  album        347        347          0          0.020s 
                 artist        275        275          0          0.017s 
               customer         59         59          0          0.024s 
                  genre         25         25          0          0.024s 
            invoiceline       2240       2240          0          0.049s 
               employee          8          8          0          0.018s 
                invoice        412        412          0          0.032s 
               playlist         18         18          0          0.026s 
              mediatype          5          5          0          0.030s 
          playlisttrack       8715       8715          0          0.064s 
                  track       3503       3503          0          0.131s 
-----------------------  ---------  ---------  ---------  --------------
COPY Threads Completion          4          4          0          0.188s 
         Create Indexes         22         22          0          0.168s 
 Index Build Completion         22         22          0          0.066s 
        Reset Sequences          0          0          0          0.029s 
           Primary Keys         12         11          1          0.013s 
    Create Foreign Keys         11         11          0          0.042s 
        Create Triggers          0          0          0          0.001s 
       Install Comments          0          0          0          0.000s 
-----------------------  ---------  ---------  ---------  --------------
      Total import time      15607      15607          0          2.123s

SQLiteのカタログインスペクションは期待するほど素直ではないため、インデックスが主キーインデックスになる正確なタイミングを理解するのが困難でした。SQLiteのドキュメント「ROWIDとINTEGER PRIMARY KEY」のおかげでこうした部分をある程度解明でき、助かったと思います。

なお上で表示されているmultiple primary keys for table "playlisttrack" are not allowedエラーについては私もよくわかりません。ひととおりうまくできるようにするには、SQLiteのカタログイントロスペクションをもう少しハックすることになりそうです。

Microsoft SQL ServerからPostgreSQLへの移行

今回リリースのpgloaderでは、Microsoft SQL Serverのサポートについても細かな改善を多数行い、おおむねうまくいっています。これも、問題の再現とバグ修正に有用なテストケースを提供してくださった皆さまのおかげです。テスト実行用のSQL Serverが私の手元にないプロプライエタリなソフトウェアでこうした修正を行えたことは、実に画期的です。

CSVファイルの読み込み

CSVファイルからPostgreSQLへのデータ読み込みと、大規模かつ柔軟なCSV定義への対応は、pgloaderの当初からの課題です。今回のリリースでは、copyフォーマットファイル、固定幅フォーマット（fixed）ファイル、dBaseファイル（dbf、db3）、IXFファイル（ixf）のサポートが追加されました。

loadコマンドと新オプション

前述の「シンプルなものがシンプルであるべき」で申し上げたように、ときには複雑な作業も必要になります。そうした高度なユースケースはpgloaderのコマンド言語で扱えます。このコマンドはDSL（ドメイン固有言語）であり、pgloaderの実行時にバイナリにコンパイルされます。

ORMの事例: 既存のスキーマへの統合

昔からある複雑な事例のひとつに、アプリがORMでデータベーススキーマを扱っている場合が挙げられます。多くの移行はORMによって楽になりますが、それ以外の場合は難易度がさらにアップします。そうした理由から、私はORMの利用を推奨していません。詳しくは別記事「SQLの書き方」をご覧ください。昔からの定番ネタではありますが、「ORMはコンピュータサイエンスにおける泥沼戦争だ」を未見の方はそちらもどうぞ。

何であれ、ORMがどれほど性に合わないとしても、pgloaderのユーザーを寒空の下に放り出すわけにはいきません。pgloaderはもちろんこうしたケースもサポートしています。dataオプションを指定するだけで移行元スキーマと移行先スキーマの両方を検出し、mergeで移行元のデータをマージして移行を行えます。

今回のリリースから既存のスキーマも移行元に指定できるようになり、かつ従来同様にpgloaderのインデックス処理のメリットも得られます。これは大きな利点と言えます。PostgreSQLから大量のデータを読み込む場合、最も望ましい手順は次のとおりです。

既存のインデックスと、それらに依存する外部キーを削除する
データを読み込む
インデックスを並列処理で一気に再作成する
uniqueインデックスをALTER TABLEで主キーにアップグレードする
インデックスに外部キー制約を再インストールする

言うまでもなく、この最も望ましい手順こそpgloaderで行われる手順そのものです。移行先スキーマのインデックスが事前定義済みであっても、--with "drop indexes"オプションを使えます。

コマンドファイルを作成しなくても、以下のようにコマンドラインで利用できます。

$ pgloader --with "data only"          \
           --with truncate             \
           --with "drop indexes"       \
           --verbose                   \
           mysql://root@localhost/f1db \
           pgsql:///f1db

上のコマンドが機能するには、PostgreSQLの移行先データベースf1dbを事前に定義しておく必要があります。もちろん、有効なSQLオブジェクトが揃っている必要もあります。実はこれらと同じことがORMで行われているのです。

スキーママッピングオプション

pgloaderのコマンド言語では、移行元データベースと移行先データベースの間でスキーマ名やテーブル名をマッピングできます。また、以下のように読み込み時にオプションを追加できます。

load database
     from      mysql://root@localhost/sakila
     into postgresql:///sakila

 ALTER TABLE NAMES MATCHING 'sales_by_store' RENAME TO 'sales_by_store_list'
 ALTER TABLE NAMES MATCHING 'film' RENAME TO 'films'
 ALTER TABLE NAMES MATCHING ~/./ SET (fillfactor='40')

 ALTER SCHEMA 'sakila' RENAME TO 'pagila'

上のALTERコマンドは、実際には（移行元/移行先も含め）どのデータベースでも実行されません。Alterは移行元と移行先の仕様のマッピングに使われます。この仕様によって、移行元にあるsakilaスキーマにあるテーブルを移行先のpagilaスキーマに作成できるようになります。

上のコマンドでは、移行先の全テーブルのfillfactorも変更しています。pgloaderコマンド内のALTER句は、pgloader内部カタログに対して実行されます。実際の反映は、後でPostgreSQLでCREATE TABLEを行ったタイミングになります。

ユーザー定義の型変換

今回のリリースでは、pgloaderで移行元データベースと移行先データベースの間でのユーザー定義型変換を扱う機能を追加しませんでした。デフォルトの型変換の一部については改良されていますので、詳しくはpgloaderのマニュアルをご覧ください。

MySQLの同時読み出しサポートについて

今回リリースのpgloaderにはひとつ実験的な機能が追加されています。現時点では読み込み元に指定できるのはMySQLのみですが、同じテーブルから同時に並列読み出しできるようになりました。

ポイントは、WHERE句を使ってデータの特定範囲ごとに読み出しを分割していることです。これにより、MySQLのマニュアル「rangeの最適化」にあるMySQLインデックスの範囲スキャンを利用できます。

私の行ったテストでは、最適化の効果が現れるのに必要なCPU数を満たしたマシンにアクセスできませんでしたが、それでもおそらく有用なのではないかと考えています。この機能が必要な方がいらっしゃいましたらぜひお試しください。そして効果が現れたらどうぞ私までお知らせください。

pgloader 3.4.1でスレッドごとの同時読み出しを利用する場合の設定例は次のとおりです。

load database
     from      mysql://root@localhost/sakila
     into postgresql:///sakila

 WITH concurrency = 2, workers = 6,
      prefetch rows = 25000,
      multiple readers per thread, rows per range = 50000;

まとめ

これであなたも晴れてPostgreSQLユーザーの仲間入りです。データを思いどおりに処理できる高度な機能を多数搭載した、強力なSQL方言を存分に味わってください。

PostgreSQLが初めての方や、PostgreSQLへの移行を完了して間もない方向けには、SQLのデータセットの詳細をどうぞ。

pgloaderの開発に金銭的支援の価値ありとお考えいただいた方は、ぜひともpgloader Moral Licenseの購入をご検討ください。pgloaderを使って低予算かつ納期遵守で移行を達成できた方にもお願いいたします。オプションでpgloaderスポンサーページに御社名を掲載できますので、こちらも合わせてご検討いただければと思います。

pgloader 3.4.1でMySQLからPostgreSQLへスマートに移行しよう（翻訳）

概要

pgloader 3.4.1でMySQLからPostgreSQLへスマートに移行しよう（翻訳）

MySQLからPostgreSQLへの移行

MySQLの空間インデックス（spatial index）の地理情報への変換

SQLiteからPostgreSQLへの移行

Microsoft SQL ServerからPostgreSQLへの移行

CSVファイルの読み込み

loadコマンドと新オプション

ORMの事例: 既存のスキーマへの統合

スキーママッピングオプション

ユーザー定義の型変換

MySQLの同時読み出しサポートについて

まとめ

関連記事（PostgreSQL）

週刊Railsウォッチ（20191202前編）Rails 6のimplicit_order_columnはカスタマイズ可能、rubocop-rails 2.4.0リリース、Capistrano記事ほか

Kotlinの拡張機能で冗長な〇〇Utilsから脱却する

Rails: ビューでstrftimeを直書きするのはたぶんよくない（翻訳）

週刊Railsウォッチ（20191119後編）メソッド参照演算子が廃止、GitHub新機能続々、平成Ruby会議、GitHub OAuthバイパスほか

Rails 6の新しいデフォルト設定と安全な移行方法を詳しく解説（翻訳）

WebエンジニアがPhotoshopで画像の文字を修正する方法

[Rails 5] モデルの継承元がActiveRecord::BaseからApplicationRecordに変更された

【保存版】Rubyスタイルガイド（日本語・解説付き）総もくじ

RailsとPHP各フレームワークの「複数形」変換処理を比較してみる

eclipseが起動しなくなった

Rails 8.0 Changelog（全項目リンク付き）

開発ツールの画面設計で守るべき2つの「フロー」原則（翻訳）

Tailwind CSS: 開発者が知っておきたいデリケートなUIデザインの便利技11種（翻訳）

Rails 8: strong parametersの新しいparams.expectの使い方（翻訳）

Solid Cable README: DBベースのAction Cableアダプタ（翻訳）

関連記事

CONTACT

pgloader 3.4.1でMySQLからPostgreSQLへスマートに移行しよう（翻訳）

概要

pgloader 3.4.1でMySQLからPostgreSQLへスマートに移行しよう（翻訳）

MySQLからPostgreSQLへの移行

MySQLの空間インデックス（spatial index）の地理情報への変換

SQLiteからPostgreSQLへの移行

Microsoft SQL ServerからPostgreSQLへの移行

CSVファイルの読み込み

loadコマンドと新オプション

ORMの事例: 既存のスキーマへの統合

スキーママッピングオプション

ユーザー定義の型変換

MySQLの同時読み出しサポートについて

まとめ

関連記事（PostgreSQL）

週刊Railsウォッチ（20191202前編）Rails 6のimplicit_order_columnはカスタマイズ可能、rubocop-rails 2.4.0リリース、Capistrano記事ほか

Kotlinの拡張機能で冗長な〇〇Utilsから脱却する

Rails: ビューでstrftimeを直書きするのはたぶんよくない（翻訳）

週刊Railsウォッチ（20191119後編）メソッド参照演算子が廃止、GitHub新機能続々、平成Ruby会議、GitHub OAuthバイパスほか

Rails 6の新しいデフォルト設定と安全な移行方法を詳しく解説（翻訳）

WebエンジニアがPhotoshopで画像の文字を修正する方法

[Rails 5] モデルの継承元がActiveRecord::BaseからApplicationRecordに変更された

【保存版】Rubyスタイルガイド（日本語・解説付き）総もくじ

RailsとPHP各フレームワークの「複数形」変換処理を比較してみる

eclipseが起動しなくなった

Rails 8.0 Changelog（全項目リンク付き）

開発ツールの画面設計で守るべき2つの「フロー」原則（翻訳）

Tailwind CSS: 開発者が知っておきたいデリケートなUIデザインの便利技11種（翻訳）

Rails 8: strong parametersの新しいparams.expectの使い方（翻訳）

Solid Cable README: DBベースのAction Cableアダプタ（翻訳）

関連記事

ExcelでCSV保存したときに半角スペースがはてな（？）に文字化けする

Ruby: CSVでヘッダとボディを同時に定義するやり方

週刊Railsウォッチ（20190617-1/2前編）マルチプルデータベースガイドが追加、mmcと「Ruby 3の型解析に向けた計画」、Ruby 2.6のCSVライブラリはいいほか

CONTACT