Подключение ClickHouse к PostgreSQL

Страница с описанием различных способов подключения ClickHouse к PostgreSQL

На этой странице рассматриваются следующие варианты интеграции PostgreSQL с ClickHouse:

• использование движка таблицы PostgreSQL для чтения данных из таблицы PostgreSQL
• использование экспериментального движка базы данных MaterializedPostgreSQL для синхронизации базы данных PostgreSQL с базой данных в ClickHouse

Ознакомьтесь с нашим сервисом Managed Postgres. Благодаря NVMe-хранилищу, физически расположенному рядом с вычислительными ресурсами, он обеспечивает производительность до 10 раз выше для рабочих нагрузок, упирающихся в производительность диска, по сравнению с альтернативами на сетевом хранилище, такими как EBS, а также позволяет реплицировать данные из Postgres в ClickHouse с помощью коннектора Postgres CDC в ClickPipes.

​

Использование движка таблицы PostgreSQL

Движок таблицы PostgreSQL позволяет выполнять из ClickHouse операции SELECT и INSERT с данными, хранящимися на удалённом сервере PostgreSQL. В этой статье на примере одной таблицы показаны базовые способы интеграции.

​

1. Настройка PostgreSQL

1. В postgresql.conf добавьте следующую запись, чтобы PostgreSQL прослушивал сетевые интерфейсы:

  listen_addresses = '*'

2. Создайте пользователя для подключения из ClickHouse. Для демонстрации в этом примере предоставляются полные права суперпользователя.

  CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

3. Создайте новую базу данных в PostgreSQL:

  CREATE DATABASE db_in_psg;

4. Создайте новую таблицу:

  CREATE TABLE table1 (
      id         integer primary key,
      column1    varchar(10)
  );

5. Давайте добавим несколько строк для проверки:

  INSERT INTO table1
    (id, column1)
  VALUES
    (1, 'abc'),
    (2, 'def');

6. Чтобы настроить PostgreSQL так, чтобы новая база данных принимала подключения нового пользователя для репликации, добавьте следующую запись в файл pg_hba.conf. В строке адреса укажите подсеть или IP-адрес вашего сервера PostgreSQL:

  # TYPE  DATABASE        USER            ADDRESS                 METHOD
  host    db_in_psg             clickhouse_user 192.168.1.0/24          password

7. Перезагрузите конфигурационный файл pg_hba.conf (скорректируйте эту команду в зависимости от вашей версии):

  /usr/pgsql-12/bin/pg_ctl reload

8. Убедитесь, что новый clickhouse_user может войти:

  psql -U clickhouse_user -W -d db_in_psg -h <your_postgresql_host>

Если вы используете эту возможность в ClickHouse Cloud, вам может потребоваться разрешить IP-адресам ClickHouse Cloud доступ к вашему экземпляру PostgreSQL. Сведения об исходящем трафике см. в ClickHouse Cloud Endpoints API.

​

2. Создайте таблицу в ClickHouse

1. Войдите в clickhouse-client:

  clickhouse-client --user default --password ClickHouse123!

2. Создадим новую базу данных:

  CREATE DATABASE db_in_ch;

3. Создайте таблицу, использующую движок PostgreSQL:

  CREATE TABLE db_in_ch.table1
  (
      id UInt64,
      column1 String
  )
  ENGINE = PostgreSQL('postgres-host.domain.com:5432', 'db_in_psg', 'table1', 'clickhouse_user', 'ClickHouse_123');

Минимально необходимые параметры:

параметр	Описание	пример
host:port	имя хоста или IP-адрес и порт	postgres-host.domain.com:5432
database	имя базы данных PostgreSQL	db_in_psg
user	имя пользователя для подключения к PostgreSQL	clickhouse_user
password	пароль для подключения к PostgreSQL	ClickHouse_123

Полный список параметров см. на странице документации движка таблицы PostgreSQL.

​

3 Проверьте интеграцию

1. В ClickHouse просмотрите первые строки:

  SELECT * FROM db_in_ch.table1

Таблица ClickHouse должна автоматически заполниться двумя строками, которые уже были в таблице PostgreSQL:

  Query id: 34193d31-fe21-44ac-a182-36aaefbd78bf

  ┌─id─┬─column1─┐
  │  1 │ abc     │
  │  2 │ def     │
  └────┴─────────┘

2. Вернувшись в PostgreSQL, добавьте в таблицу пару строк:

  INSERT INTO table1
    (id, column1)
  VALUES
    (3, 'ghi'),
    (4, 'jkl');

4. Эти две новые строки должны появиться в таблице ClickHouse:

  SELECT * FROM db_in_ch.table1

Ответ должен быть следующим:

  Query id: 86fa2c62-d320-4e47-b564-47ebf3d5d27b

  ┌─id─┬─column1─┐
  │  1 │ abc     │
  │  2 │ def     │
  │  3 │ ghi     │
  │  4 │ jkl     │
  └────┴─────────┘

5. Посмотрим, что произойдет, если добавить строки в таблицу ClickHouse:

  INSERT INTO db_in_ch.table1
    (id, column1)
  VALUES
    (5, 'mno'),
    (6, 'pqr');

