ククログ

株式会社クリアコード > ククログ > pglogicalとPGroongaを使ったレプリケーション対応の高速日本語全文検索可能なPostgreSQLクラスターの作り方

pglogicalとPGroongaを使ったレプリケーション対応の高速日本語全文検索可能なPostgreSQLクラスターの作り方

PostgreSQLは標準機能ではインデックスを使った日本語全文検索機能がありません。PostgreSQLでインデックスを使った高速な日本語全文検索を実現する拡張機能にはPGroonga（ぴーじーるんが）があります。しかし、PGroongaはPostgreSQL標準のレプリケーション機能を使えません。これは、PostgreSQLが拡張機能で追加したインデックスのレプリケーションをサポートしていないからです。

レプリケーション機能を提供する拡張機能があり、それを使うとPGroongaでもレプリケーションを実現できます。たとえば、pg_shardを使う方法があります。

ここでは、別の方法としてpglogicalを使う方法を紹介します。

なお、pglogicalの開発者はPostgreSQL 9.6へpglogicalを含めることを提案しています。どうなるかはわかりませんが、もしかしたら、将来のPostgreSQLにはpglogicalが含まれているかもしれません。

構築方法

pglogicalとPGroongaを使ったレプリケーション対応の高速日本語全文検索可能なPostgreSQLクラスターの構築方法を説明します。

ディストリビューションはCentOS 7を使い、PostgreSQLは9.5を使います。

クラスターには次のノードがあるとします。

役割	ホスト名	IPアドレス
マスターノード	master	192.168.0.16
スレーブノード1	slave1	192.168.0.17
スレーブノード2	salve2	192.168.0.18

このクラスターではblogデータベースをレプリケーションします。

それでは、まずはマスターノードをセットアップし、その後スレーブノードをセットアップします。

マスターノードのセットアップ

PostgreSQLをパッケージでインストールします。

% sudo rpm -ivh http://yum.postgresql.org/9.5/redhat/rhel-$(rpm -qf --queryformat="%{VERSION}" /etc/redhat-release)-$(rpm -qf --queryformat="%{ARCH}" /etc/redhat-release)/pgdg-centos95-9.5-2.noarch.rpm
% sudo -H yum install -y postgresql95-server
% sudo -H /usr/pgsql-9.5/bin/postgresql95-setup initdb
% sudo -H systemctl enable postgresql-9.5

pglogicalをパッケージでインストールします。詳細はpglogicalのインストールドキュメント（英語）を参照してください。

% sudo -H yum install -y postgresql95-contrib
% sudo -H yum install -y http://packages.2ndquadrant.com/pglogical/yum-repo-rpms/pglogical-rhel-1.0-1.noarch.rpm
% sudo -H yum install -y postgresql95-pglogical

PGroongaをパッケージでインストールします。詳細はPGroongaのインストールドキュメントを参照してください。

% sudo -H yum install -y http://packages.groonga.org/centos/groonga-release-1.1.0-1.noarch.rpm
% sudo -H yum install -y postgresql95-pgroonga

これで必要なパッケージはすべてインストールできたので設定をします。

まずはpostgresql.confを設定します。

外部からの接続を受け付けるようにするため、listen_addressを*にします。

/var/lib/pgsql/9.5/data/postgresql.conf:

listen_addresses = '*'

続いてpglogical用の設定をします。詳細はpglogicalのドキュメント（英語）を参照してください。

次の項目を設定します。

項目	値
`wal_level`	`'logical'`
`max_replication_slots`	`2`
`max_wal_senders`	`2`
`shared_preload_libraries`	`'pglogical'`

max_replication_slotsとmax_wal_sendersが2なのは、今回の例ではスレーブノードが2台だからです。スレーブノードの数だけこの値を増やす必要があります。

なお、max_worker_processesをレプリケーションするデータベースの数以上に設定する必要もありますが、今回はデフォルト（8）から変更しません。理由は、今回の例ではblogデータベースだけをレプリケーションするからです。1つなのでデフォルトの8で十分だからです。

具体的には次のように設定します。

/var/lib/pgsql/9.5/data/postgresql.conf:

wal_level = 'logical'

max_replication_slots = 2
max_wal_senders       = 2

shared_preload_libraries = 'pglogical'

続いてpg_hba.confを設定します。

次の2つの設定を追加します。

後で作成するデータベース操作用のユーザー（blog_user）がローカルネットワークからblogデータベースへパスワード接続することを許可
後で作成するレプリケーション用のユーザー（blog_replication）がスレーブノードからのレプリケーションのためにパスワード接続することを許可

/var/lib/pgsql/9.5/data/postgresql.conf:

host blog        blog_user        192.168.0.0/24 md5
host replication blog_replication 192.168.0.0/24 md5

これで起動前の設定は完了したのでPostgreSQLを起動します。

% sudo -H systemctl start postgresql-9.5

まず、データベースを使用するユーザーblog_userを作成します。パスワードはuser_passwordにしたとします。

% sudo -u postgres -H createuser blog_user --pwprompt

続いて、レプリケーション時にスレーブノードから接続するユーザーblog_replicationを作成します。パスワードはreplication_passwordにしたとします。

% sudo -u postgres -H createuser blog_replication --pwprompt --replication

blogデータベースを作成します。blog_userをオーナーにします。

% sudo -u postgres -H createdb --owner blog_user blog

blogデータベースをセットアップします。これはスーパーユーザーで実行する必要があります。

% sudo -u postgres -H psql blog

データベースにpglogicalをインストールします。詳細はpglogicalのドキュメント（英語）を参照してください。

CREATE EXTENSION pglogical;
GRANT USAGE ON SCHEMA pglogical TO blog_user;
GRANT USAGE ON SCHEMA pglogical TO blog_replication;
GRANT SELECT ON ALL TABLES IN SCHEMA pglogical TO blog_replication;

PGroongaもインストールします。詳細はPGroongaのドキュメントを参照してください。

CREATE EXTENSION pgroonga;
GRANT USAGE ON SCHEMA pgroonga TO blog_user;

スーパーユーザー権限が必要なのはここまでです。blog_userで接続しなおします。

% psql --user blog_user --host 192.168.0.16 blog

テーブルとPGroongaのインデックスを作成します。いくつかデータも投入します。PGroongaのインデックス作成方法・使い方についてはPGroongaのチュートリアルを参照してください。

CREATE TABLE posts (
  id text PRIMARY KEY,
  title text NOT NULL,
  body text NOT NULL
);
CREATE INDEX posts_full_text_index ON posts USING pgroonga (id, title, body);
INSERT INTO posts VALUES ('2016-03-20-pgroonga',
                          'はじめてのPGroonga',
                          'PGroongaを使いはじめました！');
INSERT INTO posts VALUES ('2016-03-21-pglogical',
                          'pglogicalにトライ',
                          'pglogicalを試しています。PGroongaと一緒に使えるかな？');
INSERT INTO posts VALUES ('2016-03-22-pgroonga-and-pglogical',
                          'PGroongaとpglogical',
                          'pglogicalとPGroongaを一緒に使えました！');

インデックスを使って「一緒」が含まれる投稿を全文検索してみます。

SET enable_seqscan = off;
SELECT body, pgroonga.score(posts) FROM posts WHERE body %% '一緒';
--                          body                          | score 
-- -------------------------------------------------------+-------
--  pglogicalを試しています。PGroongaと一緒に使えるかな？ |     1
--  pglogicalとPGroongaを一緒に使えました！               |     1
-- (2 行)

うまく動いていますね。

それでは、blogデータベースをレプリケーション対象にしましょう。

まず、ノードとして登録します。これは1度だけやる処理です。詳細はpglogicalのドキュメント（英語）を参照してください。

SELECT pglogical.create_node(
  node_name := 'master',
  dsn := 'host=192.168.0.16 port=5432 dbname=blog'
);

現在のblogデータベース（のpublicスキーマ）内のすべてのテーブルをレプリケーション対象にします。

SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

新しくテーブルを追加したときはこの処理を再度実行する必要があることに注意してください。そうしないとレプリケーション対象になりません。これはハマりポイントなので最後に改めて言及します。

これでマスターのセットアップは完了です。

スレーブノードのセットアップ

スレーブノードのセットアップは基本的なセットアップはマスターノードと同じですが、省略せずに説明します。

PostgreSQLをパッケージでインストールします。

% sudo rpm -ivh http://yum.postgresql.org/9.5/redhat/rhel-$(rpm -qf --queryformat="%{VERSION}" /etc/redhat-release)-$(rpm -qf --queryformat="%{ARCH}" /etc/redhat-release)/pgdg-centos95-9.5-2.noarch.rpm
% sudo -H yum install -y postgresql95-server
% sudo -H /usr/pgsql-9.5/bin/postgresql95-setup initdb
% sudo -H systemctl enable postgresql-9.5

pglogicalをパッケージでインストールします。詳細はpglogicalのインストールドキュメント（英語）を参照してください。

% sudo -H yum install -y postgresql95-contrib
% sudo -H yum install -y http://packages.2ndquadrant.com/pglogical/yum-repo-rpms/pglogical-rhel-1.0-1.noarch.rpm
% sudo -H yum install -y postgresql95-pglogical

PGroongaをパッケージでインストールします。詳細はPGroongaのインストールドキュメントを参照してください。

% sudo -H yum install -y http://packages.groonga.org/centos/groonga-release-1.1.0-1.noarch.rpm
% sudo -H yum install -y postgresql95-pgroonga

これで必要なパッケージはすべてインストールできたので設定をします。

まずはpostgresql.confを設定します。

外部からの接続を受け付けるようにするため、listen_addressを*にします。

/var/lib/pgsql/9.5/data/postgresql.conf:

listen_addresses = '*'

続いてpglogical用の設定をします。詳細はpglogicalのドキュメント（英語）を参照してください。

次の項目を設定します。

項目	値
`max_replication_slots`	`1`
`shared_preload_libraries`	`'pglogical'`

pglogicalでは複数のマスターノードからレプリケーションすることもできます。その場合は、max_replication_slotsとmax_worker_processesをマスターノード数以上に設定する必要があります。

今回はmax_worker_processesはデフォルト（8）から変更しません。理由は、今回の例ではマスターノードが1つなのでデフォルトの8で十分だからです。

具体的には次のように設定します。

/var/lib/pgsql/9.5/data/postgresql.conf:

wal_replication_slots = 1

shared_preload_libraries = 'pglogical'

続いてpg_hba.confを設定します。なお、この設定はpglogicalの設定ではなく一般的な設定です。自分の利用方法に合わせて調整してください。ただし、データベース操作用のユーザー名はマスターノードと合わせておいた方がデフォルト設定を使えるため便利です。

次の設定を追加します。

後で作成するデータベース操作用のユーザー（blog_user）がローカルネットワークからblogデータベースへパスワード接続することを許可

/var/lib/pgsql/9.5/data/postgresql.conf:

host blog blog_user 192.168.0.0/24 md5

これで起動前の設定は完了したのでPostgreSQLを起動します。

% sudo -H systemctl start postgresql-9.5

データベースを使用するユーザーblog_userを作成します。

% sudo -u postgres -H createuser blog_user --pwprompt

レプリケーション初期化時のデータ同期時に必要になる（マスターノードでGRANT ... TO blog_replicationしているため）のでblog_replicationユーザーを作成します。ログインするわけではないのでログイン不可にします。

% sudo -u postgres -H createuser blog_replication --no-login

blogデータベースを作成します。blog_userをオーナーにします。

% sudo -u postgres -H createdb --owner blog_user blog

blogデータベースをセットアップします。これはスーパーユーザーで実行する必要があります。

% sudo -u postgres -H psql blog

データベースにpglogicalをインストールします。詳細はpglogicalのドキュメント（英語）を参照してください。

CREATE EXTENSION pglogical;
GRANT USAGE ON SCHEMA pglogical TO blog_user;

PGroongaもインストールします。詳細はPGroongaのドキュメントを参照してください。

CREATE EXTENSION pgroonga;
GRANT USAGE ON SCHEMA pgroonga TO blog_user;

スーパーユーザー権限が必要なのはここまでです。blog_userで接続しなおします。接続先のIPアドレスは捜査対象のスレーブノードのIPアドレスになっているか確認してください。

% psql --user blog_user --host 192.168.0.17 blog

まず、ノードとして登録します。これは1度だけやる処理です。スレーブノードごとnode_nameを変えてください。以下は詳細はpglogicalのドキュメント（英語）を参照してください。

なお、ここで設定した接続情報はレプリケーション開始時にデータベースの内容を同期するときに使われます。その際、スーパーユーザー権限が必要になるのでスーパーユーザーの接続情報を指定してください。以下の設定ではUNIXドメインソケット経由でpostgresユーザーで接続します。（pglogical.create_subscriptionのオプションで同期を無効にできます。その場合はスーパーユーザー権限は必要ありません。）

SELECT pglogical.create_node(
  node_name := 'slave1',
  dsn := 'dbname=blog'
);

