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. 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 ...]
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;
┌─name───┐
│ table1 │
└────────┘
SELECT * FROM postgres_db.postgres_table;
Ajout dynamique de nouvelles tables à la réplication
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
DETACH TABLE postgres_database.table_to_remove PERMANENTLY;
- 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`;
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`;
-
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;
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. materialized_postgresql_tables_list
materialized_postgresql_tables_list = 'table1(co1, col2),table2,table3(co3, col5, col7)
materialized_postgresql_schema
materialized_postgresql_schema_list
materialized_postgresql_max_block_size
65536.
materialized_postgresql_replication_slot
materialized_postgresql_snapshot.
materialized_postgresql_snapshot
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;
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
0.
Si cette valeur est définie sur 1, elle permet de configurer plusieurs tables MaterializedPostgreSQL pointant vers la même table PostgreSQL.
Basculement du slot de réplication logique
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'
-
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
