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

Vigneshwaran C

FUJITSU Limited
Software Products Business Unit Data Management Division
Software Lead Developer

はじめに

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

背景

PostgreSQL 14は、パブリッシャーからサブスクライバーへの、データベース内の特定のテーブルまたはすべてのテーブルの論理レプリケーションをサポートしました。もし利用者が1つのスキーマや複数のスキーマに存在するテーブルをパブリッシュしたい場合、利用者はデータベースへクエリを実行してテーブルリストを手動で準備し、そのリストを使用してパブリケーションを作成する必要がありました。スキーマに存在するテーブルが少ない場合は、リストの作成は簡単ですが、スキーマに存在するテーブルが数百、数千に及ぶ場合は、面倒な作業になります。
この問題を解決するために、PostgreSQL 15では、パブリケーションの作成または変更時に "ALL 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プロセスは、データをローカルテーブルにマッピングし、受け取った個々の変更を正しいトランザクション順に適用します。

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

構文の改良

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

例1

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

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

例2

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

CREATE PUBLICATION pub1 FOR ALL TABLES IN SCHEMA sch1,sch2, TABLE t1,t2;
OR
ALTER PUBLICATION pub1 ADD ALL 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 ALL 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 ALL 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は、スキーマ内の全テーブルをパブリッシュするために、パブリケーションの作成の有無を識別して "ALL 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 ALL TABLES IN SCHEMA sch1;
--
-- Name: pub1 sch2; Type: PUBLICATION TABLES IN SCHEMA; Schema: sch2; Owner: dba
--
ALTER PUBLICATION pub1 ADD ALL TABLES IN SCHEMA sch2;

psqlの改良

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

"ALL TABLES IN SCHEMA data" の使い方は?

新しく追加された "ALL 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 ALL 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.t1;
 c1 
----
 23
(1 rows)
postgres=# SELECT * FROM sch2.t2;
 c1 
----
 24
(1 rows)

今後に向けて

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

2022年3月11日公開

本コンテンツに関するお問い合わせ

お電話でのお問い合わせ

Webでのお問い合わせ

当社はセキュリティ保護の観点からSSL技術を使用しております。

ページの先頭へ