PostgreSQL

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

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

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

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

подсказка

Рекомендуем использовать ClickPipes, управляемый сервис интеграции для ClickHouse Cloud на базе PeerDB. В качестве альтернативы доступен PeerDB как инструмент CDC с открытым исходным кодом, специально разработанный для репликации баз данных PostgreSQL как для саморедактируемого ClickHouse, так и для ClickHouse Cloud.

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

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

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

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

listen_addresses = '*'

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

CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

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

CREATE DATABASE db_in_psg;

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

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

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

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

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


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

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

/usr/pgsql-12/bin/pg_ctl reload

Проверьте, может ли новый пользователь clickhouse_user войти в систему:

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

примечание

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

2. Определение таблицы в ClickHouse

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

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

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

CREATE DATABASE db_in_ch;

Создайте таблицу, которая использует 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	имя пользователя для подключения к postgres	clickhouse_user
password	пароль для подключения к postgres	ClickHouse_123

примечание

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

3. Тестирование интеграции

В ClickHouse просмотрите начальные строки:

SELECT * FROM db_in_ch.table1

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

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

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

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

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

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

SELECT * FROM db_in_ch.table1

Ответ должен быть:

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

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

Давайте посмотрим, что произойдет, когда вы добавите строки в таблицу ClickHouse:

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

Строки, добавленные в 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 с использованием движка таблиц PostgreSQL. Посмотрите на документацию для движка таблиц PostgreSQL для получения информации о дополнительных функциях, таких как указание схем, возврат только подмножества столбцов и подключение к нескольким репликам. Также ознакомьтесь с ClickHouse и PostgreSQL - идеальная пара в мире данных - часть 1 блога.

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

Not supported in ClickHouse Cloud

Experimental feature. Learn more.

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

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

1. В PostgreSQL

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

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

listen_addresses = '*'
max_replication_slots = 10
wal_level = logical

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

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

CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

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

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

CREATE DATABASE db1;

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

\connect db1

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

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

Добавьте начальные строки:

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

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


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

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

Перезагрузите конфигурацию pg_hba.conf с помощью чего-то подобного (отрегулируйте для вашей версии):

/usr/pgsql-12/bin/pg_ctl reload

Проверьте вход с новым clickhouse_user:

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

2. В ClickHouse

Войдите в CLI ClickHouse:

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

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

SET allow_experimental_database_materialized_postgresql=1

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

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

Проверьте, что начальная таблица содержит данные:

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. Тестирование базовой репликации

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

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

В 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 не поддерживаются для этой репликации, движок может быть настроен для обнаружения изменений и перезагрузки таблиц при внесении структурных изменений.

к сведению

Для получения информации о дополнительных функциях, доступных для продвинутых опций, пожалуйста, смотрите справочную документацию.

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

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

2. Определение таблицы в ClickHouse​

3. Тестирование интеграции​

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

1. В PostgreSQL​

2. В ClickHouse​

3. Тестирование базовой репликации​

4. Резюме​