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
sudo aptitude install postgresql-9.1 postgresql-contrib-9.1
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.
sudo passwd postgres
Ensuite nous pouvons nous connecter avec cet utilisateur à l’aide de la commande suivante:
sudo -i -u postgres
Et accéder à la base de données:
psql
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:
CREATE USER <nom_utilisateur> ;
Lui ajouter un mot de passe:
ALTER USER <nom_utilisateur> WITH ENCRYPTED PASSWORD 'motdepasse';
Et lui donner les droits de création de base de données:
ALTER ROLE <nom_utilisateur> WITH CREATEDB;
Maintenant qu’un utilisateur existe, vous pouvez créer votre première base de données:
CREATE DATABASE <nom_database> OWNER <nom_utilisateur>;
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 :
local all all md5
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:
psql -U <nom_utilisateur> <nom_database>
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
easy_install 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:
py-mysql2pgsql
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:
# if a socket is specified we will use that # if tcp is chosen you can use compression mysql: hostname: monserveurmysql port: 3306 socket: username: utilisateur password: motdepasse database: nom_database compress: false destination: # if file is given, output goes to file, else postgres file: schema.sql postgres: hostname: port: username: password: database: # if tables is given, only the listed tables will be converted. leave empty to convert all tables. #only_tables: #- table2 # if exclude_tables is given, exclude the listed tables from the conversion. # exclude_tables: # - table1 # if supress_data is true, only the schema definition will be exported/migrated, and not the data supress_data: true # if supress_ddl is true, only the data will be exported/imported, and not the schema supress_ddl: false # if force_truncate is true, forces a table truncate before table loading force_truncate: false
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:
psql -U nom_utilisateur nom_database < schema.sql
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:
# if a socket is specified we will use that # if tcp is chosen you can use compression mysql: hostname: monserveurmysql port: 3306 socket: username: utilisateur password: motdepasse database: nom_database compress: false destination: # if file is given, output goes to file, else postgres file: personne.sql postgres: hostname: port: username: password: database: # if tables is given, only the listed tables will be converted. leave empty to convert all tables. only_tables: - personne # if exclude_tables is given, exclude the listed tables from the conversion. # exclude_tables: # - table1 # if supress_data is true, only the schema definition will be exported/migrated, and not the data supress_data: false # if supress_ddl is true, only the data will be exported/imported, and not the schema supress_ddl: true # if force_truncate is true, forces a table truncate before table loading force_truncate: false
Ensuite, vous pouvez ajouter les données de la table directement dans Postgresql:
psql -U nom_utilisateur nom_database < personne.sql
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:
sudo aptitude install php5-pgsql
Et enfin, modifier le fichier /application/config/database.php:
$db['default']['hostname'] = 'localhost'; $db['default']['username'] = 'user'; $db['default']['password'] = 'motdepasse'; $db['default']['database'] = 'database'; $db['default']['dbdriver'] = 'postgre';
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:
pg_dump nom_database > dump.sql
