はじめに
この記事は NTTテクノクロス Advent Calendar 2022 の 18日目の記事です。
こんにちは、NTTテクノクロスの金澤です。
普段はPostgreSQL関連の開発や検証業務に携わっています。
この記事ではPostgreSQL15のメジャーバージョンアップで機能に変更があったロジカルレプリケーションについて、色々と動作確認をしていきたいと思います。
私自身、PostgreSQLはバージョン14.4から触り始めた初心者なので、自分の理解を深めるためにも、
そもそもロジカルレプリケーションとはなんぞやというところから書いていこうと思います。
ロジカルレプリケーションとは？
今回の記事のテーマであるロジカルレプリケーションとは、PostgreSQLのバージョン10から新たに追加されたレプリケーション機能です。
データベースについて、あまり触れたことのない方にとっては、そもそもレプリケーション機能と言われても、何のことかわからないと思います。(私もそうでした・・・)
以降でレプリケーションとは何かについて簡単に紹介したうえで、PostgreSQLの持っている2つのレプリケーション機能を比較しながらロジカルレプリケーションについて整理いたします。
レプリケーションとは
私はレプリケーションとは何かを概要を掴むために、
オープンソースデータベースの認定資格であるOSS-DBの学習サイトを参照にしました。
そのサイトでは、レプリケーションは以下のように説明されています。
レプリケーション(replication)を日本語にすると「複製」という意味になります。
PostgreSQLを含め、多くのRDBMSにはレプリケーション、つまりデータベースを複製する機能があります。
上記にあるように、レプリケーションとはデータベースを複製する機能になります。
レプリケーションを使うことで、データベースへのアクセス先を複数のデータベースに分散させ負荷を軽減させたり、
一つのデータベースがダウンしても、他データベースにアクセス可能にして継続したデータベース利用が実現できます。
このような負荷分散や可用性の観点から、レプリケーションはデータベースシステムを設計する上で必須といってもよい機能となります。
2つのレプリケーション
冒頭でも軽く触れていますが、PostgreSQLでは代表的なレプリケーション機能が2つあります。
一つはストリーミングレプリケーションで、
もう一つは今回のメインテーマであるロジカルレプリケーションというものです。
ストリーミングレプリケーションとロジカルレプリケーション
上述した2つのレプリケーションについても、OSS-DBの学習ページに記載されている内容を引用いたします。
ストリーミングレプリケーションは、マスター側から全てのトランザクションログ(WAL)を転送し、スタンバイ側はWALを適用（リカバリ）します。WALにはデータベースの全ての変更情報が物理的なレベルで記載されています。このWALを適用することで、データベースクラスタの完全なコピーが作成できます。
ロジカルレプリケーションではWALに記載されている変更情報を論理的なレベルに変換（デコード）してから転送します。
上記の引用文についてざっくりと捉えると、
データベースの更新情報(WAL)を
- コピー元からまるっとそのままコピー先に渡して複製するのがストリーミングレプリケーション
- 論理更新ログを解釈して、コピー元の反映情報の一部をコピー先に反映して複製するのがロジカルレプリケーション
のイメージです。
ストリーミングレプリケーションは、データベースを完全に複製するので、負荷分散や高可用なデータベースを構築するために使用されます。
一方でロジカルレプリケーションは、データベース中のテーブルだけを複製するといった柔軟なレプリケーションを組むことができるので、特定のデータのみを残しておきたいといったケースで使用することができます。
ストリーミングレプリケーションとロジカルレプリケーションでは、コピー元とコピー先をそれぞれ以下のように呼びます。
- ストリーミングレプリケーション
- コピー元：プライマリ
- コピー先：スタンバイ
- ロジカルレプリケーション
- コピー元：パブリッシャ
- コピー先：サブスクライバ
PostgreSQL15でのロジカルレプリケーションの追加機能
今回のメインテーマであるロジカルレプリケーションですが、
PostgreSQL15以前から、複製対象をテーブル単位に絞って実行することが可能でした。（もちろんデータベース全体でもできます。)
PostgreSQL15では、ロジカルレプリケーションのテーブル単位の複製を、より詳細な条件で指定することができるようになりました。
今回はそんなPostgreSQL15で追加されたロジカルレプリケーションのテーブル単位の複製条件を指定する機能である
「列指定」機能と「行フィルタ」機能を実際に触ってみたいと思います。
検証環境
今回の検証では、以下の環境をVirtual BOX上で作成して確認していきます。
Rocky 8.7
[root@localhost kanaza-s]# cat /etc/rocky-release
Rocky Linux release 8.7 (Green Obsidian)
PostgreSQL15.1
[postgres@localhost ~]$ psql --version
psql (PostgreSQL) 15.1
列指定
列指定とは、レプリケーションをするテーブルのうち、特定の列だけを複製する機能となります。
以前はテーブル定義の全部の列のレプリケーションしかできませんでしたが、
PostgreSQL15のからはテーブル内の特定の列のみをレプリケーションできるようになりました。
基本動作
特定の列のみを複製するロジカルレプリケーション構成を組んでみたいと思います。
p_test_1：パブリッシャ側データベース
s_test_1：サブスクライバ側データベース
・テーブル作成
p_test_1=# CREATE TABLE t1(id int PRIMARY KEY, a text, b text);
CREATE TABLE
s_test_1=# CREATE TABLE t1(id int PRIMARY KEY, a text, b text);
CREATE TABLE
・パブリケーション設定
★idとaの列だけをレプリケーション
p_test_1=# CREATE PUBLICATION p1 FOR TABLE t1(id ,a);
CREATE PUBLICATION
p_test_1=# \dRp+
パブリケーション p1
所有者 | 全テーブル | Insert文 | Update文 | Delete文 | Truncate文 | 最上位パーティションテーブル経由
----------+------------+----------+----------+----------+------------+----------------------------------
postgres | f | t | t | t | t | f
テーブル:
"public.t1" (id, a)
・パブリッシャ側のテーブル内にデータを投入
p_test_1=# INSERT INTO t1 VALUES(1,'test-a-1','test-b-1');
INSERT 0 1
p_test_1=# INSERT INTO t1 VALUES(2,'test-a-2','test-b-2');
INSERT 0 1
p_test_1=# INSERT INTO t1 VALUES(3,'test-a-3','test-b-3');
INSERT 0 1
p_test_1=# INSERT INTO t1 VALUES(4,'test-a-4','test-b-4');
INSERT 0 1
p_test_1=# SELECT * FROM t1;
id | a | b
----+----------+----------
1 | test-a-1 | test-b-1
2 | test-a-2 | test-b-2
3 | test-a-3 | test-b-3
4 | test-a-4 | test-b-4
・サブスクリプションの設定
s_test_1=# CREATE TABLE t1(id int PRIMARY KEY, a text, b text);
CREATE TABLE
s_test_1=# CREATE SUBSCRIPTION s1
CONNECTION 'host=192.168.162.17 port=5432 user=logi_user dbname=p_test_1'
PUBLICATION p1;
NOTICE: 発行サーバーでレプリケーションスロット"s1"を作成しました
CREATE SUBSCRIPTION
・サブスクライバ側のテーブルを確認すると、パブリッシャ側で指定したidとaの列のみ、複製されている。
s_test_1=# SELECT * FROM t1 ORDER BY id;
id | a | b
----+----------+---
1 | test-a-1 |
2 | test-a-2 |
3 | test-a-3 |
4 | test-a-4 |
(4 行)
・パブリッシャ側でアップデートを実施。
p_test_1=# UPDATE t1 SET a='update' WHERE id='2';
UPDATE 1
p_test_1=# SELECT * FROM t1 ORDER BY id;
id | a | b
----+----------+----------
1 | test-a-1 | test-b-1
2 | update | test-b-2
3 | test-a-3 | test-b-3
4 | test-a-4 | test-b-4
・サブスクライバ側でも対象列の内容がアップデートされている。
s_test_1=# SELECT * FROM t1 ORDER BY id;
id | a | b
----+----------+---
1 | test-a-1 |
2 | update |
3 | test-a-3 |
4 | test-a-4 |
(4 行)
・レプリケーションをしていない列をアップデートしたら、もちろん更新されない。
p_test_1=# UPDATE t1 SET b='update' WHERE id='3';
UPDATE 1
p_test_1=# SELECT * FROM t1;
id | a | b
----+----------+----------
1 | test-a-1 | test-b-1
4 | test-a-4 | test-b-4
2 | update | test-b-3
3 | test-a-3 | update
(4 行)
s_test_1=# SELECT * FROM t1 ORDER BY id;
id | a | b
----+----------+---
1 | test-a-1 |
2 | update |
3 | test-a-3 |
4 | test-a-4 |
(4 行)
複製対象の列だけを用意したサブスクライバ
上述の確認ではパブリッシャ側とサブスクライバ側で全く同じテーブルを用意して列指定のレプリケーションを実行しました。
特定の列だけレプリケーションできるのであれば、サブスクライバ側のテーブルは複製する列だけでも動くのかという点が気になってきます。
次は、サブスクライバ側ではa列のみのテーブルを用意して、レプリケーションが動くのか確認してみます。
・パブリケーション設定
CREATE PUBLICATION p1 FOR TABLE t1(a);
CREATE PUBLICATION
p_test_1=# \dRp+
パブリケーション p1
所有者 | 全テーブル | Insert文 | Update文 | Delete文 | Truncate文 | 最上位パーティションテーブル経由
----------+------------+----------+----------+----------+------------+----------------------------------
postgres | f | t | t | t | t | f
テーブル:
"public.t1" (a)
・パブリッシャ側のテーブルには以下が投入されている。
p_test_1=# SELECT * FROM t1 ORDER BY id;
id | a | b
----+----------+----------
1 | test-a-1 | test-b-1
2 | test-a-2 | test-b-2
3 | test-a-3 | test-b-3
4 | test-a-4 | test-b-4
・サブスクリプションの設定
★ テーブルはa列だけを用意して、サブスクリプションする。
s_test_1=# CREATE TABLE t1(a text);
CREATE TABLE
s_test_1=# CREATE SUBSCRIPTION s1
CONNECTION 'host=192.168.162.17 port=5432 user=logi_user dbname=p_test_1'
PUBLICATION p1;
NOTICE: 発行サーバーでレプリケーションスロット"s1"を作成しました
CREATE SUBSCRIPTION
・サブスクライバ側のテーブルを確認すると、正常に複製されていそう。
s_test_1=# SELECT * FROM t1;
a
----------
test-a-1
test-a-2
test-a-3
test-a-4
(4 行)
・パブリッシャ側でアップデートを実施。
p_test_1=# UPDATE t1 SET a='update' WHERE id='3';
ERROR: テーブル"t1"の更新ができません
DETAIL: このパブリケーションで使用されてる列リストは識別列を包含していません。
以上のように特定列をレプリケーションしようとする際に、"識別列"がレプリケーション対象となっていないとアップデートがうまく機能しませんでした。
今回であれば、パブリッシャ側のid列が一意な値となるPRIMARY KEYとなっているため、列指定時はid列も指定する必要がありそうです。
PostgreSQL15へのアップデート前でも、"識別列"がないとアップデートや削除ができなかったので、この点は列指定ができるようになっても同様のようです。
PostgreSQL 文書の「31.4. Column Lists」章にも以下の記載がありました。1
If a publication publishes UPDATE or DELETE operations, any column list must include the table's replica identity columns (see ). If a publication publishes only INSERT operations, then the column list may omit replica identity columns.
《機械翻訳》パブリケーションがUPDATEまたはDELETE操作をパブリッシュする場合、どのカラム・リストにもテーブルのレプリカIDカラムが含まれている必要があります(REPLICA IDENTITYを参照)。 パブリケーションがINSERT操作のみをパブリッシュする場合、カラム・リストでレプリカIDカラムを省略できます。
パブリッシャ側/サブスクライバ側それぞれでid列をレプリケーションするとアップデートもうまくいきました。
・パブリッシャ側
p_test_1=# CREATE PUBLICATION p1 FOR TABLE t1(id,a);
CREATE PUBLICATION
p_test_1=# table t1;
id | a | b
----+----------+----------
1 | test-a-1 | test-b-1
2 | test-a-2 | test-b-2
3 | test-a-3 | test-b-3
4 | test-a-4 | test-b-4
(4 行)
p_test_1=# UPDATE t1 SET a='update' WHERE id='4';
UPDATE 1
p_test_1=# table t1;
id | a | b
----+----------+----------
1 | test-a-1 | test-b-1
2 | test-a-2 | test-b-2
3 | test-a-3 | test-b-3
4 | update | test-b-4
(4 行)
p_test_1=# DELETE FROM t1 WHERE id=4;
DELETE 1
p_test_1=# table t1;
id | a | b
----+----------+----------
1 | test-a-1 | test-b-1
2 | test-a-2 | test-b-2
3 | test-a-3 | test-b-3
(3 行)
・サブスクライバ側
★部分でパブリッシャ側の変更が反映されている
s_test_1=# CREATE TABLE t1(id int Primary key ,a text);
CREATE TABLE
s_test_1=# CREATE SUBSCRIPTION s1
CONNECTION 'host=192.168.162.17 port=5432 user=logi_user dbname=p_test_1'
PUBLICATION p1;
NOTICE: 発行サーバーでレプリケーションスロット"s1"を作成しました
CREATE SUBSCRIPTION
s_test_1=# table t1;
id | a
----+----------
1 | test-a-1
2 | test-a-2
3 | test-a-3
4 | test-a-4
(4 行)
s_test_1=# table t1;
id | a
----+----------
1 | test-a-1
2 | test-a-2
3 | test-a-3
4 | update ★
(4 行)
s_test_1=# table t1;
id | a
----+----------
1 | test-a-1
2 | test-a-2
3 | test-a-3
(3 行)
★id=4は削除
しかし、サブスク側のid列にPRIMARY KEYを付与しない場合、
パブリッシャ側アップデートではエラーになりませんが、サブスクライバ側でアップデートがうまく行きませんでした。
・パブリッシャ側のアップデート
p_test_1=# CREATE PUBLICATION p1 FOR TABLE t1(id ,a);
p_test_1=# SELECT * FROM t1 ORDER BY id;
id | a | b
----+----------+----------
1 | test-a-1 | test-b-1
2 | test-a-2 | test-b-2
3 | test-a-3 | test-b-3
4 | test-a-4 | test-b-4
(4 行)
p_test_1=# UPDATE t1 SET a='update' WHERE id='3';
UPDATE 1
p_test_1=# SELECT * FROM t1 ORDER BY id;
id | a | b
----+----------+----------
1 | test-a-1 | test-b-1
2 | test-a-2 | test-b-2
3 | update | test-b-3
4 | test-a-4 | test-b-4
(4 行)
・サブスクライバ側の確認
s_test_1=# CREATE TABLE t1(id int,a text);
CREATE TABLE
s_test_1=# CREATE SUBSCRIPTION s1
CONNECTION 'host=192.168.162.17 port=5432 user=logi_user dbname=p_test_1'
PUBLICATION p1;
s_test_1=# SELECT * FROM t1;
id | a
----+----------
1 | test-a-1
2 | test-a-2
3 | test-a-3
4 | test-a-4
(4 行)
s_test_1=# SELECT * FROM t1;
id | a
----+----------
1 | test-a-1
2 | test-a-2
3 | test-a-3
4 | test-a-4
(4 行)
・サブスク側のサーバーログでは、以下のような主キーがないためエラーとなっている旨が出力されています。
2022-12-07 21:43:51.904 JST [2237] LOG: サブスクリプション"s1"に対応する論理レプリケーション適用ワーカーが起動しました
2022-12-07 21:43:51.921 JST [2237] ERROR: 論理レプリケーション先のリレーション"public.t1"が識別列インデックスも主キーをもっておらず、かつ発行されたリレーションがREPLICA IDENTITY FULLとなっていません
2022-12-07 21:43:51.921 JST [2237] 文脈: 0/2806798で終了したトランザクション836中、レプリケーション先リレーション"public.t1"に対するメッセージタイプ"UPDATE"でレプリケーション基点"pg_32833"のリモートからのデータを処理中
2022-12-07 21:43:51.923 JST [2220] LOG: バックグラウンドワーカー"logical replication worker" (PID 2237)は終了コード1で終了しました
複数パブリッシャ構成の検証
次は複数のデータベース上にあるテーブルを、全て同じサブスクライバにレプリケーションした際にどうなるか検証してみます。
上述の検証で、列指定の際にはPRIMARYKEYのような一意な値となる列を含めてレプリケーションを組む必要があると分かりました。
一意となる値をもたせて、以下2パターンに分けて検証してみました。
- パターン1：2つのパブリッシャ側テーブルでそれぞれ別の列を指定して、サブスクライバ側にレプリケーションする
- パターン2：2つのパブリッシャ側テーブルで同じ列を指定して、サブスクライバ側にレプリケーションする
今回の検証では、パブリッシャ側のテーブル、サブスクライバ側のテーブルを以下として作業をしています。
パブリッシャ①：p_test_1
パブリッシャ②：p_test_2
サブスクライバ：s_test_1
◆パターン1
2つのパブリッシャ側テーブルでそれぞれ別の列を指定して、サブスクライバ側にレプリケーションするパターン。
・パブリッシャ①の設定
CREATE TABLE t1(id int, a text, PRIMARY KEY(id));
CREATE TABLE
p_test_1=# INSERT INTO t1 VALUES(1, 't-a-1'),(2, 't-a-2'),(3, 't-a-3'),(4,'t-a-4'),(5,'t-a-5');
INSERT 0 5
p_test_1=# CREATE PUBLICATION p1 FOR TABLE t1(id,a);
CREATE PUBLICATION
p_test_1=# table t1;
id | a
----+-------
1 | t-a-1
2 | t-a-2
3 | t-a-3
4 | t-a-4
5 | t-a-5
(5 行)
・パブリッシャ②の設定
p_test_2=# CREATE TABLE t1(id_2 int, c text, PRIMARY KEY(id_2));
CREATE TABLE
p_test_2=# INSERT INTO t1 VALUES(1, 't-c-1'),(2, 't-c-2'),(3, 't-c-3'),(4, 't-c-4'),(5, 't-c-5');
INSERT 0 5
p_test_2=# CREATE PUBLICATION p2 FOR TABLE t1(id_2,c);
CREATE PUBLICATION
p_test_2=# TABLE t1;
id_2 | c
------+-------
1 | t-c-1
2 | t-c-2
3 | t-c-3
4 | t-c-4
5 | t-c-5
(5 行)
・サブスクライバ側の設定
s_test_1=# CREATE TABLE t1(id int,id_2 int,a text,c text,PRIMARY KEY (id,id_2));
CREATE TABLE
s_test_1=# CREATE SUBSCRIPTION s1
CONNECTION 'host=192.168.162.17 port=5432 user=logi_user dbname=p_test_1'
PUBLICATION p1;
NOTICE: 発行サーバーでレプリケーションスロット"s1"を作成しました
CREATE SUBSCRIPTION
s_test_1=# CREATE SUBSCRIPTION s2
CONNECTION 'host=192.168.162.17 port=5432 user=logi_user dbname=p_test_2'
PUBLICATION p2;
NOTICE: 発行サーバーでレプリケーションスロット"s2"を作成しました
CREATE SUBSCRIPTION
・サブスクライバ側でデータの確認
s_test_1=# TABLE t1;
id | id_2 | a | c
----+------+---+---
(0 行)
うまくサブスクライバ側にレプリケーションができていませんでした。
・サーバーログを確認
2022-12-09 13:21:46.828 JST [2902] ERROR: リレーション"t1"の列"id_2"のNULL値が非NULL制約に違反しています
2022-12-09 13:21:46.828 JST [2902] 詳細: 失敗した行は(1, null, t-a-1, null)を含みます
2022-12-09 13:21:46.828 JST [2902] 文脈: t1のCOPY、行 1: "1 t-a-1"
2022-12-09 13:21:46.831 JST [1470] LOG: バックグラウンドワーカー"logical replication worker" (PID 2902)は終了コード1で終了しました
サブスクライバ側のテーブルでidとid_2をPRIMARY KEYにしてしまったため、非NULL制約に引っかかってしまっているようです。
サブスクライバ側で該当列をNULLを許容するUNIQUEキーにしてみました。
・サブスクリプション設定
s_test_1=# CREATE TABLE t1(id int,id_2 int,a text,c text,UNIQUE (id,id_2));
CREATE TABLE
s_test_1=# CREATE SUBSCRIPTION s1
CONNECTION 'host=192.168.162.17 port=5432 user=logi_user dbname=p_test_1'
PUBLICATION p1;
NOTICE: 発行サーバーでレプリケーションスロット"s1"を作成しました
CREATE SUBSCRIPTION
s_test_1=# CREATE SUBSCRIPTION s2
CONNECTION 'host=192.168.162.17 port=5432 user=logi_user dbname=p_test_2'
PUBLICATION p2;
NOTICE: 発行サーバーでレプリケーションスロット"s2"を作成しました
CREATE SUBSCRIPTION
・サブスクライバ側でデータの確認
INSERTがうまくいきました。
s_test_1=# TABLE t1;
id | id_2 | a | c
----+------+-------+-------
1 | | t-a-1 |
2 | | t-a-2 |
3 | | t-a-3 |
4 | | t-a-4 |
5 | | t-a-5 |
| 1 | | t-c-1
| 2 | | t-c-2
| 3 | | t-c-3
| 4 | | t-c-4
| 5 | | t-c-5
(10 行)
・パブリッシャ①でアップデート実施
p_test_1=# UPDATE t1 SET a='update' WHERE id='1';
UPDATE 1
p_test_1=# table t1;
id | a
----+--------
2 | t-a-2
3 | t-a-3
4 | t-a-4
5 | t-a-5
1 | update
(5 行)
・サブスクライバ側でデータの確認
アップデートされていない
s_test_1=# TABLE t1;
id | id_2 | a | c
----+------+-------+-------
1 | | t-a-1 |
2 | | t-a-2 |
3 | | t-a-3 |
4 | | t-a-4 |
5 | | t-a-5 |
| 1 | | t-c-1
| 2 | | t-c-2
| 3 | | t-c-3
| 4 | | t-c-4
| 5 | | t-c-5
(10 行)
・サーバーログをみると以下エラーが出ている
2022-12-09 13:27:46.454 JST [2960] LOG: サブスクリプション"s1"に対応する論理レプリケーション適用ワーカーが起動しました
2022-12-09 13:27:46.472 JST [2960] ERROR: 論理レプリケーション先のリレーション"public.t1"が識別列インデックスも主キーをもっておらず、かつ発行されたリレーションがREPLICA IDENTITY FULLとなっていません
2022-12-09 13:27:46.472 JST [2960] 文脈: 0/2AB9408で終了したトランザクション961中、レプリケーション先リレーション"public.t1"に対するメッセージタイプ"UPDATE"でレプリケーション基点"pg_41168"のリモートからのデータを処理中
2022-12-09 13:27:46.475 JST [1470] LOG: バックグラウンドワーカー"logical replication worker" (PID 2960)は終了コード1で終了しました
サブスクライバ側でidとid_2をUNIQUEにしただけでは、REPLICA IDENTITYを満たせないようです。1
REPLICA IDENTITY
This form changes the information which is written to the write-ahead log to identify rows which are updated or deleted. In most cases, the old value of each column is only logged if it differs from the new value; however, if the old value is stored externally, it is always logged regardless of whether it changed. This option has no effect except when logical replication is in use.
この形式は、先行書き込みログに書き込まれる情報を変更して、更新または削除された行を識別します。 ほとんどの場合、各列の古い値は、新しい値と異なる場合にのみログに記録されます。 ただし、古い値が外部に保存されている場合は、変更されたかどうかに関係なく、常にログに記録されます。 このオプションは、論理レプリケーションが使用されている場合を除いて何の効果もありません。
◆パターン2
それではパブリッシャ①と②で同じ列をPRIMARY KEYにした場合はどうなるか確認してみます。
①と②でid列をPRIMARY KEYとして、一つのサブスクライバテーブルにレプリケーションしてみます。
・パブリッシャ①の設定
CREATE TABLE t1(id int, a text, PRIMARY KEY(id));
CREATE TABLE
p_test_1=# INSERT INTO t1 VALUES(1, 't-a-1'),(2, 't-a-2'),(3, 't-a-3'),(4,'t-a-4'),(5,'t-a-5');
INSERT 0 5
p_test_1=# CREATE PUBLICATION p1 FOR TABLE t1(id,a);
CREATE PUBLICATION
p_test_1=# table t1;
id | a
----+-------
1 | t-a-1
2 | t-a-2
3 | t-a-3
4 | t-a-4
5 | t-a-5
(5 行)
・パブリッシャ②の設定
p_test_2=# CREATE TABLE t1(id int, c text, d text, PRIMARY KEY(id));
CREATE TABLE
p_test_2=# CREATE PUBLICATION p2 FOR TABLE t1(id,c);
CREATE PUBLICATION
p_test_2=# INSERT INTO t1 VALUES(1, 't-c-1', 't-d-1'),(2, 't-c-2', 't-d-2'),(3, 't-c-3', 't-d-3'),(4, 't-c-4', 't-d-4'),(5, 't-c-5', 't-d-5');
INSERT 0 5
p_test_2=# TABLE t1;
id | c | d
----+-------+-------
1 | t-c-1 | t-d-1
2 | t-c-2 | t-d-2
3 | t-c-3 | t-d-3
4 | t-c-4 | t-d-4
5 | t-c-5 | t-d-5
(5 行)
・サブスクライバ
パブリッシャ①の方はレプリケーションされていますが、パブリッシャ②のデータは反映されていません。
s_test_1=# CREATE TABLE t1(id int,a text,c text,PRIMARY KEY (id));
CREATE TABLE
s_test_1=# CREATE SUBSCRIPTION s1
CONNECTION 'host=192.168.162.17 port=5432 user=logi_user dbname=p_test_1'
PUBLICATION p1;
NOTICE: 発行サーバーでレプリケーションスロット"s1"を作成しました
CREATE SUBSCRIPTION
s_test_1=# CREATE SUBSCRIPTION s2
CONNECTION 'host=192.168.162.17 port=5432 user=logi_user dbname=p_test_2'
PUBLICATION p2;
NOTICE: 発行サーバーでレプリケーションスロット"s2"を作成しました
CREATE SUBSCRIPTION
s_test_1=# TABLE t1;
id | a | c
----+-------+---
1 | t-a-1 |
2 | t-a-2 |
3 | t-a-3 |
4 | t-a-4 |
5 | t-a-5 |
(5 行)
ログを見てみると、一意性の制約に違反しています。
2022-12-09 13:35:45.218 JST [3049] LOG: サブスクリプション"s2"、テーブル"t1"に対応する論理レプリケーションテーブル同期ワーカーが起動しました
2022-12-09 13:35:45.301 JST [3049] ERROR: 重複したキー値は一意性制約"t1_pkey"違反となります
2022-12-09 13:35:45.301 JST [3049] 詳細: キー (id)=(1) はすでに存在します。
2022-12-09 13:35:45.301 JST [3049] 文脈: t1のCOPY、行 1
2022-12-09 13:35:45.303 JST [3003] LOG: バックグラウンドワーカー"logical replication worker" (PID 3049)は終了コード1で終了しました
サブスクライバ側のid列の値をみて、パブリッシャ②のc列を更新してくれたらいいなと考えていたのですが、
パブリッシャ①をINSERT⇒パブリッシャ②をINSERTのように動いているようです。
解消するためには、パブリッシャ①と②それぞれのPRIMARY KEYは被らない値となっている必要がありそうです。
・パブリッシャ①のテーブル
p_test_1=# table t1;
id | a | b
----+-------+-------
1 | t-a-1 | t-b-1
2 | t-a-2 | t-b-2
3 | t-a-3 | t-b-3
4 | t-a-4 | t-b-4
5 | t-a-5 | t-b-5
(5 行)
・パブリッシャ②のテーブル
p_test_2=# TABLE t1;
id | c | d
----+--------+--------
6 | t-c-6 | t-d-6
7 | t-c-7 | t-d-7
8 | t-c-8 | t-d-8
9 | t-c-9 | t-d-9
10 | t-c-10 | t-d-10
(5 行)
・サブスクライバ側
s_test_1=# CREATE TABLE t1(id int PRIMARY KEY,a text,c text);
CREATE TABLE
s_test_1=# CREATE SUBSCRIPTION s1
CONNECTION 'host=192.168.162.17 port=5432 user=logi_user dbname=p_test_1'
PUBLICATION p1;
NOTICE: 発行サーバーでレプリケーションスロット"s1"を作成しました
CREATE SUBSCRIPTION
s_test_1=# CREATE SUBSCRIPTION s2
CONNECTION 'host=192.168.162.17 port=5432 user=logi_user dbname=p_test_2'
PUBLICATION p2;
CREATE SUBSCRIPTION
s_test_1=# TABLE t1;
id | a | c
----+-------+--------
1 | t-a-1 |
2 | t-a-2 |
3 | t-a-3 |
4 | t-a-4 |
5 | t-a-5 |
6 | | t-c-6
7 | | t-c-7
8 | | t-c-8
9 | | t-c-9
10 | | t-c-10
(10 行)
アップデートも反映されています。
・パブリッシャ①でid=1のa列をアップデート
p_test_1=# UPDATE t1 SET a='update' WHERE id='1';
UPDATE 1
p_test_1=# table t1;
id | a | b
----+--------+-------
2 | t-a-2 | t-b-2
3 | t-a-3 | t-b-3
4 | t-a-4 | t-b-4
5 | t-a-5 | t-b-5
1 | update | t-b-1
(5 行)
・サブスクライバ側
id=1のa列はアップデートされる
s_test_1=# TABLE t1;
id | a | c
----+--------+--------
2 | t-a-2 |
3 | t-a-3 |
4 | t-a-4 |
5 | t-a-5 |
6 | | t-c-6
7 | | t-c-7
8 | | t-c-8
9 | | t-c-9
10 | | t-c-10
1 | update |
(10 行)
行フィルタ
行フィルタ機能とは、レプリケーションをするテーブルのうち、条件を満たしている行だけを複製する機能となります。
PostgreSQL1から、テーブル内で条件の一致するの特定の行をレプリケーションすることができるようになりました。
基本動作
a列の値が5より小さい場合のみ複製するロジカルレプリケーション構成を組んでみたいと思います。
p_test_1：パブリッシャ側データベース
s_test_1：サブスクライバ側データベース
行フィルタで指定している列をPRIMARY KEYとしない場合
行フィルタとしているa列をPRIMARY KEYにしないで、構成を組んでみます。
・テーブル作成
p_test_1=# CREATE TABLE t1(id int PRIMARY KEY, a int , b text );
CREATE TABLE
s_test_1=# CREATE TABLE t1(id int PRIMARY KEY, a int , b text );
CREATE TABLE
・パブリッシャ設定
aが5より小さいものをレプリケーション
p_test_1=# CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a < 5);
CREATE PUBLICATION
p_test_1=# \dRp+
パブリケーション p1
所有者 | 全テーブル | Insert文 | Update文 | Delete文 | Truncate文 | 最上位パーティションテーブル経由
----------+------------+----------+----------+----------+------------+----------------------------------
postgres | f | t | t | t | t | f
テーブル:
"public.t1" WHERE (a < 5)
p_test_1=# table t1;
id | a | b
----+-----+----------
1 | 1 | test-b-1
2 | 2 | test-b-2
3 | 3 | test-b-3
4 | 10 | test-b-4
5 | 111 | test-b-5
6 | 4 | test-b-6
(6 行)
・サブスクライブの設定
s_test_1=# CREATE SUBSCRIPTION s1
CONNECTION 'host=192.168.162.17 port=5432 user=logi_user dbname=p_test_1'
PUBLICATION p1;
NOTICE: 発行サーバーでレプリケーションスロット"s1"を作成しました
・サブスクライバ側では、aが5以下のデータのみ複製されている。
s_test_1=# table t1;
id | a | b
----+---+----------
1 | 1 | test-b-1
2 | 2 | test-b-2
3 | 3 | test-b-3
6 | 4 | test-b-6
(4 行)
・アップデート
パブリッシャ側id=1のa列を3にアップデート
p_test_1=# UPDATE t1 SET a=3 WHERE id='1';
ERROR: テーブル"t1"の更新ができません
DETAIL: このパブリケーションのWHERE式で使用されている列は識別列の一部ではありません。
エラーとなりました。
こちらも列指定の時と同様に、行フィルタで指定する値は識別列としなければいけないみたいです。
PostgreSQL 文書の「31.3. Row Filters」に以下の記載がありました。1
If a publication publishes UPDATE or DELETE operations, the row filter WHERE clause must contain only columns that are covered by the replica identity (see ). If a publication publishes only INSERT operations, the row filter WHERE clause can use any column.
《機械翻訳》パブリケーションがUPDATEまたはDELETE操作をパブリッシュする場合、行フィルタWHERE句には、レプリカIDの対象となる列のみを含める必要があります(REPLICA IDENTITYを参照)。 パブリケーションがINSERT操作のみをパブリッシュする場合、行フィルタWHERE句は任意の列を使用できます。
行フィルタで指定している列をパブリッシャ/サブスクライバ側の両方ともPRIMARY KEYにした場合
パブリッシャ側テーブル/サブスクライバ側テーブルそれぞれでa列もPRIMARY KEYにして、再度アップデートを実行してみました。
・テーブル作成
p_test_1=# CREATE TABLE t1(id int, a int, b text ,PRIMARY KEY (id,a));
CREATE TABLE
CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a < 5);
CREATE PUBLICATION
p_test_1=# table t1;
id | a | b
----+-----+----------
1 | 1 | test-b-1
2 | 2 | test-b-2
3 | 3 | test-b-3
4 | 10 | test-b-4
5 | 111 | test-b-5
6 | 4 | test-b-6
(6 行)
・サブスクライバ側も設定
s_test_1=# CREATE TABLE t1(id int ,a int,b text,PRIMARY KEY(id,a));
CREATE TABLE
s_test_1=# CREATE SUBSCRIPTION s1
CONNECTION 'host=192.168.162.17 port=5432 user=logi_user dbname=p_test_1'
PUBLICATION p1;
NOTICE: 発行サーバーでレプリケーションスロット"s1"を作成しました
CREATE SUBSCRIPTION
s_test_1=# table t1;
id | a | b
----+---+----------
1 | 1 | test-b-1
2 | 2 | test-b-2
3 | 3 | test-b-3
6 | 4 | test-b-6
(4 行)
・パブリッシャ側でid=1のa列をフィルタの範囲内であるa=3でアップデートを実行
p_test_1=# UPDATE t1 SET a=3 WHERE id='1';
UPDATE 1
p_test_1=# TABLE t1;
id | a | b
----+-----+----------
2 | 2 | test-b-2
3 | 3 | test-b-3
4 | 10 | test-b-4
5 | 111 | test-b-5
6 | 4 | test-b-6
1 | 3 | test-b-1
(6 行)
・サブスクライバ側に反映されている
s_test_1=# table t1;
id | a | b
----+---+----------
2 | 2 | test-b-2
3 | 3 | test-b-3
6 | 4 | test-b-6
1 | 3 | test-b-1
(4 行)
・id=2のa列が5以上になるように、パブリッシャ側でa=5でアップデート
p_test_1=# UPDATE t1 SET a=5 WHERE id='2';
UPDATE 1
p_test_1=# TABLE t1;
id | a | b
----+-----+----------
3 | 3 | test-b-3
4 | 10 | test-b-4
5 | 111 | test-b-5
6 | 4 | test-b-6
1 | 3 | test-b-1
2 | 5 | test-b-2
(6 行)
・サブスクライバ側からid=2の行が削除されている
s_test_1=# table t1;
id | a | b
----+---+----------
3 | 3 | test-b-3
6 | 4 | test-b-6
1 | 3 | test-b-1
(3 行)
・id=4のa列が5より小さくなるように、パブリッシャ側でa=4でアップデート
p_test_1=# UPDATE t1 SET a=4 WHERE id='4';
UPDATE 1
p_test_1=# table t1;
id | a | b
----+-----+----------
3 | 3 | test-b-3
5 | 111 | test-b-5
6 | 4 | test-b-6
1 | 3 | test-b-1
2 | 5 | test-b-2
4 | 4 | test-b-4
(6 行)
・サブスクライバ側ではid=4の行が追加されている
s_test_1=# table t1;
id | a | b
----+---+----------
3 | 3 | test-b-3
6 | 4 | test-b-6
1 | 3 | test-b-1
4 | 4 | test-b-4
(4 行)
行フィルタで指定している列をパブリッシャ側のみPRIMARY KEYにした場合
以下ではサブスクライバ側a列をPRIMARY KEYにしないで再度アップデートを実行してみました。
・パブリッシャ側テーブル
p_test_1=# \d t1
テーブル"public.t1"
列 | タイプ | 照合順序 | Null 値を許容 | デフォルト
----+---------+----------+---------------+------------
id | integer | | not null |
a | integer | | not null |
b | text | | |
インデックス:
"t1_pkey" PRIMARY KEY, btree (id, a)
パブリケーション:
"p1" WHERE (a < 5)
p_test_1=# table t1;
id | a | b
----+-----+----------
1 | 1 | test-b-1
2 | 2 | test-b-2
3 | 3 | test-b-3
4 | 10 | test-b-4
5 | 111 | test-b-5
6 | 4 | test-b-6
(6 行)
・サブスクライバ側設定
s_test_1=# CREATE TABLE t1(id int , a int , b text,PRIMARY KEY(id));
CREATE TABLE
s_test_1=# \d t1
テーブル"public.t1"
列 | タイプ | 照合順序 | Null 値を許容 | デフォルト
----+---------+----------+---------------+------------
id | integer | | not null |
a | integer | | |
b | text | | |
インデックス:
"t1_pkey" PRIMARY KEY, btree (id)
s_test_1=# CREATE SUBSCRIPTION s1
CONNECTION 'host=192.168.162.17 port=5432 user=logi_user dbname=p_test_1'
PUBLICATION p1;
NOTICE: 発行サーバーでレプリケーションスロット"s1"を作成しました
CREATE SUBSCRIPTION
・サブスクライバ側のテーブルを見てみると条件通りレプリケーションされている。
s_test_1=# table t1;
id | a | b
----+---+----------
1 | 1 | test-b-1
2 | 2 | test-b-2
3 | 3 | test-b-3
6 | 4 | test-b-6
(4 行)
・パブリッシャ側でid:1のa列が3になるようアップデート
p_test_1=# UPDATE t1 SET a=3 WHERE id='1';
UPDATE 1
p_test_1=# table t1;
id | a | b
----+-----+----------
2 | 2 | test-b-2
3 | 3 | test-b-3
4 | 10 | test-b-4
5 | 111 | test-b-5
6 | 4 | test-b-6
1 | 3 | test-b-1
(6 行)
・サブスクライバ側も正しくアップデートされている
s_test_1=# table t1;
id | a | b
----+---+----------
2 | 2 | test-b-2
3 | 3 | test-b-3
6 | 4 | test-b-6
1 | 3 | test-b-1
(4 行)
フィルタ範囲から外したり範囲内にしたりしてもアップデートは動作していました。
・パブリッシャ側で行フィルタの範囲外となるようid=2の行をa=5で更新、
もともと行フィルタ範囲外だったid=4の行は範囲内になるようにa=4で更新
p_test_1=# table t1;
id | a | b
----+-----+----------
2 | 2 | test-b-2
3 | 3 | test-b-3
4 | 10 | test-b-4
5 | 111 | test-b-5
6 | 4 | test-b-6
1 | 3 | test-b-1
(6 行)
p_test_1=# UPDATE t1 SET a=5 WHERE id='2';
UPDATE 1
p_test_1=# UPDATE t1 SET a=4 WHERE id='4';
UPDATE 1
p_test_1=# table t1;
id | a | b
----+-----+----------
3 | 3 | test-b-3
5 | 111 | test-b-5
6 | 4 | test-b-6
1 | 3 | test-b-1
2 | 5 | test-b-2
4 | 4 | test-b-4
(6 行)
サブスクライバ側では行フィルタの範囲に従って反映されていました。
・更新前
s_test_1=# table t1;
id | a | b
----+---+----------
2 | 2 | test-b-2
3 | 3 | test-b-3
6 | 4 | test-b-6
1 | 3 | test-b-1
(4 行)
・更新後
s_test_1=# table t1;
id | a | b
----+---+----------
3 | 3 | test-b-3
6 | 4 | test-b-6
1 | 3 | test-b-1
4 | 4 | test-b-4
(4 行)
列指定の時とは異なり、行フィルタで"識別列"を意識しないといけないのは、パブリッシャ側だけのようでした。
ざっくりまとめ
「列指定」と「行フィルタ」ともに"識別列"を意識して対象を整理することで動かすことができました。
- 列指定 ：
- パブリッシャ/サブスクライバ側それぞれの対象テーブルが"識別列"とする必要がある
- 行フィルタ：
- パブリッシャ側でフィルタに使用する列を"識別列"にする必要がある
おわりに
今回の検証では、PostgreSQL15のメジャーバージョンアップデートで追加されたロジカルレプリケーションの機能のうち、
「列指定」と「行フィルタ」にフォーカスを置いて検証をしました。
どちらの機能もしっかりと使うためには、"識別列"の設計が重要ということが今回の検証で見えてきました。
PostgreSQL15では、その他にも以下のような機能が追加されロジカルレプリケーション機能が強化されています。
- スキーマ内の全テーブル指定
- エラー時のSUBSCRIPTION無効化
- LSNスキップ
- 二相コミット対応 等...
記事に記載したもの以外にも、色々と機能が追加されているので今後検証をしてみたいですね。
また、NTTテクノクロスのWEBサイトでも PostgreSQLの技術記事を掲載しておりますので、ぜひこちらもご覧ください。
ぬこのたのしいぽすぐれ教室 | 情報畑でつかまえて | NTTテクノクロスブログ
明日は、@uehajさんです！
引き続き、NTTテクノクロス Advent Calendar 2022 をお楽しみください。