Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Do PostgreSQL 16 Dream of Multi-Master Replicat...

Do PostgreSQL 16 Dream of Multi-Master Replication?

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

nuko_yokohama

March 16, 2023
Tweet

More Decks by nuko_yokohama

Other Decks in Technology

Transcript

  1. ロジカルレプリケーションとは(おさらい) • PostgreSQL 10 から導入された機能 • wal_level=logical • 任意のデータベース /

    テーブル / 列 / 行が複製対象 • 異アーキテクチャ / 異メジャーバージョン間複製が可能 • ストリーミングレプリケーションとは目的が異なる – SR :高可用化 / 参照分散 – LR :いろいろ(集約、マイグレーション、 etc ・・・)
  2. 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 なし
  3. この構成での問題 • 更新が永遠に循環してしまう! • 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 の更新ログが 循環するからこうなる にゃーん
  4. 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 あり
  5. この構成での問題( 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)
  6. この構成での問題( 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 の更新ログが 循環するからこうなる にゃーん
  7. 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
  8. origin=none 指定を使う • SUBSCRIBER の origin 指定 – WITH (

    subscription_parameter [= value] [, ... ] ) に origin が追加 • any (デフォルト) – パブリッシャーがその起源に関係なく変更を送信する • none – サブスクリプションはパブリッシャーに対して、 origin を持 たない変更のみを送信するように要求する これを使う
  9. 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 だ
  10. 検証モデル • 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
  11. 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);
  12. 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) ヨシ!
  13. 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) ヨシ!
  14. 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) ヨシ!
  15. 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) ヨシ!
  16. Confilict パターンの検証 • 同一 PK 挿入 • 同一 PK 更新

    • 同一 PK 更新 / 削除 • 全行削除 / 挿入 • TRUNCATE と INSERT
  17. 同一 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 間の値が 異なってしまう
  18. 同一 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 が 送られない!?
  19. 同一 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"') まずサーバログを 確認する
  20. 同一 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 =#
  21. 同一 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 の値は異なったまま
  22. 同一 PK 挿入 (6) • Confilict 発生時の対処って自動化できんのか? – emit_log_hook でメッセージ監視

    – “duplicate key value violates unique constraint” – SUBSCRIPTION 名の検出 – ”finished at 0/xxxxxxx” の検出 – それらから ALTER SUBSCRIPTION SKIP ... を実行 • 自動化は簡単にはできないかも・・・
  23. 同一 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 したら?
  24. 同一 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) =#
  25. 同一 PK 更新 (3) • 何が起きている? – node1 「これってもしかして」 –

    node2 「俺たちの UPDATE が」 – node1/node2 「「入れ替わってる~!」」 • 何が起きている?(推測) – node1 の更新が node2 に伝播(そこで伝播は止まる) – node2 の更新が node1 に伝播(そこで伝播は止まる)
  26. 同一 PK 更新 (4) • INSERT の場合には、伝播先に既に同一 PK レコードがある –

    Conflict を起こすので伝播はされない。 • UPDATE の場合には、伝播先に同一 PK レコードがある – その PK のデータは更新可能 • 本来はどうあるべきか? – こうなるのは仕方ないのか – WAL を伝播させないのが正しいのか? – どちらかの UPDATE を無効にすべきか。でもどうやって?
  27. 同一 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 したら?
  28. 同一 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 対象の レコードを削除
  29. 同一 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 したら?
  30. 同一 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 が適用される
  31. 全件 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 したら?
  32. 全件 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 レコードは 消えてしまう
  33. 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 したら?
  34. 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) =#
  35. 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 したら?
  36. 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) =#
  37. 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 結果 複雑怪奇、 私の苦手な言葉です
  38. PostgreSQL 本体機能でマルチマスタ可能? • PostgreSQL 15 までだと無理 • PostgreSQL 16 だとある程度できそう

    – conflict 時の挙動が怪しい・・・ – 運用介在方式はかなり面倒(どうやって自動化するんだ?) • 今後の課題 – 高可用性 / 障害復旧 – 性能(同期レプリケーションの場合) – 3 つ以上のノードのマルチマスタ 3 ノード版も 一応組んではみたが 運用大変そう