Horoa – La voie est libreLa voie est libre

Alfresco: Migrer de MySQL vers PostgreSQL

Alfresco est un ECM opensource très complet. Il utilise pricipalement des briques elles aussi opensources, comme un serveur d’application (tomcat par défaut), une suite bureautique (openoffice ou libreoffice), des outils de manipulation d’image (imagemagick), et bien sur un SGBD! C’est cette dernière partie qui nous interresse ici. Après avoir fournit par défaut mysql, l’installeur Alfresco embarque aujourd’hui postgreSQL par défaut. On pourrait discuter les avantages de l’un ou de l’autre, et certainement qu’aucun consensus n’emmergerait…
Quoiqu’il en soit, j’ai eu l’occasion de voir des cas de figure où des DBAs souhaitent se débarrasser de leur base MySQL pour passer sous postgres.
Ce tuto explique comment réaliser cette opération pour Alfresco. Cette procédure fonctionne (pour autant que j’ai pu la tester) avec Alfresco 3.4.x et 4.x. elle ne fonctionne pas tel qu’elle avec des version plus anciennes que 3.4!

Le principe ici est relativement simple:
Le schéma de base de données est relativement complexe et entièrement géré par hibernate/ibatis… difficile de d’y comprendre quelque chose au premier abord! Afin de minimiser les sources d’erreur, on va laisser Alfresco créer la nouvelle base, puis ensuite y insérer des données brutes… Bien entendu, quelques traitements seront nécessaires mais rien de fondamentalement tordu.

  1. Prérequis:
    • On suppose ici que les versions source et destination sont identiques: Si une migration de version est prévue, elle peut être faite dans un second temps. Ceci est purement une précaution car je n’ai pas testé la procédure lors d’un changement de version…mais ca pourrait bien marcher!
    • On suppose aussi que les collations et jeux de caractères (charset) utilisés sont similaires. Par défaut postgres et mysql devraient utiliser UTF-8 et utf8_bin.
    • Il est nécessaire de prévoir un downtime lors de l’upgrade afin d’assurer la cohérence des données des instances source et destination.

     

  2. Une base toute belle, toute propre:
     
    Pour commencer on va déployer une nouvelle instance d’Alfresco, instance complètement vierge, utilisant PostgreSQL comme SGDB. On ne va pas détailler cette procédure étant donné que si vous penser à changer de SGBD, je suppose que vous maitriser l’installation d’Alfresco et ses différents composants. En résumé, installez Tomcat, créez une base Postgres et son user associé, posez les war Alfresco, dans webapps, et configurer votre fichier alfresco-global.properties afin de pointer sur la base vide nouvellement créée.
    Lorsque Tomcat va déployer Alfresco, celui-ci va trouver une base de donnée vide. Il va donc la structurer comme il se doit (si quelqu’un sait faire ça, c’est bien Alfresco lui même!), et la remplir des données nécessaires au démarrage du serveur.
    Une fois le démarrage terminé, Alfresco a fait sa part du travail, à nous de jouer donc! Nous allons utiliser cette base nouvellement créée comme un modèle pour être sur d’avoir une structure tel qu’attendu par Alfresco. Pour cela on dump la structure de la base (les données ne nous intéressent pas ici).
     
    ~$ pg_dump -h gimly -U alfresco -W -s alfresco > /tmp/pg_schema.sql
     
    Afin d’éviter des problèmes lors de la restauration des données, nous allons séparer ce fichier en deux. D’un coté on créé un fichier contenant la définition des tables (/tmp/pg_schema_table.sql), et de l’autre un second fichier qui contient les contraintes et les index (/tmp/pg_schema_fk-idx.sql). On garde ces fichiers précieusement pour plus tard, quant au reste de l’instance on peut le supprimer.
     
  3. Sauvegarde des données de prod:
     
    On va maintenant éteindre l’instance Alfresco source (celle tournant sur MySQL), afin de procéder à un backup consistant de la base de données et des autres données.
    Pour faire la sauvegarde du content store et des indexes, un outil comme tar fera tout à fait l’affaire. Dans notre cas, pour la sauvegarde de la base, ou va utiliser mysqldump et lui spécifier de ne prendre que les données en ignorant toutes les statements des création de la structure. En effet, pour la structure, on s’appuiera sur la base créée précédemment durant le déploiement d’Alfresco.
     
    ~$ mysqldump -u root -p -n -c --hex-blob --default-character-set=utf8 --compact --compatible=postgresql -t alfresco | \
      sed -r -e 's/0x00([),])/false\1/g' \
        -e 's/0x01([),])/true\1/g' \
        -e "s/([(,])0x([0-9a-fA-F]*)([),])/\1decode('\2','hex')\3/g" \
        -e 's/INSERT INTO "([a-zA-Z_-]+)" \((.*)\) VALUES \(/INSERT INTO "\1" (\L\2) VALUES (/g' \
      > /tmp/data.sql

     
    Comme vous pouvez le voir ci dessus, on procède en même temps à une série de traitement sur les requêtes produites. En voici en détails, l’explication de chaque option/traitement:
     
    -n : spécifie à mysqldump de ne pas inclure les requêtes de création de table.
     
    -c : spécifie à mysqldump d’inclure les nom des tables dans les statements d’INSERT. Ceci évite des problèmes dues à l’ordonnancement des colonnes dans une table.
     
    –compatible=postgresql : spécifie à mysqldump de produire une sortie plus PostgreSQL friendly (malheureusement ça n’est pas suffisant pour permettre à postgres de traiter le fichier).
     
    –hex-blob : spécifie à mysqldump de traiter les champ de type binaire (blolb ou booléen) comme des chaines hexa-décimales. Cela permet un traitement plus simple via sed.
     
    –default-character-set=utf8 : spécifie le jeu de caractère par défaut comme étant UTF-8.
     
    –compact: spécifie à mysqldump de zapper différents statement inutiles ici.
     
    s/0x00([),])/false\1/g : on map la valeure hexadecimale 0x00 inconnue de Postgres vers false (typiquement requis pour les champs booléens).
     
    s/0x01([),])/true\1/g : on map la valeure hexadecimale 0x01 inconnue de Postgres vers true (typiquement requis pour les champs booléens).
     
    s/([(,])0x([0-9a-fA-F]*)([),])/\1decode(‘\2′,’hex’)\3/g : on insert la fonction decode() de Postgres afin de re-créer les champs binaires correctement.
     
    s/INSERT INTO « ([a-zA-Z_-]+) » \((.*)\) VALUES \( : On utilise sed afin de passer les nom de table et de colonnes en lower case.Le fichier ainsi obtenu et presque prêt, il ne reste plus qu’à y ajouter les lignes ci dessus au tout début:
     
    SET statement_timeout = 0;
    SET client_encoding = 'UTF8';
    SET standard_conforming_strings = off;
    SET check_function_bodies = false;
    SET client_min_messages = warning;
    SET escape_string_warning = off;

     
  4. Restaurons les données sur PostgreSQL:
     
    Afin de restaurer les données sur notre nouveau SGDB, on va tout d’abord supprimer la base postgres que nous avons créé à la première étape, puis la recréer vide. Celle ci ne nous a servi qu’à avoir un dump postgres de la structure de la base.
     
    ~$ dropdb alfresco
    ~$ createdb -Oalfresco alfresco

     
    On restaure la structure seule de la base, sans les index ni les contraintes:
     
    ~$ psql -h gimly -U alfresco -W alfresco < /tmp/pg_schema_table.sql
     
    Puis on insert les données:
     
    ~$ psql -h gimly -U alfresco -W alfresco < /tmp/data.sql
     
    Et ensuite on recréé les index et les contraintes:
     
    ~$ psql -h gimly -U alfresco -W alfresco < /tmp/pg_schema3411_fkIdx.sql
     
    A ce stade, la base de données est quasiment prête pour me démarrage d’alfresco. Le dernier problème qu’il nous reste a traiter est la gestion des séquence. En effet Postgres, comme la plupart des RDBMS, utilise des séquences pour choisir automatiquement les valeurs d’entier à venir. MySQL, quant à lui, ne possèdent pas de séquence mais utilise à la place une option pour les champs de type entiers: AUTO_INCREMENT. Là encore on pourrait disserter sur le bien fondé et les limitations de chaque approche (sans parlé du respect des standards SQL)…
    Toujours est il qu’il est nécessaire de mettre à jour les séquences postgreSQL afin de ne pas créer de problème lors de l’utilisation d’Alfresco.
    Pour cela on va extraire de MySQL, les valeurs des champs impliqués, et les stocker dans un fichier:
     
    mysql> use information_schema
    mysql> SELECT TABLE_NAME,AUTO_INCREMENT
      FROM TABLES
      WHERE TABLE_SCHEMA='alfresco'
        AND TABLE_NAME NOT LIKE 'jbpm_%'
      INTO OUTFILE '/tmp/AI.sql';

     
    Puis on traite ce fichier afin de mettre à jour les séquences PostgreSQL:
     
    grep -v '\N$' /tmp/AI.sql | \
      awk '{print "ALTER SEQUENCE " $1 "_seq RESTART WITH " $2 ";"}' | \
      psql -h gimly -U alfresco -W alfresco

     
    Enfin il reste une séquence à mettre à jour. Cette dernière est un peu différentes car elle est partagée entre plusieurs tables (voir ce le JIRA Alfresco MNT-7445 pour plus de détails). Afin de trouver la valeur à la quelle on doit la mettre à jour on utilise la commande MySQL suivante:
     
    mysql> SELECT max(AUTO_INCREMENT)
      FROM TABLES
      WHERE TABLE_SCHEMA='alfresco'
        AND TABLE_NAME LIKE 'JBPM%';

     
    On récupère la valeur la plus élevée du résultat et on l’utilise dans la requête PostgreSQL suivante:
     
    ALTER SEQUENCE hibernate_sequence RESTART WITH <VALUE>
     
  5. Configuration d’Alfresco:
     
    Dernière étape avant de démarrer Alfresco, il faut mettre à jour la configuration afin d’utiliser Postgres et non MySQL. On réalise ceci dans le fichier alfresco-global.properties (consultez la doc officielle):
     
    db.driver=org.postgresql.Driver
    db.url=jdbc:postgresql://localhost:5432/${db.name}
    db.name=alfresco
    db.username=alfresco
    db.password=donttellanybody

     
    Assurez d’avoir les jar nécessaires au driver postgres dans votre installation tomcat, et redémarrer Alfresco.

Notez bien que cette procédure (que j’ai testée avec Alfresco entreprise 3.4.11 suivi une upgrade vers 4.2) n’est pas officiellement supportée. Donc si vous avez contracté du support auprès d’Alfresco, il sera toujours préférable de les contacter avant de vous lancer dans ce type de migration. De même si vous souhaitez faire ce genre de choses sur votre environnement de production, veiller à en tester tous les aspects. Pour ma par je n’ai pas pu tout tester!

14 comments

  • I appreciate you sharing this article.Thanks Again. Really Cool.

  • Bonjour,

    Comment séparer ce fichier pg_schema.sql en 2 fichiers (pg_schema_table.sql & pg_schema_fk-idx.sql)?

    Merci d’avance,

    • si je me souviens bien, le fichier de dump créé contient les indexes à la fin. Donc normalement, il chercher dans le fichier la première occurence de ‘CREATE INDEX’ ou de ‘ALTER TABLE blablabla ADD CONSTRAINT….’). Le debut du fichier devrait etre pg_schema_table.sql et la fin pg_schema_fk-idx.sql

  • guillaume vola

    Bonjour,

    Merci pour la procédure qui est claire et qui fonctionne à 99%.
    Lors de l’import des données j’ai des erreurs dans certaines tables ACT. (workflow)
    ERREUR: la colonne « has_start_form_key_ » est de type boolean mais l’expression est de type integer
    LIGNE 1 : …497-4b49-466c-8774-d3f1e3537f01activitiAdhoc.png’,1,1,’1′,NU…
    Je dois donc reprendre mon fichier pour rajouter des ‘1’ au lieu de 1 à certaines valeurs.
    Avec l’ordre des colonnes qui change c’est assez fastidieux.
    Avez vous rencontrer ce type de problème ?
    Je précise que je migre de mysql à postgres sur une version 4.1.8 d’ Alfresco.
    Cordialement

    • Non je n’ai pas eu ce problème sur la 3.4 que j’ai testé. Cependant, si le champ était un booléen en mysql, le cas devrait être géré par l’option  » –hex-blob » et les sed suivants. Avez vous bien utilisé cette option pour l’export? Le champs source en mysql est il bien un booléen?

  • guillaume vola

    Bonjour

    Voici le script que j’utilise pour transformer le dump mysql.

    #!/bin/sh

    INPUT_FILE=$1

    sed « /./!d » $INPUT_FILE | sed « /^USE*/d » > dumppostgresql1.sql
    sed « s/0x00/false/g » dumppostgresql1.sql | sed « s/0x01/true/g » | sed « s/,0x\([0-9A-F]*\)/,decode(‘\1′,’hex’)/g » > dumppostgresql2.sql
    sed « s/\\\’/\’\’/g » dumppostgresql2.sql > dumppostgresql_tmp.sql
    sed « s/,\([0-1]\),/,’\1′,/g » dumppostgresql_tmp.sql > dumppostgresql_tmp2.sql
    sed « s/,\([0-1]\),/,’\1′,/g » dumppostgresql_tmp2.sql > dumppostgresql_tmp.sql
    sed « s/,\([0-1]\),/,’\1′,/g » dumppostgresql_tmp.sql > dumppostgresql_tmp2.sql
    sed « s/,\([0-1]\),/,’\1′,/g » dumppostgresql_tmp2.sql > dumppostgresql_final.sql
    rm -f dumppostgresql1.sql dumppostgresql2.sql dumppostgresql_tmp.sql dumppostgresql_tmp2.sql

    • Ce script ne correspond pas à ce que j’ai publié il me semble… les sed semblent un peu approximatifs et n’utilisent pas les regex étendues. Quel est le problème avec la procédure fournie sur le site? Il est important que le dump SL sur lequel vous travaillez soit fait avec les options présentes dans l’article.

  • guillaume vola

    Il me reste des erreurs à cause des noms de colonnes en majuscule.

    psql:dumppostgresql_final.sql:1: ERREUR: la colonne « ID_ » de la relation « act_ge_bytearray » n’existe pas
    LIGNE 1 : INSERT INTO « act_ge_bytearray » (« ID_ », « REV_ », « NAME_ », « DEP…
    ^
    psql:dumppostgresql_final.sql:2: ERREUR: la colonne « NAME_ » de la relation « act_ge_property » n’existe pas
    LIGNE 1 : INSERT INTO « act_ge_property » (« NAME_ », « VALUE_ », « REV_ ») VA…
    ^
    psql:dumppostgresql_final.sql:3: ERREUR: la colonne « ID_ » de la relation « act_hi_actinst » n’existe pas
    LIGNE 1 : INSERT INTO « act_hi_actinst » (« ID_ », « PROC_DEF_ID_ », « PROC_I…
    ^
    psql:dumppostgresql_final.sql:4: ERREUR: la colonne « ID_ » de la relation « act_hi_comment » n’existe pas
    LIGNE 1 : INSERT INTO « act_hi_comment » (« ID_ », « TYPE_ », « TIME_ », « USER…
    ^
    psql:dumppostgresql_final.sql:5: ERREUR: la colonne « ID_ » de la relation « act_hi_detail » n’existe pas
    LIGNE 1 : INSERT INTO « act_hi_detail » (« ID_ », « TYPE_ », « PROC_INST_ID_ »…
    ^
    psql:dumppostgresql_final.sql:6: ERREUR: la colonne « ID_ » de la relation « act_hi_identitylink » n’existe pas
    LIGNE 1 : INSERT INTO « act_hi_identitylink » (« ID_ », « GROUP_ID_ », « TYPE…
    ^
    psql:dumppostgresql_final.sql:7: ERREUR: la colonne « ID_ » de la relation « act_hi_procinst » n’existe pas
    LIGNE 1 : INSERT INTO « act_hi_procinst » (« ID_ », « PROC_INST_ID_ », « BUSI…
    ^
    psql:dumppostgresql_final.sql:8: ERREUR: la colonne « ID_ » de la relation « act_hi_taskinst » n’existe pas
    LIGNE 1 : INSERT INTO « act_hi_taskinst » (« ID_ », « PROC_DEF_ID_ », « TASK_…
    ^
    psql:dumppostgresql_final.sql:9: ERREUR: la colonne « ID_ » de la relation « act_hi_varinst » n’existe pas
    LIGNE 1 : INSERT INTO « act_hi_varinst » (« ID_ », « PROC_INST_ID_ », « EXECU…
    ^
    psql:dumppostgresql_final.sql:10: ERREUR: la colonne « ID_ » de la relation « act_re_deployment » n’existe pas
    LIGNE 1 : INSERT INTO « act_re_deployment » (« ID_ », « NAME_ », « DEPLOY_TIM…
    ^
    psql:dumppostgresql_final.sql:11: ERREUR: la colonne « ID_ » de la relation « act_re_procdef » n’existe pas
    LIGNE 1 : INSERT INTO « act_re_procdef » (« ID_ », « CATEGORY_ », « NAME_ », « …
    ^
    psql:dumppostgresql_final.sql:12: ERREUR: la colonne « ID_ » de la relation « act_ru_execution » n’existe pas
    LIGNE 1 : INSERT INTO « act_ru_execution » (« ID_ », « REV_ », « PROC_INST_ID…
    ^
    psql:dumppostgresql_final.sql:13: ERREUR: la colonne « ID_ » de la relation « act_ru_identitylink » n’existe pas
    LIGNE 1 : INSERT INTO « act_ru_identitylink » (« ID_ », « REV_ », « GROUP_ID_…
    ^
    psql:dumppostgresql_final.sql:14: ERREUR: la colonne « ID_ » de la relation « act_ru_task » n’existe pas
    LIGNE 1 : INSERT INTO « act_ru_task » (« ID_ », « REV_ », « EXECUTION_ID_ », « …
    ^
    psql:dumppostgresql_final.sql:15: ERREUR: la colonne « ID_ » de la relation « act_ru_variable » n’existe pas
    LIGNE 1 : INSERT INTO « act_ru_variable » (« ID_ », « REV_ », « TYPE_ », « NAME…

    • Ceci devrait être géré par la commande sed -e 's/INSERT INTO "([a-zA-Z_-]+)" \((.*)\) VALUES \(/INSERT INTO "\1" (\L\2) VALUES (/g' \ présente dans l’article. Encore une fois, merci de suivre la procédure.

  • I am trying to achieve a migration from mysql to postgresql on Alfresco Community 4.0.d prior to proceding with an upgrade to Alfresco One 5.x. I hop you can help me to resolve this difficulty. Ubuntu Server 14.04 LTS in a VirtualBox so we can’t do any damage to the production server!

    I’ve installed the fresh postgresql enabled copy, started-up and shut down alfresco server. I have postgresql running per your instructions. Here’s the output from my attempt get the schema out following your instruction above:

    pg_dump -h 127.0.0.1 -p 5432 -U alfresco -W -s alfresco > schemadump.sql
    Password:
    pg_dump: column number -1 is out of range 0..21
    pg_dump: column number -1 is out of range 0..21
    pg_dump: cannot duplicate null pointer

    All help would be greatly, greatly appreciated.

    • Hi Ray,

      From what I can see this is a postgresql bug. Which version of potgresql are you using?
      This bug seems to be related with your issue:
      http://www.postgresql.org/message-id/20140305120520.1918.871@wrigleys.postgresql.org
      Please also note I never tested this procdure with alfresco 5. It may work ootb or need some tweaks.

      • Hi,

        Many thanks for your response. I’ve been on holiday – hence the delay…

        I’m only going to attempt the upgrade to Alfresco 5 after I’ve successfully migrated from mysql to postgres within the existing Alfresco 4.0.d version. So my version of postgres is whichever comes rolled in with Alfresco 4.0.d default wizard installation: version 9.0.2 if they matched their pre-requisite notes here https://wiki.alfresco.com/wiki/Alfresco_Community_4.0.d_Release_Notes

        I was sticking to your instructions as you seem to have tested with 3.x and 4.x versions of Alfresco, so I was really surpised to hit this problem straight away.

        Thansk for further advice orsuggestions.

      • Actually, iirc, this procedure has only on 3.4. Having said that, it should work on 4. However I would highly recommend deploying yourself tomcat, postgres and other needed component from your package manager and then use Alfresco war (I never tested the procedure with the installer). This is a lot easier to maintain and is definitely the way to go for a production system.
        In your case the postgres shipped with this crappy installer is so old it is certainly impacted by the bug in mentioned earlier.
        To proceed quickly, you can try to install a fresh and decent version of postgres, beside the old one (run it on a different port) and change your alfreaco-global.properties file to point to the new pgsql. Then try to go ahead with the procedure.

  • THANK YOU! This saved my ass on migrating from Alfresco 4.2 Community MySQL (which I was using from an OOTB bitnami installation) -> Alfresco 5.0d Community Postgres!

    It feels good to be on Postgres now, but what a pain to get there!

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *