MaterializedPostgreSQL

PostgreSQL データベースのテーブルを取り込んだ ClickHouse データベースを作成します。まず、MaterializedPostgreSQL エンジンのデータベースが PostgreSQL データベースのスナップショットを作成し、必要なテーブルを読み込みます。必要なテーブルには、指定したデータベース内の任意のスキーマにある任意のテーブルの部分集合を含めることができます。スナップショットの作成とあわせて、データベースエンジンは LSN を取得し、テーブルの初期ダンプが完了すると WAL から更新の取り込みを開始します。データベースの作成後に PostgreSQL データベースへ新たに追加されたテーブルは、自動的にはレプリケーションに追加されません。これらは ATTACH TABLE db.table クエリで手動で追加する必要があります。

レプリケーションは PostgreSQL Logical Replication Protocol を用いて実装されています。このプロトコルでは DDL はレプリケートできませんが、レプリケーションを破壊する変更 (カラム型の変更、カラムの追加・削除) が発生したかどうかは把握できます。こうした変更が検出されると、該当するテーブルは更新を受信しなくなります。この場合は、ATTACH / DETACH PERMANENTLY クエリを使用してテーブル全体を再読み込みする必要があります。DDL がレプリケーションを破壊しない場合 (たとえばカラム名の変更など) は、テーブルは引き続き更新を受信します (挿入は位置ベースで行われます) 。

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializedPostgreSQL('host:port', 'database', 'user', 'password') [SETTINGS ...]

エンジンパラメータ

• host:port — PostgreSQL サーバーのエンドポイント。
• database — PostgreSQL データベースの名前。
• user — PostgreSQL ユーザー。
• password — ユーザーのパスワード。

CREATE DATABASE postgres_db
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password');

SHOW TABLES FROM postgres_db;

┌

MaterializedPostgreSQL データベースを作成しても、対応する PostgreSQL データベース内の新しいテーブルは自動では検出されません。こうしたテーブルは手動で追加できます。

ATTACH TABLE postgres_database.new_table;

特定のテーブルをレプリケーション対象から外すことができます。

DETACH TABLE postgres_database.table_to_remove PERMANENTLY;

PostgreSQL のスキーマは、3 つの方法で設定できます (バージョン 21.12 以降) 。

1. MaterializedPostgreSQL データベースエンジン 1 つにつき 1 つのスキーマ。設定 materialized_postgresql_schema を使用する必要があります。 テーブルには、テーブル名だけでアクセスします:

CREATE DATABASE postgres_database
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_schema = 'postgres_schema';

SELECT * FROM postgres_database.table1;

2. 1 つの MaterializedPostgreSQL データベースエンジンに対して、指定したテーブルのセットを含むスキーマを任意の数だけ指定できます。設定 materialized_postgresql_tables_list を使用する必要があります。各テーブルは、対応するスキーマとあわせて記述します。 テーブルには、スキーマ名とテーブル名を併せて使用してアクセスします:

CREATE DATABASE database1
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_tables_list = 'schema1.table1,schema2.table2,schema1.table3',
         materialized_postgresql_tables_list_with_schema = 1;

SELECT * FROM database1.`schema1.table1`;
SELECT * FROM database1.`schema2.table2`;

ただし、この場合、materialized_postgresql_tables_list 内のすべてのテーブルは、スキーマ名を付けて記述する必要があります。 materialized_postgresql_tables_list_with_schema = 1 が必要です。

警告: この場合、テーブル名にドットは使用できません。

3. 1 つの MaterializedPostgreSQL データベースエンジンに対して、完全なテーブルセットを含む任意の数のスキーマ。設定 materialized_postgresql_schema_list を使用する必要があります。

CREATE DATABASE database1
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_schema_list = 'schema1,schema2,schema3';

SELECT * FROM database1.`schema1.table1`;
SELECT * FROM database1.`schema1.table2`;
SELECT * FROM database1.`schema2.table2`;

警告: この場合、テーブル名にドットは使用できません。

1. PostgreSQL の設定ファイルで、wal_level の値を logical に設定し、max_replication_slots パラメータの値を少なくとも 2 にする必要があります。

2. 各レプリケートテーブルには、以下のいずれかの replica identity が必要です。

• 主キー (デフォルト)

• 索引

postgres# CREATE TABLE postgres_table (a Integer NOT NULL, b Integer, c Integer NOT NULL, d Integer, e Integer NOT NULL);
postgres# CREATE unique INDEX postgres_table_index on postgres_table(a, c, e);
postgres# ALTER TABLE postgres_table REPLICA IDENTITY USING INDEX postgres_table_index;

主キーが常に最初に確認されます。主キーが存在しない場合は、replica identity 索引 として定義された索引が確認されます。 索引を replica identity として使用する場合、そのような索引は1つのテーブルに1つだけでなければなりません。 特定のテーブルでどの種類が使用されているかは、次のコマンドで確認できます。

postgres# SELECT CASE relreplident
          WHEN 'd' THEN 'default'
          WHEN 'n' THEN 'nothing'
          WHEN 'f' THEN 'full'
          WHEN 'i' THEN 'index'
       END AS replica_identity
FROM pg_class
WHERE oid = 'postgres_table'::regclass;

MaterializedPostgreSQLデータベースエンジンでレプリケートする、PostgreSQLデータベースのテーブルをカンマ区切りで指定します。

各テーブルでは、レプリケートするカラムの一部を角括弧で指定できます。カラムの指定を省略した場合は、そのテーブルのすべてのカラムがレプリケートされます。

materialized_postgresql_tables_list = 'table1(co1, col2),table2,table3(co3, col5, col7)

デフォルト値: 空のリスト — PostgreSQL データベース全体がレプリケートされます。

デフォルト値: 空文字列。 (デフォルトのスキーマが使用されます)

デフォルト値: 空のリスト。 (デフォルトのスキーマが使用されます)

データを PostgreSQL データベースのテーブルに書き出す前に、メモリに蓄積する行数を設定します。

設定可能な値:

• 正の整数。

デフォルト値: 65536.

ユーザーが作成したレプリケーションスロットです。materialized_postgresql_snapshot と一緒に使用する必要があります。

PostgreSQLテーブルの初期ダンプの実行元となるスナップショットを識別するテキスト文字列です。materialized_postgresql_replication_slotとあわせて使用する必要があります。

CREATE DATABASE database1
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_tables_list = 'table1,table2,table3';

SELECT * FROM database1.table1;

必要に応じて、設定はDDLクエリで変更できます。ただし、materialized_postgresql_tables_list 設定は変更できません。この設定内のテーブル一覧を更新するには、ATTACH TABLE クエリを使用します。

ALTER DATABASE postgres_database MODIFY SETTING materialized_postgresql_max_block_size = <new_size>;

レプリケーションには一意の consumer 識別子を使用します。デフォルトは 0 です。 1 に設定すると、同じ PostgreSQL テーブルを参照する複数の MaterializedPostgreSQL テーブルを設定できます。

PostgreSQL の date および timestamp/timestamptz 型を、これらの PostgreSQL 型よりも広い値範囲をカバーする ClickHouse の Date32 および DateTime64 にマッピングします。デフォルト: 1。 0 に設定すると、代わりにより範囲の狭い Date および DateTime 型が使用されます (それらの範囲外の値や、秒未満の精度を持つ値は表現できません) 。

この設定が制御するのは、ネストされたテーブルの作成時に型推論によって選択されるカラム型のみであるため、CREATE DATABASE の時点で指定する必要があります。その後に ALTER DATABASE ... MODIFY SETTING で変更することはできません (すでに作成されたネストされたテーブルは固定のカラム型を保持するため、このような変更は拒否されます) 。変更するには、データベースを再作成してください。カラム型が明示的に宣言される MaterializedPostgreSQL テーブルエンジンには適用されません。

TLS/SSLパラメータはlibpqに渡され、名前付きコレクションまたはエンジンの末尾に指定するキー・バリュー引数として指定できます。指定できるのは、sslmode (disable、allow、prefer、require、verify-ca、verify-full。未設定の場合はlibpqのデフォルトであるpreferが適用されます) と、証明書および秘密鍵です。証明書と秘密鍵は、次の2つの形式のいずれかで指定できます。sslrootcert (CA証明書) 、sslcert (クライアント証明書) 、sslkey (クライアント秘密鍵) はサーバーローカルファイルへのパスであり、サーバー設定ファイルで定義された名前付きコレクションからのみ指定できます。一方、sslrootcert_pem、sslcert_pem、sslkey_pemには対応するファイルの内容をリテラルとして指定でき、SQLから設定可能です。また、パスワードと同様に、ログおよびSHOWクエリではマスクされます。

TLSを必須とするPostgreSQLサーバーに接続し、サーバー証明書を検証する例:

