Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
HowTO: Migrate from Qmail/Vpopmail to Postfix Dbmail
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks
View previous topic :: View next topic  
Author Message
msalerno
Veteran
Veteran


Joined: 17 Dec 2002
Posts: 1338
Location: Sweating in South Florida

PostPosted: Mon May 02, 2005 8:13 pm    Post subject: HowTO: Migrate from Qmail/Vpopmail to Postfix Dbmail Reply with quote

Updated 27/06/05
- Script changes to comply with updated schema

I have a large Qmail/Vpopmail server with many domains and users (stored in mysql) and I wrote a few scripts to handle the migration. None of the scripts do anything more than output sql that you can review before you copy/paste and execute. They are all in Perl and work with my current Postfix/MailScanner/DBMail configuration that includes support for domain aliasing build into DBmail:

https://forums.gentoo.org/viewtopic-t-312591-highlight-.html

All users will be created as user@domain

There are 3 steps to the migration, 3 different scripts to run. Before you can move on to the next script, you must execute the sql that gets printed to the screen from the previous script.

The first script will query your vpopmail database and qmail configuration files to get information about the domains that are hosted on the server. Then it will get the user information for the virtual domains. Each new domain gets a different group id. Throughout the process it will output sql that you can use to populate your dbmail tables.

The second script will query your DBMail dbmail_users table. This is required because we cannot populate the dbmail_aliases table until we have the user_idnr and group of the users created by the sql output from the previous script. Once this information is collected, the script will output the sql to populate the dbmail_aliases table.

The third script will scan the filesystem for .qmail aliases and forwards and use this information to output sql to populate the dbmail_aliases table, creating aliases and forwards.

Script 1:
Code:
#!/usr/bin/perl -w
use strict;
use DBI;

my %allinfo;
my @sql;

# Setup SQL Connection credentials to the vpopmail server

my $dbhost = "localhost";
my $database = "vpopmail";
my $dbuser = "vpopmail";
my $dbpass = "password";

#Path the the qmail assign file - Contains domains and aliased domains

my $assign = "/var/qmail/users/assign";

# Query to get users for domains/sql
my $getusers = "Select pw_name, pw_passwd, pw_shell from ";

#This number will be the first used as the ID for the accounts created
#It will increment by 1 for each new domain.

my $groupid = 5;

my $dsn = "DBI:mysql:database=$database;host=$dbhost";
my $dbh = DBI->connect($dsn, $dbuser,$dbpass,{RaiseError => 1});

open (ASSIGN, $assign) || die "Cannot open \"$assign\" $!";;
my @domains = <ASSIGN>;
close(ASSIGN);

foreach (@domains){
        next if $_ !~ /:/g;
        my @indiv = (split(":", $_))[0,1];
        foreach (@indiv){
                s/^\+//;
                s/-$//;
                s/^\s+//;
                s/\s+$//;
                }
        push @{ $allinfo{$indiv[1]} }, $indiv[0];
}

foreach my $maindomain( keys %allinfo ) {
        $groupid++ if $groupid =~ /0$/;
        my $quoteddn = $dbh->quote($maindomain);
        push @sql, "insert into dbmail_vdomains (domain, comment) Values ($quoteddn,\'Primary Domain\');";

        # Get domains and domain aliases
        foreach (@{$allinfo{$maindomain}}){
                next if $_ =~ /$maindomain/i;
                push @sql, "insert into dbmail_vdomains (domain, pridomain, comment) Values (\'$_\', \'$maindomain\', \'Alias for $maindomain\');";
        }
        my $vdomsql = $maindomain;
        for ($vdomsql) {
                s/^\s+//;
                s/\s+$//;
                s/\./_/g;
        }
        my $sth = $dbh->prepare("$getusers $vdomsql");
        $sth->execute();
        while ( my @users = $sth->fetchrow_array ) {
                my $username = "$users[0]\@$maindomain";
                $username = $dbh->quote($username);
                my $password = $dbh->quote($users[1]);
                my $quota = $dbh->quote($users[2]);
                $quota =~ s/NOQUOTA/0/;
                push @sql, "Insert into dbmail_users ( userid, passwd, maxmail_size, client_idnr, encryption_type ) Values ( $username, $password, $quota, $groupid, \'md5\' );";
        }
$groupid++;
}

print "$_\n" foreach @sql;


$dbh->disconnect;


Script 2:
Code:
#!/usr/bin/perl -w
use strict;
use DBI;

my %allinfo;
my @sql;

# Setup SQL Connection credentials for the dbmail host

my $dbhost = "dbmailhost";
my $database = "dbmail";
my $dbuser = "dbmail";
my $dbpass = "password";

# Query to get users for domains/sql
my $getusers = "Select user_idnr, userid, client_idnr from dbmail_users";

