Migrujemy Drupala z bazy danych MySQL na PostgreSQL

quex, czw., 10/09/2009 - 02:59
Tagi:
Urodziny bloga!
Workrave – bo BHP jest ważne.

Baza danych MySQL jest bardzo popularna, dostępna na licencji GPL lub komercyjnej. Praktycznie każdy hosting oferuje właśnie ten typ baz danych pomijając groźnego konkurenta jakim bez wątpienia jest PostgreSQL dostępny na licencji BSD. O różnicach, podobieństwach, mocnych stronach jak i słabościach tych dwóch baz danych można przeczytać na WikiVS. W tym wpisie skupię się na praktycznym procesie migracji z PostgreSQL na MySQL oraz odwrotnie.

Największym problemem w migracji pomiędzy tymi bazami jest struktura tabel. O ile posiadamy taką samą strukturę w bazie MySQL oraz PostgreSQL to możemy bez problemu przenieść dane. Niestety sprawa struktury nie wygląda tak wesoło jak przenoszenie danych. Mamy dwa wyjścia z sytuacji – przepiszemy tabele z jednego systemu na drugi lub użyjemy instalatora dla konkretnego programu który chcemy przenieść na inny typ baz danych. Do tej pory przeprowadziłem dwie migracje jedną Drupala oraz drugą Django. Jedna w stronę PgSQL druga w stronę MySQL. Dzięki temu, że Drupal jak i Django posiadają instalatory bazy danych problem struktury został rozwiązany.

Na początku proponuję zrobić backup obecnej strony, zwłaszcza bazy danych (MySQL). Następnym krokiem powinno być założenie bazy danych na którą chcemy przenieść naszego Drupala (PostgreSQL). Kiedy mamy już wszystko przygotowane najwygodniej będzie stworzyć tymczasową subdomenę, bądź katalog wskazujący na stronę z Drupalem. Wszystko dlatego, że musimy postawić czystą instancję Drupala wraz z wszystkimi modułami które są zainstalowane na przenoszonej stronie z jedną uwagą – instalacja odbywa się już na bazie do której migrujemy (PostgreSQL). Cały zabieg ma na celu stworzenie struktury tabel w nowej bazie danych.
Kiedy przeprowadzimy instalację na docelowej bazie danych i zainstalujemy wszystkie modułu które są zainstalowane na stronie – chodzi o samą instalację. Proces konfiguracji możemy pominąć, ponieważ dane zostaną przeniesione ze źródłowej bazy danych (MySQL).

Teraz nie pozostaje nic innego jak wyczyszczenie tabel z bazy danych PostgreSQL i przerzucenie danych z MySQLa. Eksport danych można wykonać na przykład przez phpMyAdmin lub też w konsoli używając programu mysqldump (opcja której jestem zwolennikiem).

  1.  mysqldump -n -t -u użytkownik_MYSQL (-p) baza_adnych > plik.sql

Parametry -n i -t odpowiadają za nietworzenie bazy danych i strukury tabel, natomiast parametr -p informuje mysqldump że autoryzacja powinna odbyć się z hasłem i program zapyta nas o hasło użytkownika. Mając już zrzut danych należy go zaimportować do Postgresa.

