Script to copy data from MySQL to postgreSQL

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.

#!/usr/bin/perl
#
# this script moves all the data from mysql to postgres
# modules required are:
#
# http://search.cpan.org/~timb/DBI-1.616/DBI.pm
#
# please change the dsn, srcUser/srcPass and dstUser/Pass variables.

use DBI;
use strict;

my $srcUser   = 'ivan';
my $srcPass   = '123';
my $srcDbName = 'datatocopy';
my $srcDbHost = 'mini';

my $dstUser  = 'ivan';
my $dstPass  = '456';
my $dstDbName = 'migrateddata';
my $dstDbHost = '192.168.1.145';

### do not modify ##

my $srcDb  = DBI->connect("dbi:mysql:database=$srcDbName;host=$srcDbHost", $srcUser, $srcPass) or die;
my $dstDb  = DBI->connect("dbi:Pg:database=$dstDbName;host=$dstDbHost", $dstUser, $dstPass) or die;

####main########
print "Starting to move data from $srcDbHost to $dstDbHost\n";
$srcDb->do("SET NAMES \'UTF8\'"); 
$dstDb->do("SET CLIENT_ENCODING=\'UTF8\'") or die;
 
 
my $srcTables = $srcDb->selectall_arrayref("SHOW TABLES");
my $dstTables = $dstDb->selectall_hashref("SELECT * FROM pg_tables WHERE NOT tablename ~\'^(pg_|sql_)\'","tablename");
 
my $seqlist = $dstDb->selectall_arrayref("SELECT RELNAME FROM pg_class WHERE relkind=\'S\'");
my $seqmap;
foreach my $s(@$seqlist)  {
    my $seqname = $s->[0];
    if ($seqname=~/^(.*)_([a-z]+)_([a-z]+$)/) {   
        my $item;
        $item->{seq}=$seqname;
        $item->{field}=$2;
        my $table = $1; 
        if (exists $dstTables->{$table}) {   
            push @{$seqmap->{$table}},$item;
        } else {   
            print STDERR "WARN: cannot find table for sequence $seqname\n";
        }   
    }   
}
$dstDb->{AutoCommit}=0;
my $totalRowCount = 0;
foreach my $t(@$srcTables) {  
    my $table = $t->[0];
    unless (exists $dstTables->{$table}) {
        print STDERR "WARN: table $table does not exists in dest db Pg:$dstDbName\n";
        next;
      }
    print "Clearing data from table $table in $dstDbHost\n";
    clear_table($dstDb,$table);
    next if $table=~/^cache/;
    next if $table=~/^locales/;
    print "Copying data from mysql table $table in $srcDbHost to postgres table $table in $dstDbHost\n";
    my $cnt = copy_table($srcDb,$dstDb,$table) unless $table=~/^cache/;
    $totalRowCount = $totalRowCount + $cnt;
    print "$cnt rows copied ($table)\n";
    init_seq($dstDb,$table,$seqmap->{$table}); # if (exists $seqmap->{$table});
  }  
print "Commiting changes \n";     
$dstDb->commit;
print "$totalRowCount rows were copied.\n";
exit 0;
     
     
sub init_seq  {  
    my ($db,$table,$list)=@_;
    return unless $list;
    for my $item (@{$list}) {
        my $ref = $db->selectall_arrayref("SELECT MAX($item->{field}) FROM $table") or die;
        my $val = $ref->[0]->[0];
        if ($val) {
            $db->do("SELECT SETVAL(\'$item->{seq}\',$val)") or die;
          }
      }
}  
     
sub clear_table {  
    my ($dbh,$table)=@_;
    return unless $table;
    $dbh->do("DELETE FROM $table") or die;
}  

sub copy_table { 
    my ($srcDb,$dstDb,$table)=@_;
    die unless $table;
    my $slf = $srcDb->prepare("select * from $table limit 1");
    my $rows = $slf->execute() or die;
    return 0 if $rows < 1;
    my $rec1 = $slf->fetchrow_hashref;
    $slf->finish;
    my @fieldnames = sort keys %$rec1;
    my @qm =  map { '?'} @fieldnames;
    my $ins = $dstDb->prepare("INSERT INTO $table (".join(",",@fieldnames).") VALUES(".join(",",@qm).")");
    my $sel = $srcDb->prepare("SELECT * FROM $table");
    $sel->execute or die;
    my $cnt = 0;
    while (my $data = $sel->fetchrow_hashref) {
        $ins->execute(map {$data->{$_}} @fieldnames) or die;
        $cnt++;
      }
    $ins->finish;
    $sel->finish;
    return $cnt;
}