MaterializedPostgreSQL - ClickHouse Documentation

يُوصى لمستخدمي ClickHouse Cloud باستخدام ClickPipes لنسخ PostgreSQL إلى ClickHouse. ويدعم ذلك أصلاً CDC (التقاط تغييرات البيانات) عالي الأداء لـ PostgreSQL.

ينشئ قاعدة بيانات في ClickHouse تحتوي على جداول من قاعدة بيانات PostgreSQL. في البداية، تنشئ قاعدة البيانات التي تستخدم المحرك MaterializedPostgreSQL لقطة لقاعدة بيانات PostgreSQL وتحمّل الجداول المطلوبة. ويمكن أن تشمل الجداول المطلوبة أي subset من الجداول من أي subset من المخططات ضمن قاعدة البيانات المحددة. وبالتزامن مع إنشاء لقطة، يحصل محرك قاعدة البيانات على LSN، وبعد تنفيذ تفريغ أولي للجداول يبدأ في سحب التحديثات من WAL. بعد إنشاء قاعدة البيانات، لا تتم إضافة الجداول التي تُضاف لاحقاً إلى قاعدة بيانات PostgreSQL تلقائياً إلى النسخ المتماثل. ويجب إضافتها يدوياً باستخدام الاستعلام ATTACH TABLE db.table. يُنفَّذ النسخ المتماثل باستخدام PostgreSQL Logical Replication Protocol، الذي لا يسمح بتكرار DDL، لكنه يتيح معرفة ما إذا كانت قد حدثت breaking changes تؤثر في النسخ المتماثل (مثل تغيير نوع العمود أو إضافة الأعمدة/إزالتها). تُكتشف هذه التغييرات، وعندها تتوقف الجداول المعنية عن تلقي التحديثات. في هذه الحالة، يجب استخدام استعلامي ATTACH و DETACH PERMANENTLY لإعادة تحميل الجدول بالكامل. وإذا كان DDL لا يعطّل النسخ المتماثل (على سبيل المثال، إعادة تسمية عمود)، فسيستمر الجدول في تلقي التحديثات (تتم عملية insertion حسب الموضع).

محرك قاعدة البيانات هذا تجريبي. لاستخدامه، اضبط allow_experimental_database_materialized_postgresql على 1 في configuration files أو باستخدام الأمر SET:

SET allow_experimental_database_materialized_postgresql=1

إنشاء قاعدة بيانات

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;

┌─name───┐
│ table1 │
└────────┘

SELECT * FROM postgres_db.postgres_table;

إضافة جداول جديدة إلى النسخ المتماثل بشكل ديناميكي

بعد إنشاء قاعدة البيانات MaterializedPostgreSQL، فإنها لا تكتشف تلقائيًا الجداول الجديدة في قاعدة بيانات PostgreSQL المقابلة. ويمكن إضافة هذه الجداول يدويًا:

ATTACH TABLE postgres_database.new_table;

قبل الإصدار 22.1، كانت إضافة جدول إلى النسخ المتماثل تترك فتحة النسخ المتماثل مؤقتة بدون حذف (اسمها {db_name}_ch_replication_slot_tmp). إذا كنت تُرفق جداول في إصدار ClickHouse أقدم من 22.1، فتأكّد من حذفها يدويًا (SELECT pg_drop_replication_slot('{db_name}_ch_replication_slot_tmp')). وإلا فسيزداد استخدام القرص. وقد أُصلحت هذه المشكلة في الإصدار 22.1.

إزالة الجداول من النسخ المتماثل ديناميكيًا

يمكن إزالة جداول محددة من النسخ المتماثل:

DETACH TABLE postgres_database.table_to_remove PERMANENTLY;

مخطط PostgreSQL

يمكن إعداد مخطط PostgreSQL بثلاث طرق (ابتداءً من الإصدار 21.12).

مخطط واحد لكل محرك قاعدة بيانات MaterializedPostgreSQL. يتطلب ذلك استخدام الإعداد 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;

أي عدد من المخططات مع مجموعة جداول محددة لمحرك قاعدة بيانات 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. تحذير: في هذه الحالة، لا يُسمح بوجود نقاط في اسم الجدول.

