PostgreSQL論理レプリケーション設定

PostgreSQL v10で実装されたロジカルレプリケーション機能を利用すると、テーブル単位でレプリケーション構成をとれます。
一般的なRead/Writeスプリット構成のほか、アプリケーションフレームワークから見て単一DB内に外部マスターのテーブルを持ち込むこともできるため、マイクロサービスの難題になりがちなデータ伝播の解決への活用も可能です。

セットアップの大まかな流れは以下のとおりです。

postgresql.conf設定
元DB：接続セキュリティ設定
複製先：テーブル作成（スキーマ定義）
元DB：PUBLICATION作成
複製先：SUBSCRIPTION作成

postgresql.conf設定

ロジカルレプリケーションが利用するリソースを設定しておきます。設定を有効にするためには再起動が必要です。

関連する項目には以下のようなものがあります。設定値はとくに推奨値ではなく、枯渇しない値であれば動作します。

# Publication側
wal_level = logical
max_worker_processes = 8
max_wal_senders = 10
max_replication_slots = 10

# Subscription側
max_worker_processes = 8
max_logical_replication_workers = 4
max_replication_slots = 10

Configuration Settingsに詳細な解説があります。
接続数に依存するため、数が増える場合には、あらかじめ適切なパラメータを設定する必要があります。

同期接続

レプリケーションのデフォルト構成は非同期です。性能重視でベストエフォートの複製を維持するので、多くの場合妥当な構成と言えます。
同期モードで動作させるには、Publication側のpostgres.confに同期対象のsubscriptionを追加指定します。

同期接続の設定詳細

synchronous_standby_names = 'ANY 2 (subscription_1, subscription_2)'
synchronous_commit = remote_apply

subscription名の部分にはワイルドカード*も指定できますが、必ずしも全てのサブスクリプションを同期モードで動作させるとは限らないでしょう。
この設定はPublication側に指定するものですが、サブスクリプション名はSubscription側のDBでSELECT * FROM pg_subscriptionした方が特定しやすいかもしれません。

また非常に分かりづらいポイントとして、第2引数のnum_syncの適切な設定値が自明ではありません。
ロジカルレプリケーション構成では、データベースごとにサブスクリプションを作成します。
上の例で、subscription_1とsubscription_2が2ホストへの複製ではなく、2DBを1ホストに複製する構成をとることがあります。この場合、2つとも同期レプリケーションする必要があるなら、num_syncは2にする必要があります。
1ホストのつもりで1を指定するといずれかは同期レプリケーションで、他は非同期の挙動になります。

synchronous_commitは、デフォルトはonという値です。
リファレンスの説明のとおり、onはリモートのWALに書き込み完了した時点でクエリが完了します。リモートのオンラインDBに反映されるまでにわずかなラグがあり、その点もカバーしたい場合にはremote_applyを指定します。

同期モードはオンラインDBの性能にも直接の影響があるため、synchronous_styndby_namesの設定方法を確認のうえ、設計が必要です。

同期レプリケーション対象として正常に認識できた場合、Publication側のログに参入時記録が出ます。

2023-10-22 03:03:10.618 UTC [468] LOG:  standby "subscription_1" is now a candidate for quorum synchronous standby

当然ながら、既存のレプリケーション処理がコンフリクトしていると同期対象として認識しません。
この場合のエラーは、Subscription側に出力されることが多いでしょう。

同期モードをセットアップする場合、非同期構成を一度完成させたうえでsynchronous_standby_namesを追加設定した方が、トラブル時の切り分けを明確にしやすいと言えます。

接続ロール設定

一般的な接続に関するセキュリティ設定として、必要に応じて元DBでレプリケーション処理を実行するDBロールを作成し、テーブルへのアクセス権を設定します。
一方、Subscription側はROLE設定することなく後述のCREATE SUBSCRIPTIONのパラメータに指定することで動作します。

# Publication側DBをセットアップ
postgres=# CREATE ROLE rep_user PASSWORD 'testpassword' LOGIN REPLICATION;
CREATE ROLE

