PostgreSQL

Página que descreve como integrar o PostgreSQL ao ClickHouse

Um guia completo de migração do PostgreSQL para o ClickHouse, incluindo orientações sobre modelagem de dados e conceitos equivalentes, pode ser encontrado aqui. A seguir, descrevemos como conectar o ClickHouse ao PostgreSQL.

Esta página aborda as seguintes opções para integrar o PostgreSQL ao ClickHouse:

• usar o mecanismo de tabela PostgreSQL para ler dados de uma tabela do PostgreSQL
• usar o mecanismo de banco de dados experimental MaterializedPostgreSQL para sincronizar um banco de dados do PostgreSQL com um banco de dados no ClickHouse

Confira nosso serviço Managed Postgres. Com armazenamento NVMe fisicamente co-localizado com o compute, ele oferece desempenho até 10x superior para workloads limitadas por disco em comparação com alternativas que usam armazenamento conectado à rede, como EBS, e permite replicar seus dados do Postgres para o ClickHouse usando o conector Postgres CDC no ClickPipes.

​

Usando o mecanismo de tabela PostgreSQL

O mecanismo de tabela PostgreSQL permite realizar operações SELECT e INSERT em dados armazenados no servidor PostgreSQL remoto a partir do ClickHouse. Este artigo ilustra métodos básicos de integração usando uma única tabela.

​

1. Configurando o PostgreSQL

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

  listen_addresses = '*'

2. Crie um usuário para se conectar no ClickHouse. Para fins de demonstração, este exemplo concede privilégios totais 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 teste:

  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 de 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 o arquivo de configuração 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>

Se você estiver usando este recurso no ClickHouse Cloud, talvez seja necessário permitir que os endereços IP do ClickHouse Cloud acessem sua instância do PostgreSQL. Consulte a API de Endpoints do ClickHouse Cloud para ver os detalhes do tráfego de saída.

​

2. Defina uma tabela no ClickHouse

1. Acesse o 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 utilize 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:

parâmetro	Descrição	exemplo
host:port	nome do host ou IP e porta	postgres-host.domain.com:5432
database	nome do banco de dados PostgreSQL	db_in_psg
user	nome de usuário para se conectar ao Postgres	clickhouse_user
password	senha para se conectar ao Postgres	ClickHouse_123

Consulte a página de documentação do mecanismo de tabela PostgreSQL para ver a lista completa de parâmetros.

​

3 Teste a integração

1. No ClickHouse, veja as linhas iniciais:

  SELECT * FROM db_in_ch.table1

A tabela do ClickHouse deve ser preenchida automaticamente 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 PostgreSQL, 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 quando você adiciona linhas à tabela do ClickHouse:

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

6. As linhas adicionadas no 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 mecanismo de tabela PostrgeSQL. Consulte a página de documentação do mecanismo de tabela PostgreSQL para conhecer mais recursos, como especificar esquemas, retornar apenas um subconjunto de colunas e conectar-se a várias réplicas. Consulte também o blog ClickHouse and PostgreSQL - a match made in data heaven - part 1.

​

Usando a mecanismo de banco de dados MaterializedPostgreSQL

A mecanismo de banco de dados PostgreSQL usa os recursos de replicação do PostgreSQL para criar uma réplica do banco de dados com todos os schemas e tabelas, ou com apenas um subconjunto deles. Este artigo ilustra métodos básicos de integração usando um banco de dados, um schema e uma tabela.

Nos procedimentos a seguir, são usados o PostgreSQL CLI (psql) e o ClickHouse CLI (clickhouse-client). O servidor PostgreSQL está instalado em Linux. A seguir estão as configurações mínimas caso o banco de dados PostgreSQL seja uma nova instalação de teste.

​

1. No PostgreSQL

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

adicione as seguintes entradas:

listen_addresses = '*'
max_replication_slots = 10
wal_level = logical

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

2. Usando uma conta de administrador, crie um usuário para conexão a partir do ClickHouse:

CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

*para fins de demonstração, foram concedidas permissões completas de superusuário.

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 com o novo banco de dados usando o novo usuário para 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, isto usa o método de authentication 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 conforme 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>

​

2. No ClickHouse

1. faça login na 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 a 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:

parâmetro	Descrição	exemplo
host:port	nome do host ou IP e porta	postgres-host.domain.com:5432
database	nome do banco de dados PostgreSQL	db1
user	nome de usuário para se conectar ao Postgres	clickhouse_user
password	senha para se conectar ao Postgres	ClickHouse_123
settings	configurações adicionais para o mecanismo	materialized_postgresql_tables_list = ‘table1’

Para ver o guia completo do mecanismo de banco de dados PostgreSQL, consulte https://clickhouse.com/docs/engines/database-engines/materialized-postgresql/#settings

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

​

3. Teste a replicação básica

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

​

4. Resumo

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

Para conhecer mais recursos disponíveis nas opções avançadas, consulte a documentação de referência.