Connexion de ClickHouse à PostgreSQL

1. Dans postgresql.conf, ajoutez l’entrée suivante pour que PostgreSQL écoute sur les interfaces réseau :

listen_addresses = '*'

2. Créez un utilisateur pour vous connecter depuis ClickHouse. Pour les besoins de la démonstration, cet exemple accorde tous les droits de superutilisateur.

CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

3. Créez une nouvelle base de données dans PostgreSQL :

CREATE DATABASE db_in_psg;

4. Créez une nouvelle table :

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

5. Ajoutons quelques lignes pour tester :

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

6. Pour configurer PostgreSQL afin d’autoriser les connexions à la nouvelle base de données avec le nouvel utilisateur pour la réplication, ajoutez l’entrée suivante au fichier pg_hba.conf. Mettez à jour la ligne d’adresse avec le sous-réseau ou l’adresse IP de votre serveur PostgreSQL :

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

7. Rechargez le fichier de configuration pg_hba.conf (adaptez cette commande en fonction de votre version) :

/usr/pgsql-12/bin/pg_ctl reload

8. Vérifiez que le nouveau clickhouse_user peut se connecter :

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

1. Connectez-vous à l’aide de clickhouse-client :

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

2. Créons une nouvelle base de données :

CREATE DATABASE db_in_ch;

3. Créez une table utilisant 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');

Les paramètres minimaux requis sont :

1. Dans ClickHouse, affichez les lignes initiales :

SELECT * FROM db_in_ch.table1

La table ClickHouse devrait automatiquement être alimentée avec les deux lignes déjà présentes dans la table PostgreSQL :

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

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

2. De retour dans PostgreSQL, ajoutez deux lignes dans la table :

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

4. Ces deux nouvelles lignes devraient apparaître dans votre table ClickHouse :

SELECT * FROM db_in_ch.table1

La réponse doit être :

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

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

5. Voyons ce qui se passe lorsque vous ajoutez des lignes à la table ClickHouse :

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

6. Les lignes ajoutées dans ClickHouse devraient apparaître dans la table PostgreSQL :

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

Cet exemple a présenté l’intégration de base entre PostgreSQL et ClickHouse à l’aide du moteur de table PostrgeSQL. Consultez la page de documentation du moteur de table PostgreSQL pour découvrir d’autres fonctionnalités, comme la spécification de schémas, le renvoi d’un sous-ensemble de colonnes et la connexion à plusieurs répliques. Consultez également le billet de blog ClickHouse and PostgreSQL - a match made in data heaven - part 1.

1. Dans postgresql.conf, définissez les niveaux d’écoute minimaux, le wal level de réplication et les slots de réplication :

ajoutez les entrées suivantes :

listen_addresses = '*'
max_replication_slots = 10
wal_level = logical

*ClickHouse nécessite au minimum un wal level défini sur logical et 2 slots de réplication

2. À l’aide d’un compte administrateur, créez un utilisateur pour la connexion depuis ClickHouse :

CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

*à des fins de démonstration, des privilèges complets de superutilisateur ont été accordés.

3. créer une nouvelle base de données :

CREATE DATABASE db1;

4. connectez-vous à la nouvelle base de données avec psql :

\connect db1

5. créez une nouvelle table :

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

6. insérez les lignes initiales :

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

7. Configurez PostgreSQL pour autoriser le nouvel utilisateur de réplication à se connecter à la nouvelle base de données. Vous trouverez ci-dessous l’entrée minimale à ajouter au fichier pg_hba.conf :

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

*à des fins de démonstration, cette méthode d’authentification par mot de passe en clair est utilisée. mettez à jour la ligne d’adresse avec le sous-réseau ou l’adresse du serveur, conformément à la documentation PostgreSQL

8. rechargez la configuration pg_hba.conf avec une commande comme celle-ci (à adapter selon votre version) :

/usr/pgsql-12/bin/pg_ctl reload

9. Testez la connexion avec le nouvel utilisateur clickhouse_user :

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

1. connectez-vous à la CLI ClickHouse

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

2. Activez la fonctionnalité expérimentale PostgreSQL du moteur de base de données :

SET allow_experimental_database_materialized_postgresql=1

3. Créez la nouvelle base de données qui sera répliquée et définissez la table initiale :

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

options minimales :

4. Vérifiez que la table initiale contient des données :

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. Dans PostgreSQL, insérez de nouvelles lignes :

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

2. Dans ClickHouse, vérifiez que les nouvelles lignes sont bien visibles :

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

Ce guide d’intégration se concentre sur un exemple simple montrant comment répliquer une base de données avec une table. Il existe toutefois des options plus avancées, notamment la réplication de l’ensemble de la base de données ou l’ajout de nouvelles tables et de nouveaux schémas aux réplications existantes. Bien que les commandes DDL ne soient pas prises en charge pour cette réplication, le moteur peut être configuré pour détecter les changements et recharger les tables lorsque des modifications structurelles sont apportées.