2018年 10月 の投稿一覧

pg_hint_plan (PostgreSQL 実行計画制御ツール)

1. pg_hint_planとは

pg_hint_plan は PostgreSQL のクエリの実行計画を制御するためのモジュールです。本モジュールにより、他のリレーショナルデータベースで一般的な機能であるヒント句を PostgreSQL で利用することが可能になります。ヒント句とは実行されるクエリの実行計画の決定をプランナに任せきりにせず、プランナに実行計画を直接指示することを可能とする機能です。

PostgreSQL ではクエリの実行計画はプランナが統計情報を用いて決定します。プランナのバグや使用する統計情報が正確でないことで不適切な実行計画が作成されたり、
刻々と変化する統計情報に依存する実行計画によってクエリの実行性能が不安定になる場合があります。pg_hint_plan を用いることで、プランナの不適切な実行計画を改善したり、統計情報に依存する不安定な実行計画を安定化することが可能になります。

2018年 10月時点では、PostgreSQL10.x のみに対応した 1.3.1 がリリースされています。そのほかも PostgreSQL バージョン 9.1 から 9.6 まで、それぞれに対応するパッケージを提供しています。
PostgreSQL 9.0.x 以前のバージョンに対応していないのは PostgreSQL 側にヒント句を追加できる枠組みが備わっていないためです。

2. 本文書の環境

本文書は、PostgreSQL 9.6.2 と pg_hint_plan 1.2.0 を使って、pg_hint_plan の Linux 上での使い方を解説します。

3. 導入方法

本節では CentOS 7.2 で pg_hint_plan 1.2.0 を導入する例を示します。PostgreSQL 9.6 が RPM でインストールされているものとします。

◆ インストール

pg_hint_plan のダウンロードページから pg_hint_plan96-1.2.0-1.el7.x86_64.rpm をダウンロードします。ダウンロード後はローカルファイルに対して yum や rpm コマンドでインストールを実行します。

# yum install pg_hint_plan96-1.2.0-1.el7.x86_64.rpm
もしくは
# rpm -ivh pg_hint_plan96-1.2.0-1.el7.x86_64.rpm

◆ データベースへの登録

pg_hint_plan モジュールを利用するには利用先データベースへの登録が必要になります。

$ psql -d db1 -c "CREATE EXTENSION pg_hint_plan"

データベースの登録が済むと次のオブジェクトが作成されます。

  • hint_plan スキーマ
  • hint_plan.hints テーブル
  • hint_plan.hints_pkey インデックス
  • hint_plan.hints_norm_and_app インデックス

◆ セッションへのロード

セッションにおいて pg_hint_plan を利用するには予めロードが必要になります。特定のセッションでのみ利用する場合は LOAD コマンドを実行してください。

db1=# LOAD ‘pg_hint_plan’;
LOAD

全セッションで利用する場合は PostgreSQL の 設定ファイル postgresql.conf の shared_preload_libraries パラメータに ‘pg_hint_plan’ を追加してください。

shared_preload_libraries = 'pg_hint_plan'

4. ヒントの使用

ヒントはクエリにコメントの形で与えます。ヒント用のコメントは通常のコメントと区別するために /*+ で始めるようにして、「/*+ ヒント */ クエリ」という書式で記述します。複数のヒントを指定する場合には空白文字で区切って並べます。

以下表に示すカテゴリのヒントが利用可能です。使用できるヒント一覧はマニュアルに記載があります。

<利用可能なヒントのカテゴリ>

スキャン方式 テーブルのスキャン方式を指定できます
結合順序 複数あるテーブルをどの順で結合するか指定できます
結合方式 テーブル結合の方式を指定できます
見積件数補正 テーブル結合結果の件数見積もりを補正する指定ができます
パラレル実行 パラレル実行を強制あるいは禁止する指定ができます
設定パラメータ SETコマンドで指定できるパラメータ設定をヒントから与えます

ヒント句一覧
http://pghintplan.osdn.jp/hint_list-ja.html
(上記ページは本文書作成時点では v1.1 の内容です)

◆ 実行例

ここから実際にヒントを与えて実行計画を変更する例を示します。この例ではヒントを指定することでプランナの計画した不適切な実行計画よりも適切な実行計画を指定しています。

本検証例のサンプルデータは以下の通り pgbench コマンドで作成しています。

$ pgbench -U postgres -i -s 10 db1
creating tables...
...
$ psql db1
db1=# VACUUM ANALYZE;
VACUUM

pgnemch コマンドで作成された pgbench_accounts テーブル、pgbench_branches テーブルに対して結合とソート操作を行うクエリの実行計画を検証します。

<ヒントを指定しない場合>

db1=# EXPLAIN ANALYZE
        SELECT * FROM pgbench_branches b
          JOIN pgbench_accounts a ON b.bid = a.bid ORDER BY a.aid;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.42..192378.55 rows=1000000 width=465) (actual time=0.033..2147.035 rows=1000000 loops=1)
   Join Filter: (b.bid = a.bid)
   Rows Removed by Join Filter: 9000000
   ->  Index Scan using pgbench_accounts_pkey on pgbench_accounts a  (cost=0.42..42377.43 rows=1000000 width=97) (actual time=0.025..210.312 rows=1000000 lo
ops=1)
   ->  Materialize  (cost=0.00..1.15 rows=10 width=364) (actual time=0.000..0.001 rows=10 loops=1000000)
         ->  Seq Scan on pgbench_branches b  (cost=0.00..1.10 rows=10 width=364) (actual time=0.003..0.005 rows=10 loops=1)
 Planning time: 0.222 ms
 Execution time: 2219.069 ms
(8 行)

ヒントを指定しない場合は Index Scan, Nested Loop が計画されました。
これに対して Seq Scan, Hash Join を計画するようにヒント指定してみます。SeqScan(a) はテーブル a をシーケンシャルスキャンすること、HashJoin(a b) はテーブル a , b をハッシュジョインすることを意味します。ヒント指定においては、クエリ内でテーブル名に別名が付けられている場合、ヒント指定では別名を使う点に注意してください。

<ヒントを指定した場合>

