Les rollbacks de transaction sont-ils répliqués dans ClickHouse ?
Puis-je conserver les données dans ClickHouse plus longtemps que dans mon Postgres source ?
Puis-je répliquer depuis plusieurs instances Postgres vers un ou plusieurs services ClickHouse ?
Les tables doivent-elles avoir des clés primaires pour être incluses dans Postgres CDC ?
- Clé primaire : l’approche la plus simple consiste à définir une clé primaire sur la table. Cela fournit un identifiant unique pour chaque ligne, ce qui est essentiel pour suivre les mises à jour et les suppressions. Dans ce cas, vous pouvez définir REPLICA IDENTITY sur
DEFAULT (comportement par défaut).
- Identité de réplication : si une table n’a pas de clé primaire, vous pouvez définir une identité de réplication. L’identité de réplication peut être définie sur
FULL, ce qui signifie que la ligne entière sera utilisée pour identifier les modifications. Vous pouvez également la configurer pour utiliser un index unique s’il en existe un sur la table, puis définir REPLICA IDENTITY sur USING INDEX index_name.
Pour définir l’identité de réplication sur FULL, vous pouvez utiliser la commande SQL suivante :
ALTER TABLE your_table_name REPLICA IDENTITY FULL;
REPLICA IDENTITY FULL permet également de répliquer les colonnes TOAST inchangées. Plus d’informations à ce sujet ici.
Notez que l’utilisation de REPLICA IDENTITY FULL peut avoir des conséquences sur les performances, ainsi qu’accélérer la croissance du WAL, en particulier pour les tables sans clé primaire et faisant l’objet de mises à jour ou de suppressions fréquentes, car cela nécessite de journaliser davantage de données pour chaque modification. En cas de doute ou si vous avez besoin d’aide pour configurer des clés primaires ou des identités de réplication pour vos tables, veuillez contacter notre équipe de support.
Il est important de noter que si aucune clé primaire ni aucune identité de réplication n’est définie, ClickPipes ne pourra pas répliquer les modifications de cette table, et vous risquez de rencontrer des erreurs pendant le processus de réplication. Il est donc recommandé de vérifier les schémas de vos tables et de vous assurer qu’ils respectent ces exigences avant de configurer votre ClickPipe.
Les tables partitionnées sont-elles prises en charge avec Postgres CDC ?
Puis-je connecter des bases de données Postgres qui n’ont pas d’adresse IP publique ou qui se trouvent sur des réseaux privés ?
-
Tunnel SSH
- Convient à la plupart des cas d’utilisation
- Consultez les instructions de configuration ici
- Fonctionne dans toutes les régions
-
AWS PrivateLink
- Disponible dans trois régions AWS :
- us-east-1
- us-east-2
- eu-central-1
- Pour des instructions de configuration détaillées, consultez notre documentation sur PrivateLink
- Dans les régions où PrivateLink n’est pas disponible, utilisez le tunnel SSH
ClickPipes for Postgres capture à la fois les INSERT et les UPDATE de Postgres sous forme de nouvelles lignes avec des versions différentes (à l’aide de la version column _peerdb_) dans ClickHouse. Le table engine ReplacingMergeTree effectue périodiquement une déduplication en arrière-plan en fonction de la clé de tri (colonnes ORDER BY), en ne conservant que la ligne dont la version _peerdb_ est la plus récente.
Les DELETE de Postgres sont propagés sous forme de nouvelles lignes marquées comme supprimées (à l’aide de la colonne _peerdb_is_deleted). Comme le processus de déduplication est Asynchronous, vous pouvez voir temporairement des doublons. Pour y remédier, vous devez gérer la déduplication au niveau de la query.
Notez également que, par défaut, Postgres n’envoie pas les valeurs des colonnes qui ne font pas partie de la primary key ou de l’identité de réplication lors des opérations DELETE. Si vous souhaitez capturer l’intégralité des données de la ligne lors des DELETE, vous pouvez définir REPLICA IDENTITY sur FULL.
Pour plus de détails, consultez :
Puis-je mettre à jour les colonnes de clé primaire dans PostgreSQL ?
Par défaut, les mises à jour de clé primaire dans PostgreSQL ne peuvent pas être correctement répercutées dans ClickHouse.Cette limitation s’explique par le fait que la déduplication de ReplacingMergeTree repose sur les colonnes ORDER BY (qui correspondent généralement à la clé primaire). Lorsqu’une clé primaire est mise à jour dans PostgreSQL, elle apparaît dans ClickHouse comme une nouvelle ligne avec une clé différente, plutôt que comme une mise à jour de la ligne existante. Cela peut entraîner la présence simultanée des anciennes et des nouvelles valeurs de clé primaire dans votre table ClickHouse.
Les changements de schéma sont-ils pris en charge ?
Quels sont les coûts de ClickPipes for Postgres CDC ?
La taille de mon slot de réplication augmente ou ne diminue pas ; quel peut être le problème ?
-
Pics soudains d’activité de la base de données
- D’importantes mises à jour par lot, des insertions en masse ou des modifications de schéma significatives peuvent rapidement générer un grand volume de données WAL.
- Le slot de réplication conserve ces enregistrements WAL jusqu’à ce qu’ils soient consommés, ce qui provoque une hausse temporaire de sa taille.
-
Transactions longues
- Une transaction ouverte oblige Postgres à conserver tous les segments WAL générés depuis le début de la transaction, ce qui peut augmenter considérablement la taille du slot.
- Définissez
statement_timeout et idle_in_transaction_session_timeout sur des valeurs raisonnables afin d’éviter que des transactions restent ouvertes indéfiniment :
SELECT
pid,
state,
age(now(), xact_start) AS transaction_duration,
query AS current_query
FROM
pg_stat_activity
WHERE
xact_start IS NOT NULL
ORDER BY
age(now(), xact_start) DESC;
-
Opérations de maintenance ou utilitaires (par ex.,
pg_repack)
- Des outils comme
pg_repack peuvent réécrire des tables entières, générant de grandes quantités de données WAL en peu de temps.
- Planifiez ces opérations pendant les périodes de moindre trafic ou surveillez de près votre consommation de WAL pendant leur exécution.
-
VACUUM et VACUUM ANALYZE
- Bien qu’indispensables à la bonne santé de la base de données, ces opérations peuvent générer du trafic WAL supplémentaire, en particulier si elles analysent de grandes tables.
- Envisagez d’ajuster les paramètres d’autovacuum ou de planifier des opérations VACUUM manuelles pendant les heures creuses.
-
Le consommateur de réplication ne lit pas activement le slot
- Si votre pipeline CDC (par exemple, ClickPipes) ou un autre consommateur de réplication s’arrête, se met en pause ou plante, les données WAL s’accumuleront dans le slot.
- Assurez-vous que votre pipeline fonctionne en continu et vérifiez les logs pour détecter d’éventuelles erreurs de connectivité ou d’authentication.
Pour une excellente analyse approfondie de ce sujet, consultez notre article de blog : Overcoming Pitfalls of Postgres Logical Decoding.
ClickPipes for Postgres vise à mapper les types de données de Postgres aussi nativement que possible côté ClickHouse. Ce document fournit une liste complète de chaque type de données et de son mapping : Matrice des types de données.
Puis-je définir mon propre mapping de types de données lors de la réplication des données de Postgres vers ClickHouse ?
Que se passe-t-il pour les insertions lorsqu’un mirror est en pause ?
- Pour sync, si l’opération est annulée en cours d’exécution, le confirmed_flush_lsn dans Postgres n’est pas avancé. Le sync suivant repartira donc de la même position que celui qui a été interrompu, ce qui garantit la cohérence des données.
- Pour normalize, l’ordre d’insertion de ReplacingMergeTree gère la déduplication.
En résumé, même si les processus sync et normalize sont arrêtés pendant une pause, cela ne présente pas de risque, car ils peuvent reprendre sans perte de données ni incohérence.
La création d’un ClickPipe peut-elle être automatisée ou effectuée via l’API ou la CLI ?
snapshot number of tables in parallel ou spécifier une colonne de partitionnement indexée personnalisée pour les grandes tables.
Vous pouvez laisser ClickPipes gérer vos publications (cela nécessite des permissions supplémentaires) ou les créer vous-même. Avec les publications gérées par ClickPipes, nous gérons automatiquement l’ajout et la suppression de tables à mesure que vous modifiez le pipe. Si vous les gérez vous-même, définissez soigneusement le périmètre de vos publications pour n’inclure que les tables que vous devez répliquer - inclure des tables inutiles ralentira le décodage du WAL de Postgres.
Si vous incluez une table dans votre publication, assurez-vous qu’elle dispose soit d’une clé primaire, soit de REPLICA IDENTITY FULL. Si vous avez des tables sans clé primaire, créer une publication pour toutes les tables entraînera l’échec des opérations DELETE et UPDATE sur ces tables.
Pour identifier les tables sans clé primaire dans votre base de données, vous pouvez utiliser cette requête :
SELECT table_schema, table_name
FROM information_schema.tables
WHERE
(table_catalog, table_schema, table_name) NOT IN (
SELECT table_catalog, table_schema, table_name
FROM information_schema.table_constraints
WHERE constraint_type = 'PRIMARY KEY') AND
table_schema NOT IN ('information_schema', 'pg_catalog', 'pgq', 'londiste');
-
Exclure de ClickPipes les tables sans clé primaire :
Créez la publication en n’incluant que les tables ayant une clé primaire :
CREATE PUBLICATION clickpipes_publication FOR TABLE table_with_primary_key1, table_with_primary_key2, ...;
-
Inclure dans ClickPipes les tables sans clé primaire :
Si vous souhaitez inclure des tables sans clé primaire, vous devez modifier leur identité de réplication en
FULL. Cela garantit le bon fonctionnement des opérations UPDATE et DELETE :
ALTER TABLE table_without_primary_key1 REPLICA IDENTITY FULL;
ALTER TABLE table_without_primary_key2 REPLICA IDENTITY FULL;
CREATE PUBLICATION clickpipes_publication FOR TABLE <...>, <...>;
Si vous créez une publication manuellement au lieu de laisser ClickPipes la gérer, nous vous déconseillons de créer une publication FOR ALL TABLES, car cela génère davantage de trafic de Postgres vers ClickPipes (en envoyant les modifications d’autres tables qui ne font pas partie du pipe) et réduit l’efficacité globale.Pour les publications créées manuellement, ajoutez à la publication toutes les tables souhaitées avant de les ajouter au pipe.
Si vous répliquez depuis une réplique de lecture / hot standby Postgres, vous devrez créer votre propre publication sur l’instance primaire, qui sera automatiquement propagée vers le standby. Dans ce cas, le ClickPipe ne pourra pas gérer la publication, car il n’est pas possible de créer des publications sur un standby.
Paramètres recommandés pour max_slot_wal_keep_size
- Au minimum : définissez
max_slot_wal_keep_size de manière à conserver au moins deux jours de données WAL.
- Pour les grandes bases de données (volume élevé de transactions) : conservez au moins 2 à 3 fois le pic quotidien de génération de WAL.
- Pour les environnements soumis à des contraintes de stockage : ajustez ce paramètre avec prudence pour éviter de saturer le disque tout en garantissant la stabilité de la réplication.
Pour déterminer le paramètre approprié, mesurez le taux de génération du WAL :
Pour PostgreSQL 10 et versions ultérieures
SELECT pg_wal_lsn_diff(pg_current_wal_insert_lsn(), '0/0') / 1024 / 1024 AS wal_generated_mb;
Pour PostgreSQL 9.6 et les versions antérieures :
SELECT pg_xlog_location_diff(pg_current_xlog_insert_location(), '0/0') / 1024 / 1024 AS wal_generated_mb;
- Exécutez la requête ci-dessus à différents moments de la journée, en particulier pendant les périodes de forte activité transactionnelle.
- Calculez la quantité de WAL générée sur une période de 24 heures.
- Multipliez cette valeur par 2 ou 3 afin de garantir une rétention suffisante.
- Définissez
max_slot_wal_keep_size sur la valeur obtenue, en Mo ou en Go.
Exemple
Si votre base de données génère 100 Go de WAL par jour, définissez :
max_slot_wal_keep_size = 200GB
Je vois une erreur ReceiveMessage EOF dans les logs. Qu’est-ce que cela signifie ?
ReceiveMessage est une fonction du protocole de décodage logique de Postgres qui lit les messages du flux de réplication. Une erreur EOF (End of File) indique que la connexion au serveur Postgres a été interrompue de manière inattendue lors de la lecture du flux de réplication.
Il s’agit d’une erreur récupérable, sans gravité. ClickPipes tentera automatiquement de se reconnecter et de reprendre le processus de réplication.
Cela peut se produire pour plusieurs raisons :
- Problèmes réseau : Des perturbations réseau temporaires peuvent entraîner une interruption de la connexion.
- Redémarrage du serveur Postgres : Si le serveur Postgres redémarre ou plante, la connexion sera perdue.
Mon slot de réplication est invalidé. Que dois-je faire ?
max_slot_wal_keep_size trop faible sur votre base de données PostgreSQL (par exemple, quelques gigaoctets). Nous vous recommandons d’augmenter cette valeur. Consultez cette section pour savoir comment ajuster max_slot_wal_keep_size. Idéalement, cette valeur devrait être définie à au moins 200GB afin d’éviter l’invalidation du slot de réplication.
Dans de rares cas, nous avons constaté que ce problème se produisait même lorsque max_slot_wal_keep_size n’était pas configuré. Cela peut être dû à un bug rare et complexe dans PostgreSQL, bien que la cause reste incertaine.
Je rencontre des erreurs de mémoire insuffisante (OOM) sur ClickHouse pendant l’ingestion de données par mon ClickPipe. Pouvez-vous m’aider ?
-
Une technique d’optimisation courante pour les
JOIN s’applique lorsque vous avez un LEFT JOIN dont la table de droite est très volumineuse. Dans ce cas, réécrivez la requête pour utiliser un RIGHT JOIN et déplacer la table la plus volumineuse du côté gauche. Cela permet au planificateur de requêtes d’utiliser la mémoire plus efficacement.
-
Une autre optimisation pour les
JOIN consiste à filtrer explicitement les tables au moyen de subqueries ou de CTEs, puis à effectuer le JOIN sur ces sous-requêtes. Cela donne au planificateur des indications pour filtrer efficacement les lignes et exécuter le JOIN.
Je vois une erreur invalid snapshot identifier pendant le chargement initial. Que dois-je faire ?
invalid snapshot identifier survient lorsqu’il y a une perte de connexion entre ClickPipes et votre base de données Postgres. Cela peut être dû à des timeouts de passerelle, à des redémarrages de la base de données ou à d’autres problèmes temporaires.
Nous vous recommandons de ne pas effectuer d’opérations perturbatrices, comme des mises à niveau ou des redémarrages, sur votre base de données Postgres pendant le chargement initial, et de vous assurer que la connexion réseau à votre base de données est stable.
Pour résoudre ce problème, vous pouvez déclencher une resynchronisation depuis l’interface utilisateur de ClickPipes. Cela relancera le processus de chargement initial depuis le début.
Que se passe-t-il si je supprime une publication dans Postgres ?
- Créez une nouvelle publication avec le même nom et les tables requises dans Postgres
- Cliquez sur le bouton ‘Resync tables’ dans l’onglet Settings de votre ClickPipe
Cette resynchronisation est nécessaire, car la publication recréée aura un identifiant d’objet (OID) différent dans Postgres, même si elle porte le même nom. Le processus de resynchronisation actualise vos tables de destination et rétablit la connexion.
Vous pouvez également créer un tout nouveau pipe si vous le préférez.
Notez que si vous travaillez avec des tables partitionnées, veillez à créer votre publication avec les paramètres appropriés :
CREATE PUBLICATION clickpipes_publication
FOR TABLE <...>, <...>
WITH (publish_via_partition_root = true);
Que faire si je vois des erreurs Unexpected Datatype ou Cannot parse type XX ... ?
Je vois des erreurs comme invalid memory alloc request size <XXX> lors de la réplication/de la création du slot
Je dois conserver un historique complet dans ClickHouse, même lorsque les données sont supprimées de la base de données source Postgres. Puis-je ignorer complètement les opérations DELETE et TRUNCATE de Postgres dans ClickPipes ?
CREATE PUBLICATION <pub_name> FOR TABLES IN SCHEMA <schema_name> WITH (publish = 'insert,update');
Pourquoi ne puis-je pas répliquer ma table si son nom contient un point ?
Le chargement initial est terminé, mais il n’y a pas de données / il manque des données dans ClickHouse. Quel peut être le problème ?
- Que l’utilisateur dispose des permissions nécessaires pour lire les tables source.
- Qu’il n’existe pas de politiques de lignes côté ClickHouse susceptibles de filtrer certaines lignes.
ClickPipe peut-il créer un slot de réplication avec le basculement activé ?
Advanced Settings lors de la création du ClickPipe. Notez que votre version de Postgres doit être 17 ou supérieure pour utiliser cette fonctionnalité.
Si la source est configurée en conséquence, le slot est conservé après un basculement vers une réplique de lecture Postgres, ce qui garantit une réplication continue des données. Pour en savoir plus, consultez ce lien.
J’observe des erreurs comme Internal error encountered during logical decoding of aborted sub-transaction
ReorderBufferPreserveLastSpilledSnapshot, cela indique que le décodage logique ne parvient pas à lire le snapshot écrit sur disque. Il peut être utile d’essayer d’augmenter la valeur de logical_decoding_work_mem.
J’obtiens des erreurs telles que error converting new tuple to map ou error parsing logical message pendant la réplication CDC
Puis-je inclure des colonnes que j’avais initialement exclues de la réplication ?
Je constate que mon ClickPipe est passé à l’état Snapshot, mais aucune donnée n’arrive : quel pourrait être le problème ?
La prise d’instantanés en parallèle prend du temps pour récupérer les partitions
La création du slot de réplication est bloquée par une transaction
CREATE_REPLICATION_SLOT bloquée dans l’état Lock. Cela peut être dû à une autre transaction qui maintient des verrous sur des objets que Postgres utilise pour créer des slots de réplication.
Pour voir les requêtes bloquantes, vous pouvez exécuter la requête ci-dessous sur votre source Postgres :
SELECT
blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query,
blocking.state AS blocking_state
FROM pg_locks blocked_lock
JOIN pg_stat_activity blocked
ON blocked_lock.pid = blocked.pid
JOIN pg_locks blocking_lock
ON blocking_lock.locktype = blocked_lock.locktype
AND blocking_lock.database IS NOT DISTINCT FROM blocked_lock.database
AND blocking_lock.relation IS NOT DISTINCT FROM blocked_lock.relation
AND blocking_lock.page IS NOT DISTINCT FROM blocked_lock.page
AND blocking_lock.tuple IS NOT DISTINCT FROM blocked_lock.tuple
AND blocking_lock.virtualxid IS NOT DISTINCT FROM blocked_lock.virtualxid
AND blocking_lock.transactionid IS NOT DISTINCT FROM blocked_lock.transactionid
AND blocking_lock.classid IS NOT DISTINCT FROM blocked_lock.classid
AND blocking_lock.objid IS NOT DISTINCT FROM blocked_lock.objid
AND blocking_lock.objsubid IS NOT DISTINCT FROM blocked_lock.objsubid
AND blocking_lock.pid != blocked_lock.pid
JOIN pg_stat_activity blocking
ON blocking_lock.pid = blocking.pid
WHERE NOT blocked_lock.granted;
