This is a perl script I’ve used to move data from mysql 5.1 to postgres 9, this script doesn’t create a schema, it only grabs the data from mysql and try to put it on the same table in postgres.

The only requirement is DBI for mysql and postgres.

  1. #!/usr/bin/perl
  2. #
  3. # this script moves all the data from mysql to postgres
  4. # modules required are:
  5. #
  6. # http://search.cpan.org/~timb/DBI-1.616/DBI.pm
  7. #
  8. # please change the dsn, srcUser/srcPass and dstUser/Pass variables.
  9.  
  10. use DBI;
  11. use strict;
  12.  
  13. my $srcUser   = 'ivan';
  14. my $srcPass   = '123';
  15. my $srcDbName = 'datatocopy';
  16. my $srcDbHost = 'mini';
  17.  
  18. my $dstUser  = 'ivan';
  19. my $dstPass  = '456';
  20. my $dstDbName = 'migrateddata';
  21. my $dstDbHost = '192.168.1.145';
  22.  
  23. ### do not modify ##
  24.  
  25. my $srcDb  = DBI->connect("dbi:mysql:database=$srcDbName;host=$srcDbHost", $srcUser, $srcPass) or die;
  26. my $dstDb  = DBI->connect("dbi:Pg:database=$dstDbName;host=$dstDbHost", $dstUser, $dstPass) or die;
  27.  
  28. ####main########
  29. print "Starting to move data from $srcDbHost to $dstDbHost\n";
  30. $srcDb->do("SET NAMES \'UTF8\'"); 
  31. $dstDb->do("SET CLIENT_ENCODING=\'UTF8\'") or die;
  32.  
  33.  
  34. my $srcTables = $srcDb->selectall_arrayref("SHOW TABLES");
  35. my $dstTables = $dstDb->selectall_hashref("SELECT * FROM pg_tables WHERE NOT tablename ~\'^(pg_|sql_)\'","tablename");
  36.  
  37. my $seqlist = $dstDb->selectall_arrayref("SELECT RELNAME FROM pg_class WHERE relkind=\'S\'");
  38. my $seqmap;
  39. foreach my $s(@$seqlist)  {
  40.     my $seqname = $s->[0];
  41.     if ($seqname=~/^(.*)_([a-z]+)_([a-z]+$)/) {   
  42.         my $item;
  43.         $item->{seq}=$seqname;
  44.         $item->{field}=$2;
  45.         my $table = $1; 
  46.         if (exists $dstTables->{$table}) {   
  47.             push @{$seqmap->{$table}},$item;
  48.         } else {   
  49.             print STDERR "WARN: cannot find table for sequence $seqname\n";
  50.         }   
  51.     }   
  52. }
  53. $dstDb->{AutoCommit}=0;
  54. my $totalRowCount = 0;
  55. foreach my $t(@$srcTables) {  
  56.     my $table = $t->[0];
  57.     unless (exists $dstTables->{$table}) {
  58.         print STDERR "WARN: table $table does not exists in dest db Pg:$dstDbName\n";
  59.         next;
  60.       }
  61.     print "Clearing data from table $table in $dstDbHost\n";
  62.     clear_table($dstDb,$table);
  63.     next if $table=~/^cache/;
  64.     next if $table=~/^locales/;
  65.     print "Copying data from mysql table $table in $srcDbHost to postgres table $table in $dstDbHost\n";
  66.     my $cnt = copy_table($srcDb,$dstDb,$table) unless $table=~/^cache/;
  67.     $totalRowCount = $totalRowCount + $cnt;
  68.     print "$cnt rows copied ($table)\n";
  69.     init_seq($dstDb,$table,$seqmap->{$table}); # if (exists $seqmap->{$table});
  70.   }  
  71. print "Commiting changes \n";     
  72. $dstDb->commit;
  73. print "$totalRowCount rows were copied.\n";
  74. exit 0;
  75.      
  76.      
  77. sub init_seq  {  
  78.     my ($db,$table,$list)=@_;
  79.     return unless $list;
  80.     for my $item (@{$list}) {
  81.         my $ref = $db->selectall_arrayref("SELECT MAX($item->{field}) FROM $table") or die;
  82.         my $val = $ref->[0]->[0];
  83.         if ($val) {
  84.             $db->do("SELECT SETVAL(\'$item->{seq}\',$val)") or die;
  85.           }
  86.       }
  87. }  
  88.      
  89. sub clear_table {  
  90.     my ($dbh,$table)=@_;
  91.     return unless $table;
  92.     $dbh->do("DELETE FROM $table") or die;
  93. }  
  94.  
  95. sub copy_table { 
  96.     my ($srcDb,$dstDb,$table)=@_;
  97.     die unless $table;
  98.     my $slf = $srcDb->prepare("select * from $table limit 1");
  99.     my $rows = $slf->execute() or die;
  100.     return 0 if $rows < 1;
  101.     my $rec1 = $slf->fetchrow_hashref;
  102.     $slf->finish;
  103.     my @fieldnames = sort keys %$rec1;
  104.     my @qm =  map { '?'} @fieldnames;
  105.     my $ins = $dstDb->prepare("INSERT INTO $table (".join(",",@fieldnames).") VALUES(".join(",",@qm).")");
  106.     my $sel = $srcDb->prepare("SELECT * FROM $table");
  107.     $sel->execute or die;
  108.     my $cnt = 0;
  109.     while (my $data = $sel->fetchrow_hashref) {
  110.         $ins->execute(map {$data->{$_}} @fieldnames) or die;
  111.         $cnt++;
  112.       }
  113.     $ins->finish;
  114.     $sel->finish;
  115.     return $cnt;
  116. }