my $dsn = "DBI:mysql:database=$database;host=$dbhost";
my $dbh = DBI->connect($dsn, $dbuser,$dbpass,{RaiseError => 1});

my $sth = $dbh->prepare($getusers);
        $sth->execute();
        while ( my @users = $sth->fetchrow_array ) {
                push @sql, "Insert into dbmail_aliases ( alias, deliver_to, client_idnr ) Values (\'$users[1]\', \'$users[0]\', \'$users[2]\')";
        }

print "$_;\n" foreach @sql;
$dbh->disconnect;


Script 3:
Code:
#!/usr/bin/perl -w
use strict;
use File::Find;
use DBI;

my @sql;
my %aliasalias;
# Setup SQL Connection credentials for the dbmail host

my $dbhost = "dbmailhost";
my $database = "dbmail";
my $dbuser = "dbmail";
my $dbpass = "password";

# Query to get users for domains/sql
my $getusers = "Select user_idnr, userid, client_idnr from dbmail_users";

# Query to get aliases for domains/sql
my $getaliases = "Select alias_idnr, alias, deliver_to, client_idnr from dbmail_aliases";

my $getdomains = qq |
Select REPLACE(dv1.domain, '\@', '') as domain, da.client_idnr from dbmail_vdomains dv1 left join dbmail_aliases da on
REPLACE(dv1.domain, '\@', '') = substring( da.alias, LOCATE( '\@', da.alias ) +1 ) or
REPLACE(dv1.pridomain, '\@', '') = substring( da.alias, LOCATE( '\@', da.alias ) +1 )
Group by dv1.domain|;

my $vpopmailroot = '/var/vpopmail/domains/';

my $dsn = "DBI:mysql:database=$database;host=$dbhost";
my $dbh = DBI->connect($dsn, $dbuser,$dbpass,{RaiseError => 1});

my $dbmailusers = $dbh->selectall_hashref($getusers, 'userid');
my $dbmailaliases = $dbh->selectall_hashref($getaliases, 'alias');
my $domainhref = $dbh->selectcol_arrayref($getdomains, { Columns=>[1,2] });
my %domains = @$domainhref;
$dbh->disconnect;

find({wanted =>\&wanted}, "$vpopmailroot");

while (my($key, $value) = each %aliasalias) {
        my @aliasedal = split(/:/, $key);
        process($aliasedal[0],$aliasedal[1]);
}

sub wanted {
        my $i = 0;
        if (-f && /^\.qmail/) {
                my ($alias, $aliasguts);
                my @paths = split("/", "$File::Find::dir/$_");
                if ($_ =~ /\.qmail-/){
                        $alias = $_;
                        $alias =~ s/\.qmail-//;
                }
                else {
                        $alias = "$paths[5]";
                }
                        {
                        local(*QMAILALIAS, $/);
                        open (QMAILALIAS, "$File::Find::dir/$_") || die "can't open $File::Find::dir/$_: $!";
                        $aliasguts = <QMAILALIAS>;
                        $aliasguts =~ s/\n/\\/g;
                        my @aliasdest = split(/\\/, $aliasguts);
                        foreach (@aliasdest){
                                s/&//g;
                                next if $_ =~ /^\//;
                                next if $_ =~ /^\|/;
                                next if length($_) < 2;
                                my $dbdest;
                                my $alias = "$alias\@$paths[4]";
                                if ($_ =~ /@/g){
                                        $dbdest = "$_";
                                }
                                else {
                                        $dbdest = "$_\@$paths[4]";
                                }
                                process($dbdest,$alias);
                        }
                }
        }
}

sub process {
        my $dbdest = $_[0];
        my $alias = $_[1];
        my $key = "$dbdest:$alias";
        my $domain = (split(/@/, $dbdest))[1];
        my $destdom = (split(/@/, $alias))[1];
        my $user_idnr = $dbmailusers->{$dbdest}{user_idnr};
        if (!exists($domains{$domain}) and !exists($domains{"\@$domain"})){
                push @sql, "Insert into dbmail_aliases (alias, deliver_to, client_idnr) Values ('$alias', '$dbdest', '$domains{$destdom}');";
        }
        if ( $dbmailusers->{$dbdest}{userid} ){
                my $client_idnr = $dbmailusers->{$dbdest}{client_idnr};
                my $user_idnr = $dbmailusers->{$dbdest}{user_idnr};
                push @sql, "Insert into dbmail_aliases (alias, deliver_to, client_idnr) Values ('$alias', '$user_idnr', '$client_idnr');";
        }
        if ( !$dbmailusers->{$dbdest}{userid} ){
                push @sql, "Insert into dbmail_aliases (alias, deliver_to, client_idnr) Values ('$alias', '$dbdest', '$domains{$destdom}');";
        }
        return;
}

print "$_\n" foreach @sql;
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum