🐘 pgloader : migrer une base MySQL vers PostgreSQL sans y passer le week-end

📚 Introduction

La migration MySQL → PostgreSQL revient régulièrement : une application vieillissante, un besoin de types plus riches, un hébergeur qui pousse Postgres, ou simplement l’envie d’arrêter de contourner les bizarreries de MySQL. Et à chaque fois, la même tentation : mysqldump, quelques sed bien sentis, et on verra bien.

On ne verra rien de bon. Le dialecte SQL de mysqldump n’est pas celui de PostgreSQL : les backticks, AUTO_INCREMENT, ENGINE=InnoDB, tinyint(1) en guise de booléen, les dates 0000-00-00, les ENUM déclarés inline… Chacun de ces points demande une transformation, et il y en a une bonne trentaine. Trois jours de bricolage plus tard, on a une base à moitié convertie et zéro confiance dedans.

pgloader règle le problème d’un coup. Il se connecte à MySQL, lit le catalogue, en déduit un schéma PostgreSQL équivalent, streame les données via COPY, puis reconstruit index et clés étrangères en parallèle. Le projet est distribué sous la licence PostgreSQL et existe depuis assez longtemps pour avoir croisé toutes les horreurs que votre base contient.

🔎 Qu’est-ce que pgloader, et pourquoi pas un simple dump ?

pgloader est un chargeur de données pour PostgreSQL bâti autour de COPY. Son avantage sur un \copy brut, c’est son comportement transactionnel : là où PostgreSQL annule tout le chargement d’une table dès la première ligne invalide, pgloader met les lignes fautives de côté dans une paire de fichiers reject.dat / reject.log et continue à charger les bonnes.

Sur une migration de base, cette différence est décisive. Une table de 12 millions de lignes avec trois dates pourries ne fait pas échouer la migration : elle produit un rapport de trois lignes à traiter à la main. Le reste du travail — conversion des types, création des index, réinitialisation des séquences — est fait par l’outil, pas par vous.

🚀 Installer et lancer une première migration

Deux lignées coexistent aujourd’hui. La v3, écrite en Common Lisp, est la dernière branche stable (3.6.10) et celle que reprennent les gestionnaires de paquets. Sur Debian et Ubuntu :

apt-get install pgloader

Sur macOS, la formule Homebrew installe la même lignée :

brew install pgloader

La v4 est une réécriture complète en Clojure, distribuée sous forme d’un JAR auto-contenu qui ne demande qu’un Java 21 ou supérieur. Elle se veut un remplaçant direct de la v3 : même syntaxe de fichier .load, mêmes options en ligne de commande.

curl -L -o pgloader.jar \
  https://github.com/dimitri/pgloader/releases/download/v4-dev/pgloader.jar
java -jar pgloader.jar --version

Si vous préférez ne rien installer, l’image Docker est construite par la CI du projet :

docker pull ghcr.io/dimitri/pgloader:latest
docker run --rm -it ghcr.io/dimitri/pgloader:latest pgloader --version

Et voilà la migration complète, dans sa forme la plus courte :

createdb pagila
pgloader mysql://root@localhost/sakila postgresql:///pagila

C’est tout. Tables, types, index, clés étrangères, commentaires et données : pgloader fait le tour. Avant de lancer pour de vrai, --dry-run vérifie les deux connexions sans rien charger — ça évite de découvrir un mot de passe erroné après vingt minutes de COPY.

🧱 Le fichier .load, là où tout se joue

La ligne de commande suffit pour un essai. Dès que la base a une histoire, on passe à un fichier de commandes versionné dans le dépôt :

LOAD DATABASE
     FROM      mysql://root:pass@db:3306/sakila?useSSL=false
     INTO postgresql://pguser@pghost/pagila
 
 WITH include drop, create tables, create indexes, reset sequences,
      workers = 8, concurrency = 1,
      multiple readers per thread, rows per range = 50000
 
  SET PostgreSQL PARAMETERS
      maintenance_work_mem to '128MB',
      work_mem to '12MB'
 
  SET MySQL PARAMETERS
      net_read_timeout  = '120',
      net_write_timeout = '120'
 
 CAST type tinyint when (= precision 1) to boolean drop typemod,
      type varchar to varchar keep typemod,
      type year to integer
 
 EXCLUDING TABLE NAMES MATCHING ~/^tmp_/, 'sessions'
 
 BEFORE LOAD DO
   $$ create schema if not exists pagila; $$;

Quelques repères sur la clause WITH, dont le défaut pour MySQL est no truncate, create tables, include drop, create indexes, reset sequences, foreign keys, downcase identifiers, uniquify index names :

• include drop supprime côté PostgreSQL les tables portant le même nom que celles de MySQL, en CASCADE. C’est ce qui rend la commande rejouable en boucle pendant la mise au point — et c’est aussi le meilleur moyen de vider une base par erreur.
• reset sequences recale chaque séquence sur le max() de sa colonne après le chargement. Sans ça, votre premier INSERT applicatif tombe sur un doublon de clé primaire.
• workers et concurrency pilotent le parallélisme ; rows per range découpe les grosses tables en tranches lues en parallèle.

