MaterializedPostgreSQL - ClickHouse Documentation

Il est recommandé aux utilisateurs de ClickHouse Cloud d’utiliser ClickPipes pour répliquer PostgreSQL vers ClickHouse. Cela prend en charge nativement la capture des changements de données (CDC) haute performance pour PostgreSQL.

Crée une base de données ClickHouse à partir des tables d’une base de données PostgreSQL. Dans un premier temps, une base de données utilisant le moteur MaterializedPostgreSQL crée un snapshot de la base de données PostgreSQL et charge les tables requises. Les tables requises peuvent inclure n’importe quel sous-ensemble de tables issu de n’importe quel sous-ensemble de schémas de la base de données spécifiée. En plus du snapshot, le moteur de base de données récupère le LSN et, une fois le dump initial des tables effectué, il commence à extraire les mises à jour depuis le WAL. Une fois la base de données créée, les tables ajoutées par la suite à la base de données PostgreSQL ne sont pas automatiquement ajoutées à la réplication. Elles doivent être ajoutées manuellement avec la requête ATTACH TABLE db.table. La réplication s’appuie sur le protocole de réplication logique de PostgreSQL, qui ne permet pas de répliquer le DDL, mais permet de savoir si des changements incompatibles avec la réplication se sont produits (modification du type de colonne, ajout/suppression de colonnes). Ces changements sont détectés et les tables concernées cessent alors de recevoir des mises à jour. Dans ce cas, vous devez utiliser les requêtes ATTACH/ DETACH PERMANENTLY pour recharger entièrement la table. Si le DDL ne casse pas la réplication (par exemple, en renommant une colonne), la table continuera à recevoir des mises à jour (l’insertion se fait par position).

Ce moteur de base de données est expérimental. Pour l’utiliser, définissez allow_experimental_database_materialized_postgresql sur 1 dans vos fichiers de configuration ou à l’aide de la commande SET :

SET allow_experimental_database_materialized_postgresql=1

Créer une base de données

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializedPostgreSQL('host:port', 'database', 'user', 'password') [SETTINGS ...]

Paramètres du moteur

host:port — endpoint du serveur PostgreSQL.
database — nom de la base de données PostgreSQL.
user — utilisateur PostgreSQL.
password — mot de passe de l’utilisateur.

Exemple d’utilisation

CREATE DATABASE postgres_db
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password');

SHOW TABLES FROM postgres_db;

┌─name───┐
│ table1 │
└────────┘

SELECT * FROM postgres_db.postgres_table;

Ajout dynamique de nouvelles tables à la réplication

Une fois la base de données MaterializedPostgreSQL créée, elle ne détecte pas automatiquement les nouvelles tables de la base de données PostgreSQL correspondante. Ces tables peuvent être ajoutées manuellement :

ATTACH TABLE postgres_database.new_table;

Avant la version 22.1, l’ajout d’une table à la réplication laissait un slot de réplication temporaire qui n’était pas supprimé (nommé {db_name}_ch_replication_slot_tmp). Si vous attachez des tables avec une version de ClickHouse antérieure à 22.1, veillez à le supprimer manuellement (SELECT pg_drop_replication_slot('{db_name}_ch_replication_slot_tmp')). Sinon, l’utilisation du disque augmentera. Ce problème a été corrigé dans la version 22.1.

Retrait dynamique de tables de la réplication

Il est possible de retirer certaines tables de la réplication :

DETACH TABLE postgres_database.table_to_remove PERMANENTLY;

Schéma PostgreSQL

Le schéma PostgreSQL peut être configuré de 3 façons (à partir de la version 21.12).

Un schéma pour un moteur de base de données MaterializedPostgreSQL. Nécessite l’utilisation du paramètre materialized_postgresql_schema. Les tables sont accessibles uniquement par leur nom :

CREATE DATABASE postgres_database
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_schema = 'postgres_schema';

SELECT * FROM postgres_database.table1;

Un nombre quelconque de schémas avec un ensemble donné de tables pour un moteur de base de données MaterializedPostgreSQL. Nécessite l’utilisation du paramètre materialized_postgresql_tables_list. Chaque table est spécifiée avec son schéma. Les tables sont accessibles en utilisant à la fois le nom du schéma et celui de la table :

CREATE DATABASE database1
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_tables_list = 'schema1.table1,schema2.table2,schema1.table3',
         materialized_postgresql_tables_list_with_schema = 1;

SELECT * FROM database1.`schema1.table1`;
SELECT * FROM database1.`schema2.table2`;

Mais dans ce cas, toutes les tables de materialized_postgresql_tables_list doivent être indiquées avec leur nom de schéma. Nécessite materialized_postgresql_tables_list_with_schema = 1. Avertissement : dans ce cas, les points ne sont pas autorisés dans le nom de la table.

Un nombre quelconque de schémas avec l’ensemble complet des tables pour un moteur de base de données MaterializedPostgreSQL. Nécessite l’utilisation du paramètre materialized_postgresql_schema_list.

CREATE DATABASE database1
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_schema_list = 'schema1,schema2,schema3';

SELECT * FROM database1.`schema1.table1`;
SELECT * FROM database1.`schema1.table2`;
SELECT * FROM database1.`schema2.table2`;

Avertissement : dans ce cas, le nom de la table ne peut pas contenir de points.

Prérequis

Le paramètre wal_level doit être défini sur logical, et le paramètre max_replication_slots doit avoir une valeur d’au moins 2 dans le fichier de configuration de PostgreSQL.
Chaque table répliquée doit avoir l’une des identités de réplica suivantes :

clé primaire (par défaut)
index

postgres# CREATE TABLE postgres_table (a Integer NOT NULL, b Integer, c Integer NOT NULL, d Integer, e Integer NOT NULL);
postgres# CREATE unique INDEX postgres_table_index on postgres_table(a, c, e);
postgres# ALTER TABLE postgres_table REPLICA IDENTITY USING INDEX postgres_table_index;

La clé primaire est toujours vérifiée en premier. Si elle est absente, l’index défini comme index d’identité de réplica est alors vérifié. Si l’index est utilisé comme identité de réplica, il ne peut y avoir qu’un seul index de ce type dans une table. Vous pouvez vérifier quel type est utilisé pour une table donnée à l’aide de la commande suivante :

postgres# SELECT CASE relreplident
          WHEN 'd' THEN 'default'
          WHEN 'n' THEN 'nothing'
          WHEN 'f' THEN 'full'
          WHEN 'i' THEN 'index'
       END AS replica_identity
FROM pg_class
WHERE oid = 'postgres_table'::regclass;

La réplication des valeurs TOAST n’est pas prise en charge. La valeur par défaut de ce type de données sera utilisée.

Paramètres

`materialized_postgresql_tables_list`

Définit une liste de tables de base de données PostgreSQL séparées par des virgules, qui seront répliquées via le moteur de base de données MaterializedPostgreSQL. Chaque table peut avoir un sous-ensemble de colonnes répliquées entre crochets. Si ce sous-ensemble de colonnes est omis, toutes les colonnes de la table seront répliquées.

materialized_postgresql_tables_list = 'table1(co1, col2),table2,table3(co3, col5, col7)

Valeur par défaut : liste vide — ce qui signifie que l’intégralité de la base de données PostgreSQL sera répliquée.

`materialized_postgresql_schema`

Valeur par défaut : chaîne vide. (Le schéma par défaut est utilisé)

`materialized_postgresql_schema_list`

Valeur par défaut : liste vide. (Le schéma par défaut est alors utilisé)

`materialized_postgresql_max_block_size`

Définit le nombre de lignes conservées en mémoire avant l’écriture des données dans la table PostgreSQL. Valeurs possibles :

Entier positif.

Valeur par défaut : 65536.

`materialized_postgresql_replication_slot`

Un slot de réplication créé par l’utilisateur. Doit être utilisé avec materialized_postgresql_snapshot.

`materialized_postgresql_snapshot`

