こんにちは、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スポンサーページに御社名を掲載できますので、こちらも合わせてご検討いただければと思います。
関連記事（PostgreSQL）
- PostgreSQLを使う理由（更新5年目）（翻訳）
- PostgreSQLの機能と便利技トップ10（2016年版）（翻訳）
- Rails開発者のためのPostgreSQLの便利技（翻訳）
- [Rails] RubyistのためのPostgreSQL EXPLAINガイド（翻訳）
- 【ゆるふわDocker部】任意バージョンのPostgreSQLコマンドを実行して外部DBに接続する
- RailsでPostgresを使おうとしてはまった