スキーマ内のテーブルを対象とした論理レプリケーション – PostgreSQL 15でコミットされた機能の先行紹介：技術者Blog
PostgreSQLインサイド

この記事では、富士通OSSチームがPostgreSQLオープンソースコミュニティーと協力して、次期PostgreSQL 15に追加した、スキーマ内のテーブル（スキーマ内のすべてのテーブルが対象）の論理レプリケーションを可能にする新機能について説明します。

PostgreSQL 10から、パブリッシャーからサブスクライバーへの、データベース内の特定のテーブルまたはすべてのテーブルの論理レプリケーションがサポートされました。もし利用者が1つのスキーマや複数のスキーマに存在するテーブルをパブリッシュしたい場合、利用者はデータベースへクエリを実行してテーブルリストを手動で準備し、そのリストを使用してパブリケーションを作成する必要がありました。スキーマに存在するテーブルが少ない場合は、リストの作成は簡単ですが、スキーマに存在するテーブルが数百、数千に及ぶ場合は、面倒な作業になります。
この問題を解決するために、PostgreSQL 15では、パブリケーションの作成または変更時に "TABLES IN SCHEMA" というオプション機能が追加され、1つ以上のスキーマを指定することで、パブリッシャーがそのスキーマ配下のテーブルを選択してサブスクライバーにデータを送信できるようになりました。
なお、本記事では、スキーマ内のテーブルの論理レプリケーションを可能にするこの新機能を「スキーマパブリケーション」と定義します。

下図は、スキーマパブリケーションの動作を表しています。

上図を順に見ていきましょう。

• ① 利用者は、テーブルに対して様々なDML操作を行い、PREPARE / COMMIT操作を実行します。
• ② backendプロセスは、利用者が行った上記の操作に対してData / WALを生成します。
• ③ backendプロセスは、WALレコードが処理可能であることを知らせるために、SIGUSR1シグナルをwal senderプロセスに送信します。
• ④ wal senderプロセスは、WALレコードのロジカルデコーディングを開始し、pgoutputプラグインはWALから読み込んだ変更を変換します。
• ⑤ wal senderプロセスは、データがスキーマパブリケーションの一部であるかをチェックし、該当した場合、ストリーミングレプリケーションプロトコルを使用してapply workerプロセスにデータを連続的に転送します。
• ⑥ apply workerプロセスは、データをローカルテーブルにマッピングし、受け取った個々の変更を正しいトランザクション順に適用します。

実装の詳細は、以下ページでご覧いただけます。

• Allow publishing the tables of schema.（git.postgresql.org Git）

構文の改良

CREATE PUBLICATION文とALTER PUBLICATION文に新しいオプション "TABLES IN SCHEMA" が追加され、1つまたは複数のスキーマを指定できるようになりました。

例1

パブリケーションの作成または変更時に、指定したスキーマ内のテーブルを指定できます。

CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA sch1,sch2;
OR
ALTER PUBLICATION pub1 ADD TABLES IN SCHEMA sch3,sch4;

例2

パブリケーションの作成または変更時に、指定したスキーマのテーブルと一緒に、別のスキーマのテーブルを個別に指定できます。

CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA sch1,sch2, TABLE t1,t2;
OR
ALTER PUBLICATION pub1 ADD TABLES IN SCHEMA sch3,sch4, TABLE t3,t4;

すでにサブスクライブされているパブリケーションにスキーマを追加する場合は、サブスクライバー側で "ALTER SUBSCRIPTION ... REFRESH PUBLICATION" を実行する必要があることに注意してください。

新しいシステムテーブル pg_publication_namespace

利用者がパプリケーション用に指定したスキーマの情報を保持するために、新しいシステムテーブル "pg_publication_namespace" が追加されます。利用者は、pg_publication_namespaceとpg_publicationを使用して、パブリケーションとスキーマのマッピングを取得できます。

例3

postgres=# CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA sch1,sch2;
CREATE PUBLICATION
postgres=# SELECT pubname, pnnspid::regnamespace FROM pg_publication_namespace pn, pg_publication p WHERE pn.pnpubid = p.oid;
 pubname | pnnspid 
---------+---------
 pub1    | sch1
 pub1    | sch2
(2 rows)

pgoutputプラグインの改良

