1. pg_bulkload とは
pg_bulkload は PostgreSQL に大量のデータを高速にロードすることができるツールです。
pg_bulkload 自体はそのバージョンにもよりますが、 PostgreSQL 8.3 以降で使用が可能です。PostgreSQL 9.6 に対応したものは pg_bulkload 3.1.10 以降です。最近の PostgreSQL との対応バージョンについての詳細は、pg_bulkload 3.1.X のリリースノートまたは導入する pg_bulkload バージョンのドキュメントを確認してください。本文書では、PostgreSQL 9.6.2 と pg_bulkload 3.1.13 を Linux 上で使用した場合のコマンド例などを紹介しています。
2. 使用方法
◆ インストール
pg_bulkload は以下のページから対応する PostgreSQL バージョンと Linux のバージョンごとの rpm パッケージもしくはソースコードをダウンロードすることができます。また、パッケージは PostgreSQL Yum Repository からも取得可能となっています。ここでは、ソースコードからビルドする方法を示します。
ソフトウェア配布ページ： https://github.com/ossc-db/pg_bulkload/releases
ソースコードをダウンロード後は以下の手順で他のツールと同様に対象の PostgreSQL に導入することができます。インストール終了後は、pg_bulkload を使用したいデータベースにて CREATE EXTENSION を実行してください。
$ tar zxf pg_bulkload-VERSION3_1_13.tar.gz $ cd pg_bulkload-VERSION3_1_13 $ make USE_PGXS=1 $ su # make USE_PGXS=1 install $ psql db1 db1=# CREATE EXTENSION pg_bulkload; CREATE EXTENSION
◆ 使用方法
pg_bulkload では提供されるOSコマンドを実行して、テーブルにデータの読み込みを行います。対象テーブルは空データである必要はなく、既にレコードを持つテーブルにデータを追加するときにも使用できます。
対象テーブルやデータファイル、諸条件はコマンドラインオプションまたは制御ファイルで指定します。制御ファイルに記述する方がオプション指定よりも細かな設定を行うことができます。
コマンド書式は以下のようになります。
$ pg_bulkload [オプション] [制御ファイル]
制御ファイルを使う場合には、コマンドラインオプションに指定すべきは以下表に示す接続オプションのみです。これらは psql などの PostgreSQL クライアントツールと同様の記述ができます。指定を省略した場合には環境変数が使われたり、OS ユーザ名と同じユーザ名が使われる等も同様の振る舞いです。
| 接続オプション | 説明 |
|---|---|
| -d DBNAME –dbname=DBNAME |
接続するデータベース名を指定します。 |
| -h HOSTNAME –host=HOSTNAME |
サーバが稼働しているマシンのホスト名を指定します。 |
| -p PORT –port=PORT |
サーバが接続を監視する TCP ポートもしくは Unix ドメインソケットファイルを指定します。 |
| -U USERNAME –username=USERNAME |
接続するユーザ名を指定します。 |
| -W –password |
データベースに接続する前に、強制的にパスワード入力を促します。 |
制御ファイルの例を示します。以下は CSV 形式のファイル (bulkload.dat) から、テーブル t_bulkload にデータの読み込みを行います。 行の # (シャープ) 以降はコメントとして扱われます。
# # Control file to load CSV # OUTPUT = t_bulkload INPUT = /var/lib/pgsql/bulkload.dat TYPE = CSV DELIMITER = ","
以下表は制御ファイルの指定項目の説明です。TYPE と DELIMITER に関しては、記載がない場合でもデフォルトで CSV と ” , (カンマ)” が選択されます。
| 項目 | 説明 |
|---|---|
| OUTPUT | ロード先のテーブルを指定します。 table_name |
| INPUT | 読み込むファイルのパスを記載します。相対パスでファイルを指定した場合、この制御ファイルがある場所がカレントになります。 |
| TYPE | 入力データのタイプを以下のいずれかで指定します。
|
| DELIMITER | TYPE で CSV を指定した場合のデリミタを指定します。デフォルトはカンマです。 |
CSV を TYPE として選択した場合、以下の項目も設定できます。
| 項目 | 説明 |
|---|---|
| QUOTE = quote_character | 引用符を指定します。デフォルトは「”」です。 |
| ESCAPE = escape_character | エスケープ文字を指定します。デフォルトは「”」です。 |
| NULL = null_string | NULL 値を表す文字列を指定します。デフォルトは空文字列です。 |
| FORCE_NOT_NULL = column | 入力ファイル中の表現が NULL 値文字列であっても NULL として扱わないカラムを 1 行 1 カラムで指定することができます。 |
TYPE で BINARY を選んだ際の設定項目も各種あります。こちらについては pg_bulkload のマニュアルドキュメントを参照ください。
◆ COPY との比較
pg_bulkload がどれくらい高速でロードを行うことができるのか COPY 文使った場合と比べてみます。今回テストでは 500 万件のデータを pgbench で作成して、ここから 500 万件の行をもつ CSV ファイルを作成しました。これをロードします。
それではまず COPY 文でロードした場合を見てます。
COPY 5000000 real 0m11.835s user 0m0.001s sys 0m0.003s
次に pg_bulkload を単純にファイルとテーブルだけ指定して実行した場合の結果が以下です。
COPY 文を使用した場合、12秒ほどかかっていましたが、pg_bulkload では 6.8 秒でロードすることができました。速度は 1.7 倍といったところです。
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
0 Rows skipped.
5000000 Rows successfully loaded.
0 Rows not loaded due to parse errors.
0 Rows not loaded due to duplicate errors.
0 Rows replaced with new rows.
real 0m6.774s
user 0m0.001s
sys 0m0.001s
◆ 並列処理
pg_bulkload は入力ファイルの読み込みとテーブルへのデータ書き込みを別プロセスで並列に実行することができ、マシンリソースに余裕があれば、さらに高速なロードが可能になります。
並列処理をさせるための制御ファイルの書き方が二つあります。
1 つは、以下のように WRITER を設定することです。
WRITER = PARALLEL
もうひとつは、以下のように MULTI_PROCESS を設定することです。
MULTI_PROCESS = YES
1 つ目の方法を制御ファイルに記載している場合 MULTI_PROCESS は記載があったとしても無視されます。MULTI_PROCESS を有効にするには WRITER は DIRECT が設定されている必要があります。DIRECT は WRITER のデフォルト値なので、明示的に記載する必要はありません。
◆ 制約の検査
pg_bulkload はデフォルトではロード先テーブルの制約について検査を行いません。したがって、制約違反になるかもしれないデータを投入する場合には安全ではありません。
制約の検査をするには制御ファイルで以下を設定します。
CHECK_CONSTRAINTS = YES
投入途中で制約違反が検出された場合、デフォルトでは直ちに処理が中断されます。たとえ制約違反やその他エラーになっても、そのレコードだけスキップしてロード継続を行う設定方法も用意されています。
PARSE_ERRORS = n DUPLICATE_ERRORS = n
n には何件のエラーまでスキップして処理継続するかを記載します。デフォルトは 0 です。
設定した件数以上のエラーが出た場合の動作がエラー種別によって異なる点は注意が必要です。投入元レコードの構文解析エラー（PARSE_ERRORS）の場合は、その時点でコミットが行われそれ以降のデータはロードされません。キー重複エラー（DUPLICATE_ERRORS）の場合は、その時点でロールバックされてロード処理全体がなかったことになります。
スキップされた投入元レコードは以下項目で設定されたファイルに書き出されます。
PARSE_BADFILE = /path/of/bad/file DUPLICATE_BADFILE = /path/of/bad/file
なお、制約の検査はWRITER=BINARY と設定している場合には利用できません。
3. 注意事項
◆ クラッシュ時
pg_bulkload の実行途中で PostgreSQLがクラッシュ終了したり、pg_bulkload実行中にサーバダウンが生じた場合、データベースクラスタディレクトリ内に残骸データが残ってしまいます。このことは、$PGDATA/pg_bulkload に .loadstatus ファイルが残っているかで判別できます。
pg_bulkload には -r オプションがあり、これで不正な状態の解消（リカバリと呼びます）ができるのですが実行すべきタイミングは pg_ctl start をする前です。この時の推奨方法として postgresql スクリプトが用意されています。中身は pg_bulkload -r を実行し、pg_ctl start を行っているだけですが、起動前に pg_bulkload のリカバリをし忘れない目的で利用できます。
◆ PITR / レプリケーション
pg_bulkload は適切な WAL を残しません。
したがって、PITR 方式のオンラインバックアップを使っている場合には、pg_bulkload でのデータロードが完了した後にベースバックアップを再度取得する必要があります。同様に、ストリーミングレプリケーションを使っている場合には、pg_bulkload の完了後にスタンバイサーバを作り直す必要があります。
4. まとめ
pg_bulkload はデータを高速にロードすることに特化したツールです。PITR やレプリケーション環境で期待した通りにならない制約などありますが、サーバ移行の際に、元のサーバからすべてのデータを移動させるときに使えます。また定期的に溜まったデータをテーブルに流し込む際、テスト用の大量のデータを少しでも早く読み込ませたいときなどにもその力を発揮してくれるはずです。
本文書では触れませんでしたが、pg_bulkload には作成した任意のフィルタプログラムを指定して、データを変換しながら読み込む機能もあります。pg_bulkload のその他の機能・設定オプションについてはソフトウェア付属ドキュメントに詳細に記載されています。
pg_bulkloadドキュメント：http://ossc-db.github.io/pg_bulkload/pg_bulkload-ja.html