ロジカルレプリケーションの紹介 (1)

本記事では、PostgreSQL 10 より導入された新たなレプリケーション方式「ロジカルレプリケーション」の概要、構築方法について紹介します。

ロジカルレプリケーションの概要

ロジカルレプリケーションとは

ロジカルレプリケーションは PostgreSQL 10 より導入されたレプリケーションの機能です。レプリケーション元となる PostgreSQL でパブリケーション（publication・発行者）を作成し、レプリケーション先となる PostgreSQL でサブスクリプション（subscription・購読者）を作成することで、レプリケーション機能が有効となります。また、レプリケーション元の PostgreSQL をパブリッシャ、レプリケーション先の PostgreSQL をサブスクライバと呼びます。まずはストリーミングレプリケーションとの違いを見ながらロジカルレプリケーションの概要をご説明いたします。

※本記事では PostgreSQL 12 を想定しています。

ロジカルレプリケーションとストリーミングレプリケーションの違い

ロジカルレプリケーションとストリーミングレプリケーションの違いは以下のとおりです。

(*1) PostgreSQL 10 では TRUNCATE はレプリケーションされませんので TRUNCATE の代わりに DELETE で削除する必要があります。
(*2) シーケンス、ラージオブジェクト、ビューなど。

ロジカルレプリケーションを活用できる状況

上記表でご確認いただいたように、下記のようなことを行いたい場合、ロジカルレプリケーションが役立つのではないかと考えられます。

• テーブル単位でのレプリケーションを行いたい
• メジャーバージョンが異なる PostgreSQL 間でのレプリケーションを行いたい
• サブスクライバ側でデータを受け取った際に、トリガを起動させたい
• ストリーミングレプリケーションで発生する「マスタの更新と、スタンバイの参照の競合」を回避したい

ロジカルレプリケーション環境の構築

実際にパブリケーションとサブスクリプションを作成して、ロジカルレプリケーションが動作する環境を構築していきます。

以降の解説内では

• パブリッシャとなる PostgreSQL のサーバ名を first_server、データベース名を first
• サブスクライバとなる PostgreSQL のデータベース名を second
• ロジカルレプリケーション用のユーザ名を logi_user

としています。

そのため、

• first=# で始まる SQL はパブリッシャで実行するコマンド
• second=# で始まる SQL はサブスクライバで実行するコマンド

となります。

postgresql.conf の設定

ロジカルレプリケーションを機能させるには、パブリッシャの wal_level が logical である必要があります。wal_level のデフォルト値は replica ですので、パブリッシャの postgresql.conf で下記のように wal_level を logical に変更します。

wal_level = 'logical'

その他、ロジカルレプリケーションに関連するパラメータは

• max_logical_replication_workers
• max_replication_slots
• max_sync_workers_per_subscription
• max_wal_senders
• max_worker_processes

があります。機能させたいロジカルレプリケーションの数によっては、上記パラメータの値を調整する必要があります。それぞれのパラメータに必要な設定値は下記マニュアルをご確認ください。

[ 参考マニュアル – 論理レプリケーション 構成設定 ]

pg_hba.conf の設定

サブスクライバの PostgreSQL からパブリッシャの PostgreSQL に接続できるように、パブリッシャの pg_hba.conf に設定を加えます。

host  first           logi_user           xxx.xxx.xxx.xxx/32      md5

パブリケーションの作成

レプリケーション元となるパブリッシャで、パブリケーションを作成します。パブリケーションには、1つのテーブルだけでなく、複数のテーブルを指定できますので、複数のテーブルをまとめてレプリケーションできます。また、オプションでロジカルレプリケーションの対象とする操作（INSERT、UPDATE、DELETE、TRUNCATE）を指定できます。たとえば、INSERT のみをレプリケーションさせることができます。

