Migrating postfix database logs to pfsense 2.2

marcelloc

If you have postfix log databases from previous versions (2.1, 2.0), follow these manual steps to read logs on 2.2

First install sqlite2 to able to read old log files

pkg install sqlite
rehash

then create and run the convert_postfixdb.php with this content


foreach (glob("/var/db/postfix/*.db") as $file) {
    $x=`head -c 48 $file`;
    if (preg_match("/This file contains an SQLite 2.1 database/", $x)){
        rename($file,"{$file}.sqlite2");
        echo "Converting $file to sqlite3 format...";
        `sqlite $file.sqlite2 .dump | sqlite3 $file`;
        echo "ok\n";
        }
?>


php convert_postfixdb.php

This convert script will not be native on postfix package as it may take too long depending on your db files and need old sqlite2 version to be installed

Current package version v.2.4.1, logs to database is not working on pfsense 2.2.

I'm working to fix it for v.2.4.2 as soon as possible.
Current status:

Database migrating (ok)
widgets (ok)
search mail tab (ok)
logs to database (work in progress…)

Once you have tested converted databases, you can remove old .sqlite2 database files from /var/db/postfix.

samgaw

Marcelloc

After upgrading to 2.2 with the current postfix package installed.

I gotta be doing something wrong, I followed your instructions on converting the DB files. However nothing seems to be converting. When I go into the postfix folder the original db file is still there with it's original name nothing has changed.

My logs are filling with the following over and over
Jan 25 11:45:53 postfix/master[62813]: warning: /usr/local/libexec/postfix/postscreen: bad command startup – throttling
Jan 25 11:45:53 postfix/master[62813]: warning: process /usr/local/libexec/postfix/postscreen pid 82492 exit status 1
Jan 25 11:45:52 postfix/master[62813]: warning: /usr/local/libexec/postfix/qmgr: bad command startup – throttling
Jan 25 11:45:52 postfix/master[62813]: warning: process /usr/local/libexec/postfix/qmgr pid 78765 exit status 1
Jan 25 11:45:52 postfix/master[62813]: warning: /usr/local/libexec/postfix/pickup: bad command startup – throttling
Jan 25 11:45:52 postfix/master[62813]: warning: process /usr/local/libexec/postfix/pickup pid 78653 exit status 1
Jan 25 11:44:53 postfix/master[62813]: warning: /usr/local/libexec/postfix/postscreen: bad command startup – throttling
Jan 25 11:44:53 postfix/master[62813]: warning: process /usr/local/libexec/postfix/postscreen pid 64885 exit status 1
Jan 25 11:44:52 postfix/master[62813]: warning: /usr/local/libexec/postfix/qmgr: bad command startup – throttling
Jan 25 11:44:52 postfix/master[62813]: warning: process /usr/local/libexec/postfix/qmgr pid 64812 exit status 1
Jan 25 11:44:52 postfix/master[62813]: warning: /usr/local/libexec/postfix/pickup: bad command startup – throttling
Jan 25 11:44:52 postfix/master[62813]: warning: process /usr/local/libexec/postfix/pickup pid 64728 exit status 1
Jan 25 11:43:53 postfix/master[62813]: warning: /usr/local/libexec/postfix/postscreen: bad command startup – throttling
Jan 25 11:43:53 postfix/master[62813]: warning: process /usr/local/libexec/postfix/postscreen pid 47579 exit status 1
Jan 25 11:43:52 postfix/master[62813]: warning: /usr/local/libexec/postfix/qmgr: bad command startup – throttling
Jan 25 11:43:52 postfix/master[62813]: warning: process /usr/local/libexec/postfix/qmgr pid 47536 exit status 1
Jan 25 11:43:52 postfix/master[62813]: warning: /usr/local/libexec/postfix/pickup: bad command startup – throttling
Jan 25 11:43:52 postfix/master[62813]: warning: process /usr/local/libexec/postfix/pickup pid 47299 exit status 1
Jan 25 11:42:53 postfix/master[62813]: warning: /usr/local/libexec/postfix/postscreen: bad command startup – throttling
Jan 25 11:42:53 postfix/master[62813]: warning: process /usr/local/libexec/postfix/postscreen pid 32677 exit status 1
Jan 25 11:42:52 postfix/master[62813]: warning: /usr/local/libexec/postfix/qmgr: bad command startup – throttling
Jan 25 11:42:52 postfix/master[62813]: warning: process /usr/local/libexec/postfix/qmgr pid 32657 exit status 1
Jan 25 11:42:52 postfix/master[62813]: warning: /usr/local/libexec/postfix/pickup: bad command startup – throttling
Jan 25 11:42:52 postfix/master[62813]: warning: process /usr/local/libexec/postfix/pickup pid 32385 exit status 1

Any suggestions?

Sam

marcelloc

I've updated database log integration from sqlite2 to sqlite3 but if I send a pull request for it then postfix will not work on 2.1 too.

So until we find a way to fix it on current pfsense 2.2 pbi, I suggest to use postfix on 2.1(as a server for exemple on virtual machine) :-\

samgaw

Looks like I'm downgrading to 2.1 then.

Thanks,

Sam