PostgreSQL

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 ClickHouse 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.

Configurando o PostgreSQL

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

listen_addresses = '*'

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';

Crie um novo banco de dados no PostgreSQL:

CREATE DATABASE db_in_psg;

Crie uma nova tabela:

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

Vamos adicionar algumas linhas para teste:

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

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

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

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.

Defina uma tabela no ClickHouse

Faça login no clickhouse-client:

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

Vamos criar um novo banco de dados:

CREATE DATABASE db_in_ch;

Crie uma tabela que use o mecanismo de tabela 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	hostname 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 PostgreSQL table engine para ver a lista completa de parâmetros.

Teste a integração

No ClickHouse, visualize as primeiras linhas:

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

De volta ao PostgreSQL, adicione algumas linhas à tabela:

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

Essas duas novas linhas devem aparecer na tabela do 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     │
└────┴─────────┘

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');

As linhas adicionadas no ClickHouse devem aparecer na tabela no 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.

No PostgreSQL

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

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.

crie um novo banco de dados:

CREATE DATABASE db1;

conecte-se ao novo banco de dados no psql:

\connect db1

crie uma nova tabela:

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

insira as linhas iniciais:

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

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

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

Teste o acesso com o novo clickhouse_user:

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

No ClickHouse

acesse a CLI do ClickHouse

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

Habilite o recurso experimental do PostgreSQL para o mecanismo de banco de dados:

SET allow_experimental_database_materialized_postgresql=1

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	hostname ou IP e porta	postgres-host.domain.com:5432
database	nome do banco de dados PostgreSQL	db1
user	nome de usuário para conectar ao Postgres	clickhouse_user
password	senha para conectar ao Postgres	ClickHouse_123
settings	configurações adicionais do motor	materialized_postgresql_tables_list = ‘table1’

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

Verifique se a tabela inicial contém 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     │
└────┴─────────┘

Testar a replicação básica

No PostgreSQL, adicione novas linhas:

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

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

Resumo

Este guia de integração apresentou um exemplo simples de como replicar um banco de dados com uma tabela. No entanto, há opções mais avançadas, incluindo replicar todo o banco de dados ou adicionar novas tabelas e schemas à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 forem feitas alterações estruturais.

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

Fontes de dados

Visualização de dados

Ingestão de dados

Integrações de dados

Clientes SQL

Ferramentas

Usando o mecanismo de tabela PostgreSQL

Usando a mecanismo de banco de dados MaterializedPostgreSQL

​Usando o mecanismo de tabela PostgreSQL

​Usando a mecanismo de banco de dados MaterializedPostgreSQL

Usando o mecanismo de tabela PostgreSQL

Usando a mecanismo de banco de dados MaterializedPostgreSQL