database recovery

-----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&q
uot;, "$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)

iD8DBQFCaWwkpX79e6kZbJARAvrKAJ43Rj5n3yskX1T611IJgWzY1ZfHTQCf
QY68
mb0gIdiGyZ4/NoWG8cgOwIE=
=bgOe
-----END PGP SIGNATURE-----

_______________________________________________
Dbmail mailing list
Dbmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

database recovery

Thread: database recovery