Database setup for PostgreSQL, MySQL, SQLite and Oracle

PostgreSQL Setup

First you need to create the postgresql user dbmail is going to use.

createuser -U postgres dbmail

Note that this command can only be performed by user postgres or another PostgreSQL user with the privileges to create users and and databases. You will need to give the user 'dbmail' permission to create a database.

You can use another username than dbmail. You can set a password for the user by doing a:

ALTER USER dbmail WITH PASSWORD '<password>';

In a postgresql console, with <password> replaced by the actual password you want to use. After doing this you should create the database for dbmail:

createdb -U dbmail dbmail

The first dbmail is the user you just created, the second the name of the database. Of course you can use an other databasename. After setting up the user and database it's time to create the tables, do a: You can find the create_table.pgsql script either in the source under sql/postgresql or when using a package under /usr/share/docs/dbmail-pgsql/examples.

psql -U dbmail dbmail < sql/postgresql/create_tables.pgsql

Don't forget to start postgresql with the -i option, so it accepts TCP/IP connections.

Note: autovacuum is highly recommend.

MySQL

Please read the whole section before performing any actions!

First, you need to decide whether you want a new DBMail database or you will upgrade from an existing installation. If you do not have DBMail already and do not have a database for DBMail yet, you'll need to create the DBMail database in MySQL. You can do this by issuing the following command. Note that you will be prompted for the MySQL root password.

mysqladmin create dbmail -u root -p

This creates a database with the name “dbmail”. Now you have to give a non-root user access to this database. Start the MySQL command-line client as root:mysql -u root -p and enter the following command:

GRANT ALL ON dbmail.* to dbmail@localhost identified by '<pass>';

Where <pass> should be replaced with the password you want for the dbmail user. After this step, the database is ready to be used by the dbmail user. The next step is the creation of the database tables used by DBMail. Log out of the MySQL client and run the following command from the command line. You will have to enter the password you set in the previous step. You can find the create_table.mysql script either in the source under sql/mysql or when using a package under /usr/share/doc/dbmail-mysql/examples/.

Please note, file create_tables.mysql is not included on the standard Ubuntu feisty package.

mysql -u dbmail dbmail -p < create_tables.mysql

If you use Debian stable and the package from the stable distribution, use this command:

zcat /usr/share/doc/dbmail-mysql/examples/create_tables.mysql.gz  | mysql -u dbmail dbmail -p

You can also use '-u root' instead in the command if you want to use your root access again. Remember to change your MySQL root password using mysql_setpermission if have not done this yet!

After this, the DBMAIL tables will have been created and we can go on to the compilation and installation of DBMail itself.

Note: If you use Fedora/RHEL/CentOS, the default settings for an innodb database are inadequate for a busy dbmail server. The following settings should be made to /etc/my.cnf:

• innodb_buffer_pool_size should be 50-80% of available memory (defaults to 8M)
• innodb_log_file_size should be 25% of innodb_buffer_pool_size (defaults to 2M)

Also check the information (here and MySQL documentation) about using innodb_file_per_table option in the InnoDB section of /etc/mysql/my.cnf.

These changes should be made before creating the dbmail database. The innodb_log_file_size setting is difficult to change later. Not making these changes will result in database deadlock and rollback issues with possible data loss!

If you already have an older version of DBMail you should check the relevant Wiki pages about upgrading and you should consider using the scripts update_headervalue_01.mysql and 2_1_7-2_2_0.mysql depending on your existing version of DBMail. See the scripts for information about the changes before running them! You should consider migrating by creating a new database rather than converting your existing one, especially if you use special options for fine tuning your InnoDB and MySQL.

For more information, please see the MySQL Notes page.

Note: Since some DBMail tables can get VERY large (depending on your mail server load), we advise using InnoDB as database storage backend. As of version 2.1.X using InnoDB is required.

SQLite

SQLite is supported with dbmail from version 2.1.7 upwards.

The name of the file holding the database is specified in the [DBMAIL] section of the dbmail.conf file, as follows:

# 
# Database name.
#
db                   = /path/to/dbmail/database

Note that the directory where the database is located must be writable by the dbmail user, since SQLite needs to create a journal file in this location.

Oracle

DBMail supports Oracle although it's outside current testing.