PostgreSQL 16 は マルチマスタレプリケーションの 夢を見るか？ (Do PostgreSQL 16 Dream of Multi-Master Replication?) PostgreSQL Unconferecnce #40 (2023-03-16)

自己紹介 ● ぬこ＠横浜 , @nuko_yokohama ● にゃーん ● 趣味でポスグレをやってる者だ ● レプリケーション、私の苦手な言葉です

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

ロジカルレプリケーションとは（おさらい） ● PostgreSQL 10 から導入された機能 ● wal_level=logical ● 任意のデータベース / テーブル / 列 / 行が複製対象 ● 異アーキテクチャ / 異メジャーバージョン間複製が可能 ● ストリーミングレプリケーションとは目的が異なる – SR ：高可用化 / 参照分散 – LR ：いろいろ（集約、マイグレーション、 etc ・・・）

ロジカルレプリケーションで マルチマスタレプリケーションは可能？

PostgreSQL 15 まで (1) ● 2 ノード同一テーブルマルチマスタ構成（ PK なし） DB クラスタ： port=16010 データベース： testdb foo テーブル publisher node1_foo_pub subscriber node1_foo_sub DB クラスタ： port=16020 データベース： testdb foo テーブル publisher node2_foo_pub subscriber node2_foo_sub PK なし PK なし

この構成での問題 ● 更新が永遠に循環してしまう！ ● 1 件 INSERT しても、それが無限に増えていく・・・ =# TABLE foo; id | data ----+------ (0 rows) =# INSERT INTO foo VALUES (1, 'ABC'); INSERT 0 1 =# TABLE foo; id | data ----+------ 1 | ABC 1 | ABC （略） 1 | ABC (18 rows) INSERT の更新ログが 循環するからこうなる にゃーん

PostgreSQL 15 まで (2) ● 2 ノード同一テーブルマルチマスタ構成（ PK あり） DB クラスタ： port=16010 データベース： testdb foo テーブル publisher node1_foo_pub subscriber node1_foo_sub DB クラスタ： port=16020 データベース： testdb foo テーブル publisher node2_foo_pub subscriber node2_foo_sub PK あり PK あり

この構成での問題（ 1/2 ） ● この構成でも更新が永遠に循環してしまう！ ● 1 件 INSERT した直後は問題なさげだが・・・ $ psql -p 16010 testdb -c "TABLE foo" id | data ----+------ (0 rows) $ psql -p 16010 testdb -c "INSERT INTO foo VALUES (1, 'ABC')" INSERT 0 1 $ psql -p 16010 testdb -c "TABLE foo" id | data ----+------ 1 | ABC (1 row) $ psql -p 16020 testdb -c "TABLE foo" id | data ----+------ 1 | ABC (1 row)

この構成での問題（ 2/2 ） ● DELETE で削除しても消えない！？ ● というより削除されているタイミング「も」ある $ psql -p 16010 testdb -c "DELETE FROM foo WHERE id = 1" DELETE 1 $ psql -p 16010 testdb -c "TABLE foo" id | data ----+------ 1 | ABC (1 row) $ psql -p 16020 testdb -c "TABLE foo" id | data ----+------ (0 rows) $ psql -p 16020 testdb -c "TABLE foo" id | data ----+------ 1 | ABC (1 row) INSERT の更新ログと DELETE の更新ログが 循環するからこうなる にゃーん

PostgreSQL 15 時点での結論 ● PostgreSQL のロジカルレプリケーション機構を使った マルチマスタ構成の実現はできない。 ● あるノードで更新するテーブルを分ければ、 一応双方向レプリケーションは可能ではあるが・・・ 残念・・・

ロジカルレプリケーション機能差分 （ PostgreSQL 15/16 ）

