Migration von Plesk/Qmail zu Postfix/MySQL

Ein schneller Überblick / a quick overview

Zuerst ein Blick in die Plesk-Datenbank: Welche Postfächer und sonstige Mailnamen (Weiterleitungen, Mailgruppen etc.) gibt es?

First let's look at the Plesk database: which mailboxes and other mailnames (forwards, mailgroups etc.) do exist?

SELECT
 mail . mail_name , domains . name , accounts . password , 
 mail . postbox , mail . mbox_quota, mail . redirect , 
 mail . redir_addr , mail_group , mail . autoresponder  
FROM `mail` , accounts , domains
WHERE
 mail . account_id =accounts . id AND
 mail . dom_id =domains . id
ORDER BY `mail` . `postbox`  ASC

Wir können auch mal gucken, welche Aliase Unteradressen einer lokalen Mailbox sind.

We can also check which aliases have a local mailbox as target.

SELECT
 concat (mail_aliases.alias , '@', domains.name) as address,
 concat (mail.mail_name , '@', domains.name) as goto,
 domains.name as domain
FROM `mail_aliases`, mail, domains
WHERE
 mn_id=mail.id AND
 mail . dom_id =domains . id

Und wir können uns eine Liste aller Catch-all Mailboxen anzeigen lassen.

And we can get a list of all catch-all mailboxes.

SELECT
 concat('@' , domains.name) as address,
 Parameters.value as goto,
 domains.name as domain,
 'catchall' as source
FROM `Parameters`,domains,  DomainServices
WHERE 
 DomainServices.dom_id=domains.id AND
 DomainServices.dom_id=domains.id AND
 DomainServices.parameters_id=Parameters.id AND
 Parameters.parameter = 'catch_addr'

Migration zu Postfix / migration to postfix

Jetzt aber endlich zur Migration auf eine Postfix-kompatible Datenbank...

Now let's finally migrate everything to a postfix compatible database...

Der einfache Teil: Migration der Postfächer

The easy part: migrating the mailboxes

SELECT
 concat (mail . mail_name  , '@', domains.name) as username,
 accounts . password as password,
 concat (domains.name, '/', mail . mail_name  , '/' ) as maildir,
 mail . mbox_quota as quota,
 domains . name as domain
FROM `mail` , accounts , domains 
WHERE
 mail . account_id =accounts . id AND
 mail . dom_id =domains . id AND
 mail.postbox = 'true'

Die Query für die Aliase hat Chancen auf den angesprochenen Längenrekord. Um die Herkunft der Aliase kontrollieren zu können, habe ich eine zusätliche Spalte "source" eingefügt. Für den laufenden Betrieb ist sie überflüssig und kann später wieder entfernt werden.

The query for the aliases has chances to reach the mentioned length record. To be able to verify the origin of the aliases, I have added a "source" column. It is not needed to run the mail server and can be dropped later.

SELECT
 concat(mail . mail_name  , '@', domains.name) as address,
 concat(mail . mail_name  , '@', domains.name, ',', mail.redir_addr) as goto,
 domains . name as domain,
 'mbox+redir' as source
FROM `mail` , accounts , domains
WHERE
 mail . account_id =accounts . id AND
 mail . dom_id =domains . id AND
 mail.postbox = 'true' AND
 mail.redirect='true'

UNION ALL

SELECT
 concat(mail . mail_name  , '@', domains.name) as address,
 concat(mail . mail_name  , '@', domains.name) as goto,
 domains . name as domain,
 'mbox' as source
FROM `mail` , accounts , domains
WHERE
 mail . account_id =accounts . id AND
 mail . dom_id =domains . id AND
 mail.postbox = 'true' and mail.redirect='false'

UNION ALL

SELECT
 concat(mail . mail_name  , '@', domains.name) as address,
 mail.redir_addr as goto,
 domains . name as domain,
 'redir' as source
FROM `mail` , accounts , domains
WHERE
 mail . account_id =accounts . id AND
 mail . dom_id =domains . id AND
 mail.postbox = 'false' and mail.redirect='true'

UNION ALL

SELECT
 concat(mail_aliases.alias , '@', domains.name) as address,
 concat(mail.mail_name , '@', domains.name) as goto,
 domains.name as domain,
 'alias' as source
FROM `mail_aliases`, mail, domains
WHERE
 mn_id=mail.id AND
 mail . dom_id =domains . id

UNION ALL

SELECT
 concat('@' , domains.name) as address,
 Parameters.value as goto,
 domains.name as domain,
 'catchall' as source
FROM `Parameters`,domains,  DomainServices
WHERE 
 DomainServices.dom_id=domains.id AND
 DomainServices.dom_id=domains.id AND
 DomainServices.parameters_id=Parameters.id AND
 Parameters.parameter = 'catch_addr'

ORDER BY domain, address

Um aus den SELECT-Abfragen gleich Tabellen zu erstellen, stellt man ihnen einfach ein "CREATE TABLE xy" voran. Oder man verwendet "INSERT INTO xy ...", um die Daten in eine bereits erstellte Tabelle zu übernehmen.

To convert the SELECT queries to create tables, simply prepend a "CREATE TABLE xy" statement. Or use "INSERT INTO xy ..." if you have already created the table and want to insert the data there:

CREATE TABLE xy SELECT ...   - OR -
INSERT INTO xy SELECT ...

Und was sonst noch war / and some other stuff

Was bleibt, ist die Migration von Mailgruppen, Mailinglisten und Autorespondern. Dafür kann ich nur die SELECT-Queries zur Auflistung anbieten, da mangels Masse eine manuelle Migration dieser Dinge schneller ging.

What remains is the migration of mail groups, mailinglists and autoresponders. I can only offer the SELECT queries for those - it were that few that a manual migration was faster.

Mailgruppen und Autoresponder / mail groups and autoresponders:

SELECT
 mail . mail_name ,
 domains . name as domain ,
 mail.id as mn_id,
 mail . postbox ,
 mail . redirect ,
 mail . redir_addr ,
 mail_group ,
 mail . autoresponder  
FROM `mail` , domains
WHERE 
  mail . dom_id =domains . id  and 
 (mail.mail_group = 'true' or mail.autoresponder  = 'true' )

Mailinglisten / mailing lists:

SELECT MailLists.name, domains.name AS domain
FROM `MailLists` , domains
WHERE MailLists.dom_id = domains.id

Ich hoffe, dass diese Queries möglichst viele Leuten Helfen, vom nervigen Plesk wegzukommen. Wer will, darf sich gern durch Mitarbeit bei Postfixadmin (in Form von Programmierung, Übersetzungen oder Hilfe im Forum) revanchieren - mehr Infos gibt es bei Bedarf direkt bei mir (per Mail) oder auf der postfixadmin-devel Mailingliste. Und falls mir jemand auf die Schnelle ein paar Ostereier schicken will, bin ich bestimmt auch nicht böse - Adresse steht im Impressum ;-)

I hope that these queries help lots of people to get rid of the annoying Plesk. If you want, you can return the favor by helping on postfixadmin (by programming, translations or helping in the forum) - more details on request (just mail me) or on the postfixadmin-devel mailinglist. If someone wants to send me some easter eggs, I also won't object - my address is listed in the imprint ("Impressum").