ただし、UPDATE と DELETE をレプリケーションするためには、パブリッシャのテーブルの REPLICA IDENTITY （行を一意に特定できるカラムの情報）の設定が必要となります。テーブルに主キーが存在する場合は自動的に REPLICA IDENTITY に主キーが設定されます。主キーが存在しないテーブルに REPLICA IDENTITY の指定（USING INDEX または FULL）がない場合、UPDATE および DELETE を実行した際に、実行エラーが発生しますのでご注意ください。

ロジカルレプリケーション用のテーブル作成

ロジカルレプリケーションを行うためのテーブル logical_table をパブリッシャに作成します。

first=# CREATE TABLE logical_table
        (id INTEGER PRIMARY KEY, no INTEGER , data TEXT);

パブリケーションの作成例

作成したテーブルに対して行った操作をサブスクライバに伝搬させるためのパブリケーションを作成します。パブリケーションの作成例を下記に記載します。

-- logical_table に対する操作を伝搬させるパブリケーションの作成
first=# CREATE PUBLICATION logical_pub FOR TABLE logical_table;

-- すべてのテーブルに対する操作を伝搬させるパブリケーションの作成
first=# CREATE PUBLICATION logical_pub_all_table FOR ALL TABLES;

-- logical_table に対する INSERT を伝搬させるパブリケーションの作成
first=# CREATE PUBLICATION logical_pub_insert_only
        FOR TABLE logical_table WITH (publish = 'insert');

-- パブリケーションの枠だけ作成
first=# CREATE PUBLICATION logical_pub_non_table ;

※各パラメータの詳細は下記マニュアルをご確認ください。

[ 参考マニュアル – CREATE PUBLICATION ]

パブリケーションの確認

pg_publication システムカタログを参照することで、作成されているパブリケーションを確認できます。

first=# SELECT * FROM pg_publication;

  oid  |    pubname    | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate
-------+---------------+----------+--------------+-----------+-----------+-----------+-------------
 16651 | logical_pub   |       10 | f            | t         | t         | t         | t
(1 行)

psql であればメタコマンド \dRp で確認できます。

first=# \dRp
                               パブリケーション一覧
    名前     |  所有者  | 全テーブル | Insert文 | Update文 | Delete文 | Truncate文
-------------+----------+------------+----------+----------+----------+------------
 logical_pub | postgres | f          | t        | t        | t        | t
(1 行)

パブリケーションとテーブルのマッピングを確認する

pg_publication_tables システムカタログで、パブリケーションとそれに含まれるテーブルのマッピングを確認できます。

first=# SELECT * FROM pg_publication_tables;

    pubname   | schemaname | tablename
--------------+------------+---------------
 logical_pub  | public     | logical_table
(1 行)

上記の出力から、logical_pub というパブリケーションに logical_table テーブルがマッピングされていることが分かります。

パブリケーションとテーブルのマッピングを、名称ではなく oid で確認するには pg_publication_rel システムカタログを参照します。

first=# select * from pg_publication_rel;
  oid  | prpubid | prrelid
-------+---------+---------
 16665 |   16664 |   16658
(1 行)

サブスクリプションの作成

パブリケーションを作成できたら、今度はパブリケーションから送られる更新情報を受け取るためのサブスクリプションをサブスクライバに作成します。

ロジカルレプリケーション用のテーブル作成

サブスクライバにもパブリッシャと同様のテーブルを作成します。これがロジカルレプリケーションの受け口になるテーブルです。

サブスクライバのテーブルは、パブリッシャのテーブルと

• スキーマ名
• テーブル名
• 列名

が同じである必要があります。サブスクライバのテーブル定義はパブリッシャのテーブルと完全に同一である必要はなく、サブスクライバのテーブル定義がパブリッシャのテーブル定義を内包しているのであれば、ロジカルレプリケーションが行われます。

下記の作成例では、パブリッシャのテーブル定義に加えて data2 カラムが存在しますが、id, no, data カラムがパブリッシャのテーブル定義と同一なためロジカルレプリケーションが行われます。

second=# CREATE TABLE logical_table
         (id INTEGER PRIMARY KEY, no INTEGER, data TEXT, data2 TEXT);

サブスクリプションの作成例

パブリッシャへの接続文字列などを指定して、サブスクリプションを作成します。サブスクリプションの作成例を下記に記載します。

-- パブリッシャの logical_pub パブリケーションに対する操作を受け取るための
-- サブスクリプションを作成
second=# CREATE SUBSCRIPTION logical_sub
         CONNECTION 'host=first_server port=5432 user=logi_user dbname=first'
         PUBLICATION logical_pub;

-- パブリッシャに作成するレプリケーションスロット名（slot_name）を指定して、
-- サブスクリプションを作成
-- (slot_name を指定しない場合、レプリケーションスロット名にはサブスクリプション名が設定されます)
second=# CREATE SUBSCRIPTION logical_sub
         CONNECTION 'host=first_server port=5432 user=logi_user dbname=first'
         PUBLICATION logical_pub WITH (slot_name = 'logical_sub_slot');

-- パブリッシャに通知するアプリケーション名（application_name）を指定して、
-- サブスクリプションを作成
-- (application_name を指定しない場合、アプリケーション名にはサブスクリプション名が設定されます)
second=# CREATE SUBSCRIPTION logical_sub
         CONNECTION 'host=first_server port=5432 user=logi_user dbname=first application_name=logical_sub_app'
         PUBLICATION logical_pub;

-- コミットの同期モード（synchronous_commit）を指定して、サブスクリプションを作成
-- （postgresql.conf で設定した synchronous_commit よりも優先されます）
-- （synchronous_commit を指定しない場合、off が設定されます）
second=# CREATE SUBSCRIPTION logical_sub
         CONNECTION 'host=first_server port=5432 user=logi_user dbname=first'
         PUBLICATION logical_pub WITH (synchronous_commit = 'on');

※上記以外にも指定できるパラメータやオプションが存在します。詳細はマニュアルをご確認ください。

[ 参考マニュアル – CREATE SUBSCRIPTION ]

作成したサブスクリプションの確認

作成したサブスクリプションの情報は pg_subscription を参照することで確認できます。

second=# SELECT * FROM pg_subscription;

-[ RECORD 1 ]---+--------------------------------------------------------------------------------------------
oid             | 24704
subdbid         | 13266
subname         | logical_sub
subowner        | 10
subenabled      | t
subconninfo     | host=first_server port=5432 user=logi_user dbname=first application_name=logical_sub_app
subslotname     | logical_sub_slot
subsynccommit   | on
subpublications | {logical_pub}

psql であればメタコマンド \dRs で確認できます。

second=# \dRs
                サブスクリプション一覧
    名前     |  所有者  | 有効状態 | パブリケーション
-------------+----------+----------+------------------
 logical_sub | postgres | t        | {logical_pub}
(1 行)

サブスクリプションのレプリケーション状態は pg_stat_subscription を参照することで確認できます。

second=# SELECT * FROM pg_stat_subscription;

-[ RECORD 1 ]---------+------------------------------
subid                 | 24704
subname               | logical_sub
pid                   | 9374
relid                 |
received_lsn          | 6/B9064B18
last_msg_send_time    | 2020-10-14 16:43:37.585108+09
last_msg_receipt_time | 2020-10-14 16:43:37.586771+09
latest_end_lsn        | 6/B9064B18
latest_end_time       | 2020-10-14 16:43:37.585108+09

また、サブスクリプションとテーブルのマッピングは pg_subscription_rel で確認できます。

second=# SELECT * FROM pg_subscription_rel;

 srsubid | srrelid | srsubstate | srsublsn
---------+---------+------------+----------
   24704 |   24679 | d          |
(1 行)

