Do PostgreSQL 16 Dream of Multi-Master Replication?

PostgreSQL 16の新機能を使って、マルチマスターレプリケーションをテストしてみました。
I tested multi-master replication using the new PostgreSQL 16 features.

1. PostgreSQL 16 は マルチマスタレプリケーションの 夢を見るか？ (Do PostgreSQL 16 Dream of Multi-Master Replication?) PostgreSQL Unconferecnce #40 (2023-03-16)
2. 自己紹介 • ぬこ＠横浜 , @nuko_yokohama • にゃーん • 趣味でポスグレをやってる者だ • レプリケーション、私の苦手な言葉です
3. ロジカルレプリケーション
4. ロジカルレプリケーションとは（おさらい） • PostgreSQL 10 から導入された機能 • wal_level=logical • 任意のデータベース / テーブル / 列 / 行が複製対象 • 異アーキテクチャ / 異メジャーバージョン間複製が可能 • ストリーミングレプリケーションとは目的が異なる – SR ：高可用化 / 参照分散 – LR ：いろいろ（集約、マイグレーション、 etc ・・・）
5. ロジカルレプリケーションで マルチマスタレプリケーションは可能？
6. 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 なし
7. この構成での問題 • 更新が永遠に循環してしまう！ • 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 の更新ログが 循環するからこうなる にゃーん
8. 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 あり
9. この構成での問題（ 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)
10. この構成での問題（ 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 の更新ログが 循環するからこうなる にゃーん
11. PostgreSQL 15 時点での結論 • PostgreSQL のロジカルレプリケーション機構を使った マルチマスタ構成の実現はできない。 • あるノードで更新するテーブルを分ければ、 一応双方向レプリケーションは可能ではあるが・・・ 残念・・・
12. ロジカルレプリケーション機能差分 （ PostgreSQL 15/16 ）
13. 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
14. PostgreSQL 16 で マルチマスタ構成を組んでみる
15. origin=none 指定を使う • SUBSCRIBER の origin 指定 – WITH ( subscription_parameter [= value] [, ... ] ) に origin が追加 • any （デフォルト） – パブリッシャーがその起源に関係なく変更を送信する • none – サブスクリプションはパブリッシャーに対して、 origin を持 たない変更のみを送信するように要求する これを使う
16. 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 だ
17. 検証モデル • 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
18. 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);
19. 基本パターンの検証 • node1, node2 への挿入 • node1, node2 への更新 • node2 への削除 • node1 への TRUNCATE
20. 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) ヨシ！
21. 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) ヨシ！
22. 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) ヨシ！
23. 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) ヨシ！
24. 2 ノードマルチマスタ検証 （ Confilict パターンの検証）
25. Confilict パターンの検証 • 同一 PK 挿入 • 同一 PK 更新 • 同一 PK 更新 / 削除 • 全行削除 / 挿入 • TRUNCATE と INSERT
26. 同一 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 間の値が 異なってしまう
27. 同一 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 が 送られない！？
28. 同一 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"') まずサーバログを 確認する
29. 同一 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 =#
30. 同一 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 の値は異なったまま
31. 同一 PK 挿入 (6) • Confilict 発生時の対処って自動化できんのか？ – emit_log_hook でメッセージ監視 – “duplicate key value violates unique constraint” – SUBSCRIPTION 名の検出 – ”finished at 0/xxxxxxx” の検出 – それらから ALTER SUBSCRIPTION SKIP ... を実行 • 自動化は簡単にはできないかも・・・
32. 同一 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 したら？
33. 同一 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) =#
34. 同一 PK 更新 (3) • 何が起きている？ – node1 「これってもしかして」 – node2 「俺たちの UPDATE が」 – node1/node2 「「入れ替わってる～！」」 • 何が起きている？（推測） – node1 の更新が node2 に伝播（そこで伝播は止まる） – node2 の更新が node1 に伝播（そこで伝播は止まる）
35. 同一 PK 更新 (4) • INSERT の場合には、伝播先に既に同一 PK レコードがある – Conflict を起こすので伝播はされない。 • UPDATE の場合には、伝播先に同一 PK レコードがある – その PK のデータは更新可能 • 本来はどうあるべきか？ – こうなるのは仕方ないのか – WAL を伝播させないのが正しいのか？ – どちらかの UPDATE を無効にすべきか。でもどうやって？
36. 同一 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 したら？
37. 同一 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 対象の レコードを削除
38. 同一 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 したら？
39. 同一 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 が適用される
40. 全件 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 したら？
41. 全件 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 レコードは 消えてしまう
42. 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 したら？
43. 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) =#
44. 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 したら？
45. 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) =#
46. 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 結果 複雑怪奇、 私の苦手な言葉です
47. まとめ＆聞いてみたいこと
48. PostgreSQL 本体機能でマルチマスタ可能？ • PostgreSQL 15 までだと無理 • PostgreSQL 16 だとある程度できそう – conflict 時の挙動が怪しい・・・ – 運用介在方式はかなり面倒（どうやって自動化するんだ？） • 今後の課題 – 高可用性 / 障害復旧 – 性能（同期レプリケーションの場合） – 3 つ以上のノードのマルチマスタ 3 ノード版も 一応組んではみたが 運用大変そう
49. 聞いてみたいこと • ここまでやってみたけど、これで「マルチマスタ」と言える？ • こういう機能を使えばもっと良いマルチマスタ構成組めるよ！ みたいな知見 • そもそも 「マルチマスタ」構成ができると嬉しい人って本当にいる？ 猫には マルチマスタは 難しすぎた！
50. おしまい