Użytkownik Alex Tutubalin napisał specjalny skrypt w perlu który zrobi całą czarną robotę (związaną z przenoszeniem danych) za nas.

  1. #!/usr/bin/perl
  2. # Copyright (C) 2008, Alex Tutubalin, lexa@lexa.ru
  3.  
  4. ## NOTES
  5. ##  1. Backup your existing MySQL-based drupal installation
  6. ##  2. Apply PgSQL-related patches (blog.lexa.ru/programmirovanie/web/drupal)
  7. ##  3. Create new drupal instance (PgSQL based), dump them to create clean PgSQL db dump
  8. ##  4. Activate same modules and themes in new instance as in old one
  9. ##  5. Run this script drupal-mysql2pgsql.pl old-mysql-db-name new-pgsql-db-name
  10. ##     NOTE: you need passwordless account in both databases to run this script
  11. ##  6. Copy localized strings by standard Drupal way (export/import) b/c on MySQL locale
  12. ##     can have incorrect symbols
  13.  
  14. use DBI;
  15. use Data::Dumper;
  16. use strict;
  17. my $srcdb = DBI->connect("dbi:mysql:database=$ARGV[0]", "użytkownik_PgSQL", "hasło_MySQL") or die;
  18. my $destdb = DBI->connect("dbi:Pg:dbname=$ARGV[1]", "użytkownik_PgSQL", "hasło_PgSQL") or die;
  19.  
  20.  
  21. $srcdb->do("SET names \'utf8\'"); #character_set_client = utf8");
  22. $destdb->do("SET client_encoding=\'UTF8\'") or die;
  23.  
  24.  
  25. my $srctables = $srcdb->selectall_arrayref("show tables");
  26. my $desttables = $destdb->selectall_hashref("select * from pg_tables where not tablename ~\'^(pg_|sql_)\'","tablename");
  27.  
  28. my $seqlist = $destdb->selectall_arrayref("select relname  from pg_class where relkind=\'S\'");
  29. my $seqmap;
  30. foreach my $s(@$seqlist)
  31.   {
  32.     my $seqname = $s->[0];
  33.     if ($seqname=~/^(.*)_([a-z]+)_([a-z]+$)/)
  34.       {
  35.         my $item;
  36.         $item->{seq}=$seqname;
  37.         $item->{field}=$2;
  38.         my $table = $1;
  39.         if (exists $desttables->{$table})
  40.           {
  41.             push @{$seqmap->{$table}},$item;
  42.           }
  43.         else
  44.           {
  45.             print STDERR "WARN: cannot find table for sequence $seqname\n";
  46.           }
  47.       }
  48.   }
  49.  
  50. $destdb->{AutoCommit}=0;
  51.  
  52. foreach my $t(@$srctables)
  53.   {
  54.     my $table = $t->[0];
  55.     unless (exists $desttables->{$table})
  56.       {
  57.         print STDERR "WARN: table $table does not exists in dest db Pg:$ARGV[1]\n";
  58.         next;
  59.       }
  60.     clear_table($destdb,$table);
  61.     next if $table=~/^cache/;
  62.     next if $table=~/^locales/;
  63.     my $cnt = copy_table($srcdb,$destdb,$table) unless $table=~/^cache/;
  64.     print "$cnt rows copied ($table)\n";
  65.     init_seq($destdb,$table,$seqmap->{$table}); # if (exists $seqmap->{$table});
  66.   }
  67.  
  68. $destdb->commit;
  69. exit 0;
  70.  
  71.  
  72. sub init_seq
  73.   {
  74.     my ($db,$table,$list)=@_;
  75.     return unless $list;
  76.     for my $item (@{$list})
  77.       {
  78.         my $ref = $db->selectall_arrayref("select max($item->{field}) from $table") or die;
  79.         my $val = $ref->[0]->[0];
  80.         if ($val)
  81.           {
  82.             $db->do("select setval(\'$item->{seq}\',$val)") or die;
  83.           }
  84.       }
  85.  
  86.  
  87.   }
  88.  
  89. sub clear_table
  90.   {
  91.     my ($dbh,$table)=@_;
  92.     return unless $table;
  93.     $dbh->do("delete from $table") or die;
  94.   }
  95.  
  96. # hope, field set in both databases is same
  97. sub copy_table
  98.   {
  99.     my ($srcdb,$destdb,$table)=@_;
  100.     die unless $table;
  101.     my $slf = $srcdb->prepare("select * from $table limit 1");
  102.     my $rows = $slf->execute() or die;
  103.     return 0 if $rows < 1;
  104.     my $rec1 = $slf->fetchrow_hashref;
  105.     $slf->finish;
  106.     my @fieldnames = sort keys %$rec1;
  107.     my @qm =  map { '?'} @fieldnames;
  108.     my $ins = $destdb->prepare("INSERT into $table (".join(",",@fieldnames).") values(".join(",",@qm).")");
  109.     my $sel = $srcdb->prepare("select * from $table");
  110.     $sel->execute or die;
  111.     my $cnt = 0;
  112.     while (my $data = $sel->fetchrow_hashref)
  113.       {
  114.         $ins->execute(map {$data->{$_}} @fieldnames) or die;
  115.         $cnt++;
  116.       }
  117.     $ins->finish;
  118.     $sel->finish;
  119.     return $cnt;
  120.   }

Skrypt uruchamiamy z dwoma parametrami - pierwszy to nazwa źródłowej bazy danych w MySQL-u a drugi to nazwa docelowej bazy danych w PostgreSQL-u.
Oczywiście nie jest to rozwiązanie dedykowane pod migrację Drupala - jest to uniwersalny skrypt do przenoszenia danych MySQL -> PostgreSQL.
Niestety do odwrotnego procesu skryptu już nie można wykorzystać - jednak po co przenosić się z PostgreSQL-a ;-)

To byłoby na tyle jeżeli chodzi o zmianę bazy danych.

Portret użytkownika Gość

kwiat

pon., 08/11/2010 - 16:25

odnośnik

mysqldump posiada opcję --compatible:

o --compatible=name

Produce output that is more compatible with other database systems
or with older MySQL servers. The value of name can be ansi,
mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb,
no_key_options, no_table_options, or no_field_options. To use
several values, separate them by commas. These values have the same
meaning as the corresponding options for setting the server SQL
mode. See Section 5.1.8, "Server SQL Modes".

This option does not guarantee compatibility with other servers. It
only enables those SQL mode values that are currently available for
making dump output more compatible. For example,
--compatible=oracle does not map data types to Oracle types or use
Oracle comment syntax.

This option requires a server version of 4.1.0 or higher. With
older servers, it does nothing.

Portret użytkownika quex

quex

pon., 08/11/2010 - 16:33

odnośnik

Muszę przyznać że nie próbowałem robić importu odpowiednio wyeksportowanych danych z mysql do postgresql.
Jeżeli znajdę chwilę czasu to sprawdzę to - być może ułatwi to proces migracji w przypadku kiedy wykonywanie takich skryptów jest niemożliwe np. jeżeli nie mamy dostępu do powłoki.

Dodaj nową odpowiedź

Zawartość pola nie będzie udostępniana publicznie. Jeżeli posiadasz konto w Gravatar, Twój avatar zostanie wyświetlony.
  • Adresy internetowe są automatycznie zamieniane w odnośniki, które można kliknąć.
  • Dozwolone znaczniki HTML: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Znaki końca linii i akapitu dodawane są automatycznie.
  • Dostępne jest formatowanie fragmentów kodu źródłowego używając tagów: <code>, <blockcode>, <bash>, <c>, <cpp>, <css>, <drupal5>, <drupal6>, <html>, <java>, <js>, <perl>, <php>, <python>, <ruby>, <sql>.

Więcej informacji na temat formatowania

  ___   ___   ___    ___   ___ 
 / __| | __| | _ \  / __| | _ \
 \__ \ | _|  |  _/ | (__  |  _/
 |___/ |_|   |_|    \___| |_|
Proszę wpisać litery (tylko wielkie litery) z napisu w stylu ASCII.