PostgreSQL 16 の変更点 ● Document から見た CREATE PUBLICATION/SUBSCRIPTION の変更点 – 2023-03-11 時点 コマンド 変更点 備考 CREATE PUBLICATION MERGE 文の挙動に関する記述 の追加 ドキュメント修正のみ？ CREATE SUNSCRIPTION streaming オプションの型変 更 (boolean→enum ） 値域が off(default), on, parallel になった CREATE SUNSCRIPTION origin オプションの追加 値域は any(default), none

PostgreSQL 16 で マルチマスタ構成を組んでみる

origin=none 指定を使う ● SUBSCRIBER の origin 指定 – WITH ( subscription_parameter [= value] [, ... ] ) に origin が追加 ● any （デフォルト） – パブリッシャーがその起源に関係なく変更を送信する ● none – サブスクリプションはパブリッシャーに対して、 origin を持 たない変更のみを送信するように要求する これを使う

origin=none ってどういうこと？（想像） ● たぶん、カスケードされた論理 WAL に起点となる origin という情報が入っているのだろう。 ● 最初の段には入っていない (none ） origin=none foo テーブル publisher node1_foo_pub foo テーブル publisher node2_foo_pub foo テーブル subscriber node1_foo_sub subscriber node2_foo_sub これの origin は node1_foo_pub だ

検証モデル ● 2 ノード同一テーブルマルチマスタ構成（ PK あり、 origin=none ） DB クラスタ： port=16010 データベース： testdb foo テーブル publisher node1_foo_pub subscriber node1_foo_sub DB クラスタ： port=16020 データベース： testdb foo テーブル publisher node2_foo_pub subscriber node2_foo_sub PK あり PK あり origin=none origin=none

DDL 例 ● node1, node2 共通 CREATE TABLE foo (id int primary key, data text); ● node1 CREATE PUBLICATION node1_foo_pub FOR TABLE foo; CREATE SUBSCRIPTION node1_foo_sub CONNECTION 'port=16020 dbname=testdb user=postgres' PUBLICATION node2_foo_pub WITH (origin=none); ● node2 CREATE PUBLICATION node2_foo_pub FOR TABLE foo; CREATE SUBSCRIPTION node2_foo_sub CONNECTION 'port=16010 dbname=testdb user=postgres' PUBLICATION node1_foo_pub WITH (origin=none);

基本パターンの検証 ● node1, node2 への挿入 ● node1, node2 への更新 ● node2 への削除 ● node1 への TRUNCATE

node1, node2 から挿入 node1 $ psql -p 16010 testdb -c "TABLE foo" id | data ----+------ (0 rows) $ psql -p 16020 testdb -c "TABLE foo" id | data ----+------ (0 rows) node2 $ psql -p 16010 testdb -c "INSERT INTO foo VALUES (1, 'ABC')" INSERT 0 1 $ psql -p 16020 testdb -c "INSERT INTO foo VALUES (2, 'DEF')" INSERT 0 1 $ psql -p 16010 testdb -c "TABLE foo" id | data ----+------ 1 | ABC 2 | DEF (2 rows) $ psql -p 16020 testdb -c "TABLE foo" id | data ----+------ 1 | ABC 2 | DEF (2 rows) ヨシ！

node1, node2 への更新 node1 $ psql -p 16010 testdb -c "TABLE foo" id | data ----+------ 1 | ABC 2 | DEF (2 rows) $ psql -p 16020 testdb -c "TABLE foo" id | data ----+------ 1 | ABC 2 | DEF (2 rows) node2 $ psql -p 16010 testdb -c "UPDATE foo SET data = 'def' WHERE id = 2" UPDATE 1 $ psql -p 16020 testdb -c "UPDATE foo SET data = 'abc' WHERE id = 1" UPDATE 1 $ psql -p 16010 testdb -c "TABLE foo" id | data ----+------ 2 | def 1 | abc (2 rows) $ psql -p 16020 testdb -c "TABLE foo" id | data ----+------ 2 | def 1 | abc (2 rows) ヨシ！

node2 への削除 node1 $ psql -p 16010 testdb -c "TABLE foo" id | data ----+------ 2 | def 1 | abc (2 rows) $ psql -p 16020 testdb -c "TABLE foo" id | data ----+------ 2 | def 1 | abc (2 rows) node2 $ psql -p 16020 testdb -c "DELETE FROM foo WHERE id = 2" DELETE 1 $ $ psql -p 16010 testdb -c "TABLE foo" id | data ----+------ 1 | abc (1 row) $ psql -p 16020 testdb -c "TABLE foo" id | data ----+------ 1 | abc (1 row) ヨシ！

node1 への TRUNCATE node1 $ psql -p 16010 testdb -c "TABLE foo" id | data ----+------ 1 | abc (1 row) $ psql -p 16010 testdb -c "TABLE foo" id | data ----+------ 1 | abc (1 row) node2 $ psql -p 16010 testdb -c "TRUNCATE foo" TRUNCATE TABLE $ psql -p 16010 testdb -c "TABLE foo" id | data ----+------ (0 rows) $ psql -p 16020 testdb -c "TABLE foo" id | data ----+------ (0 rows) ヨシ！

2 ノードマルチマスタ検証 （ Confilict パターンの検証）

Confilict パターンの検証 ● 同一 PK 挿入 ● 同一 PK 更新 ● 同一 PK 更新 / 削除 ● 全行削除 / 挿入 ● TRUNCATE と INSERT

同一 PK 挿入 (1) node1 =# BEGIN; BEGIN =*# INSERT INTO foo VALUES (1, 'ABC'); INSERT 0 1 =*# TABLE foo; id | data ----+------ 1 | ABC (1 row) =*# =# BEGIN; BEGIN =*# INSERT INTO foo VALUES (1, 'abc'); INSERT 0 1 =*# TABLE foo; id | data ----+------ 1 | abc (1 row) =*# node2 =*# COMMIT; COMMIT =# TABLE foo; id | data ----+------ 1 | ABC (1 row) =# =*# COMMIT; COMMIT =# TABLE foo; id | data ----+------ 1 | abc (1 row) =# node 間の値が 異なってしまう

同一 PK 挿入 (2) node1 =# TABLE foo; id | data ----+------ 1 | ABC (1 row) =# INSERT INTO foo VALUES (2, 'DEF'); INSERT 0 1 =# TABLE foo; id | data ----+------ 1 | ABC 2 | DEF (2 rows) node2 =# TABLE foo; id | data ----+------ 1 | abc (1 row) こうなると 運用者介在が必要 先行する id=1 の 挿入 WAL が残っているので 後続の id=2 の挿入 WAL が 送られない！？

同一 PK 挿入 (3) node1 サーバログ 2023-03-12 15:22:34.111 JST [3040] ERROR: duplicate key value violates unique constraint "foo_pkey" 2023-03-12 15:22:34.111 JST [3040] DETAIL: Key (id)=(1) already exists. 2023-03-12 15:22:34.111 JST [3040] CONTEXT: processing remote data for replication origin "pg_16401" during message type "INSERT" for replication target relation "public.foo" in transaction 752, finished at 0/1949D88 2023-03-12 15:22:34.111 JST [2643] LOG: background worker "logical replication worker" (PID 3040) exited with exit code 1 node2 サーバログ 2023-03-12 15:22:34.109 JST [3041] LOG: starting logical decoding for slot "node2_foo_sub" 2023-03-12 15:22:34.109 JST [3041] DETAIL: Streaming transactions committing after 0/1949D88, reading WAL from 0/1949BB0. 2023-03-12 15:22:34.109 JST [3041] STATEMENT: START_REPLICATION SLOT "node2_foo_sub" LOGICAL 0/0 (proto_version '4', origin 'none', publication_names '"node1_foo_pub"') 2023-03-12 15:22:34.110 JST [3041] LOG: logical decoding found consistent point at 0/1949BB0 2023-03-12 15:22:34.110 JST [3041] DETAIL: There are no running transactions. 2023-03-12 15:22:34.110 JST [3041] STATEMENT: START_REPLICATION SLOT "node2_foo_sub" LOGICAL 0/0 (proto_version '4', origin 'none', publication_names '"node1_foo_pub"') まずサーバログを 確認する

同一 PK 挿入 (4) node1 =# ALTER SUBSCRIPTION node2_foo_sub SKIP ( LSN = '0/1949D88' ); ALTER SUBSCRIPTION =# node2 2023-03-12 15:29:04.382 JST [3573] ERROR: duplicate key value violates unique constraint "foo_pkey" 2023-03-12 15:29:04.382 JST [3573] DETAIL: Key (id)=(1) already exists. 2023-03-12 15:29:04.382 JST [3573] CONTEXT: processing remote data for replication origin "pg_16401" during message type "INSERT" for replication target relation "public.foo" in transaction 752, finished at 0/1949CC8 2023-03-12 15:29:04.383 JST [2634] LOG: background worker "logical replication worker" (PID 3573) exited with exit code 1 運用者介在 （ ALTER SUBSCRIPTION) が必要 =# ALTER SUBSCRIPTION node1_foo_sub SKIP ( LSN = '0/1949CC8'); ALTER SUBSCRIPTION =#

同一 PK 挿入 (5) node1 =# INSERT INTO foo VALUES (2, 'DEF'); INSERT 0 1 node2 $ psql -p 16010 testdb -c "TABLE foo" id | data ----+------ 1 | ABC 2 | DEF (2 rows) $ psql -p 16020 testdb -c "TABLE foo" id | data ----+------ 1 | abc 2 | DEF (2 rows) conflict は解消したが id=1 の値は異なったまま

同一 PK 挿入 (6) ● Confilict 発生時の対処って自動化できんのか？ – emit_log_hook でメッセージ監視 – “duplicate key value violates unique constraint” – SUBSCRIPTION 名の検出 – ”finished at 0/xxxxxxx” の検出 – それらから ALTER SUBSCRIPTION SKIP ... を実行 ● 自動化は簡単にはできないかも・・・

同一 PK 更新 (1) node1 =# TABLE foo; id | data ----+------ (0 rows) =# INSERT INTO foo VALUES (1, 'ABC'),(2, 'DEF'); INSERT 0 2 =# TABLE foo; id | data ----+------ 1 | ABC 2 | DEF (2 rows) =# TABLE foo ; id | data ----+------ 1 | ABC 2 | DEF (2 rows) node2 =*# UPDATE foo SET data = 'XYZ' WHERE id = 1; UPDATE 1 =*# TABLE foo; id | data ----+------ 2 | DEF 1 | XYZ (2 rows) =*# UPDATE foo SET data = 'xyz' WHERE id = 1; UPDATE 1 =*# TABLE foo; id | data ----+------ 2 | DEF 1 | xyz (2 rows) node1,node2 が COMMIT したら？

同一 PK 更新 (2) node1 =*# COMMIT; COMMIT =# TABLE foo; id | data ----+------ 2 | DEF 1 | xyz (2 rows) =# node2 =# TABLE foo; id | data ----+------ 2 | DEF 1 | XYZ (2 rows) =# ！？ =*# COMMIT; COMMIT =# TABLE foo; id | data ----+------ 2 | DEF 1 | xyz (2 rows) =#

同一 PK 更新 (3) ● 何が起きている？ – node1 「これってもしかして」 – node2 「俺たちの UPDATE が」 – node1/node2 「「入れ替わってる～！」」 ● 何が起きている？（推測） – node1 の更新が node2 に伝播（そこで伝播は止まる） – node2 の更新が node1 に伝播（そこで伝播は止まる）

同一 PK 更新 (4) ● INSERT の場合には、伝播先に既に同一 PK レコードがある – Conflict を起こすので伝播はされない。 ● UPDATE の場合には、伝播先に同一 PK レコードがある – その PK のデータは更新可能 ● 本来はどうあるべきか？ – こうなるのは仕方ないのか – WAL を伝播させないのが正しいのか？ – どちらかの UPDATE を無効にすべきか。でもどうやって？

同一 PK 更新 / 削除 (1-1) node1 =# TABLE foo; id | data ----+------ 1 | ABC (1 row) =# TABLE foo; id | data ----+------ 1 | ABC (1 row) node2 =# BEGIN; BEGIN =*# DELETE FROM foo WHERE id = 1; DELETE 1 =*# TABLE foo ; id | data ----+------ (0 rows) =*# =# BEGIN; BEGIN =*# UPDATE foo SET data = 'XYZ' WHERE id = 1; UPDATE 1 =*# TABLE foo; id | data ----+------ 1 | XYZ (1 row) =*# node1→node2 が COMMIT したら？

同一 PK 更新 / 削除 (1-2) node1 =*# COMMIT; COMMIT =# TABLE foo; id | data ----+------ 1 | XYZ (1 row) =# =*# COMMIT; COMMIT =# TABLE foo ; id | data ----+------ (0 rows) =# node2 =# TABLE foo; id | data ----+------ (0 rows) =# UPDATE した レコードを削除する WAL が適用される UPDATE 対象の レコードを削除

同一 PK 更新 / 削除 (2-1) node1 =# TABLE foo; id | data ----+------ 1 | ABC (1 row) =# TABLE foo; id | data ----+------ 1 | ABC (1 row) node2 =# BEGIN; BEGIN =*# DELETE FROM foo WHERE id = 1; DELETE 1 =*# TABLE foo ; id | data ----+------ (0 rows) =*# =# BEGIN; BEGIN =*# UPDATE foo SET data = 'XYZ' WHERE id = 1; UPDATE 1 =*# TABLE foo; id | data ----+------ 1 | XYZ (1 row) =*# node2→node1 が COMMIT したら？

同一 PK 更新 / 削除 (2-2) node1 =*# COMMIT; COMMIT =# TABLE foo; id | data ----+------ (0 rows) =# =*# COMMIT; COMMIT =# TABLE foo; id | data ----+------ (0 rows) =# node2 =# TABLE foo; id | data ----+------ (0 rows) =# UPDATE 対象の レコードを削除 UPDATE した レコードを削除する WAL が適用される

全件 DELETE と INSERT(1) node1 =# TABLE foo; id | data ----+------ 1 | ABC (1 row) =# TABLE foo; id | data ----+------ 1 | ABC (1 row) node2 =# BEGIN; BEGIN =*# INSERT INTO foo VALUES (2, 'def'); INSERT 0 1 =*# TABLE foo; id | data ----+------ 1 | ABC 2 | def (2 rows) =*# =# BEGIN; BEGIN =*# DELETE FROM foo; DELETE 1 =*# TABLE foo; id | data ----+------ (0 rows) =*# node1→node2 が COMMIT したら？

全件 DELETE と INSERT(2) node1 =*# COMMIT; COMMIT =# TABLE foo; id | data ----+------ (0 rows) =# node2 =*# COMMIT; COMMIT =# TABLE foo; id | data ----+------ 2 | def (1 row) =# 全削除→ id=2 の レコードが残る =# TABLE foo; id | data ----+------ 2 | def (1 row) =# =*# TABLE foo; id | data ----+------ 2 | def (1 row) =*# トランザクション中 だけど id=1 レコードは 消えてしまう

TRUNCATE と INSERT(1-1) node1 =# TABLE foo; id | data ----+------ 1 | ABC (1 row) =# TABLE foo; id | data ----+------ 1 | ABC (1 row) node2 =# BEGIN; BEGIN =*# INSERT INTO foo VALUES (2, 'def'); INSERT 0 1 =*# TABLE foo; id | data ----+------ 1 | ABC 2 | def (2 rows) =*# =# BEGIN; BEGIN =*# TRUNCATE foo; TRUNCATE TABLE =*# TABLE foo; id | data ----+------ (0 rows) =*# node1→node2 が COMMIT したら？

TRUNCATE と INSERT(1-2) node1 =*# COMMIT; COMMIT =# TABLE foo; id | data ----+------ (0 rows) =# node2 =*# COMMIT; COMMIT =# TABLE foo; id | data ----+------ (0 rows) =# node1 にのみ INSERT 結果が残る =# TABLE foo; id | data ----+------ 2 | def (1 row) =#

TRUNCATE と INSERT(2-1) node1 =# TABLE foo; id | data ----+------ 1 | ABC (1 row) =# TABLE foo; id | data ----+------ 1 | ABC (1 row) node2 =# BEGIN; BEGIN =*# INSERT INTO foo VALUES (2, 'def'); INSERT 0 1 =*# TABLE foo; id | data ----+------ 1 | ABC 2 | def (2 rows) =*# =# BEGIN; BEGIN =*# TRUNCATE foo; TRUNCATE TABLE =*# TABLE foo; id | data ----+------ (0 rows) =*# node2→node1 が COMMIT したら？

TRUNCATE と INSERT(1-2) node1 =*# COMMIT; COMMIT =# TABLE foo; id | data ----+------ 2 | def (1 row) =# node2 =*# COMMIT; COMMIT =# TABLE foo; id | data ----+------ 1 | ABC 2 | def (2 rows) =# node1 にのみ INSERT 結果が残る =# TABLE foo ; id | data ----+------ (0 rows) =#

Confilict パターンの検証（まとめ） パターン 系全体 node1 node2 同一 PK 挿入 値は不一致 レプリケーション停止 （運用介在が必要になる） node1 への挿入結果 node2 への挿入結果 同一 PK 更新 値は不一致 （対向側の値が更新される） node2 への更新結果 node1 への更新結果 同一 PK 更新 / 削除 値は一致 node2 への delete 結果 node2 への delete 結果 全行削除 /INSERT 値は一致 全 DELETE→node2 への insert 結果 全 DELETE→node2 への insert 結果 TRUNCATE と INSERT 値は不一致 node2 への挿入結果 TRUNCATE 結果 複雑怪奇、 私の苦手な言葉です

まとめ＆聞いてみたいこと

PostgreSQL 本体機能でマルチマスタ可能？ ● PostgreSQL 15 までだと無理 ● PostgreSQL 16 だとある程度できそう – conflict 時の挙動が怪しい・・・ – 運用介在方式はかなり面倒（どうやって自動化するんだ？） ● 今後の課題 – 高可用性 / 障害復旧 – 性能（同期レプリケーションの場合） – 3 つ以上のノードのマルチマスタ 3 ノード版も 一応組んではみたが 運用大変そう

聞いてみたいこと ● ここまでやってみたけど、これで「マルチマスタ」と言える？ ● こういう機能を使えばもっと良いマルチマスタ構成組めるよ！ みたいな知見 ● そもそも 「マルチマスタ」構成ができると嬉しい人って本当にいる？ 猫には マルチマスタは 難しすぎた！

おしまい