PostgreSQL

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

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

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

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

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

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

Настройка 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, вам может понадобиться разрешить доступ к вашему экземпляру PostgreSQL с IP-адресов ClickHouse Cloud. Подробности об исходящем трафике см. в ClickHouse Cloud Endpoints API.

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

Войдите в клиент ClickHouse:

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	имя пользователя для подключения к PostgreSQL	clickhouse_user
password	пароль для подключения к PostgreSQL	ClickHouse_123

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

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

В 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 с использованием движка таблицы 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.

В 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

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

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

/usr/pgsql-12/bin/pg_ctl reload

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

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

В ClickHouse

войдите в ClickHouse CLI

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	имя пользователя для подключения к PostgreSQL	clickhouse_user
password	пароль для подключения к PostgreSQL	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     │
└────┴─────────┘

Проверка базовой репликации

В 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     │
└────┴─────────┘

Итоги

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

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

Data sources

Data visualization

Data ingestion

Data integrations

SQL-clients

Tools

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

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

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

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

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

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