Движок таблицы MaterializedPostgreSQL

Создает таблицу ClickHouse с начальным дампом данных из таблицы PostgreSQL и запускает процесс репликации, то есть выполняет фоновую задачу, применяющую новые изменения по мере их появления в таблице PostgreSQL в удаленной базе данных PostgreSQL.

Если требуется реплицировать несколько таблиц, настоятельно рекомендуется использовать движок базы данных MaterializedPostgreSQL вместо движка таблицы и параметр materialized_postgresql_tables_list, который задает таблицы для репликации (также появится возможность добавить schema базы данных). Это значительно эффективнее с точки зрения нагрузки на CPU, количества соединений и числа слотов репликации в удаленной базе данных PostgreSQL.

CREATE TABLE postgresql_db.postgresql_replica (key UInt64, value UInt64)
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgresql_table', 'postgres_user', 'postgres_password')
PRIMARY KEY key;

Параметры движка

• host:port — адрес сервера PostgreSQL.
• database — имя удалённой базы данных.
• table — имя удалённой таблицы.
• user — пользователь PostgreSQL.
• password — пароль пользователя.

Параметры 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 так же, как пароль.

CREATE TABLE postgresql_db.postgresql_replica (key UInt64, value UInt64)
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgresql_table', 'postgres_user', 'postgres_password',
                                sslmode = 'verify-full', sslrootcert_pem = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----')
PRIMARY KEY key;

Параметры TLS/SSL входят в параметры подключения к PostgreSQL и задаются при создании таблицы.

1. Параметр wal_level должен иметь значение logical, а параметр max_replication_slots — значение не менее 2 в конфигурационном файле PostgreSQL.

2. Таблица с движком MaterializedPostgreSQL должна иметь первичный ключ — тот же, что и индекс replica identity (по умолчанию это первичный ключ) таблицы PostgreSQL (см. подробнее об индексе replica identity).

3. Допускается только база данных Atomic.

4. Движок таблицы MaterializedPostgreSQL работает только с PostgreSQL версии >= 11, поскольку для его реализации требуется функция PostgreSQL pg_replication_slot_advance.

• _version — Счётчик транзакций. Тип: UInt64.

• _sign — Метка удаления. Тип: Int8. Возможные значения:

  • 1 — строка не удалена,
  • -1 — строка удалена.

Эти столбцы не нужно указывать при создании таблицы. Они всегда доступны в запросе SELECT. Столбец _version соответствует позиции LSN в WAL, поэтому его можно использовать, чтобы проверить, насколько актуальна репликация.

CREATE TABLE postgresql_db.postgresql_replica (key UInt64, value UInt64)
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgresql_replica', 'postgres_user', 'postgres_password')
PRIMARY KEY key;

SELECT key, value, _version FROM postgresql_db.postgresql_replica;