PostgreSQL 16の新機能を使って、マルチマスターレプリケーションをテストしてみました。 I tested multi-master replication using the new PostgreSQL 16 features.
PostgreSQL 16 はマルチマスタレプリケーションの夢を見るか?(Do PostgreSQL 16 Dream of Multi-Master Replication?)PostgreSQL Unconferecnce #40 (2023-03-16)
View Slide
自己紹介● ぬこ@横浜 , @nuko_yokohama● にゃーん● 趣味でポスグレをやってる者だ● レプリケーション、私の苦手な言葉です
ロジカルレプリケーション
ロジカルレプリケーションとは(おさらい)● PostgreSQL 10 から導入された機能● wal_level=logical● 任意のデータベース / テーブル / 列 / 行が複製対象● 異アーキテクチャ / 異メジャーバージョン間複製が可能● ストリーミングレプリケーションとは目的が異なる– SR :高可用化 / 参照分散– LR :いろいろ(集約、マイグレーション、 etc ・・・)
ロジカルレプリケーションでマルチマスタレプリケーションは可能?
PostgreSQL 15 まで (1)● 2 ノード同一テーブルマルチマスタ構成( PK なし)DB クラスタ: port=16010データベース: testdbfoo テーブルpublishernode1_foo_pubsubscribernode1_foo_subDB クラスタ: port=16020データベース: testdbfoo テーブルpublishernode2_foo_pubsubscribernode2_foo_subPK なし PK なし
この構成での問題● 更新が永遠に循環してしまう!● 1 件 INSERT しても、それが無限に増えていく・・・=# TABLE foo;id | data----+------(0 rows)=# INSERT INTO foo VALUES (1, 'ABC');INSERT 0 1=# TABLE foo;id | data----+------1 | ABC1 | ABC(略)1 | ABC(18 rows)INSERT の更新ログが循環するからこうなるにゃーん
PostgreSQL 15 まで (2)● 2 ノード同一テーブルマルチマスタ構成( PK あり)DB クラスタ: port=16010データベース: testdbfoo テーブルpublishernode1_foo_pubsubscribernode1_foo_subDB クラスタ: port=16020データベース: testdbfoo テーブルpublishernode2_foo_pubsubscribernode2_foo_subPK あり 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 時点コマンド 変更点 備考CREATEPUBLICATIONMERGE 文の挙動に関する記述の追加ドキュメント修正のみ?CREATESUNSCRIPTIONstreaming オプションの型変更 (boolean→enum )値域が off(default), on,parallel になったCREATESUNSCRIPTIONorigin オプションの追加 値域は any(default),none
PostgreSQL 16 でマルチマスタ構成を組んでみる
origin=none 指定を使う● SUBSCRIBER の origin 指定– WITH ( subscription_parameter [= value] [, ... ] )に origin が追加● any (デフォルト)– パブリッシャーがその起源に関係なく変更を送信する● none– サブスクリプションはパブリッシャーに対して、 origin を持たない変更のみを送信するように要求するこれを使う
origin=none ってどういうこと?(想像)● たぶん、カスケードされた論理 WAL に起点となるorigin という情報が入っているのだろう。● 最初の段には入っていない (none )origin=nonefoo テーブルpublishernode1_foo_pubfoo テーブルpublishernode2_foo_pubfoo テーブルsubscribernode1_foo_subsubscribernode2_foo_subこれの origin はnode1_foo_pub だ
検証モデル● 2 ノード同一テーブルマルチマスタ構成( PK あり、 origin=none )DB クラスタ: port=16010データベース: testdbfoo テーブルpublishernode1_foo_pubsubscribernode1_foo_subDB クラスタ: port=16020データベース: testdbfoo テーブルpublishernode2_foo_pubsubscribernode2_foo_subPK あり PK ありorigin=none origin=none
DDL 例● node1, node2 共通CREATE TABLE foo (id int primary key, data text);● node1CREATE PUBLICATION node1_foo_pub FOR TABLE foo;CREATE SUBSCRIPTION node1_foo_sub CONNECTION 'port=16020dbname=testdb user=postgres' PUBLICATION node2_foo_pub WITH(origin=none);● node2CREATE PUBLICATION node2_foo_pub FOR TABLE foo;CREATE SUBSCRIPTION node2_foo_sub CONNECTION 'port=16010dbname=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 | ABC2 | DEF(2 rows)$ psql -p 16020 testdb -c "TABLE foo"id | data----+------1 | ABC2 | DEF(2 rows)ヨシ!
node1, node2 への更新node1$ psql -p 16010 testdb -c "TABLE foo"id | data----+------1 | ABC2 | DEF(2 rows)$ psql -p 16020 testdb -c "TABLE foo"id | data----+------1 | ABC2 | 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 | def1 | abc(2 rows)$ psql -p 16020 testdb -c "TABLE foo"id | data----+------2 | def1 | abc(2 rows)ヨシ!
node2 への削除node1$ psql -p 16010 testdb -c "TABLE foo"id | data----+------2 | def1 | abc(2 rows)$ psql -p 16020 testdb -c "TABLE foo"id | data----+------2 | def1 | 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 への TRUNCATEnode1$ 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 | ABC2 | 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 keyvalue 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: processingremote data for replication origin "pg_16401" duringmessage type "INSERT" for replication target relation"public.foo" in transaction 752, finished at 0/1949D882023-03-12 15:22:34.111 JST [2643] LOG: backgroundworker "logical replication worker" (PID 3040) exitedwith exit code 1node2 サーバログ2023-03-12 15:22:34.109 JST [3041] LOG: starting logicaldecoding for slot "node2_foo_sub"2023-03-12 15:22:34.109 JST [3041] DETAIL: Streamingtransactions committing after 0/1949D88, reading WAL from0/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 decodingfound consistent point at 0/1949BB02023-03-12 15:22:34.110 JST [3041] DETAIL: There are norunning 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=#node22023-03-12 15:29:04.382 JST [3573] ERROR: duplicate keyvalue 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: processingremote data for replication origin "pg_16401" duringmessage type "INSERT" for replication target relation"public.foo" in transaction 752, finished at 0/1949CC82023-03-12 15:29:04.383 JST [2634] LOG: backgroundworker "logical replication worker" (PID 3573) exitedwith 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 1node2$ psql -p 16010 testdb -c "TABLE foo"id | data----+------1 | ABC2 | DEF(2 rows)$ psql -p 16020 testdb -c "TABLE foo"id | data----+------1 | abc2 | 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 | ABC2 | DEF(2 rows)=# TABLE foo ;id | data----+------1 | ABC2 | DEF(2 rows)node2=*# UPDATE foo SET data = 'XYZ' WHERE id = 1;UPDATE 1=*# TABLE foo;id | data----+------2 | DEF1 | XYZ(2 rows)=*# UPDATE foo SET data = 'xyz' WHERE id = 1;UPDATE 1=*# TABLE foo;id | data----+------2 | DEF1 | xyz(2 rows)node1,node2 がCOMMIT したら?
同一 PK 更新 (2)node1=*# COMMIT;COMMIT=# TABLE foo;id | data----+------2 | DEF1 | xyz(2 rows)=#node2=# TABLE foo;id | data----+------2 | DEF1 | XYZ(2 rows)=#!?=*# COMMIT;COMMIT=# TABLE foo;id | data----+------2 | DEF1 | 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 | ABC2 | 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 | ABC2 | 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 | ABC2 | 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 | ABC2 | 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 ノード版も一応組んではみたが運用大変そう
聞いてみたいこと● ここまでやってみたけど、これで「マルチマスタ」と言える?● こういう機能を使えばもっと良いマルチマスタ構成組めるよ!みたいな知見● そもそも「マルチマスタ」構成ができると嬉しい人って本当にいる?猫にはマルチマスタは難しすぎた!
おしまい