Chaîne de texte identifiant un snapshot à partir duquel le dump initial des tables PostgreSQL sera effectué. Doit être utilisée avec materialized_postgresql_replication_slot.

CREATE DATABASE database1
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_tables_list = 'table1,table2,table3';

SELECT * FROM database1.table1;

Les paramètres peuvent être modifiés, si nécessaire, à l’aide d’une requête DDL. En revanche, il est impossible de modifier le paramètre materialized_postgresql_tables_list. Pour mettre à jour la liste des tables de ce paramètre, utilisez la requête ATTACH TABLE.

ALTER DATABASE postgres_database MODIFY SETTING materialized_postgresql_max_block_size = <new_size>;

`materialized_postgresql_use_unique_replication_consumer_identifier`

Utilisez un identifiant unique de consommateur de réplication pour la réplication. Valeur par défaut : 0. Si cette valeur est définie sur 1, elle permet de configurer plusieurs tables MaterializedPostgreSQL pointant vers la même table PostgreSQL.

`materialized_postgresql_use_extended_date_and_time_types`

Associe les types PostgreSQL date et timestamp/timestamptz aux types ClickHouse Date32 et DateTime64, qui couvrent la plage de valeurs plus large des types PostgreSQL. Valeur par défaut : 1. S’il est défini sur 0, les types plus limités Date et DateTime sont utilisés à la place (les valeurs en dehors de leur plage ou avec une précision sub-seconde ne peuvent pas être représentées). Ce paramètre contrôle uniquement les types de colonne choisis par l’inférence de type lors de la création des tables imbriquées. Il doit donc être spécifié au moment du CREATE DATABASE. Il ne peut pas être modifié ensuite avec ALTER DATABASE ... MODIFY SETTING (les tables imbriquées déjà créées conservent leurs types de colonne fixes, et une telle modification est refusée) ; pour le modifier, recréez la base de données. Il ne s’applique pas au moteur de table MaterializedPostgreSQL, où les types de colonne sont déclarés explicitement.

Remarques

Basculement du slot de réplication logique

Les slots de réplication logique qui existent sur le primaire ne sont pas disponibles sur les répliques de secours. Ainsi, en cas de basculement, le nouveau primaire (l’ancien standby physique) n’aura connaissance d’aucun des slots qui existaient sur l’ancien primaire. Cela entraînera une rupture de la réplication depuis PostgreSQL. Pour résoudre ce problème, vous pouvez gérer vous-même les slots de réplication et définir un slot de réplication permanent (vous trouverez plus d’informations ici). Vous devrez transmettre le nom du slot via le paramètre materialized_postgresql_replication_slot, et celui-ci devra être exporté avec l’option EXPORT SNAPSHOT. L’identifiant du snapshot devra être transmis via le paramètre materialized_postgresql_snapshot. Notez que cette solution ne doit être utilisée qu’en cas de besoin réel. S’il n’y a pas de nécessité avérée ni de compréhension claire de la raison pour laquelle elle est requise, il est préférable de laisser le moteur de table créer et gérer son propre slot de réplication. Exemple (de @bchrobot)

Configurez le slot de réplication dans PostgreSQL.

apiVersion: "acid.zalan.do/v1"
kind: postgresql
metadata:
  name: acid-demo-cluster
spec:
  numberOfInstances: 2
  postgresql:
    parameters:
      wal_level: logical
  patroni:
    slots:
      clickhouse_sync:
        type: logical
        database: demodb
        plugin: pgoutput

Attendez que le slot de réplication soit prêt, puis démarrez une transaction et exportez l’identifiant du snapshot de transaction :
BEGIN; SELECT pg_export_snapshot();

Dans ClickHouse, créez la base de données :

CREATE DATABASE demodb
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS
  materialized_postgresql_replication_slot = 'clickhouse_sync',
  materialized_postgresql_snapshot = '0000000A-0000023F-3',
  materialized_postgresql_tables_list = 'table1,table2,table3';