db1=# /*+ HashJoin(a b) SeqScan(a) */
      EXPLAIN ANALYZE
        SELECT * FROM pgbench_branches b
          JOIN pgbench_accounts a ON b.bid = a.bid ORDER BY a.aid;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=563632.07..566132.07 rows=1000000 width=465) (actual time=942.519..1081.707 rows=1000000 loops=1)
   Sort Key: a.aid
   Sort Method: external sort  Disk: 119264kB
   ->  Hash Join  (cost=1.23..40145.22 rows=1000000 width=465) (actual time=0.038..395.556 rows=1000000 loops=1)
         Hash Cond: (a.bid = b.bid)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..26394.00 rows=1000000 width=97) (actual time=0.020..103.247 rows=1000000 loops=1)
         ->  Hash  (cost=1.10..1.10 rows=10 width=364) (actual time=0.010..0.010 rows=10 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               ->  Seq Scan on pgbench_branches b  (cost=0.00..1.10 rows=10 width=364) (actual time=0.003..0.007 rows=10 loops=1)
 Planning time: 0.134 ms
 Execution time: 1201.389 ms
(11 行)

ヒントにより、指定にしたがったプラン実行をしていることが確認できました。

◆ ヒントを予め登録する

ヒントの与え方はクエリにコメントとして直接指定する方式の他に、ヒント用のテーブル hint_plan.hints に実行計画を制御したいクエリとヒントを登録しておく方式があります。テーブルでのヒントの指定を有効にするには、設定パラメータで pg_hint_plan_enable_hint_table = on と指定されている必要があります。

先ほどのクエリにテーブルでヒントを指定してみます。

まず、hint_plan.hints テーブルに指示対象のクエリ、アプリケーション名、ヒントを登録します。このとき、アプリケーション名に ’’(空文字列)を登録することで、どの名称のアプリケーションにも適用されます。また、ここでは実行計画検証のため、EXPLAIN ANALYZE を含めたクエリを登録していますが、実際に動作させるクエリでは除いて登録してください。

db1=# INSERT INTO hint_plan.hints
        (norm_query_string, application_name, hints)                                                                                                                                               
        VALUES (
          'EXPLAIN ANALYZE
           SELECT * FROM pgbench_branches b
           JOIN pgbench_accounts a ON b.bid = a.bid
           ORDER BY a.aid;',                                                                                                                                                                       
          '', 'HashJoin(a b c) SeqScan(a)');
INSERT 0 1

以下の通り、コメントでヒントを指定せずともヒントが適用されていることが確認できます。

db1=# EXPLAIN ANALYZE
      SELECT * FROM pgbench_branches b
        JOIN pgbench_accounts a ON b.bid = a.bid ORDER BY a.aid;
                                                                                                 QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Sort  (cost=563632.07..566132.07 rows=1000000 width=465) (actual time=923.405..1059.397 rows=1000000 loops=1)
   Sort Key: a.aid
   Sort Method: external sort  Disk: 119264kB
   ->  Hash Join  (cost=1.23..40145.22 rows=1000000 width=465) (actual time=0.018..384.143 rows=1000000 loops=1)
         Hash Cond: (a.bid = b.bid)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..26394.00 rows=1000000 width=97) (actual time=0.007..96.927 rows=1000000 loops=1)
         ->  Hash  (cost=1.10..1.10 rows=10 width=364) (actual time=0.006..0.006 rows=10 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               ->  Seq Scan on pgbench_branches b  (cost=0.00..1.10 rows=10 width=364) (actual time=0.001..0.003 rows=10 loops=1)
 Planning time: 0.225 ms
 Execution time: 1177.613 ms
(11 行)

テーブルでヒントを指定するときは以下の点に注意してください。

  • 指示対象クエリ末尾のセミコロン(;)の有無も一致する必要がある
  • 指示対象クエリ中の定数は ? で指定する必要がある
  • 「コメントでの指定」と「テーブルでの指定」の両方で指定した場合、「テーブルでの指定」が優先される

◆ Parallelヒント

もう一つ例を示します。pg_hint_plan 1.2.0 から PostgreSQL 9.6 新機能のパラレルクエリに対応しています。Parallel (テーブル名 ワーカ数 [強制度]) というヒントを指定することでクエリをパラレルクエリとして実行させることが可能です。

引き続き、pgbench で生成したテーブルを使って例を示します。

<ヒントを指定しない場合>

db1=# EXPLAIN ANALYZE  SELECT * FROM pgbench_accounts;
                                                                                                 QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Seq Scan on pgbench_accounts  (cost=0.00..26394.00 rows=1000000 width=97) (actual time=0.023..96.409 rows=1000000 loops=1)
 Planning time: 0.071 ms
 Execution time: 155.825 ms
(3 行)

以下の Parallel ヒントは、pgbench_accounts テーブルをワーカ数8で実行させるように指定していますが、実際はワーカ数3で実行されています。これは Parallel ヒントの強制度はデフォルトで soft 指定であり、max_parallel_workers_per_gather のみを変更してあとはプランナに任せるので、必ずしもワーカ数は指定した値で実行されるとは限りません。

<強制度 soft で Parallel ヒント指定する場合>

db1=# /*+ Parallel(pgbench_accounts 8) */
      EXPLAIN ANALYZE  SELECT * FROM pgbench_accounts;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..20619.81 rows=1000000 width=97) (actual time=0.152..358.230 rows=1000000 loops=1)
   Workers Planned: 3
   Workers Launched: 3
   ->  Parallel Seq Scan on pgbench_accounts  (cost=0.00..19619.81 rows=322581 width=97) (actual time=0.021..45.338 rows=250000 loops=4)
 Planning time: 0.106 ms
 Execution time: 430.357 ms
(6 行)

ワーカ数を強制するには強制度を hard に指定します。以下では、実際に指定したワーカ数 8 で実行されていることがわかります。

<強制度 hard で Parallel ヒント指定する場合>

b1=# /*+ Parallel(pgbench_accounts 8 hard) */
      EXPLAIN ANALYZE  SELECT * FROM pgbench_accounts;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Gather  (cost=0.00..17644.00 rows=1000000 width=97) (actual time=0.157..347.372 rows=1000000 loops=1)
   Workers Planned: 8
   Workers Launched: 8
   ->  Parallel Seq Scan on pgbench_accounts  (cost=0.00..17644.00 rows=125000 width=97) (actual time=0.013..20.577 rows=111111 loops=9)
 Planning time: 0.105 ms
 Execution time: 435.160 ms
(6 行)

◆ ログメッセージとデバッグ出力

