Re: Importing *huge* mysql database into pgsql - Mailing list pgsql-general

.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();