Re: Importing *huge* mysql database into pgsql

[Andy <nospam@xxxxxxxxxxx>]

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