La plupart des migrations de bases de données n’échouent pas parce que les ingénieurs ne savent pas copier des octets. Elles échouent parce que les systèmes en production sont remplis d’assomptions présentées comme des faits : bizarreries de collations, conversions implicites, valeurs par défaut « utiles » et requêtes applicatives qui fonctionnaient uniquement parce que l’ancien moteur les tolérait poliment.
Si vous migrez de MariaDB vers PostgreSQL et que vous visez zéro interruption, vous vous engagez dans un projet de systèmes distribués déguisé en projet de base de données. Ceci est le guide de terrain : quoi mesurer, quoi modifier, quoi ne pas toucher un vendredi, et comment basculer sans le classique « pourquoi tout est lent maintenant ? ».
Choisir la bonne forme de migration (et pourquoi « juste dump/restore » ment)
Vous avez trois grandes façons de passer de MariaDB à PostgreSQL :
- Big-bang (dump/restore) : arrêter les écritures, prendre un instantané, restaurer dans Postgres, basculer l’application. C’est propre, facile à raisonner, et cela exige une interruption. Même si vous l’appelez « mode maintenance », vos utilisateurs l’appellent « en panne ».
- Shadow read : migrer les données et diriger progressivement le trafic de lecture vers PostgreSQL pendant que les écritures continuent d’aller vers MariaDB. Utile pour valider les performances. Pas suffisant pour une bascule complète à moins d’ajouter une stratégie d’écriture.
- Zéro interruption (backfill + CDC + bascule contrôlée) : garder MariaDB comme source de vérité pendant que vous backfillez les données historiques dans PostgreSQL, puis appliquer les changements en cours via la capture de données de changement (CDC) ou des écritures doubles. Basculer lectures et écritures de façon planifiée et réversible.
Pour « pas d’interruption et pas de surprises », choisissez la méthode n°3. C’est plus de travail au départ, mais ça échange la panique contre un processus. Vous obtenez :
- Un pipeline répétable que vous pouvez exécuter en staging et en production.
- Un décalage quantifié (secondes derrière, pas impressions).
- Un plan de rollback qui n’implique pas la prière.
Une vérité opérationnelle : si vous ne pouvez pas mesurer le lag de réplication et la divergence des données, vous n’avez pas une migration. Vous avez un espoir.
Faits et contexte historique qui comptent vraiment
- MariaDB existe à cause d’une perte de confiance. Il a été créé par les développeurs originaux de MySQL après l’acquisition de MySQL par Oracle, avec la promesse de rester ouvert et communautaire.
- La lignée de PostgreSQL est académique au meilleur sens. Il a évolué à partir du projet POSTGRES à l’UC Berkeley, ce qui explique sa rigidité en matière de correction.
- InnoDB est devenu le moteur par défaut pour une raison. Les limitations de verrouillage au niveau table et de récupération après crash de MyISAM ont servi de leçon coûteuse.
- MVCC de PostgreSQL est « toujours activé ». Il façonne le vacuum, le bloat et le comportement des index ; ce n’est pas un interrupteur que l’on peut ignorer.
- La réplication MariaDB s’est historiquement construite autour des traditions statement/binlog. Les outils CDC reposent souvent sur cet écosystème ; vous en héritez des cas limites (DDL, non-déterminisme, fuseaux horaires).
- PostgreSQL traite les identificateurs différemment. Les identificateurs non quote sont transformés en minuscules, ce qui casse silencieusement les migrations depuis des écosystèmes qui comptent sur la casse insensible.
- JSONB de PostgreSQL n’est pas juste « du JSON plus rapide ». Il stocke une représentation parsée avec un comportement d’indexation et des sémantiques d’égalité subtils.
- UTF-8 partout n’a pas toujours été la norme. Collations et jeux de caractères restent des champs de mines lors d’une migration parce que « même chaîne » peut signifier « ordre différent ».
- Les dates « zéro » de MariaDB étaient un amortisseur de compatibilité. PostgreSQL les refuse. Votre code applicatif devra évoluer.
MariaDB vs PostgreSQL : différences qui piquent en production
Dialecte SQL et système de types : la stricte rigueur n’est pas un bug
MariaDB (comme MySQL) a été historiquement permissif : casts implicites, troncatures silencieuses selon le mode SQL, conversions « utiles ». PostgreSQL est plus strict et explicite. C’est bon pour la correction, mais ça expose les parties de votre application qui profitaient d’un comportement indéfini.
Exemples qui cassent souvent :
- Casts implicites de chaîne vers entier qui marchaient avant et qui produisent maintenant une erreur.
- Différences de comportement de GROUP BY lors de la sélection de colonnes non agrégées.
- Valeurs par défaut de timestamp et gestion des fuseaux horaires.
- Tri des NULL et ordre de collation affectant la pagination.
Transactions et verrouillage : mêmes mots, conséquences différentes
Les deux supportent les transactions ; les deux ont des niveaux d’isolation ; les deux peuvent faire des deadlocks. Mais la forme diffère :
- PostgreSQL utilise MVCC plus des verrous au niveau ligne ; les lecteurs ne bloquent pas les écrivains et inversement, mais les transactions longues empêchent le nettoyage et génèrent du bloat.
- MariaDB/InnoDB utilise aussi MVCC, mais les modèles de verrou autour des gap locks, next-key locks et vérifications d’unicité apparaissent différemment, surtout sous
REPEATABLE READ. - Le verrouillage des DDL diffère. PostgreSQL peut exécuter de nombreuses opérations concurremment, mais pas toutes ; MariaDB a ses propres règles « online DDL » dépendant de la version et du moteur.
Auto-increment vs séquences : on ne peut pas ignorer le mapping
AUTO_INCREMENT de MariaDB est lié aux métadonnées de la table. PostgreSQL utilise typiquement des objets SEQUENCE, souvent via GENERATED ... AS IDENTITY. Pendant la migration, il faut positionner correctement les séquences sinon vous aurez des collisions de clés primaires après la bascule.
Charset et collation : le tueur silencieux de la correction
Même si vous êtes « tout UTF-8 », les collations diffèrent. L’ordre et la folding de casse diffèrent. Cela casse :
- Contraintes d’unicité (deux chaînes considérées égales dans un système mais distinctes dans l’autre, ou l’inverse).
- Utilisation des index (dépendante de la collation).
- Pagination lors du tri par colonnes texte (les utilisateurs voient des doublons ou des lignes manquantes entre les pages).
Modèle opérationnel : vacuum devient votre nouvelle réunion récurrente
PostgreSQL a besoin de vacuum pour récupérer l’espace et maintenir en bonne santé les visibility maps. Autovacuum suffit la plupart du temps—jusqu’à ce qu’il ne suffise plus. Si votre charge modifie beaucoup et que vous avez des transactions longues, vous apprendrez ce que « bloat » veut dire d’une manière très personnelle.
Une citation à garder sur un post-it :
« L’espoir n’est pas une stratégie. » — General Gordon R. Sullivan
Blague #1 : Si votre plan de migration est « nous allons surveiller », vous n’avez pas de plan—vous avez un hobby.
Architecture sans interruption : backfill + CDC + bascule contrôlée
Le modèle de base
Un plan sensé pour zéro interruption ressemble généralement à ceci :
- Préparer Postgres : schéma, rôles, extensions, tuning des paramètres, observabilité.
- Backfill initial : copier les tables existantes de MariaDB vers Postgres pendant que l’appli reste en ligne et écrit dans MariaDB.
- CDC / réplication vers Postgres : streamer les changements en cours (insert/update/delete) depuis le binlog MariaDB vers Postgres pour le garder proche du temps réel.
- Validation de cohérence : checksums, comptages, comparaisons échantillonnés de lignes, plus les invariants métier.
- Basculement : basculer d’abord les lectures, puis les écritures (ou faire les deux avec un court gel des écritures mesuré en secondes, pas en minutes).
- Renforcement post-basculement : tuning d’index, tuning du vacuum, corrections de requêtes, et une fenêtre de rollback pendant laquelle MariaDB reste disponible.
CDC vs écritures doubles : choisissez votre poison avec soin
CDC (recommandé) signifie que l’application continue d’écrire uniquement dans MariaDB jusqu’à la bascule. Vous répliquez les changements vers Postgres hors bande. Cela réduit la complexité applicative et évite les bugs de cohérence des écritures doubles. Le coût est que vous devez disposer d’un pipeline CDC robuste et d’un plan pour les changements de schéma pendant la fenêtre de synchronisation.
Écritures doubles signifie que votre application écrit dans les deux bases pendant une période. Ça peut fonctionner, mais vous héritez alors du problème des transactions distribuées. Si vous faites cela, vous devez concevoir pour les pannes partielles : une écriture réussit, l’autre échoue, et votre système doit réconcilier. La plupart des équipes surestiment leur appétit pour cela.
Conseil pratique : si vous pouvez éviter les écritures doubles, évitez-les. CDC plus une bascule soigneusement gérée est moins héroïque et plus répétable.
Mécanique de bascule qui ne ruine pas votre week-end
La bascule la plus propre est « lectures d’abord, écritures ensuite » :
- Basculez le trafic en lecture (reporting, jobs batch) vers Postgres tôt pour dénicher les différences de requêtes et les manques d’index.
- Conservez les écritures OLTP sur MariaDB pendant que CDC maintient Postgres à jour.
- Planifiez un court gel des écritures (secondes à quelques minutes) pour vider le lag à zéro, valider, puis basculer les écritures.
Si votre équipe produit demande « littéralement zéro gel des écritures », c’est possible, mais vous payerez en complexité : écritures idempotentes, résolution de conflits, et parfois un système d’horloge logique temporaire. Pour la plupart des entreprises, une courte pause d’écriture est l’honnêteté la moins chère que vous achetez.
DDL pendant la migration : l’ennemi du « simple »
Les changements de schéma en cours de migration sont là où les délais meurent. Gérez-les délibérément :
- Geler les DDL pendant la fenêtre de synchronisation finale. Faites-en une politique, pas une suggestion.
- Si vous devez appliquer du DDL, appliquez-le aux deux systèmes et validez que votre outil CDC le gère correctement (beaucoup ne le font pas, ou ne gèrent qu’un sous-ensemble).
- Privilégiez l’évolution de schéma compatible en arrière (ajouter des colonnes nullable, backfiller, puis appliquer les contraintes plus tard).
Tâches pratiques avec commandes : ce que vous lancez, ce que ça signifie, ce que vous décidez
Ce sont les tâches qui transforment « projet de migration » en « migration avec reçu ». Les commandes sont représentatives et exécutables ; adaptez les identifiants et hôtes.
Tâche 1 : Vérifier le binlog MariaDB (prérequis CDC)
cr0x@server:~$ mysql -h mariadb01 -u admin -p -e "SHOW VARIABLES LIKE 'log_bin'; SHOW VARIABLES LIKE 'binlog_format'; SHOW VARIABLES LIKE 'server_id';"
Enter password:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | ROW |
+---------------+-----------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 101 |
+---------------+-------+Ce que ça signifie : log_bin=ON et binlog_format=ROW sont la base standard pour un CDC fiable. server_id doit être défini et unique.
Décision : Si le binlog est désactivé ou pas en ROW, corrigez cela avant toute autre chose. La réplication basée sur statements créera tôt ou tard des moments « mais ça marchait en staging ».
Tâche 2 : Vérifier le mode GTID de MariaDB (facilite la reprise du CDC)
cr0x@server:~$ mysql -h mariadb01 -u admin -p -e "SHOW VARIABLES LIKE 'gtid_strict_mode'; SHOW VARIABLES LIKE 'gtid_binlog_pos';"
Enter password:
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| gtid_strict_mode | ON |
+------------------+-------+
+-----------------+------------------------------------------+
| Variable_name | Value |
+-----------------+------------------------------------------+
| gtid_binlog_pos | 0-101-9876543 |
+-----------------+------------------------------------------+Ce que ça signifie : GTID facilite le redémarrage d’un pipeline CDC sans deviner le fichier/position du binlog.
Décision : Si GTID n’est pas activé, vous pouvez toujours faire du CDC, mais votre charge opérationnelle augmente. Décidez tôt si vous standardisez sur GTID.
Tâche 3 : Inventorier les caractéristiques de schéma qui ne se traduiront pas proprement
cr0x@server:~$ mysql -h mariadb01 -u admin -p -N -e "SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys') ORDER BY TABLE_SCHEMA, TABLE_NAME LIMIT 10;"
Enter password:
appdb users InnoDB utf8mb4_unicode_ci
appdb orders InnoDB utf8mb4_unicode_ci
appdb order_items InnoDB utf8mb4_unicode_ci
appdb audit_log InnoDB utf8mb4_unicode_ci
appdb sessions InnoDB utf8mb4_unicode_ci
appdb settings InnoDB utf8mb4_unicode_ci
appdb products InnoDB utf8mb4_unicode_ci
appdb inventory InnoDB utf8mb4_unicode_ci
appdb payments InnoDB utf8mb4_unicode_ci
appdb refunds InnoDB utf8mb4_unicode_ciCe que ça signifie : Vous vérifiez les moteurs et les collations. Les tables non InnoDB, les collations étranges et les encodages hérités multiplient les risques de migration.
Décision : Si vous trouvez MyISAM/Aria ou des collations mixtes, prévoyez du temps pour normaliser ou mapper explicitement le comportement dans Postgres (y compris les attentes côté appli).
Tâche 4 : Capturer le mode SQL de MariaDB (indique combien de mauvaises données vous avez)
cr0x@server:~$ mysql -h mariadb01 -u admin -p -e "SELECT @@sql_mode;"
Enter password:
+----------------------------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------+Ce que ça signifie : Des modes moins stricts masquent souvent des troncatures et des dates invalides. Le mode strict réduit les surprises, mais n’élimine pas les lignes historiques erronées.
Décision : Si la stricte n’est pas activée, lancez des vérifications de qualité des données avant la migration. Postgres refusera certaines valeurs que MariaDB acceptait.
Tâche 5 : Identifier les « zero dates » et timestamps invalides
cr0x@server:~$ mysql -h mariadb01 -u admin -p -e "SELECT COUNT(*) AS zero_dates FROM appdb.orders WHERE created_at='0000-00-00 00:00:00';"
Enter password:
+-----------+
| zero_dates|
+-----------+
| 42 |
+-----------+Ce que ça signifie : PostgreSQL n’autorise pas les timestamps année 0000. Ces lignes échoueront au chargement sauf transformation.
Décision : Choisissez une politique de remédiation : convertir en NULL, convertir en epoch, ou déplacer le champ en texte temporairement. Documentez-le ; vos auditeurs poseront la question plus tard.
Tâche 6 : Déployer Postgres avec des réglages de base et vérifier
cr0x@server:~$ psql -h pg01 -U postgres -c "SHOW server_version; SHOW wal_level; SHOW max_wal_senders;"
server_version
----------------
16.3
(1 row)
wal_level
-----------
replica
(1 row)
max_wal_senders
-----------------
10
(1 row)Ce que ça signifie : Vous confirmez le moteur cible et les réglages de réplication (même si vous n’utilisez pas la réplication PG, le comportement WAL impacte l’exploitation).
Décision : Si vous prévoyez de la réplication logique ou une ingestion intense, réglez les paramètres WAL et le checkpoint délibérément. Les réglages par défaut ne sont pas un plan de performance.
Tâche 7 : Créer des rôles et appliquer le principe du moindre privilège dès le départ
cr0x@server:~$ psql -h pg01 -U postgres -c "CREATE ROLE app_user LOGIN PASSWORD 'REDACTED'; GRANT CONNECT ON DATABASE appdb TO app_user;"
CREATE ROLE
GRANTCe que ça signifie : Vous évitez le classique « on corrigera les permissions après la bascule ».
Décision : Si votre appli dépend actuellement de privilèges larges (ex. DDL), décidez si vous les conservez (non recommandé) ou si vous refactorez maintenant.
Tâche 8 : Convertir le schéma (exemple avec pgloader) et inspecter les résultats
cr0x@server:~$ pgloader mysql://migrate:REDACTED@mariadb01/appdb postgresql://postgres@pg01/appdb
2025-12-30T10:01:12.000000Z LOG pgloader version "3.6.9"
2025-12-30T10:01:13.000000Z LOG Migrating from #<MYSQL-CONNECTION mysql://migrate@.../appdb>
2025-12-30T10:01:13.000000Z LOG Migrating into #<PGSQL-CONNECTION postgresql://postgres@pg01/appdb>
2025-12-30T10:05:44.000000Z LOG Data errors in table "orders": 0
2025-12-30T10:05:44.000000Z LOG Total import time: 00:04:31Ce que ça signifie : L’outil a fait une première passe du schéma et des données. La ligne de log sur les erreurs de données est votre premier détecteur d’alerte.
Décision : Traitez cela comme un échafaudage, pas comme la réponse finale. Passez en revue types, contraintes, indexes et séquences ; puis lancez le CDC pour les changements en cours.
Tâche 9 : Confirmer que les séquences sont bien positionnées après le backfill
cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "SELECT pg_get_serial_sequence('orders','id') AS seq; SELECT MAX(id) FROM orders;"
seq
--------------------------
public.orders_id_seq
(1 row)
max
--------
984211
(1 row)Ce que ça signifie : Vous avez trouvé le nom de la séquence et le max courant des id dans la table.
Décision : Si la séquence est en retard, corrigez-la maintenant, avant la bascule.
cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "SELECT setval('public.orders_id_seq', (SELECT MAX(id) FROM orders));"
setval
--------
984211
(1 row)Tâche 10 : Mesurer le lag CDC (exemple avec offsets Debezium stockés localement)
cr0x@server:~$ jq -r '.source.ts_ms' /var/lib/cdc/offsets/appdb.json
1735559142000Ce que ça signifie : Ce timestamp (ms depuis l’epoch) est le temps évènement source du dernier changement de binlog traité.
Décision : Comparez avec l’heure courante ; si le lag augmente, ne basculez pas. Améliorez d’abord le débit (réseau, vitesse d’application, indexes, taille des lots).
Tâche 11 : Vérifier les comptes de lignes entre systèmes (rapide, imparfait, toujours utile)
cr0x@server:~$ mysql -h mariadb01 -u admin -p -N -e "SELECT COUNT(*) FROM appdb.orders;"
Enter password:
984211cr0x@server:~$ psql -h pg01 -U postgres -d appdb -t -c "SELECT COUNT(*) FROM orders;"
984211Ce que ça signifie : Les comptes correspondent pour cette table. Cela ne prouve pas l’égalité des données, mais ça attrape les morceaux manquants.
Décision : Si les comptes diffèrent, arrêtez et découvrez pourquoi : filtres dans le loader, lignes rejetées, CDC qui manque les deletes, ou collisions de PK.
Tâche 12 : Valider des checksums pour un échantillon (mieux que les comptes)
cr0x@server:~$ mysql -h mariadb01 -u admin -p -N -e "SELECT MD5(GROUP_CONCAT(CONCAT(id,':',status,':',total_cents) ORDER BY id SEPARATOR '|')) FROM appdb.orders WHERE id BETWEEN 900000 AND 900500;"
Enter password:
2f1c7c0a9b0e0a2f8dd7d8a11d5f4b3acr0x@server:~$ psql -h pg01 -U postgres -d appdb -t -c "SELECT md5(string_agg(id||':'||status||':'||total_cents, '|' ORDER BY id)) FROM orders WHERE id BETWEEN 900000 AND 900500;"
2f1c7c0a9b0e0a2f8dd7d8a11d5f4b3aCe que ça signifie : Pour ce segment, le contenu correspond. Cette méthode est sensible au formatage/casting ; c’est une fonctionnalité si vous craignez les conversions implicites.
Décision : Exécutez cela sur plusieurs plages et tables à gros churn. En cas de non-correspondance, investiguez les transformations, l’arrondi, les conversions de fuseau horaire et la normalisation de texte.
Tâche 13 : Détecter les requêtes lentes sur PostgreSQL immédiatement après le shadow read
cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "SELECT calls, mean_exec_time, rows, query FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5;"
calls | mean_exec_time | rows | query
-------+----------------+------+------------------------------------------------
1200 | 87.332 | 1 | SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC LIMIT $2
310 | 54.910 | 25 | SELECT * FROM products WHERE name ILIKE $1
98 | 42.774 | 1 | UPDATE inventory SET qty = qty - $1 WHERE sku = $2Ce que ça signifie : Vous regardez les motifs de requêtes, pas des incidents isolés. Le temps moyen d’exécution est votre indicateur « qu’est-ce qui s’est dégradé ? ».
Décision : Ajoutez des index, réécrivez des requêtes, ou changez la stratégie de pagination avant la bascule. Ne « regardez et voyez » pas la latence côté client.
Tâche 14 : EXPLAIN sur le pire coupable et décider index vs réécriture
cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20;"
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..152.12 rows=20 width=256) (actual time=24.112..24.145 rows=20 loops=1)
Buffers: shared hit=12040 read=210
-> Index Scan Backward using orders_created_at_idx on orders (cost=0.43..81234.55 rows=10680 width=256) (actual time=24.110..24.140 rows=20 loops=1)
Filter: (user_id = 123)
Rows Removed by Filter: 420000
Buffers: shared hit=12040 read=210
Planning Time: 0.231 ms
Execution Time: 24.201 ms
(9 rows)Ce que ça signifie : Il scanne par created_at et filtre par user_id. C’est coûteux : beaucoup de lignes supprimées par le filtre.
Décision : Créez un index composite qui correspond au prédicat + ordre, p.ex. (user_id, created_at DESC), ou réécrivez la requête pour utiliser une méthode de seek si possible.
Tâche 15 : Vérifier la santé du vacuum et les tuples morts (évite le « c’était rapide hier »)
cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "SELECT relname, n_dead_tup, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;"
relname | n_dead_tup | last_autovacuum | last_autoanalyze
-------------+------------+-------------------------------+-------------------------------
audit_log | 1983321 | 2025-12-30 09:11:02+00 | 2025-12-30 09:12:40+00
sessions | 822110 | 2025-12-30 09:08:14+00 | 2025-12-30 09:10:05+00
orders | 110422 | 2025-12-30 09:00:01+00 | 2025-12-30 09:03:33+00
products | 9221 | 2025-12-30 08:55:40+00 | 2025-12-30 08:56:10+00
inventory | 1087 | 2025-12-30 08:54:11+00 | 2025-12-30 08:54:55+00Ce que ça signifie : Beaucoup de tuples morts indiquent du churn ; le vacuum tourne mais peut ne pas suivre, surtout si des transactions sont longues.
Décision : Ajustez autovacuum par table pour les tables très actives, réduisez les transactions longues, et surveillez le bloat avant qu’il ne devienne une inondation de tickets.
Tâche 16 : Confirmer le comportement des connexions et du pooler avant la bascule
cr0x@server:~$ psql -h pg01 -U postgres -c "SELECT state, count(*) FROM pg_stat_activity GROUP BY state ORDER BY count(*) DESC;"
state | count
---------+-------
idle | 180
active | 35
(2 rows)Ce que ça signifie : Le nombre et l’état des connexions vous indiquent si vous avez besoin d’un pooler. PostgreSQL n’aime pas des milliers de connexions inactives consommant de la mémoire.
Décision : Si vous observez des pics de connexions ou beaucoup d’idles, déployez un pooler (ou corrigez le pooling côté appli) avant le jour où il faut du calme.
Blague #2 : Une bascule de base de données ressemble à un saut en parachute—découvrir que vous l’avez mal plié est une expérience mémorable.
Trois mini-histoires du monde corporate (douleur incluse)
Mini-histoire n°1 : L’incident causé par une mauvaise hypothèse
L’entreprise : SaaS de taille moyenne, multi-régions, un primaire MariaDB avec des réplicas. Ils voulaient PostgreSQL pour de meilleures fonctionnalités de requêtes et un écosystème d’extensions plus sain. L’équipe de migration a fait les bonnes grandes étapes : backfill, CDC, shadow reads. Tout semblait vert. Fenêtre de bascule réservée.
La mauvaise hypothèse était minuscule : « l’ordre des chaînes est à peu près le même ». Leur appli utilisait une pagination par curseur pour les listes de clients, triant par une colonne name et appliquant WHERE name > last_seen_name. Dans MariaDB avec la collation choisie, l’ordre et les règles de comparaison correspondaient à ce que l’interface attendait. Dans PostgreSQL, la collation et les règles locales ont produit un ordre différent pour les caractères accentués et certains cas mixtes de casse.
Après la bascule, le symptôme était subtil : les clients signalaient des « utilisateurs manquants » dans les listes. Les données n’étaient pas perdues. La pagination sautait des enregistrements parce que la logique du curseur n’était plus alignée avec l’ordre. Le support a reçu des captures d’écran ; les ingénieurs sont arrivés incrédules. C’est l’ordre habituel des opérations.
La correction n’était pas héroïque. Ils ont rendu le tri déterministe en ajoutant une clé de tri secondaire stable (identifiant unique), modifié le curseur pour inclure les deux champs, et aligné les collations quand c’était possible. Ils ont aussi ajouté un test de régression qui génère des noms Unicode aléatoires et valide la pagination entre les moteurs. C’était ennuyeux, et ça a marché.
Mini-histoire n°2 : L’optimisation qui s’est retournée contre eux
L’entreprise : plateforme e‑commerce avec de grandes tables et trafic en rafales. Ils avaient un pipeline de backfill qui chargeait dans PostgreSQL. Quelqu’un a remarqué que l’ingestion était plus lente que prévu et a décidé de l’accélérer en désactivant les index et contraintes pendant le chargement, puis en recréant le tout à la fin. Ce n’est pas automatiquement une mauvaise idée.
Le retour de bâton est venu de deux côtés. D’abord, leur pipeline CDC appliquait déjà des changements. Sans contraintes, des doublons et des lignes orphelines se sont glissés pendant la phase de chargement, parce que la logique d’application supposait que la base imposerait l’unicité et les clés étrangères. Ensuite, après la recréation des index, la construction des index a saturé l’I/O et le CPU et a affamé l’appliquer CDC, ce qui a augmenté le lag. L’augmentation du lag a accru le risque de bascule. Personne n’a bien dormi.
Ils se sont stabilisés en changeant l’ordre des opérations : charger en morceaux avec les contraintes essentielles en place (clés primaires, pas toutes les clés étrangères), maintenir une performance d’application CDC prévisible, et planifier les créations d’index avec CREATE INDEX CONCURRENTLY en lots contrôlés. Ils ont aussi implémenté un handling dead-letter dans le pipeline CDC pour les lignes violant des contraintes, afin qu’un événement mauvais n’arrête pas tout.
Morale : optimiser ne signifie pas « rendre les chiffres plus grands ». Cela signifie « réduire les modes de défaillance ».
Mini-histoire n°3 : La pratique ennuyeuse mais correcte qui a sauvé la mise
L’entreprise : B2B entreprise, beaucoup d’intégrations et obsession de la traçabilité. L’équipe a fait quelque chose peu sexy : ils ont exécuté l’intégralité du pipeline de migration dans un environnement de staging ressemblant à la production avec des volumes de données et des relectures de trafic comparables. Pas un « test unitaire ». Une répétition générale.
Pendant la répétition, ils ont découvert qu’une table possédait une colonne TEXT stockant des blobs JSON avec un encodage incohérent. MariaDB l’acceptait. Postgres refusait certaines lignes lors du cast en jsonb. Puisqu’ils étaient en répétition, ils ont eu le temps d’implémenter une transformation : stocker le texte brut dans une colonne json_raw, remplir une colonne jsonb validée quand c’était possible, et ajouter un job asynchrone pour normaliser les anciennes lignes.
Ils ont aussi pratiqué le rollback. Pas juste « on peut revenir en arrière », mais basculer vraiment l’appli vers MariaDB en staging avec connexions mises en cache, poolers et TTL DNS mesurés. Ce temps est devenu une exigence pour la runbook de bascule en production.
Le jour réel de la bascule, un changement ACL réseau a ralenti le trafic vers Postgres depuis un sous‑réseau applicatif. Parce qu’ils avaient répété, les premières étapes du runbook l’ont détecté rapidement, et la préparation au rollback a maintenu le niveau de stress bas. Ils ont corrigé l’ACL et ont poursuivi. Personne n’a écrit de postmortem intitulé « On a supposé que le réseau allait bien ». L’ennui a sauvé la mise.
Playbook de diagnostic rapide
Ceci est pour le moment juste après que vous ayez commencé les shadow reads, rampé le trafic, ou basculé—et que la latence monte. Ne discutez pas avec les graphes. Triez.
Premier point : est-ce la base, le réseau ou l’appli ?
- Vérifiez le taux d’erreurs et les timeouts côté appli : saturons‑nous les pools de connexions ou rencontrons-nous de nouvelles erreurs dues à un SQL plus strict ?
- Vérifiez le RTT réseau et la perte de paquets entre l’appli et Postgres (surtout si Postgres est dans un sous‑réseau/région différente).
- Vérifiez les états de connexion Postgres : activevs idle, waiting vs running.
Second point : trouvez le wait principal, pas la requête principale
- Cherchez des verrous : des transactions bloquées en attente de verrous à cause de jobs longs ou de DDL ?
- Cherchez la pression I/O : les lectures proviennent du disque parce que les index ne sont pas utilisés ou les caches ne sont pas chauds ?
- Cherchez la pression CPU : un index manquant transforme des filtres simples en scans ?
Troisième point : confirmez que Postgres ne se tape pas la tête contre lui‑même
- Saturation autovacuum : trop de tuples morts ou vacuum bloqué par des transactions longues.
- Orages de checkpoints : réglages WAL/checkpoint provoquant des pics périodiques de latence.
- work_mem mal dimensionné : tris et hash qui débordent sur le disque.
Commandes pratiques « diagnostic rapide »
cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "SELECT wait_event_type, wait_event, count(*) FROM pg_stat_activity WHERE wait_event IS NOT NULL GROUP BY 1,2 ORDER BY count(*) DESC;"
wait_event_type | wait_event | count
-----------------+--------------------+-------
Lock | transactionid | 12
IO | DataFileRead | 9
LWLock | WALWrite | 4
(3 rows)Ce que ça signifie : Vous voyez sur quoi les sessions attendent. Les verrous et les waits I/O sont courants durant la bascule si les index sont incorrects ou que les transactions sont longues.
Décision : Si les waits de type Lock dominent, identifiez les bloqueurs et corrigez le comportement des transactions. Si les waits I/O dominent, ajoutez des index ou ajustez les requêtes ; ne « tunez Postgres » qu’après avoir fixé les chemins d’accès.
cr0x@server:~$ psql -h pg01 -U postgres -d appdb -c "SELECT pid, age(clock_timestamp(), xact_start) AS xact_age, state, wait_event, left(query,120) AS query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_age DESC LIMIT 5;"
pid | xact_age | state | wait_event | query
------+----------+--------+------------+---------------------------------------------------------
4412 | 00:34:51 | active | | UPDATE inventory SET qty = qty - $1 WHERE sku = $2
3201 | 00:12:03 | idle in transaction | ClientRead | SELECT * FROM orders WHERE id = $1
(2 rows)Ce que ça signifie : « idle in transaction » est l’assassin silencieux du vacuum et amplifie les verrous.
Décision : Corrigez la portée des transactions côté appli ; tuez les sessions pathologiques si nécessaire. Postgres ne vous sauvera pas des transactions qui traînent.
Erreurs courantes : symptôme → cause racine → correction
1) « Postgres est plus lent » juste après la bascule
Symptôme : La latence bondit, le CPU augmente, et vous voyez des scans séquentiels sur des chemins chauds.
Cause racine : Index composites manquants et hypothèses différentes de l’optimiseur. MariaDB s’en sortait souvent avec un usage d’index différent, et vos requêtes peuvent en dépendre.
Correction : Utilisez pg_stat_statements + EXPLAIN (ANALYZE, BUFFERS). Ajoutez des index qui correspondent aux filtres + tri. Validez avec de vrais paramètres, pas des exemples simplistes.
2) Collisions de clés primaires après activation des écritures sur Postgres
Symptôme : Les inserts échouent avec duplicate key sur des tables qui utilisaient AUTO_INCREMENT.
Cause racine : Séquences pas avancées au maximum id existant après backfill/CDC.
Correction : Exécutez setval() pour chaque séquence au moins à la valeur max actuelle, puis re‑vérifiez. Automatisez cela dans le runbook de bascule.
3) Le CDC accumule un lag et ne rattrape jamais
Symptôme : Le lag augmente pendant les pics ; l’application sur Postgres n’arrive pas à suivre.
Cause racine : Côté apply, maintenance d’index lourde pendant le backfill, taille de lot insuffisante, ou limites réseau/disque.
Correction : Backfill en chunks, construire seulement les index essentiels tôt, ajouter les index secondaires plus tard. Ajuster la taille des lots CDC. Assurer suffisamment d’IOPS pour Postgres et que les réglages WAL ne brident pas l’ingestion.
4) « Ça marchait en staging » mais la production échoue au chargement
Symptôme : Le loader rejette des lignes, souvent autour des dates, précision numérique, ou UTF-8 invalide.
Cause racine : Les données de production contiennent des déchets historiques : zero dates, entiers hors plage, octets invalides.
Correction : Lancez des requêtes de qualité des données sur MariaDB tôt. Décidez explicitement des règles de transformation et implémentez‑les de façon cohérente dans le backfill et le CDC.
5) Duplication/manque lors de la pagination
Symptôme : Les utilisateurs voient deux fois le même élément entre les pages ou des éléments disparaissent.
Cause racine : Tri non déterministe, différences de collation, ou tri par colonnes non uniques.
Correction : Ajoutez un tie-breaker stable (id unique) à ORDER BY et aux tokens de curseur. Évitez la pagination offset pour les listes à fort churn.
6) Des deadlocks apparaissent soudainement sous Postgres
Symptôme : Erreurs de deadlock sur des tables très mises à jour ; les retries augmentent.
Cause racine : Ordre d’acquisition de verrous différent entre requêtes, plus de nouveaux index qui changent les chemins d’exécution.
Correction : Standardisez l’ordre des verrous dans la logique applicative. Gardez les transactions courtes. Ajoutez des retries avec jitter. Analysez les graphes de deadlock via les logs serveur.
7) L’espace disque augmente plus vite que prévu sur Postgres
Symptôme : L’utilisation du stockage augmente quotidiennement ; les performances se dégradent avec le temps.
Cause racine : Autovacuum ne suit pas, transactions longues empêchant le nettoyage, ou seuils autovacuum trop faibles sur les tables chaudes.
Correction : Ajustez autovacuum par table ; éliminez les idle-in-transaction ; envisagez le partitionnement pour les logs append-only ; surveillez le bloat et le lag de vacuum.
Checklists / plan pas-à-pas
Phase 0 : Décider des éléments non négociables
- Définir le gel d’écriture acceptable (s’il y en a un) en secondes.
- Définir la fenêtre de rollback (combien de temps MariaDB reste prêt).
- Geler la politique DDL pendant la synchronisation finale.
- Choisir la stratégie de migration : CDC-first (recommandé) ou dual-write (seulement si nécessaire).
Phase 1 : Inventaire préliminaire (là où naissent les surprises)
- Lister tables, tailles et taux de churn ; marquer les tables chaudes.
- Énumérer les types de données qui ne se mappent pas proprement : JSON-as-text, zero dates, entiers unsigned, enums, collations.
- Énumérer les requêtes critiques et leurs SLOs de latence.
- Identifier les jobs background qui exécutent des transactions longues.
Phase 2 : Construire PostgreSQL sérieusement
- Choisir la version Postgres et s’y tenir entre environnements.
- Activer
pg_stat_statementset établir une observabilité de référence. - Mettre en place rôles, pipeline de migrations et gestion du schéma.
- Décider de la stratégie de partitionnement pour les grosses tables append-only (audit logs, events).
Phase 3 : Backfill en sécurité
- Backfill en chunks par plages de PK ou fenêtres temporelles.
- Garder les PK et index essentiels disponibles pour supporter l’application CDC.
- Suivre les lignes rejetées et corriger les données en amont ou les règles de transformation.
- Enregistrer des checkpoints de backfill pour que les reruns soient déterministes.
Phase 4 : Fenêtre de synchronisation CDC
- Démarrer CDC depuis une position de binlog/GTID connue.
- Surveiller le lag et les erreurs d’application en continu.
- Arrêter la dérive de schéma : pas de DDL non coordonnés.
- Exécuter des contrôles de cohérence nocturnes : counts + checksums échantillonnés + invariants métier.
Phase 5 : Shadow reads et tuning de performance
- Diriger un petit pourcentage du trafic de lecture vers Postgres et comparer les résultats.
- Corriger les incompatibilités de requêtes et régressions de performance maintenant, pas pendant la bascule.
- Valider les fonctionnalités sensibles aux collations (recherche, tri, pagination).
- Réchauffer les caches et valider la taille des pools de connexions.
Phase 6 : Runbook de bascule (une séquence exécutable sous stress)
- Annoncer le gel des changements et confirmer la couverture on-call.
- Confirmer que le lag CDC est bas et stable ; identifier le lag courant en secondes.
- Activer un court gel des écritures (ou une limitation stricte au niveau appli).
- Attendre que le CDC atteigne zéro lag ; confirmer la dernière GTID/position capturée.
- Exécuter les contrôles de cohérence finaux sur les tables critiques.
- Basculer les écritures vers Postgres (feature flag / config / discovery service).
- Basculer les lectures vers Postgres partout.
- Surveiller les erreurs, la latence p95, les verrous, la réplication et le stockage.
- Garder MariaDB en lecture seule et disponible pour rollback pendant la fenêtre convenue.
Phase 7 : Renforcement post-basculement
- Activer une gestion d’erreurs applicative plus stricte pour les échecs de requêtes ; ne masquez pas les exceptions.
- Revoir
pg_stat_statementschaque semaine le premier mois. - Tuner autovacuum pour les tables chaudes.
- Retirer le pipeline CDC seulement après la fermeture de la fenêtre de rollback et la validation des données.
FAQ
1) Puis‑je faire MariaDB → PostgreSQL avec littéralement zéro downtime ?
Oui, mais « zéro downtime » cache généralement un problème de cohérence d’écriture. L’approche pragmatique est un court gel des écritures pour vider le lag CDC à zéro. Si vous devez l’éviter, vous aurez besoin d’écritures doubles plus réconciliation et idempotence—attendez‑vous à la complexité et aux cas limites.
2) Dois‑je utiliser CDC ou écritures doubles ?
Préférez CDC. Il simplifie l’application et concentre la complexité dans un pipeline que vous pouvez observer et redémarrer. Les écritures doubles conviennent si vous avez déjà des écritures idempotentes, de fortes sémantiques de retry, et la volonté de construire des outils de réconciliation.
3) Qu’est‑ce qui casse le plus souvent au niveau SQL ?
Casts implicites, sémantique de GROUP BY, gestion date/heure, et sensibilité à la casse des identificateurs. Le fait que PostgreSQL soit strict est une fonctionnalité ; il révèle des bugs que vous aviez déjà.
4) Comment gérer les entiers UNSIGNED de MariaDB ?
Décidez par colonne : mappez sur un type signé plus grand dans Postgres (ex. unsigned int → bigint), ou imposez des contraintes et acceptez une plage réduite. Ne mappez pas aveuglément et n’espérez pas—les débordements de plage apparaîtront plus tard comme des bugs « impossibles ».
5) Qu’en est‑il des types ENUM ?
Dans Postgres vous pouvez utiliser ENUM natif ou une table lookup avec clé étrangère. Les enums natifs vont bien si les valeurs sont stables ; les tables lookup sont préférables si les valeurs changent souvent et que vous voulez traçabilité.
6) Comment valider la correction des données sans comparer chaque ligne ?
Combinez des techniques : comptes de lignes pour la couverture, checksums échantillonnés pour le contenu, et invariants métier (ex. totaux de commande égalent la somme des lignes). Validez aussi les tranches « à risque » : lignes les plus récentes, les plus anciennes, et plages à fort churn.
7) Pourquoi mon usage disque Postgres a‑t‑il augmenté plus vite que prévu ?
MVCC signifie que les updates créent des tuples morts jusqu’à ce que le vacuum les nettoie. Si vous avez des transactions longues ou si autovacuum ne suit pas, le bloat croît. Corrigez la longueur des transactions et tunez autovacuum ; ne résolvez pas juste en ajoutant des disques.
8) Ai‑je besoin d’un pooler de connexions pour Postgres ?
Souvent oui. Les connexions PostgreSQL sont plus lourdes que ce que beaucoup d’équipes attendent, et « une connexion par thread » peut devenir coûteuse. Si vous avez beaucoup d’instances applicatives, le pooling est généralement le choix apaisant.
9) Comment planifier le rollback ?
Gardez MariaDB disponible et idéalement en lecture seule après la bascule. Pour rollback propre des écritures, vous avez besoin soit (a) d’absence d’écritures dans Postgres (rare), soit (b) d’un plan de reverse sync (difficile). En pratique, rollback signifie basculer rapidement en arrière pendant l’investigation, puis re‑basculez une fois corrigé. C’est pourquoi minimiser le temps de divergence post‑basculement est crucial.
10) pgloader suffit‑il ?
C’est un bon outil pour le schéma initial et le backfill, pas une solution complète pour une migration zéro interruption. Traitez‑le comme l’étape de copie en masse, puis utilisez CDC et une discipline stricte pour la bascule.
Conclusion : prochaines étapes exécutables
Si vous voulez une migration sans interruption et sans surprises, faites l’ingénierie ennuyeuse : mesurez tout, contraignez les changements, et répétez la bascule. Les systèmes qui survivent sont ceux pour lesquels vous pouvez répondre, en temps réel, « à quel point sommes‑nous en retard ? » et « peut‑on revenir en arrière en cinq minutes ? »
Prochaines étapes que vous pouvez lancer cette semaine :
- Inventoriez MariaDB : format du binlog, GTID, collations, mode SQL, et données sales (zero dates, UTF‑8 invalide).
- Déployez Postgres avec observabilité :
pg_stat_statements, monitoring des connexions, visibilité du vacuum. - Faites une table bout‑à‑bout : backfill, CDC, validation checksum, et test de performance des requêtes en shadow reads.
- Rédigez le runbook de bascule et répétez‑le avec relecture de trafic et exercice de rollback.
Exécutez la migration comme vous exécutez la production : changements contrôlés, preuves solides, et un plan qui ne dépend pas de l’optimisme pour fonctionner.