postgres=# \c origin_db
You are now connected to database "origin_db" as user "postgres".

origin_db=# GRANT SELECT ON users, articles TO rep_user;
GRANT

# データベーススキーマ内の全テーブルに権限追加する場合
origin_db=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO rep_user;
GRANT

また、pg_hba.confでDBユーザーが接続できるようネットワーク設定も必要です。

複製先スキーマ作成

転送するテーブルと同一のスキーマで複製先に空のテーブルを作成しておきます。
基本的には両方同じテーブル構成、カラム設計になります。

テーブルスキーマをconfig定義できる sqldefやRubyDSLの ridgepoleを活用すると、２つのスキーマのメンテナンスがDRYになります。

シードデータを持っている場合、初期同期でPublicationから伝播してくるため、リードレプリカは空であることが重要です。

ロジカルレプリケーションでは元のDBと連動してINSERTなどが走りますが、ここで作成したスキーマのユニーク制約などに抵触するとエラーになるので、目的によっては元のDBよりも制約を緩くしておくという考え方もありそうです。

2系統のテーブルを一致させる

DB内の全テーブルを対象とするレプリケーションを設定した場合、PublicationとSubscriptionのテーブルが一致していることを保証する必要があります。
起きやすいトラブルは、Publication側に管理対象外のテーブルが残っているケースで、この場合、Subscription作成時に初期同期がエラーになります。

レプリケーション対象のDBは手動管理を避け、スキーマ管理のツールを整備すべきでしょう。

また、ロジカルレプリケーションでは定常的なWrite処理が流れてくるものの、実際には複製先は独立したテーブルで、元DBにはないカラムを追加して変更することも可能です（データ不整合のリスクあり）。

レプリケーション用テーブル追加設定

PRIMARY KEYを指定している一般的なテーブルはそのままレプリケーションできますが、ユニーク制約のみ設定した複合主キーのテーブルなどは、UPDATEやDELETEの際に、cannot delete from table "some_table" because it does not have areplica identity and publishes deletesというエラーにより更新できなくなります。

ユニークインデックスがある場合には、ALTER TABLEでレプリカアイデンティティに手動指定する必要があります。

ALTER TABLE <some_table> REPLICA IDENTITY USING INDEX <some_index>;

また、構成済のPublicationのエラーにより後からPRIMARY KEYを追加した場合には、自動的にREPLICA IDENTITYとして使用し始めません。 ALTER TABLE <some_table> REPLICA IDENTITY DEFAULT;を手動実行することで、PRIMARY KEYをREPLICA IDENTITYとして利用します。

REPLICA IDENTITYの状態は確認しづらいため、注意が必要です。

PUBLICATION作成

論理レプリケーションは、元DBでPUBLICATIONを作成し、複製先にSUBSCRIPTIONという通信チャネルを作成することで接続できます。

CREATE PUBLICATIONで配信するテーブルを指定して接続を受け付けるチャネルを作成します。
PUBLICATIONの名称は任意で、のちにSUBSCRIPTION作成時に指定します。データベースオブジェクトであるため、他DBのpublicationと名前が重複しても動作します。

origin_db=# CREATE PUBLICATION origindb_pub FOR TABLE users, articles;
CREATE PUBLICATION

# データベース内の全テーブルをpublishする場合
origin_db=# CREATE PUBLICATION origindb_pub FOR ALL TABLES;
CREATE PUBLICATION

同一クラスタ内・同一DB上の構成

同一クラスタ内・同一DB上でレプリケーションを作成する場合、以下のとおりロジカルデコーディングのレプリケーションスロットを手動で作成する必要があります。( CREATE SUBSCRIPTION公式マニュアルのNOTES欄に解説があります)

origin_db=# SELECT pg_create_logical_replication_slot('origin_sub1', 'pgoutput'); 
 pg_create_logical_replication_slot 
------------------------------------
 (origin_sub1,0/9C867F0)
(1 row)

レプリケーションスロットの作成を誤った場合など、削除する際にはSELECT pg_drop_replication_slot('origin_sub1')を用います。
レプリケーションスロットはDBスコープのオブジェクトですが、オブジェクト名はクラスタ単位でユニークである必要があります。

SUBSCRIPTION作成

最終的に複製先のDBでCREATE SUBSCRIPTIONします。

replica_db=# \c copy_db;

CREATE SUBSCRIPTION origin_sub1 CONNECTION 'host=127.0.0.1  dbname=origin_db user=rep_user password=testpassword' PUBLICATION origindb_pub;

# 同一クラスタ内・同一DB上の構成の場合
CREATE SUBSCRIPTION origin_sub1 CONNECTION 'host=127.0.0.1  dbname=origin_db user=rep_user password=testpassword' PUBLICATION origindb_pub WITH (create_slot=false);

SUBSCRIPTION名は任意ですが、同一DBのレプリケーション構成の場合には、オリジンDB上で作成したロジカルレプリケーションスロット名と合わせます（別名をつけるオプションもあります）。また、この構成ではWITH (create_slot=false)オプションも必要です。

デフォルトでは、SUBSCRIPTIONを作成するとすぐに転送が始まります。
この時点でログを確認すべきでしょう。

初期同期時には以下のようなログが出力されるか、接続できない場合やコンフリクトがある場合などはエラーが出力されます。

2018-03-06 06:53:51.146 UTC [871] LOG:  logical replication apply worker for subscription "origin_sub1" has started
2018-03-06 06:53:51.174 UTC [872] LOG:  starting logical decoding for slot "origin_sub1"
2018-03-06 06:53:51.174 UTC [872] DETAIL:  streaming transactions committing after 0/A68B270, reading WAL from 0/A68B238
2018-03-06 06:53:51.181 UTC [872] LOG:  logical decoding found consistent point at 0/A68B238
2018-03-06 06:53:51.181 UTC [872] DETAIL:  There are no running transactions.
2018-03-06 06:53:51.219 UTC [873] LOG:  logical replication table synchronization worker for subscription "origin_sub1", table "users" has started
2018-03-06 06:53:51.220 UTC [874] LOG:  logical replication table synchronization worker for subscription "origin_sub1", table "articles" has started
2018-03-06 06:53:51.276 UTC [876] LOG:  logical decoding found consistent point at 0/A690410
2018-03-06 06:53:51.276 UTC [876] DETAIL:  There are no running transactions.
2018-03-06 06:53:51.297 UTC [875] LOG:  logical decoding found consistent point at 0/A690448
2018-03-06 06:53:51.297 UTC [875] DETAIL:  There are no running transactions.
2018-03-06 06:53:51.517 UTC [873] LOG:  logical replication table synchronization worker for subscription "origin_sub1", table "users" has finished

運用開始後の操作については、論理レプリケーションの設定確認・テーブル追加に記述しています。

テーブル変更に伴うエラー

レプリケーション導入後にテーブルのカラム定義を変更するには、publicationとsubscriptionの各DBにALTER TABLEを実行します。テーブルの変更はレプリケーション経路では伝播しません。

また、publicationに存在するカラムがsubscriptionにはない場合、レプリケーションのエラーで一時停止します。
ADD COLUMNの例では、subscriptionにカラム追加した時点で滞留した変更が反映され、以後正常に動作します。

subscriptionに先にADD COLUMNすると不整合による滞留が起きないものの、DROP COLUMN時に問題が生じる可能性があります。
DROP COLUMNの場合も挙動は同じなのですが、subscriptionにDROP COLUMNしたあとにテーブル書き込みがあった場合、その後publicationをDROP COLUMNしても滞留が解決しない点に注意が必要です。
書き込みを生かすのであれば、subscription側で再度ADD COLUMNして転送を解決したうえで改めてDROP COLUMNしないと復旧しません。

どちらかというと、publicationを先に変更する方が安全に見えます。