Terminez la transaction PostgreSQL une fois la réplication vers la base de données ClickHouse confirmée. Vérifiez que la réplication continue après le basculement :
kubectl exec acid-demo-cluster-0 -c postgres -- su postgres -c 'patronictl failover --candidate acid-demo-cluster-1 --force'

Autorisations requises

CREATE PUBLICATION — privilège CREATE.
CREATE_REPLICATION_SLOT — privilège de réplication.
pg_drop_replication_slot — privilège de réplication ou superutilisateur.
DROP PUBLICATION — propriétaire de la publication (username dans le moteur MaterializedPostgreSQL lui-même).

Il est possible d’éviter d’exécuter les commandes 2 et 3, et donc de ne pas avoir besoin de ces autorisations. Utilisez les paramètres materialized_postgresql_replication_slot et materialized_postgresql_snapshot, mais avec une grande prudence. Accès aux tables :

pg_publication
pg_replication_slots
pg_publication_tables

Sauvegarde et restauration

Une base de données MaterializedPostgreSQL peut faire l’objet d’une sauvegarde. Les données de chaque table répliquée sont stockées dans une table ReplacingMergeTree imbriquée ; BACKUP DATABASE sauvegarde donc ces données en déléguant à la table imbriquée.

BACKUP DATABASE postgres_db TO Disk('backups', 'postgres_db.zip');

La restauration sur place d’une base de données ou d’une table MaterializedPostgreSQL n’est pas prise en charge. Un objet MaterializedPostgreSQL restauré recommence immédiatement à se répliquer depuis la source PostgreSQL en direct ; restaurer le snapshot de sauvegarde par-dessus reviendrait donc à le mélanger avec l’état distant actuel. RESTORE échoue donc par précaution dans ce cas. Restaurez plutôt les données capturées dans de simples tables ReplacingMergeTree :

Dans une sauvegarde de base de données, la définition stockée de chaque table correspond déjà à la table ReplacingMergeTree imbriquée synthétique (et non au moteur MaterializedPostgreSQL), de sorte que chaque table peut être restaurée directement dans une nouvelle table qui n’existe pas encore :
RESTORE TABLE postgres_db.table1 AS restored_db.table1 FROM Disk('backups', 'postgres_db.zip') SETTINGS allow_different_table_def = 1;
Pour la sauvegarde d’une table MaterializedPostgreSQL autonome, la définition stockée est le moteur MaterializedPostgreSQL lui-même. Créez au préalable une table ReplacingMergeTree avec la même structure que la table imbriquée (y compris les colonnes _sign et _version), puis restaurez-y les données :
RESTORE TABLE src AS existing_replacing_mergetree FROM Disk('backups', 'table.zip') SETTINGS allow_different_table_def = 1;

​Créer une base de données

​Exemple d’utilisation

​Ajout dynamique de nouvelles tables à la réplication

​Retrait dynamique de tables de la réplication

​Schéma PostgreSQL

​Prérequis

​Paramètres

​materialized_postgresql_tables_list

​materialized_postgresql_schema

​materialized_postgresql_schema_list

​materialized_postgresql_max_block_size

​materialized_postgresql_replication_slot

​materialized_postgresql_snapshot

​materialized_postgresql_use_unique_replication_consumer_identifier

​materialized_postgresql_use_extended_date_and_time_types

​Remarques

​Basculement du slot de réplication logique

​Autorisations requises

​Sauvegarde et restauration

Créer une base de données

Exemple d’utilisation

Ajout dynamique de nouvelles tables à la réplication

Retrait dynamique de tables de la réplication

Schéma PostgreSQL

Prérequis

Paramètres

`materialized_postgresql_tables_list`

`materialized_postgresql_schema`

`materialized_postgresql_schema_list`

`materialized_postgresql_max_block_size`

`materialized_postgresql_replication_slot`

`materialized_postgresql_snapshot`

`materialized_postgresql_use_unique_replication_consumer_identifier`

`materialized_postgresql_use_extended_date_and_time_types`

Remarques

Basculement du slot de réplication logique

Autorisations requises

Sauvegarde et restauration