Re: Importing *huge* mysql database into pgsql

.ep wrote:
> Hello,
>
> I would like to convert a mysql database with 5 million records and
> growing, to a pgsql database.
>
> All the stuff I have come across on the net has things like
> "mysqldump" and "psql -f", which sounds like I will be sitting forever
> getting this to work.
>
> Is there anything else?
>
> Thanks.
>

I used this perl script (you'll need to modify it a little for your
setup). It generates a psql script that uses COPY instead of INSERT, so
it runs much faster.

-Andy

#!/usr/bin/perl
# call like: ./mydump.pl dbname filename.sql

use strict;
use DBI;

my $outfile = pop;
my $database = pop;
print "dumping db: $database to file $outfile\n";
my $host = 'servername';
my $port = 33060;
my $dsn = "DBI:mysql:database=$database;host=$host;port=$port";
my $db = DBI->connect($dsn, 'username', 'password') or die;

sub dumptable
{
my $tbl = pop;
print "Dumping table: $tbl\n";
my $q = $db->prepare("select * from $tbl");
#$q->{"mysql_use_result"} = 1;
$q->execute();

my $names = $q->{'NAME'};
my $type = $q->{'mysql_type_name'};
my $numFields = $q->{'NUM_OF_FIELDS'};

print OUT "\\echo Table: $tbl\n";
print OUT "delete from $tbl;\n";
print OUT "copy $tbl (";
for (my $i = 0; $i < $numFields; $i++) {
printf(OUT "%s%s", $i ? ", " : "", $$names[$i]);
}

print OUT ") FROM stdin;\n";
my($s);
while (my $ref = $q->fetchrow_arrayref) {
for (my $i = 0; $i < $numFields; $i++) {
if (defined($$ref[$i]))
{
$s = $$ref[$i];
$s =~ s{\\}[\\\\]g;

#things that should not be double slashed
$s =~ s/\n/\\n/g;
$s =~ s/\r/\\r/g;
$s =~ s/\t/\\t/g;
$s =~ s/\000/\\000/g;
}
else {
$s = '\\N';
}

printf(OUT "%s%s", $i ? "\t" : "", $s);
}
print OUT "\n";
}

print OUT "\\.\n";
$q = undef;
}

sub dumpall
{
open(OUT, ">$outfile") or die;

# tables you dont want to dump
my $dont = {'junk' => 1,
'temp' => 1,
'temp2' => 1,
'tempetc' => 1,
};

my $q = $db->prepare('show tables');
$q->execute();
while (my $x = $q->fetchrow_arrayref)
{
if (! exists($dont->{$$x[0]}) )
{
#print "dump $$x[0]\n";
dumptable($$x[0]);
}
}
$q = undef;
print OUT "VACUUM VERBOSE ANALYZE;\n";
close(OUT);
}

#open(OUT, '>out.sql') or die;
#dumptable('note');
#close(OUT);

dumpall();
$db->disconnect();