Postfix and Dovecot both support SQLite. However, what is missing is an easy-to-use administration interface. Postfix Admin does not support SQLite, and neither does any of the other Postfix web management interfaces (as far as I know).
Postfix Admin is small and efficient, and therefore a good way to manage a mail system on lightweight systems. SQLite is small and efficient, and therefore a preferred database engine for lightweight systems.
Is the data separated from the application by a network?
Not necessarily. Especially on smaller installations, Postfix and other software can all run on the same machine.
Many concurrent writers?
No. The database managed by Postfix Admin (and used by Postfix and other software) is often read, but writes are limited and rarely concurrent.
Big data?
The biggest heap of data is probably the users' email, which is not stored in the database but managed through the file system. Postfix databases as created by Postfix Admin are quite small, even for larger installations. So again, no.
Since all three answers can be no, SQLite is a possible alternative to the already supported MySQL and PostgreSQL database engines. I did some light tests on a Raspberry Pi (model B, 512 MB of memory). Running a MySQL instance idle takes 50 MB or 10% of the available memory. Aside from Postfix and Dovecot, other mail-related web services often also support SQLlite, such as Roundcube. This makes Postfix Admin the only piece of the puzzle which requires MySQL or PostgreSQL. On smaller systems which are only used by a few users, running a separate RDBMS is a waste of resources to just support a web-based email server administration system.
As a proof of concept, I created a patch which adds SQLite support to the latest stable version (Postfix Admin 2.92, SVN revision 1685). It relies on PHP's SQLite3 object-oriented database extension (available as php5-sqlite for Debian-based distributions and as php-sqlite for Arch-based distributions). The following functions are tested and work with SQLite, Postfix, Dovecot and Fetchmail:
Also added SQLite support to the third-party script ADDITIONS/fetchmail.pl to provide an interface with Fetchmail
Send Email
Broadcast message
Change password
Backup (locks the database, creates a .tar.gz file with the SQLite database file(s), and unlocks the database)
View Log (view per domain)
Logout
User login
Edit forwarding settings
Change password
Logout
Are the main developers of Postfix Admin interested in adding and maintaining support for SQLite as a backend database engine? If yes, what would be the best approach? I am willing to release my patch for the current stable version under the same license as Postfix Admin (GPL 2+) if it helps. It conforms to the coding guidelines of the wiki.
Last edit: Zepman 2015-05-28
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I prefer to use SQLite to have an indipendent database from mysql so when mysql server is busy for other tasks, postfix still can work without troubles, and is faster to backup a file than a database :D
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Why should Postfix Admin support SQLite?
Let's follow the checklist for choosing the right database engine, taken from: https://www.sqlite.org/whentouse.html
Is the data separated from the application by a network?
Not necessarily. Especially on smaller installations, Postfix and other software can all run on the same machine.
Many concurrent writers?
No. The database managed by Postfix Admin (and used by Postfix and other software) is often read, but writes are limited and rarely concurrent.
Big data?
The biggest heap of data is probably the users' email, which is not stored in the database but managed through the file system. Postfix databases as created by Postfix Admin are quite small, even for larger installations. So again, no.
Since all three answers can be no, SQLite is a possible alternative to the already supported MySQL and PostgreSQL database engines. I did some light tests on a Raspberry Pi (model B, 512 MB of memory). Running a MySQL instance idle takes 50 MB or 10% of the available memory. Aside from Postfix and Dovecot, other mail-related web services often also support SQLlite, such as Roundcube. This makes Postfix Admin the only piece of the puzzle which requires MySQL or PostgreSQL. On smaller systems which are only used by a few users, running a separate RDBMS is a waste of resources to just support a web-based email server administration system.
As a proof of concept, I created a patch which adds SQLite support to the latest stable version (Postfix Admin 2.92, SVN revision 1685). It relies on PHP's SQLite3 object-oriented database extension (available as php5-sqlite for Debian-based distributions and as php-sqlite for Arch-based distributions). The following functions are tested and work with SQLite, Postfix, Dovecot and Fetchmail:
Are the main developers of Postfix Admin interested in adding and maintaining support for SQLite as a backend database engine? If yes, what would be the best approach? I am willing to release my patch for the current stable version under the same license as Postfix Admin (GPL 2+) if it helps. It conforms to the coding guidelines of the wiki.
Last edit: Zepman 2015-05-28
On 27 May 2015, at 09:37, Zepman zepman@users.sf.net wrote:
I don’t feel that is a valid test. Who is running postfix and postfixadmin on a rPi?
Yes, mysql uses more resources, but it is an insignificant percentage of the resources required for my (very small) postfix setup.
26667 mysql 19 20 0 247M 50420K sbwait 1 56:57 0.00% mysqld
Yep. 0.00%
Hi - yes - more than happy to receive a patch.
Either attach it to sourceforge somehow, or you do it through github -- https://github.com/postfixadmin/postfixadmin
Thank you for your efforts.
Could you upload your patch somewhere i am really interested in it
Me too!
I prefer to use SQLite to have an indipendent database from mysql so when mysql server is busy for other tasks, postfix still can work without troubles, and is faster to backup a file than a database :D
I have a simple solution for you - upgrade to PostfixAdmin 3.0.2 which already includes SQLite support ;-)