Migration MySQL vers Postgresql

J’ai été amené à effectuer un changement de base de données, et après des journées de recherche/modifications/tests, je suis enfin arrivé à mes fins.

Pourquoi changer de base de données?

C’est vrai que je n’avais aucun problème avec MySQL, mais j’ai découvert PostGIS, un plugin qui transforme Postgresql en base de données spatiale. Et ce plugin très puissant va s’avérer très utile dans mon projet. C’est donc la principale raison qui m’a poussée à migrer.

De plus, le projet n’étant pas encore en production, le changement devait se faire maintenant. Mais des données importantes étaient quand même présentes, et il n’était pas question de juste faire une adaptation de schéma.

Préparation du terrain

Afin d’effectuer le transfert proprement, j’ai utilisé deux machines.

La machine avec MySQL 5.1 qui tourne sous Debian 6.0

La nouvelle machine avec Postgresql 9.1 qui tourne sous Ubuntu 12.04 (Afin d’avoir des paquets plus récents qu’avec une Debian)

Installation basique de la nouvelle machine

Création de la base de données

Posgresql utilise un utilisateur nommé postgres afin d’effectuer toutes les taches d’administration. C’est le superutilisateur.

Afin de protéger cet utilisateur, il est nécessaire de rajouter un mot de passe à celui-ci.

Ensuite nous pouvons nous connecter avec cet utilisateur à l’aide de la commande suivante:

Et accéder à la base de données:

Vous voilà connecté à Postgresql en tant que superutilisateur (un # est présent dans la ligne de commande).

Pour commencer, nous allons créer un utilisateur:

Lui ajouter un mot de passe:

Et lui donner les droits de création de base de données:

Maintenant qu’un utilisateur existe, vous pouvez créer votre première base de données:

Afin de pouvoir vous connecter à la base avec votre nouvel utilisateur, il faut modifier le fichier /etc/postgresql/9.1/main/pg_hba.conf qui gère toutes les connexions à Postgresql.

Dans l’état actuel du fichier, vous êtes obligé d’avoir un utilisateur système du même nom que celui que vous venez de créer pour pouvoir vous connecter. Personnellement je préfère bien scinder les choses.

Il faut donc remplacer la valeur peer par md5 au niveau de la ligne local all all :

La première ligne local all postgres peer doit rester intacte, afin de garder une certaine sécurité. Cette ligne veut dire: « Je peux me connecter à Postgresql avec l’utilisateur postgres que si je suis connecté au système avec le compte postgres ».

Maintenant pour se connecter avec votre utilisateur il suffit de faire:

Maintenant que Postgresql est prêt, nous allons pouvoir commencer la migration.

Création d’un dump propre pour Postgresql

Quand je dis création d’un dump, je ferais mieux de dire créations de plusieurs dumps. Car c’est le plus simple pour éviter les erreurs.

Afin de créer ces dumps, j’ai utilisé un outil qui m’a BEAUCOUP aidé: py-mysql2pgsql

Installation de py-mysql2pgsql

Normalement toutes les dépendances s’installent avec easy_install, si vous rencontrez des problèmes, essayez de les installer manuellement.

Dans un premier temps, il faut générer un fichier de configuration avec la commande:

Le fichier mysql2pgsql.yml est maintenant présent dans le répertoire courant.

Ce fichier est extrêmement simple à comprendre. Il y a une partie pour se connecter à MySQL, une autre pour l’export (soit vers un fichier, soit directement vers Postgresql) et une partie de configuration avancée.

Afin de tout contrôler, je n’utiliserai que l’export vers un fichier durant toute la migration.

Migration du schéma

Certainement la partie la plus délicate de la migration, et c’est ici que py-mysql2pgsql est d’une très grande aide, puisqu’il va convertir tous les types de données qui diffèrent entre MySQL et Postgresql.

Je configure le fichier mysql2pgsql.yml afin de ne récupérer que le schéma:

A vous de remplacer les informations propre à votre connexion à MySQL.

Un fichier schema.sql a maintenant été généré, et il est nécessaire de tout vérifier afin d’éviter les incohérences. Personnellement j’ai dû modifier des types integer qui étaient devenus des bigint.

Une fois le schéma validé, vous pouvez l’ajouter à votre base:

Voila une bonne chose de faite, la structure est en place. Maintenant nous pouvons ajouter les données.

Migration des données

Pour cette partie il faut y aller étape par étape, et ça peut être long.

Je vais ici vous expliquer la méthodologie utilisée plutôt que de faire un tuto puisque chaque cas est différent.

Dans un premier temps j’ai exporté les tables « mères » une par une, de la plus importante à la moins importante. Ce que j’appelle ici table « mère », c’est une table dont vont dépendre d’autres tables via des clés étrangères.

Exemple: Une table personne et une table voiture. Une clé étrangère est présente dans la table voiture afin de savoir à qui appartient cette voiture. La table personne est donc une table mère par rapport à la table voiture. Je dois donc ajouter les données de la table personne avant d’ajouter celles de la table voiture afin d’éviter des erreurs de contrainte. (J’espère être clair sur cette partie, je n’ai peut-être pas le bon vocabulaire).

Pour cela, il faut modifier le fichier mysql2pgsql.yml pour chaque table:

Ensuite, vous pouvez ajouter les données de la table directement dans Postgresql:

Si des erreurs apparaissent, vérifiez les données dans votre base MySQL. Il peut exister des incohérences qui troublent l’import. Cela a été le cas pour moi, une dizaine de lignes sur plus d’un million de lignes. Dans ce cas il faut corriger l’erreur et recommencer l’export avec py-mysql2pgsql jusqu’à temps qu’il n’y ait plus d’erreur.

Une fois que toutes les tables « mères » sont importés dans Postgresql, vous pouvez ajouter les tables restantes en une fois, en utilisant la condition exclude_tables dans le fichier mysql2pgsql.yml, pour exclure les tables déjà ajoutées.

Une fois que tout cela est terminé, la migration commence à se terminer.

Après les imports

Et oui, ce n’est pas encore terminé ! Un certain nombre de choses sont maintenant à vérifier, comme la création/modification de certains index, l’ajout des triggers, de fonctions, etc…

De plus il faut modifier un certain nombre de choses dans le code votre projet, car MySQL et Postgresql n’utilisent pas les mêmes fonctions.

Modifications pour CodeIgniter

Les modifications sont ici relativement simples, et doivent être adaptables pour d’autres frameworks.

Dans un premier temps il faut installer l’extension Postgresql pour PHP:

Et enfin, modifier le fichier /application/config/database.php:

Rien de plus n’est nécessaire pour connecter votre projet à Postgresql.

Sauvegarde de la nouvelle base

Maintenant que tout est propre au niveau de la base Postgresql, n’oubliez pas de faire un dump:

 

Laisser un commentaire