Skip to content

DE:Migration von Postgres nach MySQL

ddfph edited this page Apr 12, 2018 · 1 revision

Howto Tine2.0 Datenbank umstellen von Postgres auf MySQL

Vorweg: diese Anleitung basiert zu einem grossen Teil auf dem Posting von mewis im tine20-Forum: https://www.tine20.org/forum/viewtopic.php?f=12&p=67500

Der nette Tine20-Support hat uns bei der Umstellung auch kraeftig unter die Arme gegriffen und uns in die richtige Richtung geschubst. Trotzdem waren auch hier noch viele Stunden Handarbeit noetig.

Randbedingungen: die zu konvertierende Tine-DB war ~5,4GB gross, allein der Import nach MySQL hat beim ersten mal (ohne DB Optimierungen) gut fuenf Stunden gedauert; mit Optimierungen liess es sich die Zeit auf 1,5h herunterdruecken (4-Kern CPU, rotierende Platten).

  1. Webserver mit dem tine20-Frontend anhalten

  2. MySQL-Datenbankschema in die Zieldatenbank importieren mit:

       mysql -u tine20dbuser -p tine20 < tine_db_schema.sql
    Das DB-Schema muss der dabei Tine20-Version entsprechen,die im Quellsystem verwendet wird. Der Tine20-Support schickt das Schema auf Anfrage zu.
    Den tine20-User in Mysql anlegen und mit Rechten auf der DB versehen:
      grant ALL on tine20.* to tine20dbuser@localhost identified by "GEHEIMPASSWORT";
  3. Dump aus Postgres erstellen:

    pg_dump -h localhost --no-acl --no-owner --format p --data-only --column-inserts -U tine20 -d tine20 -f pqsqlDump.sql
    Das Passwort steht in der /etc/tine20/config.inc.php des tine20-Hosts.
    Die Fehlermeldung
      pg_dump: HINWEIS: Es gibt zirkulaere Fremdschluessel-Constraints fuer diese Tabelle:
      pg_dump:   tine20_tree_nodes
      pg_dump: Moeglicherweise kann der Dump nur wiederhergestellt werden, wenn --disable-triggers verwendet wird oder die Constraints voruebergehend entfernt werden.
      pg_dump: Führen Sie einen vollen Dump statt eines Dumps mit --data-only durch, um dieses Problem zu vermeiden.
    kann ignoriert werden.
  4. Kopie des Dumps anlegen ist eine sehr gute Idee.

  5. Den Dump aus Postgres mit leichtgewichtigen Editor der Wahl (nano, joe, mcedit, wer mag, kann auch vim nehmen) bearbeiten und am Anfang alle Zeilen vor dem ersten Inserst Statement loeschen und SET FOREIGN_KEY_CHECKS=0; einfuegen. Am Dateiende alle Zeilen nach dem letzten Insert loeschen und SET FOREIGN_KEY_CHECKS=1; einfuegen.

  6. Jetzt muessen einige reservierte Keywords mit sed mit Backticks "`" versehen werden. MySQL und Postgres verhalten sich mit den verschiedenenAnfuehrungszeichen leider anders. Leider haben die beiden Datenbanken auch nicht ganz die gleiche reserved Keywords.

    sed -i 's/SELECT pg_catalog.setval/-- SELECT pg_catalog.setval/g' psqlDump.sql    sed -i 's/, "order"/, `order`/g' psqlDump.sql
    sed -i 's/, "time"/, `time`/g' psqlDump.sql
    sed -i 's/, "from"/, `from`/g' psqlDump.sql
    sed -i 's/, "timestamp"/, `timestamp`/g' psqlDump.sql
    sed -i 's/, "end"/, `end`/g' psqlDump.sql
    sed -i 's/, "ssl"/, `ssl`/g' psqlDump.sql
    sed -i 's/, "revisionProps"/, `revisionProps`/g' psqlDump.sql
    sed -i 's/, "notificationProps"/, `notificationProps`/g' psqlDump.sql
    sed -i 's/, "right"/, `right`/g' psqlDump.sql
    sed -i 's/, ssl,/, `ssl`,/g' pgsqldump.sql
    sed -i 's/"interval", /`interval`, /g' pgsqldump.sql
    Eine Liste der in MySQL reservierten Keywords gibt es bei
      https://mariadb.com/kb/en/library/reserved-words/
  7. Backslashes sind immer gefaehrlich: in einigen Tabellen (z.B. tine20_filter) kommen "\/" vor, die nach "\\/" umgequotet werden muessen mit sed -i 's/\\\//\\\\\//g' pgsqldump.sql

    NICHT AUSPROBIERT: (hatten wir direkt in der DB gemacht)
       sed -i  's/\\\\Seen"}/\\\\\\\\Seen"}/g'   pgsqldump.sql
       sed -i  's/\\\\Seen"}/\\\\\\\\Flagged"}/g'   pgsqldump.sql
       sed -i  's/\\\\Seen"}/\\\\\\\\Draft"}/g'   pgsqldump.sql
  8. Import anschmeissen und sehen was da kommt. Den bearbeiteten Dump in MySQL importieren mit mysql tine20new < /var/tmp/pgsqldump.sql -u tine20dbuser -p

    Ob der Import ohne Fehler durchgeht, sollte an dieser Stellen von den Inhalten abhaengen, mit denen die Benutzer Tine befuellt haben. Dies sind typischerweise
    * Backslashes an 'ungluecklichen' Stellen und
    * Trailing Whitespace.
    Bricht der Import ab, muss im Dump nachgesehen werden, was zu diesem Fehler gefuehrt hat und dann mit einem passenden sed-Ausdruck nachgeholfen haben. Dieser Teil wird vermutlich den Loewenanteil des Migrationsaufwands in Anspruch nehmen.
    Beispiele (gekuerzt):
      INSERT INTO tine20_felamimail_cache_message (id, account_id, messageuid, folder_id, subject,  [...] ) VALUES ([...], 'Flyer Studium \Verwaltungsinformatik\', 'te
    Hier hat das Subject einer E-Mail leider einen Backslash vor dem    Anfuehrungszeichen. Der Import bricht ab, und das die Backslashes muessen    manuell 'escape't werden:
       sed -i 's/\\Verwaltungsinformatik\\/\\\\Verwaltungsinformatik\\\\/g' pgsqldump.sql
      INSERT INTO tine20_addressbook (id, adr_one_countryname [...] VALUES ('79cd468312ee3a6d142f438d8553bd5e9ca988d4', 'NN-SCHULE\', [...]
    Aus welchen Gruenden auch immer ist hier ein Backslash ans Ende vom Feld adr_one_countryname' gekommen und das Anfuehrungszeichen wurde damit versehentlich 'escape't. Also wieder ersetzen mit
       sed -i 's/-SCHULE\\/-SCHULE\\\\/g' pgsqldump200318.sql
     INSERT INTO tine20_tree_nodes (id, parent_id, object_id, name, islink) VALUES ('a3ae699cfb3e990c18af45c0c5c0743925f9e5c6', '9b43680a671aa94b3102ebc19eca6a6e4f45561f', 'fcb2ceb9f5e93034ce721bd0b72a019edb965bfe', '1.4.1 Gesundheitszirkel ', 0);
    In diesem Fall ist das Leerzeichen hiner "Gesundheitszirkel" das Problem -- MySQL entfernt bei Strings Leerzeichen am Ende, waehrend Postgres die Leerzeichen nicht anfasst. Das Feld 'Name" war hier von einem Benutzer zweimal mit (fast) demselben Namen versehen worden: einmal mit dem Leerzeichen, einmal ohne das Leerzeichen. Da der Name unique sein muss, ist der Import an dieser Zeile abgebrochen. Als Workaround haben wir hier einfach das Leerzeichen durch einen Unterstrich ersetzt:
      sed -i 's/1.4.1 Gesundheitszirkel /1.4.1 Gesundheitszirkel_/g' pgsqldump.sql
  9. config.inc.php auf die neue DB umstellen

  10. Probleme mit den jetztigen Dump:

    • Bei ActiveSync-Clients funktioniert jetzt der Sync nicht, weil in der tabelle tine20_acsync_device aus Postgres: {"lastXML":"<?xml version=\"1.0\" encoding=\"utf-8\"? in mysql: {"lastXML":"<?xml version="1.0" encoding="utf-8"? geworden ist. Das braucht man aber nicht auf DB-Ebene beheben: der Tine20-Admin kann einfach die die Sync-Clients in der Uebersicht loeschen, die Clients verbinden sich automatisch wieder neu synchen richtig. Ausnahme: falls sich der ActiveSync auf einen Filter bezieht, ist der Filter nach dem Loeschen verschwunden. Die Clients, die solche Filter benutzen, haben in der Tabelle auch einen entsprechenden Eintrag (ggf. ganz rechts weitere Spalten einblenden). Die User muessen sich einmal ins Web-UI der Tine einloggen und den ActiveSync-Filter neu setzen. Das ist vielleicht nicht die eleganteste Loesung, fuer uns aber sehr gut gangbar, weil nur die allerwenigsten einen Filter gesetzt hatten und wir die Benutzer nach dem Update darauf aufmerksam gemacht haben, dass Sie ihren Filter einmal neu setzen muessen.

    • Zum Teil werden bereits gelesen E-Mails im Tine-Mail Client weiterhin als ungelesen angezeigt. Das betrifft nur alte E-Mails, also solche von vor der Umstellung. Das Problem tritt nicht bei direkten Mail-Abruf ueber IMAP, sondern nur innerhalb des Tine-Clients. Keine Loesung bisher.

Tipps:

  • Bei grossen Dumps kann es unangenehm lange dauern, bis man mit 'less' an der richtigen Zeile angekommen ist. Schneller ist es, wenn man eine Kopie des Dumps mit 'nl' durchnummeriert und dann nur noch mit 'grep' nach der Zeilennummer sucht.

  • Im Dump koennen die Inhalte der Tabelle felamimail_cache_message auch geloescht werden. Die Inhalte werden dann automatisch wieder mit dem Mail-Server abgeglichen.

  • Mysql/InnoDB etwas optimieren: Datenbank-tuning ist ja ein Wissenschaft fuer sich. Es lohnt sich aber allein fuer die Migration, vorher die Parameter an das konkrete System anzupassen. In unserem haben wir in /etc/mysql/mariadb.conf.d/50-server.cnf die innodb_buffer_pool_size auf ein Viertel des vorhandenen Rams gesetzt: innodb_buffer_pool_size = 2147483648 Zumindest bei Debian ist der Wert per default wesentlich niedriger eingestellt.

You can’t perform that action at this time.