أي عدد من الـ مخططات مع المجموعة الكاملة من الجداول لمحرك قاعدة البيانات 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`;

تحذير: في هذه الحالة، لا يُسمح بوجود نقاط في اسم الجدول.

المتطلبات

يجب أن تكون قيمة إعداد wal_level هي logical، وأن تكون قيمة المَعلمة max_replication_slots في ملف إعدادات PostgreSQL 2 على الأقل.
يجب أن يتضمن كل جدول مكرّر أحد خيارات هوية النسخة المتماثلة التالية:

المفتاح الأساسي (افتراضيًا)
فهرس

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;

يُتحقَّق دائمًا أولًا من المفتاح الأساسي. وإذا لم يكن موجودًا، فيُتحقَّق من الفهرس المعرَّف على أنه فهرس هوية النسخة المتماثلة. إذا استُخدم الفهرس بوصفه هويةً لنسخة متماثلة، فلا بد من وجود فهرس واحد فقط من هذا النوع في الجدول. يمكنك التحقق من النوع المستخدم لجدول معيّن باستخدام الأمر التالي:

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;

لا يدعم النسخ المتماثل لقيم TOAST. وسيُستخدم النوع الافتراضي للبيانات.

الإعدادات

`materialized_postgresql_tables_list`

يحدّد قائمة مفصولة بفواصل من جداول قاعدة بيانات PostgreSQL التي ستتم مزامنتها عبر محرك قاعدة البيانات MaterializedPostgreSQL. يمكن أن يتضمن كل جدول مجموعة فرعية من الأعمدة المُكرَّرة بين قوسين. وإذا أُهملت المجموعة الفرعية من الأعمدة، فستُكرَّر جميع أعمدة الجدول.

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

القيمة الافتراضية: قائمة فارغة — وهذا يعني أنه ستتم عملية النسخ المتماثل لقاعدة بيانات PostgreSQL بالكامل.

`materialized_postgresql_schema`

القيمة الافتراضية: سلسلة فارغة. (يُستخدَم المخطط الافتراضي)

`materialized_postgresql_schema_list`

القيمة الافتراضية: قائمة فارغة. (يُستخدَم المخطط الافتراضي)

`materialized_postgresql_max_block_size`

يحدّد عدد الصفوف التي تُجمَّع في الذاكرة قبل دفع البيانات إلى جدول قاعدة بيانات PostgreSQL. القيم الممكنة:

عدد صحيح موجب.

القيمة الافتراضية: 65536.

`materialized_postgresql_replication_slot`

فتحة النسخ المتماثل ينشئها المستخدم. يجب استخدامها مع materialized_postgresql_snapshot.

`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>;

`materialized_postgresql_use_unique_replication_consumer_identifier`

استخدم معرّف مستهلك فريدًا لعملية النسخ المتماثل. القيمة الافتراضية: 0. إذا ضُبطت القيمة على 1، فسيُسمح بإعداد عدة جداول MaterializedPostgreSQL تشير إلى جدول PostgreSQL نفسه.

`materialized_postgresql_use_extended_date_and_time_types`

يربط أنواع PostgreSQL date وtimestamp/timestamptz بالنوعين Date32 وDateTime64 في ClickHouse، لأنهما يغطيان نطاق القيم الأوسع لهذه الأنواع في PostgreSQL. القيمة الافتراضية: 1. إذا ضُبط على 0، فستُستخدم بدلًا من ذلك الأنواع الأضيق Date وDateTime (إذ لا يمكن تمثيل القيم الواقعة خارج نطاقها أو التي تتضمن دقة أجزاء من الثانية). لا يتحكم هذا الإعداد إلا في أنواع الأعمدة التي يحددها استنتاج النوع عند إنشاء الجداول المتداخلة، لذا يجب تحديده عند تنفيذ CREATE DATABASE. ولا يمكن تغييره بعد ذلك باستخدام ALTER DATABASE ... MODIFY SETTING (إذ تحتفظ الجداول المتداخلة التي أُنشئت بالفعل بأنواع أعمدتها الثابتة، ويُرفض مثل هذا التغيير)؛ أعد إنشاء قاعدة البيانات لتغييره. ولا ينطبق هذا على محرك الجداول MaterializedPostgreSQL، حيث تُصرَّح أنواع الأعمدة صراحةً.

ملاحظات

التبديل عند الفشل لفتحة النسخ المنطقي