pgoutputプラグインは、リレーションがスキーマパブリケーションの一部であるかどうかをチェックし、変更をサブスクライバーにパブリッシュするよう改良されました。

スキーマパブリケーションの表示改良

スキーマパブリケーションを表示するために、¥d系列のコマンドが改良されました。"¥dRp+" と指定することで、パブリケーションに関連付けつけられてスキーマを表示します。

postgres=# CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA sch1,sch2;
CREATE PUBLICATION
postgres=# ¥dRp+ pub1

                             Publication pub1
  Owner  | All tables | Inserts | Updates | Deletes | Truncates | Via root
---------+------------+---------+---------+---------+-----------+----------
 Dba      | f          | t       | t       | t       | t         | f

Tables from schemas:
    "sch1"
    "sch2"

pg_dumpの改良

pg_dumpは、スキーマ内のテーブルをパブリッシュするために、パブリケーションの作成の有無を識別して "TABLES IN SCHEMA" オプションを含むパブリケーションのDDLをダンプするように改良されました。

pg_dumpが生成するDDLのサンプル

--
-- Name: pub1; Type: PUBLICATION; Schema: -; Owner: dba
--
CREATE PUBLICATION pub1 WITH (publish = 'insert, update, delete, truncate');
ALTER PUBLICATION pub1 OWNER TO dba;
--
-- Name: pub1 sch1; Type: PUBLICATION TABLES IN SCHEMA; Schema: sch1; Owner: dba
--
ALTER PUBLICATION pub1 ADD TABLES IN SCHEMA sch1;
--
-- Name: pub1 sch2; Type: PUBLICATION TABLES IN SCHEMA; Schema: sch2; Owner: dba
--
ALTER PUBLICATION pub1 ADD TABLES IN SCHEMA sch2;

psqlの改良

psqlにおいて、"TABLES IN SCHEMA" オプションを入力するときの「タブによる補完」をサポートしました。

新しく追加された "TABLES IN SCHEMA" オプションを使用する手順を説明します。

1. パブリッシャーとサブスクライバーにいくつかのスキーマとテーブルを作成します。

postgres=# CREATE SCHEMA sch1;
CREATE SCHEMA
postgres=# CREATE TABLE sch1.t1(c1 int);
CREATE TABLE
postgres=# CREATE TABLE sch1.t2(c1 int);
CREATE TABLE
postgres=# CREATE SCHEMA sch2;
CREATE SCHEMA
postgres=# CREATE TABLE sch2.t3(c1 int);
CREATE TABLE
postgres=# CREATE TABLE sch2.t4(c1 int);
CREATE TABLE

1. パブリッシャーでパブリケーションを作成します。

postgres=# CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA sch1,sch2;
CREATE PUBLICATION

1. パブリッシャーのホスト（host1）、データベース（postgres）およびポート番号（6666）を指定して、サブスクライバーにサブスクリプションを作成します。

postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=host1 dbname=postgres port=6666' PUBLICATION pub1;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION

1. パブリッシャーにデータを挿入します。

postgres=# INSERT INTO sch1.t1 VALUES(11);
INSERT 0 1
postgres=# INSERT INTO sch1.t2 VALUES(12);
INSERT 0 1
postgres=# INSERT INTO sch2.t3 VALUES(23);
INSERT 0 1
postgres=# INSERT INTO sch2.t4 VALUES(24);
INSERT 0 1

1. パブリッシャーによってパブリッシュされたデータがサブスクライバーに論理的にレプリケーションされたことを確認します。

postgres=# SELECT * FROM sch1.t1;
 c1 
----
 11
(1 rows)
postgres=# SELECT * FROM sch1.t2;
 c1 
----
 12
(1 rows)
postgres=# SELECT * FROM sch2.t3;
 c1 
----
 23
(1 rows)
postgres=# SELECT * FROM sch2.t4;
 c1 
----
 24
(1 rows)

PostgreSQL 15で入れた改良により、スキーマに存在するテーブルのデコードを可能にする基盤ができました。次のステップは、スキーマに存在するいくつかのテーブルをスキップさせる対応をPostgreSQL 16以降のバージョンで実装することです。

PostgreSQLコミュニティーからのフィードバックにより、"ALL TABLES IN SCHEMA"構文を"TABLES IN SCHEMA"構文に変更しました。

2022年11月21日更新