クエリ実行時にヒントを解釈できなかった場合、クエリ自体はヒントが与えられなかったものとして通常通り実行されます。ヒントが適正でなかったことはログに出力されます。

以下に、存在しないヒントを入力したときの例を示します。

db1=# /*+ error_hint */
      EXPLAIN ANALYZE
        SELECT * FROM pgbench_branches b
          JOIN pgbench_accounts a ON b.bid = a.bid ORDER BY a.aid;

INFO:  pg_hint_plan: hint syntax error at or near "error_hint "
DETAIL:  Unrecognized hint keyword "error_hint".

このときのメッセージレベルは pg_hint_plan.parse_messages(デフォルト info)パラメータで指定できます。

また、pg_hint_plan.debug_print = on (デフォルト off)とすることで指定したヒントの実行計画への影響をログに出力させることが可能です。指定したヒントが以下 4 つのカテゴリに分類されて報告されます。

used hint 利用されたヒント
not used hint 利用されなかったヒント
duplication hint 矛盾するヒントが重複して利用されなかったヒント
error hint エラーが発生して利用されなかったヒント

pg_hint_plan.debug_print によるログ出力の例を示します。

LOG:  pg_hint_plan:
        used hint:
        SeqScan(a)
        HashJoin(a b)
        not used hint:
        duplication hint:
        error hint:

STATEMENT:  /*+ HashJoin(a b) SeqScan(a) */
EXPLAIN ANALYZE SELECT * FROM pgbench_branches b
  JOIN pgbench_accounts a ON b.bid = a.bid ORDER BY a.aid;

5. まとめ

本文書では pg_hint_plan について基本的な情報を取りまとめました。PostgreSQL では制御の難しい実行計画を容易に制御可能な pg_hint_plan は強力なツールといえます。
しかしながら、ヒントを利用するかどうかは慎重な検討してください。PostgreSQL のプランナは基本的には賢くかつ網羅的であり、コスト計算が適切であれば様々な実行計画の中から一番適切な実行計画が選択されるはずです。ヒントを指定しても正しい保証は無く、ある状況で適切に思えても他の多くの状況で不適切な計画を実行してしまう危険もはらんでいます。

◆ 関連サイトリンク

pg_bigm (bi-gram インデックス)

1. pg_bigm とは

pg_bigm は PostgreSQL に文字列の中間一致検索に使える GIN 方式のインデックスを提供するオープンソースの拡張モジュールです。以下のような B-tree インデックスを適用できない問い合わせを高速化することができます。

SELECT id FROM documents WHERE content LIKE '%文字列%';

bigm とは bi-gram (バイグラム)の略です。文字列中の連続する 2 文字ごとにインデックス項目を作ることを意味しています。
なお、 PostgreSQL 本体には追加モジュールとして pg_trgm という連続する 3 文字ごとにインデックス項目を作る tri-gram (トリグラム)のモジュールが付属しています。ただし、 pg_trgm はマルチバイト文字列が扱えないという制限があります。また、マルチバイト対応させたとしても、 2 文字から成る単語の検索には能力を発揮できませんので日本語には適しません。

2. 使用方法

◆ インストール

pg_bigm は最近の PostgreSQL バージョンに幅広く対応していますが、具体的な対応バージョンについては、導入する pg_bigm バージョンのドキュメントを確認してください。pg_bigm 1.2 バージョンのドキュメントには PostrgeSQL 9.1.x から 10.x に対応していると記載されています。
pg_bigm は以下ページでソースコードと rpm パッケージが配布されています。 rpm パッケージは PostgreSQL RPM Building Projectの PostgreSQL rpm パッケージと組み合わせて使うことができます。

ソフトウェア配布ページ: https://ja.osdn.net/projects/pgbigm/releases/

ソースコードから Linux/UNIX 上の PostrgeSQL むけに標準的な手順でビルド・インストールできます。以下のコマンドは導入する PostgreSQL のコマンドにパスが通った状態で実行します。

$ tar zxf pg_bigm-1.2-20161011.tar.gz
$ cd pg_bigm-1.2-20161011
$ make USE_PGXS=1
$ su -c 'make USE_PGXS=1 install'