CREATE DATABASE postgres_db
ENGINE = MaterializedPostgreSQL('postgres-host:5432', 'postgres_database', 'postgres_user', 'postgres_password',
                                sslmode = 'verify-full', sslrootcert_pem = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----');

TLS/SSL パラメータは、データベース の作成時に固定される PostgreSQL 接続パラメータの一部です。変更するには、データベース を再作成する必要があります。

プライマリに存在する論理レプリケーションスロットは、スタンバイレプリカでは利用できません。 そのため、フェイルオーバーが発生すると、新しいプライマリ (以前の物理スタンバイ) は、以前のプライマリに存在していたスロットを認識できません。これにより、PostgreSQL からのレプリケーションが中断されます。 この問題を回避するには、レプリケーションスロットを自分で管理し、永続的なレプリケーションスロットを定義します (詳細はこちらを参照してください) 。スロット名は materialized_postgresql_replication_slot 設定で渡す必要があり、そのスロットは EXPORT SNAPSHOT オプション付きで export されている必要があります。スナップショット識別子は materialized_postgresql_snapshot 設定で渡す必要があります。

これは、実際に必要な場合にのみ使用してください。明確な必要性がない場合や、理由を十分に理解していない場合は、テーブルエンジン にレプリケーションスロットの作成と管理を任せるほうが適切です。

例 (@bchrobot 提供)

1. PostgreSQL でレプリケーションスロットを設定します。

   apiVersion: "acid.zalan.do/v1"
   kind: postgresql
   metadata:
     name: acid-demo-cluster
   spec:
     numberOfInstances: 2
     postgresql:
       parameters:
         wal_level: logical
     patroni:
       slots:
         clickhouse_sync:
           type: logical
           database: demodb
           plugin: pgoutput

2. レプリケーションスロットの準備ができるまで待ってから、transaction を開始し、transaction スナップショット識別子を export します。

   BEGIN;
   SELECT pg_export_snapshot();

3. ClickHouse でデータベースを作成します。

   CREATE DATABASE demodb
   ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
   SETTINGS
     materialized_postgresql_replication_slot = 'clickhouse_sync',
     materialized_postgresql_snapshot = '0000000A-0000023F-3',
     materialized_postgresql_tables_list = 'table1,table2,table3';

4. ClickHouse DB へのレプリケーションを確認したら、PostgreSQL の transaction を終了します。フェイルオーバー後もレプリケーションが継続することを確認してください。

   kubectl exec acid-demo-cluster-0 -c postgres -- su postgres -c 'patronictl failover --candidate acid-demo-cluster-1 --force'

1. CREATE PUBLICATION – 作成権限。

2. CREATE_REPLICATION_SLOT – レプリケーション権限。

3. pg_drop_replication_slot – レプリケーション権限、またはスーパーユーザー権限。

4. DROP PUBLICATION – publication の所有者 (MaterializedPostgreSQL エンジン自体の username) 。

2 と 3 のコマンドを実行せず、これらの権限を持たずに済ませることも可能です。設定 materialized_postgresql_replication_slot と materialized_postgresql_snapshot を使用してください。ただし、十分に注意してください。

テーブルへのアクセス:

1. pg_publication

2. pg_replication_slots

3. pg_publication_tables

MaterializedPostgreSQLデータベースはバックアップできます。各レプリケートテーブルのデータは入れ子のReplacingMergeTreeテーブルに格納されるため、BACKUP DATABASEは入れ子テーブルに処理を委譲して、そのデータもバックアップ対象に含めます。

BACKUP DATABASE postgres_db TO Disk('backups', 'postgres_db.zip');

MaterializedPostgreSQL データベースまたはテーブルのインプレース復元はサポートされていません。復元された MaterializedPostgreSQL オブジェクトは、ライブの PostgreSQL ソースから直ちにレプリケーションを開始するため、その上にバックアップのスナップショットを復元すると、スナップショットと現在のリモートの状態が混在してしまいます。そのため、この場合 RESTORE は安全側に倒して失敗します。代わりに、取り込まれたデータを通常の ReplacingMergeTree テーブルに復元してください。

• データベースのバックアップでは、各テーブルの保存済み定義はすでに合成された入れ子の ReplacingMergeTree (MaterializedPostgreSQL エンジンではなく) になっているため、各テーブルはまだ存在していない新しいテーブルにそのまま復元できます。

  RESTORE TABLE postgres_db.table1 AS restored_db.table1
  FROM Disk('backups', 'postgres_db.zip')
  SETTINGS allow_different_table_def = 1;

• 単体の MaterializedPostgreSQL テーブルのバックアップでは、保存済み定義は MaterializedPostgreSQL エンジンそのものです。あらかじめ、入れ子テーブルと同じ構造 (_sign および _version カラムを含む) を持つ ReplacingMergeTree テーブルを作成しておき、そこに復元してください。

  RESTORE TABLE src AS existing_replacing_mergetree
  FROM Disk('backups', 'table.zip')
  SETTINGS allow_different_table_def = 1;