MaterializedPostgreSQL

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

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.

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

SHOW TABLES FROM postgres_db;

┌

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;

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

DETACH TABLE postgres_database.table_to_remove PERMANENTLY;

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

1. 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;

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

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

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

2. 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;

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.

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

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

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.

Un slot de réplication créé par l’utilisateur. Doit être utilisé avec 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>;

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.

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.

Les paramètres TLS/SSL sont transmis à libpq et peuvent être fournis via une collection nommée ou sous forme d’arguments clé-valeur ajoutés à la fin du moteur : sslmode (disable, allow, prefer, require, verify-ca ou verify-full ; s’il n’est pas défini, la valeur par défaut de libpq, prefer, s’applique), ainsi que les certificats et la clé, sous l’une de deux formes. sslrootcert (certificat de CA), sslcert (certificat client) et sslkey (clé privée client) sont des chemins vers des fichiers locaux au serveur, acceptés uniquement depuis une collection nommée définie dans le fichier de configuration du serveur. sslrootcert_pem, sslcert_pem et sslkey_pem acceptent à la place le contenu littéral du fichier correspondant, peuvent être spécifiés en SQL et sont masqués dans les logs et les requêtes SHOW, comme un mot de passe.

Exemple de connexion à un serveur PostgreSQL imposant TLS, avec vérification du certificat du serveur :

CREATE DATABASE postgres_db
ENGINE = MaterializedPostgreSQL('postgres-host:5432', 'postgres_database', 'postgres_user', 'postgres_password',
                                sslmode = 'verify-full', sslrootcert_pem = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----');

Les paramètres TLS/SSL font partie des paramètres de connexion PostgreSQL, qui sont définis lors de la création de la base de données. Pour les modifier, recréez la base de données.

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)

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

2. 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();

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

4. 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'

1. CREATE PUBLICATION – privilège CREATE.

2. CREATE_REPLICATION_SLOT – privilège de réplication.

3. pg_drop_replication_slot – privilège de réplication ou superutilisateur.

4. 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 :

1. pg_publication

2. pg_replication_slots

3. pg_publication_tables

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;