-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hello,
we had a major crash, and postgres got wiped out in the process. We
did have some of the dbmail database in sql format, so now we are
trying to import it back into postgres, using dbmail-2.1.0 and
postgresql-8.0.2
To get this done, I wrote a little perl script which basically takes
that sql file and imports it line by line into postgres (i couldnt use
copy from 'file' way because we were getting syntax errors, and the
sql dump itself states copy from stdin)
The script looks in the modified sql dump of the db for a statement
"table<tab>name" and then sticks every line after that into
corresponding table.
But now I am getting an error, which I dont know how to deal with.
Below is the perl script, a little portion of the data file (the whole
thing is 400megs!) and the error message.
I really appreciate any help on this, since im stumped (pardon cheezy
perl, but hey it works)
- -----------
dbmail.pl:
#!/usr/bin/perl
use DBI qw(:sql_types);
use DBD::Pg;
$file = $ARGV[0];
open(FILE, $file) or die $!;
foreach $line (<FILE>) {
~ chomp($line);
~ if ($line =~ /table/) {
~ ($trash, $table) = split("\t", $line);
~ next;
~ } else {
~ db_insert($line, $table);
~ next;
~ }
}
close(FILE);
sub db_insert {
~ @entry = split(" ", $_[0]);
~ $db_table = $_[1];
~ $db_host = "localhost";
~ $db_name = "dbmail";
~ $db_username = "dbmail";
~ $db_passwd = "dbmail";
~ $db_connection_id =
DBI->connect("dbi:Pg:dbname=$db_name;host=$db_host", "$db_username");
~ if ($db_table == "aliases") {
~ $db_query = "insert into aliases (alias_idnr, alias,
deliver_to, client_idnr) values (?,?,?,?)";
~ $query = $db_connection_id->prepare($db_query);
~ $query->execute($entry[0], $entry[1], $entry[2],
$entry[3]) or die "Cannot complete query: " . $query->errstr;
~ $query->finish();
~ } elsif ($db_table == "users") {
~ $db_query = "insert into users (user_idnr, userid,
passwd, client_idnr, maxmail_size, encryption_type, last_login) values
(?,?,?,?,?,?,?)";
~ $query = $db_connection_id->prepare($db_query);
~ $query->execute($entry[0], $entry[1], $entry[2],
$entry[3], $entry[4], $entry[5], $entry[6]) or die "Cannot complete
query: " . $query->errstr;
~ $query->finish();
~ } elsif ($db_table == "mailboxes") {
~ $db_query = "insert into mailboxes (mailbox_idnr,
owner_idnr, name, seen_flag, answered_flag, deleted_flag,
flagged_flag, recent_flag, draft_flag,no_inferiors, no_select,
permission, is_subscribed) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
~ $query = $db_connection_id->prepare($db_query);
~ $query->execute($entry[0], $entry[1], $entry[2],
$entry[3], $entry[4], $entry[5], $entry[6], $entry[7], $entry[8],
$entry[9], $entry[10], $entry[11], $entry[12], $entry[13]) or die
"Cannot complete query: " . $query->errstr;
~ $query->finish();
~ } elsif ($db_table == "messages") {
~ $db_query = "insert into messages (message_idnr,
mailbox_idnr, messagesize, seen_flag, answered_flag, deleted_flag,
flagged_flag, recent_flag, draft_flag, unique_id, internal_date,
status, rfcsize) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
~ $query->execute($entry[0], $entry[1], $entry[2],
$entry[3], $entry[4], $entry[5], $entry[6], $entry[7], $entry[8],
$entry[9], $entry[10], $entry[11], $entry[12], $entry[13]) or die
"Cannot complete query: " . $query->errstr;
~ $query->finish();
~ } elsif ($db_table == "messageblks" ) {
~ $db_query = "insert into messageblks (messageblk_idnr,
message_idnr, messageblk, blocksize) values (?,?,?,?)";
~ $query = $db_connection_id->prepare($db_query);
~ $query->execute($entry[0], $entry[1], $entry[2],
$entry[3]) or die "Cannot complete query: " . $query->errstr;
~ $query->finish();
~ }
}
- ----------------------
data file format:
table users
44 user1 passwd 0 0 md5sum 2004-10-18
15:36:58-04
30 user2 passwd 0 0 md5sum 2004-07-30
21:14:15-04
- ------
error:
DBD::Pg::st execute failed: ERROR: duplicate key violates unique
constraint "dbmail_aliases_pkey"
Cannot complete query: ERROR: duplicate key violates unique
constraint "dbmail_aliases_pkey"
I have absolutely no clue what that error really means. I cannot find
any reference to dbmail_aliases_pkey anywhere in the sql for the db
creation, and im not even sure what that does (my postgres is still
pretty weak)
So any help whatsoever will be greeeeaaaatly appreciated!
Thanks!
max
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3-nr1 (Windows XP)
iD8DBQFCaWwkpX79e6kZbJARAvrKAJ43Rj5n3yskX1T611IJgWzY1ZfHTQCfQY68
mb0gIdiGyZ4/NoWG8cgOwIE=
=bgOe
-----END PGP SIGNATURE-----
