Перейти к основному содержимому
Перейти к основному содержимому

MaterializedPostgreSQL

Experimental feature. Learn more.
Not supported in ClickHouse Cloud
примечание

Пользователям ClickHouse Cloud рекомендуется использовать ClickPipes для репликации PostgreSQL в ClickHouse. Это обеспечивает высокопроизводительный захват изменений данных (CDC) для PostgreSQL.

Создает базу данных ClickHouse с таблицами из базы данных PostgreSQL. Во-первых, база данных с движком MaterializedPostgreSQL создает снимок базы данных PostgreSQL и загружает необходимые таблицы. Необходимые таблицы могут включать любую подмножество таблиц из любой подмножества схем из указанной базы данных. Вместе со снимком движок базы данных получает LSN и после выполнения начальной выгрузки таблиц начинает получать обновления из WAL. После создания базы данных новые таблицы, добавленные в базу данных PostgreSQL, не автоматически добавляются в репликацию. Их необходимо добавлять вручную с помощью запроса ATTACH TABLE db.table.

Репликация осуществляется с помощью протокола логической репликации PostgreSQL, который не позволяет реплицировать DDL, но позволяет узнать, произошло ли изменение, нарушающее репликацию (изменения типов столбцов, добавление/удаление столбцов). Такие изменения обнаруживаются, и соответствующие таблицы прекращают получать обновления. В этом случае вы должны использовать запросы ATTACH/ DETACH PERMANENTLY, чтобы полностью перезагрузить таблицу. Если DDL не нарушает репликацию (например, переименование столбца), таблица все равно будет получать обновления (вставка выполняется по позиции).

примечание

Этот движок базы данных является экспериментальным. Чтобы использовать его, установите allow_experimental_database_materialized_postgresql в 1 в ваших файлах конфигурации или с помощью команды 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 postgresql_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 schema можно настроить тремя способами (начиная с версии 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;
  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.

Предупреждение: для этого случая точки в имени таблицы не допускаются.

  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. Параметр wal_level должен иметь значение logical, а параметр max_replication_slots должен иметь значение не менее 2 в конфигурационном файле PostgreSQL.

  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;

Первичный ключ всегда проверяется первым. Если он отсутствует, то проверяется индекс, определяемый как индекс идентичности реплики. Если индекс используется как идентичность реплики, то в таблице должен быть только один такой индекс. Вы можете проверить, какой тип используется для конкретной таблицы с помощью следующей команды:

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.

Заметки​

Отказоустойчивость логического слота репликации​

Логические слоты репликации, которые существуют на основном сервере, недоступны на резервных репликах. Таким образом, если происходит сбой, новая основная система (старый физический резерв) не будет осведомлена о любых слотах, которые существовали у старого основного. Это приведет к нарушению репликации из PostgreSQL. Решением этой проблемы является управление слотами репликации самостоятельно и определение постоянного слота репликации (некоторые сведения можно найти здесь). Вам нужно будет передать имя слота через настройку materialized_postgresql_replication_slot, и он должен быть экспортирован с опцией EXPORT SNAPSHOT. Идентификатор снимка необходимо передать через настройку 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. Ждите, пока слот репликации будет готов, затем начните транзакцию и экспортируйте идентификатор снимка транзакции:

    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. Завершите транзакцию PostgreSQL после подтверждения репликации в базе данных ClickHouse. Проверьте, что репликация продолжается после сбоя:

    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 -- владелец публикации (username в самом движке MaterializedPostgreSQL).

Можно избежать выполнения команд 2 и 3 и обладания этими разрешениями. Используйте настройки materialized_postgresql_replication_slot и materialized_postgresql_snapshot. Но с большой осторожностью.

Доступ к таблицам:

  1. pg_publication

  2. pg_replication_slots

  3. pg_publication_tables