上記のとおり、pg_subscription_rel の中にはテーブルの名前ではなく oid で情報が保持されています。パブリケーションと違い、現時点でマッピングの情報を名称で確認できるシステムカタログはないようです。サブスクリプションとテーブル名のマッピングは pg_subscription と pg_class を合わせて参照することで確認できます。

second=# SELECT
    pg_subscription.subname
  , pg_class.relname
  , pg_subscription_rel.srsubstate
  , pg_subscription_rel.srsublsn
FROM
  pg_subscription_rel
    INNER JOIN pg_subscription
            ON pg_subscription_rel.srsubid = pg_subscription.oid
    INNER JOIN pg_class
            ON pg_subscription_rel.srrelid = pg_class.oid
;

   subname   |    relname    | srsubstate | srsublsn
-------------+---------------+------------+----------
 logical_sub | logical_table | d          |
(1 行)

初期データコピー

サブスクリプションを作成した時点で、初期コピーと呼ばれる「パブリッシャのテーブルからサブスクライバのテーブルへのデータコピー処理」が実行されます。

-- サブスクリプション作成前の logical_table の中身（初期コピー前は 0 件）
second=# =# SELECT * FROM logical_table;
 id | no | data | data2
----+----+------+-------
(0 行)

-- サブスクリプション作成（初期コピー実施）
second=# CREATE SUBSCRIPTION logical_sub
         CONNECTION 'host=first_server port=5432 user=logi_user dbname=first'
         PUBLICATION logical_pub;
CREATE SUBSCRIPTION

-- サブスクリプション作成後の logical_table の中身
-- （パブリッシャの logical_table のデータがコピー）
second=# SELECT * FROM logical_table;
 id | no |  data  | data2
----+----+--------+-------
  1 |  1 | 北海道 |
  2 |  2 | 青森県 |
  3 |  3 | 岩手県 |
  : |  : | 　:　 |
  : |  : | 　:　 |
 47 | 47 | 沖縄県 |
(47 行)

また、この初期コピーの際に、コピーされるパブリッシャのデータとサブスクライバの既存のデータが競合し、一意制約エラーの発生する可能性があります。

これは初期コピーが「差分のみを追加する」などの制御を行わずに、単純に「パブリッシャのテーブルのデータを、そのままサブスクライバのテーブルへ追加する」ためです。

# データ競合時のエラーメッセージ
2020-10-14 15:15:27.775 JST [8365](err_code=23505) ERROR:  duplicate key value violates unique constraint "logical_table_pkey"
2020-10-14 15:15:27.775 JST [8365](err_code=23505) DETAIL:  Key (id)=(1) already exists.

こういった場合、copy_data オプションに false を指定し、初期コピーを行わないことでエラーを回避できます。

second=# CREATE SUBSCRIPTION logical_sub
         CONNECTION 'host=first_server port=5432 user=logi_user dbname=first'
         PUBLICATION logical_pub WITH (copy_data = false);

サブスクリプションの有効／無効の切り替え

サブスクリプションは有効／無効を切り替えることができます。そのため、ロジカルレプリケーションを一時的に中断したい場合でも、サブスクリプションを削除する必要がありません。

サブスクリプションの無効化

サブスクリプションを無効にします。無効化している間は、当然ですがパブリッシャで行った操作がサブスクライバへレプリケーションされなくなります。

second=# ALTER SUBSCRIPTION logical_sub DISABLE;

サブスクリプションの有効化

サブスクリプションを有効にします。サブスクリプションを有効にしても、無効化している間に行った操作はレプリケーションされませんのでご注意ください。レプリケーションされるのはサブスクリプションが有効になっている間に行った操作のみとなります。

second=# ALTER SUBSCRIPTION logical_sub ENABLE;

まとめ

以上、ロジカルレプリケーションの概要と基本的な使い方をご紹介させていただきました。次回以降では、ロジカルレプリケーションの機能を実現するプロセスについての解説や、カスケードレプリケーションの設定やコンフリクト発生時の解消方法などの運用に関する情報などをお伝えする予定です。