MaterializedPostgreSQL

ينشئ قاعدة بيانات في 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 حسب الموضع).

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 بثلاث طرق (ابتداءً من الإصدار 21.12).

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

2. أي عدد من المخططات مع مجموعة جداول محددة لمحرك قاعدة بيانات 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. أي عدد من الـ مخططات مع المجموعة الكاملة من الجداول لمحرك قاعدة البيانات 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. يجب أن تكون قيمة إعداد wal_level هي logical، وأن تكون قيمة المَعلمة max_replication_slots في ملف إعدادات PostgreSQL 2 على الأقل.

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;

يحدّد قائمة مفصولة بفواصل من جداول قاعدة بيانات PostgreSQL التي ستتم مزامنتها عبر محرك قاعدة البيانات MaterializedPostgreSQL.

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

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

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

يربط أنواع PostgreSQL date وtimestamp/timestamptz بالنوعين Date32 وDateTime64 في ClickHouse، لأنهما يغطيان نطاق القيم الأوسع لهذه الأنواع في PostgreSQL. القيمة الافتراضية: 1. إذا ضُبط على 0، فستُستخدم بدلًا من ذلك الأنواع الأضيق Date وDateTime (إذ لا يمكن تمثيل القيم الواقعة خارج نطاقها أو التي تتضمن دقة أجزاء من الثانية).

لا يتحكم هذا الإعداد إلا في أنواع الأعمدة التي يحددها استنتاج النوع عند إنشاء الجداول المتداخلة، لذا يجب تحديده عند تنفيذ CREATE DATABASE. ولا يمكن تغييره بعد ذلك باستخدام ALTER DATABASE ... MODIFY SETTING (إذ تحتفظ الجداول المتداخلة التي أُنشئت بالفعل بأنواع أعمدتها الثابتة، ويُرفض مثل هذا التغيير)؛ أعد إنشاء قاعدة البيانات لتغييره. ولا ينطبق هذا على محرك الجداول MaterializedPostgreSQL، حيث تُصرَّح أنواع الأعمدة صراحةً.

تُمرَّر معلمات TLS/SSL إلى libpq، ويمكن توفيرها عبر مجموعة مُسمّاة أو كوسيطات مفتاح-قيمة تُلحق بالمحرك: sslmode (disable أو allow أو prefer أو require أو verify-ca أو verify-full؛ عند عدم تعيينه، يُستخدم الإعداد الافتراضي prefer في libpq)، بالإضافة إلى الشهادات والمفتاح بإحدى صيغتين. تشير sslrootcert (شهادة CA) وsslcert (شهادة العميل) وsslkey (المفتاح الخاص للعميل) إلى مسارات ملفات محلية على الخادم، ولا تُقبل إلا من مجموعة مُسمّاة معرّفة في ملف تهيئة الخادم. أما sslrootcert_pem وsslcert_pem وsslkey_pem فتقبل المحتوى الحرفي للملف المقابل بدلاً من ذلك، ويمكن تحديدها من SQL، وتُخفى في السجلات واستعلامات SHOW مثل كلمة المرور.

مثال على الاتصال بخادم PostgreSQL يفرض TLS مع التحقق من شهادة الخادم:

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. كما يجب تمرير معرّف اللقطة عبر الإعداد materialized_postgresql_snapshot.

يرجى ملاحظة أن هذا يجب استخدامه فقط عند الحاجة الفعلية إليه. إذا لم تكن هناك حاجة حقيقية لذلك أو فهم واضح لسببه، فمن الأفضل السماح لـ table engine بإنشاء فتحة النسخ المتماثل الخاصة به وإدارتها.

مثال (من @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. انتظر حتى تصبح فتحة النسخ المتماثل جاهزة، ثم ابدأ معاملة وصدّر معرّف لقطة المعاملة:

   BEGIN;
   SELECT pg_export_snapshot();

3. في 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';

4. أنهِ معاملة PostgreSQL بمجرد تأكيد النسخ إلى ClickHouse DB. تحقّق من أن النسخ يستمر بعد التبديل عند الفشل:

   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 – امتياز النسخ المتماثل أو superuser.

4. DROP PUBLICATION – مالك publication (أي username في engine ‏MaterializedPostgreSQL نفسه).

يمكن تجنب تنفيذ الأمرين 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 نفسه. أنشئ مسبقًا جدول ReplacingMergeTree بالبنية نفسها الخاصة بالجدول المتداخل (بما في ذلك العمودان _sign و _version) ثم استعد البيانات إليه:

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