.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();
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
