Feature Request: Install on top of existing postfix installation

[francwalter]

Hallo
I have Postfix and Dovecot since 2006 (?), installed it at this time I think on SuSE, maybe later on Ubuntu 8.04, dont remember well. I think I used this How-To (only in wayback machine to find).
Now I have Postfix v3.3.0 (exactly: 3.3.0-1ubuntu0.3) and Dovecot v2.3 (2.3.14-5+ubuntu18.04) running on Ubuntu 18.04.

Since long I would like to install Postfixadmin to manage Postfix and Dovecot, but the tables of Postfixadmin and Postfix are very different.
In my Postfix DB I have the tables virtual_domains, virtual_users, virtual_aliases and two views (view_users and view_users).
The columns are different too, here the Postfix db scripts:

CREATE TABLE `virtual_domains` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

CREATE TABLE `virtual_aliases` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `domain_id` int(11) NOT NULL,
  `source` varchar(40) CHARACTER SET latin1 NOT NULL,
  `destination` varchar(80) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`id`),
  KEY `domain_id` (`domain_id`),
  CONSTRAINT `virtual_aliases_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=465 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

CREATE TABLE `virtual_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `domain_id` int(11) NOT NULL,
  `user` varchar(40) CHARACTER SET latin1 NOT NULL,
  `password` varchar(32) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`),
  CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=114 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

The view "view_users" just contains email and password, the view "view_aliases" contains email and destination.

I asked in the Sourceforge Forum about that, another user with the same problem, but it seems that it could only be solved manually, with a lot of work (and risk too) 😢

It would be great if the INSTALL.txt and ./DOCUMENTS/... would provide install guide to this setting.
Even greater, if the Postfixadmin installer would consider the different databases in such settings.

Thanks.
frank

[DavidGoodwin]

I'm not quite sure what the data should look like, but perhaps :

MariaDB [postfix_legacy]> select * From virtual_aliases
    -> ;
+-----+-----------+--------+-------------------------+
| id  | domain_id | source | destination             |
+-----+-----------+--------+-------------------------+
| 465 |        29 | foobar | destination@example.org |
+-----+-----------+--------+-------------------------+
1 row in set (0.001 sec)

MariaDB [postfix_legacy]> select * from virtual_domains;
+----+---------------+
| id | name          |
+----+---------------+
| 29 | legacy.domain |
+----+---------------+
1 row in set (0.001 sec)

MariaDB [postfix_legacy]> select * From virtual_users;
+-----+-----------+-----------+----------+
| id  | domain_id | user      | password |
+-----+-----------+-----------+----------+
| 115 |        29 | test-user | somepass |
+-----+-----------+-----------+----------+
1 row in set (0.001 sec)

in which case perhaps, from within the postfixadmin database :

• insert into domain (domain, description, transport) select name, name, 'virtual' from postfix_legacy.virtual_domains;
• insert into mailbox (username, password, name, maildir, local_part, domain) select concat(user, '@', d.name), password, user, concat(d.name, '/', user), user, d.name FROM postfix_legacy.virtual_users INNER JOIN postfix_legacy.virtual_domains d ON postfix_legacy.virtual_users.domain_id = d.id;
• insert into alias (address, goto, domain) select concat(a.source, '@', d.name), a.destination, d.name FROM postfix_legacy.virtual_aliases a INNER JOIN postfix_legacy.virtual_domains d ON d.id = a.domain_id;

[DavidGoodwin]

I've added DOCUMENTS/Migration.md to master; it's basically the above. It probably contains bugs :-)

[francwalter]

Thank firstly a lot, I just came to it.
So I cleaned first my postfixadmin database by postfixadmin self in the website (delete the domains), db was then empty.
Then I tried your statements. The first two statements worked :)

1.:

INSERT INTO mailbox (username, password, name, maildir, local_part, domain) 
SELECT CONCAT(user, '@', d.name), password, user, CONCAT(d.name, '/', user), user, d.name 
FROM mailserver.virtual_users 
INNER JOIN mailserver.virtual_domains d 
ON mailserver.virtual_users.domain_id = d.id;

Query OK, 17 rows affected (0.00 sec)
Records: 17  Duplicates: 0  Warnings: 0

2.:

INSERT INTO mailbox (username, password, name, maildir, local_part, domain) 
SELECT CONCAT(user, '@', d.name), password, user, CONCAT(d.name, '/', user), user, d.name 
FROM mailserver.virtual_users 
INNER JOIN mailserver.virtual_domains d ON mailserver.virtual_users.domain_id = d.id;

Query OK, 38 rows affected (0.06 sec)
Records: 38  Duplicates: 0  Warnings: 0

But the 3. gives me an error:

INSERT INTO alias (address, goto, domain)
SELECT CONCAT(a.source, '@', d.name), a.destination, d.name
FROM mailserver.virtual_aliases a
INNER JOIN mailserver.virtual_domains d ON d.id = a.domain_id;

ERROR 1062 (23000): Duplicate entry 'frank@example.org' for key 'PRIMARY'

Oh, oh, is this a sign for a corrupt database of my good old mailserver??

[DavidGoodwin]

well, you already have an entry for 'frank@example.org'.

You could change the statement to 'INSERT IGNORE INTO alias ....' which would just ignore the error :)

[cboltz]

I'd recommend to look at the original database before ignoring errors. IIRC querying aliases may result in multiple rows, and while postfixadmin has multiple targets in one row (goto is foo@example.com,bar@example.com) your old database might have two rows with one target in each.