Conectando o ClickHouse ao PostgreSQL

1. No postgresql.conf, adicione a seguinte entrada para permitir que o PostgreSQL receba conexões nas interfaces de rede:

listen_addresses = '*'

2. Crie um usuário para se conectar pelo ClickHouse. Para fins de demonstração, este exemplo concede privilégios completos de superusuário.

CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

3. Crie um novo banco de dados no PostgreSQL:

CREATE DATABASE db_in_psg;

4. Crie uma nova tabela:

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

5. Vamos adicionar algumas linhas para testar:

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

6. Para configurar o PostgreSQL para permitir conexões com o novo banco de dados usando o novo usuário para replicação, adicione a seguinte entrada ao arquivo pg_hba.conf. Atualize a linha de endereço com a sub-rede ou o endereço IP do seu servidor PostgreSQL:

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

7. Recarregue a configuração do pg_hba.conf (ajuste este comando de acordo com a sua versão):

/usr/pgsql-12/bin/pg_ctl reload

8. Verifique se o novo clickhouse_user consegue se conectar:

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

1. Faça login no clickhouse-client:

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

2. Vamos criar um novo banco de dados:

CREATE DATABASE db_in_ch;

3. Crie uma tabela que use o 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');

Os parâmetros mínimos necessários são:

1. No ClickHouse, veja as linhas iniciais:

SELECT * FROM db_in_ch.table1

A tabela do ClickHouse deve ser automaticamente preenchida com as duas linhas que já existiam na tabela do PostgreSQL:

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

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

2. De volta ao Postgres, adicione algumas linhas à tabela:

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

4. Essas duas novas linhas devem aparecer na sua tabela no ClickHouse:

SELECT * FROM db_in_ch.table1

A resposta deve ser:

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

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

5. Vamos ver o que acontece ao adicionar linhas à tabela do ClickHouse:

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

6. As linhas adicionadas ao ClickHouse devem aparecer na tabela do PostgreSQL:

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

Este exemplo demonstrou a integração básica entre PostgreSQL e ClickHouse usando o motor de tabela PostrgeSQL. Confira a página da documentação do motor de tabela PostgreSQL para conhecer mais funcionalidades, como especificar esquemas, retornar apenas um subconjunto de colunas e se conectar a várias réplicas. Confira também o post do blog ClickHouse and PostgreSQL - a match made in data heaven - part 1.

1. Em postgresql.conf, defina os níveis mínimos de escuta, o nível de WAL da replicação e os slots de replicação:

adicione as seguintes entradas:

listen_addresses = '*'
max_replication_slots = 10
wal_level = logical

*ClickHouse requer, no mínimo, wal level logical e 2 slots de replicação

2. Com uma conta de administrador, crie um usuário para se conectar a partir do ClickHouse:

CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

*apenas para fins de demonstração, foram concedidos privilégios completos de superuser.

3. crie um novo banco de dados:

CREATE DATABASE db1;

4. conecte-se ao novo banco de dados no psql:

\connect db1

5. crie uma nova tabela:

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

6. insira as linhas iniciais:

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

7. Configure o PostgreSQL para permitir conexões ao novo banco de dados com o novo usuário de replicação. Abaixo está a entrada mínima a ser adicionada ao arquivo pg_hba.conf:

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

*para fins de demonstração, está sendo usado o método de autenticação por senha em texto simples. atualize a linha de endereço com a sub-rede ou o endereço do servidor, conforme a documentação do PostgreSQL

8. recarregue a configuração do pg_hba.conf com algo como isto (ajuste para a sua versão):

/usr/pgsql-12/bin/pg_ctl reload

9. Teste o acesso com o novo clickhouse_user:

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

1. acesse o ClickHouse CLI

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

2. Ative o recurso experimental do PostgreSQL para o mecanismo de banco de dados:

SET allow_experimental_database_materialized_postgresql=1

3. Crie o novo banco de dados que será replicado e defina a tabela inicial:

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

opções mínimas:

4. Verifique se a tabela inicial tem dados:

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

1. No PostgreSQL, adicione novas linhas:

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

2. No ClickHouse, verifique se as novas linhas estão visíveis:

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

Este guia de integração se concentrou em um exemplo simples de como replicar um banco de dados com uma tabela; no entanto, existem opções mais avançadas, que incluem replicar o banco de dados inteiro ou adicionar novas tabelas e schemas às replicações existentes. Embora os comandos DDL não sejam suportados para essa replicação, o motor pode ser configurado para detectar alterações e recarregar as tabelas quando forem feitas mudanças estruturais.