PostgreSQL | ClickHouse Docs

Un guide complet de migration de PostgreSQL vers ClickHouse, avec des conseils sur la modélisation des données et les concepts équivalents, est disponible ici. La suite explique comment connecter ClickHouse à PostgreSQL.

Cette page présente les options suivantes pour intégrer PostgreSQL à ClickHouse :

utiliser le moteur de table PostgreSQL pour lire les données d'une table PostgreSQL
utiliser le moteur de base de données expérimental MaterializedPostgreSQL pour synchroniser une base de données PostgreSQL avec une base de données ClickHouse

Utilisation du moteur de table PostgreSQL

Le moteur de table PostgreSQL permet d'exécuter des opérations SELECT et INSERT sur des données stockées sur un serveur PostgreSQL distant depuis ClickHouse. Cet article illustre les méthodes de base pour l'intégration à l'aide d'une table.

Configurer PostgreSQL

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

listen_addresses = '*'

Créez un utilisateur permettant de se connecter depuis ClickHouse. À des fins de démonstration, cet exemple accorde tous les privilèges de superutilisateur.

CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

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

CREATE DATABASE db_in_psg;

Créez une nouvelle table :

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

Ajoutons quelques lignes pour effectuer des tests :

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

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

Rechargez la configuration pg_hba.conf (adaptez cette commande à votre version) :

/usr/pgsql-12/bin/pg_ctl reload

Vérifiez que le nouvel utilisateur clickhouse_user peut se connecter :

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

Définir une table dans ClickHouse

Connectez-vous à clickhouse-client :

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

Créons une nouvelle base de données :

CREATE DATABASE db_in_ch;

Créez une table qui utilise 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 :

paramètre	Description	exemple
host:port	hostname ou adresse IP et port	postgres-host.domain.com:5432
database	nom de la base de données PostgreSQL	db_in_psg
user	nom d’utilisateur pour se connecter à PostgreSQL	clickhouse_user
password	mot de passe pour se connecter à PostgreSQL	ClickHouse_123

Tester l’intégration

Dans ClickHouse, affichez les premières lignes :

SELECT * FROM db_in_ch.table1

La table ClickHouse devrait être automatiquement renseigné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     │
└────┴─────────┘

De retour dans PostgreSQL, ajoutez quelques lignes à la table :

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

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

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

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 montré 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 - partie 1.

Utilisation du moteur de base de données MaterializedPostgreSQL

Non pris en charge par ClickHouse Cloud

Fonctionnalité expérimentale

Le moteur de base de données PostgreSQL utilise les fonctionnalités de réplication de PostgreSQL pour créer une réplique de la base de données contenant tout ou partie des schémas et des tables. Cet article vise à illustrer les méthodes de base d’intégration à l’aide d’une base de données, d’un schéma et d’une table.

Dans les procédures suivantes, la CLI PostgreSQL (psql) et la ClickHouse CLI (clickhouse-client) sont utilisées. Le serveur PostgreSQL est installé sur Linux. La configuration ci-dessous présente les paramètres minimaux si la base de données PostgreSQL est une nouvelle installation de test.

Dans PostgreSQL

Dans postgresql.conf, définissez les niveaux d'écoute minimums, 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 le wal level logical et au moins 2 replication slots

Avec un compte Admin, créez un utilisateur pour vous connecter depuis ClickHouse :

CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

*pour les besoins de la démonstration, tous les droits de superutilisateur ont été accordés.

créez une nouvelle base de données :

CREATE DATABASE db1;

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

\connect db1

créez une nouvelle table :

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

ajouter les premières lignes :

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

Configurez PostgreSQL pour autoriser les connexions à la nouvelle base de données avec le nouvel utilisateur pour la réplication. Voici 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 utilise l’authentification par mot de passe en clair. mettez à jour la ligne d’adresse avec soit le sous-réseau, soit l’adresse du serveur, conformément à la documentation PostgreSQL

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

/usr/pgsql-12/bin/pg_ctl reload

Testez la connexion avec le nouvel utilisateur clickhouse_user :

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

Dans ClickHouse

connectez-vous à l’interface CLI de ClickHouse

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

Activez la fonctionnalité expérimentale PostgreSQL pour le moteur de base de données :

SET allow_experimental_database_materialized_postgresql=1

Créez la nouvelle base de données à répliquer 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 :

paramètre	Description	exemple
host:port	nom d’hôte ou adresse IP et port	postgres-host.domain.com:5432
database	nom de la base de données PostgreSQL	db1
user	nom d’utilisateur pour se connecter à Postgres	clickhouse_user
password	mot de passe pour se connecter à Postgres	ClickHouse_123
settings	paramètres supplémentaires du moteur	materialized_postgresql_tables_list = 'table1'

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

Tester la réplication

Dans PostgreSQL, ajoutez de nouvelles lignes :

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

Dans ClickHouse, vérifiez que les nouvelles lignes sont 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     │
└────┴─────────┘

Résumé

Ce guide d’intégration présente un exemple simple de réplication d’une base de données contenant une table. Des options plus avancées permettent toutefois de répliquer l’intégralité de la base de données ou d’ajouter de nouvelles tables et de nouveaux schémas aux réplications existantes. Bien que les commandes DDL ne soient pas prises en charge par cette réplication, le moteur peut être configuré pour détecter les modifications structurelles et recharger les tables en conséquence.