Côté v4, les URI acceptent aussi la forme JDBC (jdbc:mysql://…) et les paramètres du driver Connector/J passent tels quels : useSSL=false et allowPublicKeyRetrieval=true règlent 90 % des refus de connexion sur MySQL 8 en local.

🔍 Les règles de cast, le vrai sujet

C’est là que pgloader gagne ses trois jours. Un jeu de règles par défaut couvre déjà l’essentiel des pièges MySQL :

• tinyint(1) devient boolean (fonction tinyint-to-boolean), et bit(1) aussi ;
• les entiers unsigned sont promus au type supérieur — int unsigned devient bigint, tinyint unsigned devient smallint ;
• les colonnes auto_increment deviennent serial ou bigserial selon la précision ;
• datetime et timestamp deviennent timestamptz, et les valeurs 0000-00-00 00:00:00 sont converties en NULL par zero-dates-to-null — PostgreSQL n’a pas d’année zéro ;
• year devient integer, les types blob deviennent bytea, et les colonnes texte passent par remove-null-characters (le fameux \0 que Postgres refuse dans un text) ;
• chaque colonne ENUM produit un CREATE TYPE dédié, nommé d’après la table et la colonne, avec les mêmes labels dans le même ordre.

Le point à surveiller : beaucoup de règles par défaut appliquent drop typemod, donc un varchar(40) MySQL arrive en varchar sans longueur. Si vous tenez à vos contraintes de longueur, il faut l’écrire :

CAST type varchar to varchar keep typemod,
     type char    to char    keep typemod

Les gardes permettent d’affiner : when unsigned, when (= precision 1), when default '0', with extra auto_increment. Et quand une seule table utilise tinyint comme booléen alors que les autres s’en servent comme entier, on cible la colonne plutôt que le type : CAST column commande.paye to boolean.

🎯 Filtrer, renommer, réorganiser

Une migration est rarement un copier-coller. pgloader fait la réorganisation au passage, sur sa représentation en mémoire du schéma — aucun ALTER TABLE n’est envoyé à PostgreSQL :

 INCLUDING ONLY TABLE NAMES MATCHING ~/^facture/, 'client'
 EXCLUDING TABLE NAMES MATCHING ~/^cache_/
 DECODING TABLE NAMES MATCHING ~/legacy/ AS utf8
 
 MATERIALIZE VIEWS film_list, staff_list
 
 ALTER TABLE NAMES MATCHING ~/_list$/ SET SCHEMA 'mv'
 ALTER TABLE NAMES MATCHING 'film' RENAME TO 'films'
 ALTER SCHEMA 'sakila' RENAME TO 'pagila'

MATERIALIZE VIEWS mérite une mention : les vues MySQL ne sont pas migrées, mais cette clause importe le résultat d’une vue comme une table ordinaire. C’est la porte de sortie quand une vue sert de couche de compatibilité, et c’est aussi un moyen élégant de transformer les données au vol : on écrit une requête MySQL qui produit la forme voulue, et pgloader charge son résultat.

DECODING … AS utf8 répond au grand classique de la table latin1 qui contient en réalité de l’UTF-8 — ou l’inverse. On peut cumuler autant de règles que de tables mal encodées, chacune avec son encodage.

⚠️ Quelques précautions

Les vues et les triggers ne sont pas migrés. La documentation est explicite : porter les vues supposerait un parseur complet du dialecte MySQL et un moteur de réécriture. Les triggers non plus. Prévoyez de les réécrire à la main, et de les tester : c’est souvent là que se cache la logique métier qu’on avait oubliée.

downcase identifiers est actif par défaut. Vos tables CommandeClient deviennent commandeclient. C’est le bon comportement dans un monde PostgreSQL — mais si votre ORM génère des requêtes avec les noms d’origine entre guillemets, ça casse. Décidez tôt entre downcase identifiers et quote identifiers, et adaptez le code applicatif en conséquence.

include drop fait un DROP … CASCADE. La cascade touche tous les objets qui référencent les tables ciblées, y compris ceux qui ne viennent pas de MySQL. Sur une base cible partagée, préférez include no drop et recréez une base vierge à chaque essai.

Lisez le résumé de fin de course. pgloader affiche un tableau par table avec le nombre de lignes lues, importées et rejetées. Une colonne « rejetées » non nulle renvoie vers reject.dat et reject.log, écrits sous /tmp/pgloader par défaut — répertoire réglable avec --root-dir. Ne considérez jamais une migration terminée sans avoir regardé ce tableau. En phase de mise au point, --on-error-stop s’arrête à la première erreur au lieu de continuer.

Répétez la migration, ne l’improvisez pas. L’intérêt d’un fichier .load versionné, c’est de pouvoir le rejouer à l’identique — sur un dump de production, dans un job de CI, la veille de la bascule. Attention en revanche à ce que pgloader n’est pas : il rejoue une migration complète, il ne fait pas de réplication continue. Pour une bascule à froid, comptez la fenêtre de coupure sur la durée du dernier run.

🎉 Conclusion

pgloader transforme une migration MySQL → PostgreSQL de projet à part entière en tâche d’après-midi. La première commande donne un résultat exploitable en quelques minutes ; le reste du travail consiste à écrire un fichier .load de trente lignes qui encode vos décisions — les tinyint qui sont vraiment des booléens, les tables à laisser derrière, les schémas à réorganiser.

Mon conseil : commencez par la commande à une ligne sur une copie de la base, regardez le résumé, et n’ajoutez des règles de CAST que pour ce qui vous a déplu. Les défauts sont bons, et chaque règle écrite est une règle à maintenir.

Sur le choix de version : la v3 packagée par Debian et Homebrew reste la valeur sûre en production. La v4 en Clojure est autrement plus confortable à déployer — un JAR, un -Xmx pour la mémoire, et fini les problèmes de heap SBCL sur les grosses bases — mais elle est encore diffusée comme pré-version. Testez-la sur votre base réelle avant de l’inscrire dans le plan de bascule.

🔗 Liens utiles

• Site officiel de pgloader
• Documentation pgloader
• Référence MySQL vers PostgreSQL : clauses, casts et transformations
• Options de la ligne de commande pgloader
• Dépôt GitHub dimitri/pgloader
• Formule Homebrew pgloader
• Propriétés de connexion MySQL Connector/J