pg_bigm は Windows 版 PostgreSQL むけにもビルド可能です。ただし、 pg_bigm 開発元で動作確認しているプラットフォームには含まれていません。また、ビルドには Visual Studio などの Windows むけ開発環境が必要です。使用には少しハードルが高いといえます。PostgreSQL の商用版である PowerGres on WindowsPowerGres Plus Windows 版には pg_bigm モジュールも含まれていますので、これらを利用しても良いかもしれません。

pg_bigm を使用するためには、 postgresql.conf で shared_preload_libraries に pg_bigm を加えます。これは PostgreSQL 再起動で反映されます。

shared_preload_libraries = 'pg_bigm'

そのうえで、 pg_bigm を使用したいデータベース上で CREATE EXTENSION コマンドを実行します。

db1=# CREATE EXTENSION pg_bigm;
CREATE EXTENSION

◆ インデックス検索の例

以下のテーブルを作ってテストしてみます。

db1=# CREATE TABLE t_book
          (id serial primary key, file text, line int, para text);

ここに日本語訳が書き加えられた PostgreSQL マニュアルのソースファイルの全パラグラフを格納します。テーブルの総物理サイズは 15MB 、レコード数は 24134 件となりました。

これに対してキーワード検索を実行してみます。インデックスは使えませんので、シーケンシャルスキャンが行われます。全件を調べる動作ですので、どのようなキーワードを与えても同程度の応答時間となります。

db1=# SELECT id, file, line FROM t_book WHERE para LIKE '%高速%';
  id   |          file          | line
-------+------------------------+-------
   202 | array.sgml             |   925
   256 | backup.sgml            |   511
(中略)
 24119 | xtypes.sgml            |   178
(151 rows)

Time: 94.252 ms

続いて、パラグラフ内の文書を格納している para カラムに pg_bigm によるインデックスを作ってみます。動作環境とデータ規模に応じてそれなりに時間がかかります。本環境では 13.8 秒を要しました。

db1=# CREATE INDEX idx_bigm_book ON t_book
        USING gin (para gin_bigm_ops);
CREATE INDEX
Time: 13759.958 ms

インデックス作成後、もう一度同じ問い合わせを実行すると、所要時間が 94 ms から 4.5 ms に短縮されました。

db1=# SELECT id, file, line FROM t_book WHERE para LIKE '%高速%';
  id   |          file          | line
-------+------------------------+-------
   202 | array.sgml             |   925
   256 | backup.sgml            |   511
(中略)
 24119 | xtypes.sgml            |   178
(151 rows)

Time: 4.543 ms

explain コマンドで実行プランを確認すると、 Bitmap Index Scan にて作成したインデックスが使われていることがわかります。

db1=# explain
       SELECT id, file, line FROM t_book WHERE para LIKE '%高速%';
                                 QUERY PLAN
------------------------------------------------------------------
 Bitmap Heap Scan on t_book  (cost=16.02..23.84 rows=2 width=23)
   Recheck Cond: (para ~~ '%高速%'::text)
   ->  Bitmap Index Scan on idx_bigm_book  (cost=0.00..16.02 rows=2 width=0)
         Index Cond: (para ~~ '%高速%'::text)
(4 rows)

◆ その他の機能

pg_bigm は類似度検索にも使うことができます。そのための関数と演算子が提供されます。検索のサンプル用に t_book テーブルのファイル名のカラムだけ取り出したテーブルを作って、 pg_bigm によるインデックスを作ります。

db1=# CREATE TABLE t_file AS SELECT DISTINCT file FROM t_book;
db1=# CREATE INDEX idx_bigm_file ON t_file USING gin (file gin_bigm_ops);

設定パラメータ pg_bigm.similarity_limit にヒットする類似度( 0 から 1 の範囲)の閾値を指定します。そのうえで「文字列が類似しているか」という =% 演算子を使って検索すると、以下のように少し間違った文字列から検索することができます。 =% 演算子はインデックスが無くとも利用可能です。

db1=# SET pg_bigm.similarity_limit TO 0.6;
SET
db1=# SELECT file FROM t_file WHERE file =% 'pgtrigrm.sgml';
    file
-------------
 pgtrgm.sgml
(1 row)

なお、本機能は比較する文字列全体の類似度です。文章に対して検索キーワードのあいまい検索ができるわけではない点に注意してください。

◆ チューニング

pg_bigm を使った検索の速度が遅いときに、設定パラメータ pg_bigm.gin_key_limit を調整して解決できる場合があります。
pg_bigm による LIKE 中間一致検索は、まずインデックス検索で 2 文字ペアが該当するものを拾い出し、その後 Recheck で正確に条件に一致するものを絞り込みます。
pg_bigm.gin_key_limit はインデックス検索の段階で使う bi-gram (切り出した 2 文字)の個数を制限するパラメータです。SET文で指定可能です。デフォルトは 0 で、制限なく全ての bi-gram を使うという意味です。設定値を 10 とすると、検索文字列から最大 10 個だけの bi-gram を使ってインデックス検索をして、余分にヒットした結果を Recheck で絞り込むという動作になります。
検索文字列が長い場合には、本パラメータを指定して制限を加えた方が速くなる場合があります。以下に例を示します。

db1=# SELECT id, file, line FROM t_book WHERE para LIKE '%例えば、あるトランザクションがすべての支店の残高を集計する作業を行なっているとき、アリスの口座がある支店からの引き落としを勘定に入れるけれども、ボブの口座がある支店への振り込みを勘定に入れないというのは受け入れられませんし、その逆も駄目です。%';
 id |     file      | line
----+---------------+------
 95 | advanced.sgml |  298
(1 row)

Time: 9.985 ms

db1=# SET pg_bigm.gin_key_limit TO 5;

db1=# SELECT id, file, line FROM t_book WHERE para LIKE '%例えば、あるトランザクションがすべての支店の残高を集計する作業を行なっているとき、アリスの口座がある支店からの引き落としを勘定に入れるけれども、ボブの口座がある支店への振り込みを勘定に入れないというのは受け入れられませんし、その逆も駄目です。%';
 id |     file      | line
----+---------------+------
 95 | advanced.sgml |  298
(1 row)

Time: 3.100 ms

◆ 注意事項

pg_bigm にはいくつかの欠点と制限事項があります。
pg_bigm はアルファベットの大文字小文字の同一視に対応していません。つまり、 LIKE 検索には適用可能ですが、 ILIKE 検索には使えず、また、類似度検索でも大文字と小文字も違いは単なる文字の違いとして扱われます。
また、 pg_bigm のインデックス対象文字列にはサイズ上限があります。約 100MB を超える文字列には適用できません。

3. まとめ

pg_bigm は、 PostgreSQL 上のデータに対する日本語の全文検索機能を実装する最も容易な手段といえます。外部にファイル等を持たず PostgreSQL 上のデータだけで処理が完結しますので、ストリーミングレプリケーションやバックアップに際して、追加的な考慮事項がありません。
全文検索にあたって pg_bigm を導入して効果があるのは、それなりにデータ量が多い場合、あるいは、頻繁にアクセスがあって1回の検索あたりの処理量を減らすことに意義がある場合といえます。インデックスが加われば、 INSERT や UPDATE にはインデックス更新の処理が加わることになりますので、導入するかどうか慎重な判断が必要となります。
提供される関数は本文書記載のもの以外にもいくつかあります。また、 pg_bigm のバージョンによって制限や対応 PostgreSQL バージョンの違いがあります。使用を計画する際には以下 URL のサイトにある pg_bigm のマニュアルを確認してください。

pg_bigm の Webサイト
http://pgbigm.osdn.jp/index.html

pg_rman (PostgreSQL のバックアップ/リストア管理ツール)

pg_rman とは

pg_rman とは PostgreSQL のバックアップ/リストア管理ツールです。

PostgreSQL 付属のバックアップ手段にはダンプと PITR があります。ダンプはリストアしてもバックアップした時点の状態まで戻ってしまいますが、PITR を用いればバックアップした時点から稼働時の直前まで任意の時点の状態にリストアできるといった利点があります。しかし、PITR のバックアップ/リストアは煩雑な手順が必要となります。

pg_rman は PITR の機能を簡易なコマンドで扱うことができます。また、バックアップの世代管理ができることも魅力の一つです。

2018年 10月時点の最新バージョンは 1.3.7 で、PostgreSQL 9.2.x から 10.x まで対応するパッケージが用意されており、Linux 上で動作します。各種の UNIX でも概ね動作すると考えられます。Windows には対応していません。
注意点としては、バージョン 1.3.7 は 1.3.6 以前のバージョンと互換性がなく、1.3.7 で復旧させるには 1.3.7 で取得したフルバックアップが必要となります。

詳細は pg_rman プロジェクトページをご覧ください。

pg_rman を用いたリストアの一例
PC 向けゲームポータルサイトのシステム構成図

機能

pg_rman は以下の機能を持っています。

  • 全体バックアップに加え、増分バックアップが可能
  • バックアップの圧縮が可能
  • バックアップの世代管理やバックアップ一覧を表示できる
  • バックアップの対象はデータベースクラスタの他にアーカイブログ、サーバログが含めることができる
  • タイムライン指定、リカバリしたい日付時刻指定が可能
  • データベースクラスタ外のテーブルスペースを含めたバックアップが可能

基本的な使い方

まず最初に pg_rman のインストールを行います。
ソースコードからのインストールの場合、ソースコードをダウンロードした後に以下のコマンドを実行します。

$ cd pg_rman
$ make USE_PGXS=1
$ make USE_PGXS=1 install

RPM パッケージも用意されており、そちらは pg_rman-*.rpm という RPM ファイルをインストールすれば完了です。

インストールを終えたら、バックアップファイルや管理情報を保存するバックアップカタログの初期化を行います。このとき、postgresql.conf の以下の設定をしておくことで、pg_rman の設定ファイルの初期値を設定することができます。

$ vi postgresql.conf
archive_mode = on
archive_command = 'cp %p /mnt/arch/%f'
wal_level = archive
log_directory = 'pg_log'

さらに以下のパスを環境変数化しておくと今後 pg_rman のコマンドが短くてすみます。
※ 実用環境ではなるべくバックアップカタログパスは DB 稼動ホスト内のパスではなく、ネットワークマウントしたストレージ内のパスを指定してください。

$ export BACKUP_PATH=<バックアップカタログパス>
$ export PGDATA=<PostgreSQL データベースクラスタパス>

バックアップカタログを初期化します。

$ pg_rman init

これで準備は完了です。次は実際にバックアップを取得してみましょう。以下は全体バックアップの例です。

$ pg_rman backup --backup-mode=full

バックアップが完了しました。ただし、後にバックアップの検証を行う必要があります。

$ pg_rman validate

validate によるバックアップの検証はバックアップ直後に行う必要はありませんが、なるべく早い段階で行なってください。検証がなされていないバックアップはリストアができませんし、次回の増分バックアップも行えません。

全体バックアップを行なった後は増分バックアップが可能です。増分バックアップは前回のバックアップから変更のあったファイル・ページのみのバックアップです。下の例では–with-serverlog を指定することでサーバログもバックアップとして取得しています。ここでもやはり、増分バックアップもバックアップの検証を行ないます。

$ pg_rman backup --backup-mode=incremental --with-serverlog
$ pg_rman validate

では次にバックアップが取得できているか確認してみましょう。

$ pg_rman show
============================================================================
Start                Time   Total    Data     WAL     Log  Backup   Status
============================================================================
2014-08-06 19:15:18    0m    ----    16kB    67MB    ----    67MB   OK
2014-08-06 19:09:18    0m    20MB    ----    50MB    ----    66MB   OK

show コマンドによって今まで取得したバックアップの一覧表示が可能です。バックアップの開始時刻が新しいほうが上に表示されていることに注意してください。Status が OK となっていればリストアに利用することができます。

では、取得したバックアップからデータベースクラスタをリストアしてみましょう。現在のデータベースクラスタは削除されるのでご注意ください。

まず、リストアを行う前に PostgreSQL サーバを停止してください。

$ pg_ctl stop -m immediate

なるべく最新の状態に戻したい場合は特にオプションを付けずに restore コマンドを実行します。このとき、既存のデータベースクラスタが残っている場合はアーカイブ前の WAL がバックアップされます。また、その WAL は次の全体バックアップを取得するまで保存されます。

$ pg_rman restore

任意の時点の内容をリストアしたい、例えば2014年8月6日の19:12:00の時点に遡りたいときは以下のように入力します。

$ pg_rman restore --recovery-target-time '2014-08-06 19:12:00'

リストアが終了したら、PostgreSQL サーバを起動してください。起動と同時にリカバリが実行されます。

$ pg_ctl start