فتحات النسخ المنطقي الموجودة على العقدة الأساسية لا تكون متاحة على النسخ الاحتياطية. لذلك، إذا حدث تبديل عند الفشل، فلن تكون العقدة الأساسية الجديدة (أي العقدة الاحتياطية الفعلية السابقة) على علم بأي فتحات كانت موجودة على العقدة الأساسية القديمة. وسيؤدي ذلك إلى تعطل النسخ من PostgreSQL. ويتمثل أحد الحلول في أن تدير فتحات النسخ بنفسك وتحدد فتحة النسخ المتماثل دائمة (يمكن العثور على بعض المعلومات هنا). ستحتاج إلى تمرير اسم الفتحة عبر الإعداد materialized_postgresql_replication_slot، ويجب تصديرها باستخدام الخيار EXPORT SNAPSHOT. كما يجب تمرير معرّف اللقطة عبر الإعداد materialized_postgresql_snapshot. يرجى ملاحظة أن هذا يجب استخدامه فقط عند الحاجة الفعلية إليه. إذا لم تكن هناك حاجة حقيقية لذلك أو فهم واضح لسببه، فمن الأفضل السماح لـ table engine بإنشاء فتحة النسخ المتماثل الخاصة به وإدارتها. مثال (من @bchrobot)

هيّئ فتحة النسخ المتماثل في 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

انتظر حتى تصبح فتحة النسخ المتماثل جاهزة، ثم ابدأ معاملة وصدّر معرّف لقطة المعاملة:
BEGIN; SELECT pg_export_snapshot();

في ClickHouse، أنشئ database:

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';

أنهِ معاملة PostgreSQL بمجرد تأكيد النسخ إلى ClickHouse DB. تحقّق من أن النسخ يستمر بعد التبديل عند الفشل:
kubectl exec acid-demo-cluster-0 -c postgres -- su postgres -c 'patronictl failover --candidate acid-demo-cluster-1 --force'

الأذونات المطلوبة

CREATE PUBLICATION — امتياز إنشاء الاستعلام.
CREATE_REPLICATION_SLOT — امتياز النسخ المتماثل.
pg_drop_replication_slot — امتياز النسخ المتماثل أو superuser.
DROP PUBLICATION — مالك publication (أي username في engine ‏MaterializedPostgreSQL نفسه).

يمكن تجنب تنفيذ الأمرين 2 و3 وتجنّب الحاجة إلى هذه الأذونات. استخدم الإعدادين materialized_postgresql_replication_slot وmaterialized_postgresql_snapshot، ولكن بحذر شديد. الوصول إلى الجداول:

pg_publication
pg_replication_slots
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 نفسه. أنشئ مسبقًا جدول ReplacingMergeTree بالبنية نفسها الخاصة بالجدول المتداخل (بما في ذلك العمودان _sign و _version) ثم استعد البيانات إليه:
RESTORE TABLE src AS existing_replacing_mergetree FROM Disk('backups', 'table.zip') SETTINGS allow_different_table_def = 1;

​إنشاء قاعدة بيانات

​مثال للاستخدام

​إضافة جداول جديدة إلى النسخ المتماثل بشكل ديناميكي

​إزالة الجداول من النسخ المتماثل ديناميكيًا

​مخطط PostgreSQL

​المتطلبات

​الإعدادات

​materialized_postgresql_tables_list

​materialized_postgresql_schema

​materialized_postgresql_schema_list

​materialized_postgresql_max_block_size

​materialized_postgresql_replication_slot

​materialized_postgresql_snapshot

​materialized_postgresql_use_unique_replication_consumer_identifier

​materialized_postgresql_use_extended_date_and_time_types

​ملاحظات

​التبديل عند الفشل لفتحة النسخ المنطقي

​الأذونات المطلوبة

​النسخ الاحتياطي والاستعادة

إنشاء قاعدة بيانات

مثال للاستخدام

إضافة جداول جديدة إلى النسخ المتماثل بشكل ديناميكي

إزالة الجداول من النسخ المتماثل ديناميكيًا

مخطط PostgreSQL

المتطلبات

الإعدادات

`materialized_postgresql_tables_list`

`materialized_postgresql_schema`

`materialized_postgresql_schema_list`

`materialized_postgresql_max_block_size`

`materialized_postgresql_replication_slot`

`materialized_postgresql_snapshot`

`materialized_postgresql_use_unique_replication_consumer_identifier`

`materialized_postgresql_use_extended_date_and_time_types`

ملاحظات

التبديل عند الفشل لفتحة النسخ المنطقي

الأذونات المطلوبة

النسخ الاحتياطي والاستعادة