6. Строки, добавленные в ClickHouse, должны появиться в таблице PostgreSQL:

  db_in_psg=# SELECT * FROM table1;
  id | column1
  ----+---------
    1 | abc
    2 | def
    3 | ghi
    4 | jkl
    5 | mno
    6 | pqr
  (6 rows)

В этом примере показана базовая интеграция между PostgreSQL и ClickHouse с использованием движка таблицы PostrgeSQL. Дополнительные возможности, такие как указание схем, выборка только части столбцов и подключение к нескольким репликам, описаны на странице документации о движке таблицы PostgreSQL. Также ознакомьтесь со статьёй в блоге ClickHouse and PostgreSQL - a match made in data heaven - part 1.

​

Использование движка базы данных MaterializedPostgreSQL

Движок базы данных PostgreSQL использует возможности репликации PostgreSQL для создания реплики базы данных со всеми схемами и таблицами или их подмножеством. В этой статье показаны базовые способы интеграции на примере одной базы данных, одной схемы и одной таблицы.

В следующих процедурах используются PostgreSQL CLI (psql) и ClickHouse CLI (clickhouse-client). Сервер PostgreSQL установлен на Linux. Ниже приведены минимальные настройки, если PostgreSQL используется в новой тестовой установке

​

1. В PostgreSQL

1. В postgresql.conf задайте минимальные уровни прослушивания, уровень WAL для репликации и слоты репликации:

добавьте следующие параметры:

listen_addresses = '*'
max_replication_slots = 10
wal_level = logical

*Для ClickHouse требуется уровень wal не ниже logical и как минимум 2 слота репликации

2. Используя учетную запись администратора, создайте пользователя для подключения из ClickHouse:

CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

*для демонстрации были предоставлены полные права суперпользователя.

3. создайте новую базу данных:

CREATE DATABASE db1;

4. подключитесь к новой базе данных через psql:

\connect db1

5. Создайте новую таблицу:

CREATE TABLE table1 (
    id         integer primary key,
    column1    varchar(10)
);

6. добавьте исходные строки:

INSERT INTO table1
(id, column1)
VALUES
(1, 'abc'),
(2, 'def');

7. Настройте PostgreSQL так, чтобы разрешить новому пользователю подключаться к новой базе данных для репликации. Ниже приведена минимальная запись, которую нужно добавить в файл pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    db1             clickhouse_user 192.168.1.0/24          password

*в демонстрационных целях здесь используется метод аутентификации с паролем в открытом виде. обновите строку address, указав либо подсеть, либо адрес сервера в соответствии с документацией PostgreSQL

8. перезагрузите конфигурацию pg_hba.conf, например так (при необходимости скорректируйте для своей версии):

/usr/pgsql-12/bin/pg_ctl reload

9. Проверьте вход под новым clickhouse_user:

 psql -U clickhouse_user -W -d db1 -h <your_postgresql_host>

​

2. В ClickHouse

1. войдите в ClickHouse CLI

clickhouse-client --user default --password ClickHouse123!

2. Включите экспериментальную возможность PostgreSQL для движка базы данных:

SET allow_experimental_database_materialized_postgresql=1

3. Создайте новую базу данных для репликации и исходную таблицу:

CREATE DATABASE db1_postgres
ENGINE = MaterializedPostgreSQL('postgres-host.domain.com:5432', 'db1', 'clickhouse_user', 'ClickHouse_123')
SETTINGS materialized_postgresql_tables_list = 'table1';

минимальные параметры:

параметр	описание	пример
host:port	имя хоста или IP-адрес и порт	postgres-host.domain.com:5432
database	имя базы данных PostgreSQL	db1
user	имя пользователя для подключения к Postgres	clickhouse_user
password	пароль для подключения к Postgres	ClickHouse_123
settings	дополнительные настройки движка	materialized_postgresql_tables_list = ‘table1’

Полное руководство по движку базы данных PostgreSQL см. здесь: https://clickhouse.com/docs/engines/database-engines/materialized-postgresql/#settings

4. Убедитесь, что в исходной таблице есть данные:

ch_env_2 :) select * from db1_postgres.table1;

SELECT *
FROM db1_postgres.table1

Query id: df2381ac-4e30-4535-b22e-8be3894aaafc

┌─id─┬─column1─┐
│  1 │ abc     │
└────┴─────────┘
┌─id─┬─column1─┐
│  2 │ def     │
└────┴─────────┘

​

3. Проверьте базовую репликацию

1. В PostgreSQL добавьте новые строки:

INSERT INTO table1
(id, column1)
VALUES
(3, 'ghi'),
(4, 'jkl');

2. В ClickHouse убедитесь, что новые строки отображаются:

ch_env_2 :) select * from db1_postgres.table1;

SELECT *
FROM db1_postgres.table1

Query id: b0729816-3917-44d3-8d1a-fed912fb59ce

┌─id─┬─column1─┐
│  1 │ abc     │
└────┴─────────┘
┌─id─┬─column1─┐
│  4 │ jkl     │
└────┴─────────┘
┌─id─┬─column1─┐
│  3 │ ghi     │
└────┴─────────┘
┌─id─┬─column1─┐
│  2 │ def     │
└────┴─────────┘

​

4. Сводка

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

Дополнительные возможности расширенной настройки описаны в справочной документации.