応用的な使い方

バックアップの情報の詳細表示

show コマンドにより過去に取得したバックアップ一覧が表示できましたが、引数を追加することでより詳細な情報を得ることができます。

  • timeline を追加し、バックアップのタイムラインとバックアップモードを表示
    $ pg_rman show timeline
    ============================================================
    Start                Mode  Current TLI  Parent TLI  Status
    ============================================================
    2014-08-06 19:15:18  INCR            1           0  OK
    2014-08-06 19:09:18  FULL            1           0  OK
    
  • Start 列に示された日付時刻を指定して、そのバックアップに関する詳細を表示
    $ pg_rman show '2014-08-06 19:15:18'
    # configuration
    BACKUP_MODE=INCREMENTAL
    WITH_SERVERLOG=false
    COMPRESS_DATA=false
    # """"""""result
    TIMELINEID=1
    START_LSN=0/07000028
    STOP_LSN=0/070000b8
    START_TIME"""""""" ='2014-08-06 19:15:18'
    END_TIME='2014-08-06 """"""""19:15:20'
    RECOVERY_XID=1822
    RECOVERY_TIME"""""""" ='2014-08-06 19:15:20'
    TOTAL_DATA_BYTES=20876021
    READ_DATA_BYTES=16604
    READ_ARCLOG_BYTES=67109496
    WRITE_BYTES=67126100
    BLOCK_SIZE=8192
    XLOG_BLOCK_SIZE=8192
    STATUS=OK

一番古いバックアップの内容をリカバリする場合、RECOVERY_TIME を確認しておきましょう。なぜなら、最初のバックアップ時に戻す Start 列に示された日付時刻ではなく RECOVERY_TIME を指定する必要があるからです。(つまり、pg_rman がリカバリ可能なのは一番古いバックアップの RECOVERY_TIME からの時点ということです。)

レプリケーション使用時のスタンバイ側バックアップ

PostgreSQL 9.0 以降のレプリケーションをお使いの場合、スタンバイ側からバックアップを取得することが可能です。単体サーバ利用時とはバックアップ時の指定が異なります。以下の設定の場合のコマンドを示します。

マスタサーバのホスト名:master
スタンバイサーバのホスト名:localhost
スタンバイサーバのポート名:5432

$ pg_rman backup --backup-mode=full --host=master --standby-host=localhost --standby-port=5432

バックアップの削除

必要のないバックアップを delete コマンドで削除できます。

$ pg_rman delete <リストアが必要な日付時刻>

ここで、リストアが必要な日付時刻を指定していることに注意してください。つまり、delete コマンドは指定した日付時刻のバックアップを削除するのではなく、指定した日付時刻へのリカバリに不必要なバックアップを削除しています。

また、バックアップ時にデータの保持期限(世代数か保存日数)を指定し、保持期限を過ぎると削除する設定が可能です。(削除されるタイミングは次回の backup コマンド実行時です。)

(少なくとも 3 世代以上、かつ、10 日間以上保存)

$ pg_rman backup --keep-data-generations=3 --keep-data-days=10

バックアップデータ以外にアーカイブ WAL のみの保持期限やサーバログのみの保持期限に関しても同様に指定できます。

pg_basebackup との比較

pg_basebackup コマンドは PostgreSQL 9.1 から標準で使用できるようになったオンラインバックアップコマンドです。このコマンドの登場により、以前よりもベースバックアップの取得が容易になりました。

pg_rman と比較すると以下の様なことが言えます。

    • pg_rman が優れている点
      • バックアップの世代管理が可能
      • アーカイブログ、サーバログもバックアップ対象にできる
      • 増分バックアップが可能
      • PostgreSQL 8.2 から対応

 

  • pg_basebackup が優れている点
    • リモート操作が可能
    • PostgreSQL の標準コマンドである

おわりに

PostgreSQL バックアップ管理ツール pg_rman はいかがだったでしょうか。

PostgreSQL 9.1 から pg_basebackup コマンドが登場し、バックアップ作業が簡易になるという利点こそ薄れましたが、バックアップの世代管理が可能な点など、まだまだ pg_rman に軍配が上がる点は多いようです。

今回紹介しきれなかった機能やオプションなどは日本語マニュアルがありますので、ユーザマニュアルをご覧ください。