レプリケーションを開始します。これも1度だけやる処理です。subscription_nameはスレーブごとに違う値にします。hostはマスターノードのIPアドレス（またはホスト名）にします。

SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=192.168.0.16 port=5432 dbname=blog user=blog_replication password=replication_password'
);

レプリケーションを開始するとデータを同期するのでpostsテーブルができてデータが入っています。

SELECT * FROM posts;
--                 id                 |        title        |                      
--    body                          
-- -----------------------------------+---------------------+----------------------
-- ---------------------------------
--  2016-03-20-pgroonga               | はじめてのPGroonga  | PGroongaを使いはじめ
-- した！
--  2016-03-21-pglogical              | pglogicalにトライ   | pglogicalを試していま
-- す。PGroongaと一緒に使えるかな？
--  2016-03-22-pgroonga-and-pglogical | PGroongaとpglogical | pglogicalとPGroongaを
-- 一緒に使えました！
-- (3 行)

もちろん、PGroongaを使った高速日本語全文検索も動きます。

SET enable_seqscan = off;
SELECT body, pgroonga.score(posts) FROM posts WHERE body %% '一緒';
--                          body                          | score 
-- -------------------------------------------------------+-------
--  pglogicalを試しています。PGroongaと一緒に使えるかな？ |     1
--  pglogicalとPGroongaを一緒に使えました！               |     1
-- (2 行)

マスターノードでデータを追加するとスレーブノードから参照できます。

マスターノードで実行：

INSERT INTO posts VALUES ('2016-03-23-pgroonga-postgresql-96',
                          'PostgreSQL 9.6でPGroonga',
                          'PGroongaはPostgreSQL 9.6と一緒でも使えた！');

スレーブノードで実行：

SET enable_seqscan = off;
SELECT body, pgroonga.score(posts) FROM posts WHERE body %% '一緒';
--                          body                          | score 
-- -------------------------------------------------------+-------
--  pglogicalを試しています。PGroongaと一緒に使えるかな？ |     0
--  pglogicalとPGroongaを一緒に使えました！               |     0
--  PGroongaはPostgreSQL 9.6と一緒でも使えた！            |     0
-- (3 行)

どちらのスレーブノードでも新しく追加したレコードがヒットします。

まとめ

pglogicalとPGroongaを使ったレプリケーション対応の高速日本語全文検索可能なPostgreSQLクラスターの作り方を説明しました。

pglogicalのドキュメントの「4. Limitations and Restrictions」にある通り、いくつか制限はありますがレコードの追加・更新・削除をしてもスレーブノードで検索できるという基本的なことは実現可能です。制限は自分のユースケースでクリティカルなものか確認し、pglogicalの使用を検討してください。なお、制限とは、たとえば、DDL（CREATE TABLEなど）はレプリケーションされない、PRIMARY KEYがないと更新・削除がレプリケーションされない、などです。

DDLがレプリケーションされないことが運用に与える影響について少し補足します。

DDLがレプリケーションされないということはCREATE TABLEがレプリケーションされないということです。つまり、マスターノードでテーブルを作成したらスレーブノードでも同じテーブルを定義する必要があるということです。さらに、テーブルを定義するだけではレプリケーションされないなので、テーブルを作ったらレプリケーションの設定も更新する必要があります。具体的にはマスターノードで次のSQLを実行します。（pglogicalのドキュメントではトリガーでこの作業を自動化する方法を紹介しています。）

SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

次の順序で操作した場合はこれだけでOKです。

マスターノードでテーブル作成
スレーブノードでテーブル作成
マスターノードでpglogical.replication_set_add_all_tablesを実行
マスターノードでデータ追加

しかし、次のようにデータを追加してからpglogical.replication_set_add_all_tablesを実行した場合はもう1つやることがあります。

マスターノードでテーブル作成
スレーブノードでテーブル作成
マスターノードでデータ追加
マスターノードでpglogical.replication_set_add_all_tablesを実行

次のようにスレーブノードでpglogical.alter_subscription_resynchronize_tableを実行します。

SELECT pglogical.alter_subscription_resynchronize_table(
    subscription_name := 'subscription1',
    relation := 'posts'
);

これでデータが同期されます。

それでは、pglogicalとPGroongaで高速日本語全文検索を実現してください。

ククログ

pglogicalとPGroongaを使ったレプリケーション対応の高速日本語全文検索可能なPostgreSQLクラスターの作り方

構築方法

マスターノードのセットアップ

スレーブノードのセットアップ

まとめ

関連情報