Databases on Linux: PostgreSQL or MariaDB, the client, TLS and the upgrade floor

Progressiverobot

Written for 6.3.4. hMailServer 6.3.7 (17 October 2026), a security release, keeps the schema at 6108: an upgrade from 6.3.6 changes no table, from 6.3.5 or older the upgrade runs 6.3.6's three one-way steps to 6108, and from 6.3.3 or older 6.3.4's steps come first. On MySQL and MariaDB an insert that forgives a duplicate key now forgives that alone and any other failure is reported (HM5032), where it was taken as done with no row written, and a lost connection is reconnected and the statement run again, so the statement after a database restart, a failover or wait_timeout no longer fails; an error log that was quiet may now name failures that were hidden. See the 6.3.7 announcement. hMailServer 6.3.8 (2 November 2026), a security release, adds no schema step, so an upgrade from 6.3.6 or 6.3.7 changes no table. With TlsMode=verify-full on MySQL and MariaDB it checks the database server's certificate against the CA file TlsCaFile names, where in 6.3.4 to 6.3.7 a name holding a character outside ASCII reached the client as no CA file (work item 112); and through MariaDB Connector/C it refuses verify-full without TlsCaFile to a database server on this machine (127.0.0.1, ::1, localhost, an empty Server= or a Server= list that holds one) with HM7535, where Connector/C 3.4.1 and later checked neither that certificate's issuer nor its name (work item 113): before upgrading, name in TlsCaFile a CA that issues nothing but the database's certificates, the one that issued its certificate, or set TlsMode=require. The distribution's client should be MariaDB Connector/C 3.4.11 or later; where it is older and not patched, name the CA in TlsCaFile. On a server that ran under a LANG that is not UTF-8, rename a TlsCaFile named outside ASCII to its UTF-8 name before upgrading. The container image now carries MariaDB's client, so a container told MySQL reaches MariaDB. See the 6.3.8 announcement.

A Linux server keeps its accounts, domains, settings and message metadata in PostgreSQL or in MySQL/MariaDB, and in nothing else. There is no built-in database on Linux, hmailserver --create-database refuses SQL Server and SQL Server Compact by name, and SQL Server is refused on Linux as HM6390, because ADO is Windows. This topic covers the choice between the two, the client library each needs, the [Database] section, each backend's set-up step by step, TLS to the database, and what the schema upgrade asks of MySQL and MariaDB. The one thing to know first: since 6.3.4 every table on MySQL and MariaDB is utf8mb4, which needs MySQL 5.7.9 or MariaDB 10.2.2, or later. On an older server the create script stops at its first table, and an upgrade from 6.3.3 stops at its first step, 6040 to 6041, with error 1071, having changed nothing. Check the version before anything else. The first install as a whole is First install on Linux.

Choosing between the two

	PostgreSQL	MySQL / MariaDB
`[Database] Type`	`PostgreSQL`	`MySQL` (MariaDB is `MySQL`)
`Port`	`5432`	`3306`
Proven on	PostgreSQL 18 for the first Linux proof; PostgreSQL 17 in the TLS lab	MySQL 5.7.44 and 8.4.11, MariaDB 10.6.28, 11.4.13 and 11.8.9, on 25 September 2026
Client library	`libpq`, pulled in by the package's own dependencies	`libmariadb.so.3`, opened at run time, installed by you
Upgrade steps that ship	every step from `5001` onwards	every step, back to schema `0`
Transactions	DDL is transactional, so a failed schema step rolls back cleanly	used only if every table reports InnoDB at connect time
TLS with nothing set	libpq's own `prefer`	`prefer` since 6.3.4; never encrypted through MariaDB Connector/C before

MigratingDatabaseBackend.md calls PostgreSQL the best-behaved backend in this codebase, and gives "you already run it" as the reason for MySQL or MariaDB. Both are proven live on Linux: schema created, account made, message delivered and read back. That the PostgreSQL steps begin at 5001 is not a gap, because PostgreSQL was not a backend before hMailServer 5. Moving between the two later is not an upgrade, and the chain does not do it: it is a new installation against the new backend and a migration.

The client library to install

The .deb and the .rpm declare their library dependencies automatically (dpkg-shlibdeps for the .deb, rpm's own auto-requires), so libpq is pulled in with the package. MySQL and MariaDB are different: the server opens libmariadb.so.3 at run time rather than linking it, so the wiki tells you to install the client library yourself.

Distribution	Package
Debian, Ubuntu	`libmariadb3`
Fedora, RHEL	`mariadb-connector-c`
Arch	`mariadb-libs`

Where there is no libmariadb, the server loads Oracle's libmysqlclient. Only Oracle's 8.x client is given TLS options: with any other, require and verify-full are refused with the version it reports, rather than claimed. Oracle's libmysqlclient.so.24, MySQL 8.4's client as Ubuntu 26.04 packages it, is not among the names the Linux server loads. On Windows the client is bundled, as libmysql.dll; it is the same MariaDB Connector/C: 3.4.9 in the x64 installers of 6.2.3 to 6.3.6, and 3.4.11 in 6.3.7's, built from MariaDB's source, with seven authentication plugins where 6.3.6's had nine. The Linux server loads the distribution's libmariadb.so.3, which the distribution patches.

The `[Database]` section

The file is root's, so edit it with sudoedit /etc/hmailserver/hMailServer.ini.

Key	What to write
`Type`	`PostgreSQL` or `MySQL`
`Server`, `Database`, `Username`, `Password`	Where the database is and who the server connects as. The Linux package's template reads a password typed with `Passwordencryption=0` as plain text
`Port`	Written out, `5432` or `3306`: it goes into the connection string as it stands, and there is no useful default
`TlsMode`, `TlsCaFile`	Since 6.3.4, optional (below)
`UpgradeOnStart`	Since 6.3.4: `1` lets the service bring its own schema forward as it starts. Off by default, and not needed on a package installation, whose post-install step runs the upgrade
`NumberOfConnections`	The pool that deliveries, sign-ins and the rest share; five as shipped

These keys are read at the service's start, and are in the file rather than the settings store because they say how the store is reached. sudo -u hmailserver hmailserver --check-config prints the database type it made of what you wrote, without opening the database; Database type: 0 means the Type key did not take.

PostgreSQL, step by step

Create the role. With --createdb the server creates the database itself in step 3:
```
sudo -u postgres createuser --pwprompt --createdb hmailserver
```
If you would rather the service role could not create databases, which the wiki calls the tidier policy, leave the flag off and create the database yourself:
```
sudo -u postgres createuser --pwprompt hmailserver
sudo -u postgres createdb --owner hmailserver hmailserver
```
Write [Database]: Type=PostgreSQL, the server, the database, the role, its password, and Port=5432.

Create the schema, as the service user:

sudo -u hmailserver hmailserver --create-database

If TLS is to be required, that is the database server's to say: ssl=on and a certificate in postgresql.conf, and hostssl lines in pg_hba.conf.

MySQL or MariaDB, step by step

Check the version against the floor. The reason is key size: a key over a varchar(255), an account's address or a folder's name, is 1020 bytes at four bytes a character, more than an InnoDB key part may be in the older COMPACT rows (767 bytes). MySQL 5.7.9 and MariaDB 10.2.2 are the first releases whose InnoDB, as it ships, takes a key part of up to 3072 bytes and makes its tables DYNAMIC. On a MySQL 5.7 set up as 5.6 shipped, the error is 1071, "Specified key was too long; max key length is 767 bytes".
Install the client library from the table above.
Create the database, or let the verb create it, and give the account the server connects with every privilege on it:
```
CREATE DATABASE hmailserver CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
GRANT ALL PRIVILEGES ON hmailserver.* TO 'hmailserver'@'%';
```
Every table names its own character set, so a database made with another default still works. But the upgrade steps create, alter and index tables, and the 6102 step also changes the database's own default character set, which needs the ALTER privilege on the database itself; ALL PRIVILEGES ON <database>.* has it. A database the server creates itself is made utf8mb4 already. Make sure every table ends up InnoDB: the server issues transactions only if they all report it at connect time, and silently does not if any does not.
Write [Database]: Type=MySQL, the server, Port=3306, the database, the account and its password.
Create the schema: sudo -u hmailserver hmailserver --create-database.

Why utf8mb4. MySQL's utf8 is utf8mb3, three bytes a character, so nothing outside the Basic Multilingual Plane fits in it: no emoji. Before schema 6102 such a character in a contact, a folder or a signature was refused with error 1366 under strict mode, and made a question mark where strict mode is off. utf8mb4_general_ci is the twin of utf8_general_ci, the collation the tables had, so an address or a name compares as it did. What it does not do is tell characters outside the Basic Multilingual Plane apart: two address books of one account whose names differ only in an emoji are one name to its unique index.

What `--create-database` does

The package installs the server enabled and stopped, on purpose: there is no database yet, and the schema is made afterwards with this verb. If the database is not there, the verb connects to the server [Database] names with no database selected, creates it in the backend's own dialect and reconnects; if it is there and empty, it uses it. Either way it then runs the create script for the backend's type, which is what DBSetupQuick does over COM on Windows, and a database 6.3.4 creates is stamped 6104. Since 6.3.4, says the wiki's Installing on Linux, a database that already holds this build's schema, or an older one, is left as it is with exit 0, and a newer one exits 1; 6.3.3 refused any database that already held a schema. The documentation page (35.4, 35.7) still says a schema is refused, exit 1: test it on a scratch database first.

Two things follow a create, both since 6.3.4. The database, an empty one you created first included, is marked for a new installation's secure defaults, which the systemd unit's first start applies if it comes within a day; a database that already held a schema is not marked. And run as the service user on a package installation, the verb cannot write the root-owned configuration, so it makes no stored-secrets key and says what to run instead, sudo hmailserver --create-stored-secrets-key. Export that key and keep the file with the database dumps, apart from them: a dump restored without it brings back every second factor and [Settings] secret unopenable. Both are covered in full in Hardening a public mail server.

TLS to the database (since 6.3.4)

Before 6.3.4 the connection to MySQL and MariaDB through MariaDB Connector/C, the client Ubuntu packages, was never encrypted, and a database account made with REQUIRE SSL refused the server. Since 6.3.4 two [Database] keys choose the behaviour on every backend (on PostgreSQL the older PostgreSQLSslMode and PostgreSQLSslRootCert win when they are set), and are read at the service's start: TlsMode, one of disable, prefer, require or verify-full (empty is prefer: TLS when the server offers it, the certificate unchecked), and TlsCaFile, a PEM file of CA certificates that verify-full checks against:

[Database]
TlsMode=verify-full
TlsCaFile=/etc/hmailserver/db-ca.pem

Only verify-full checks the certificate, and only it protects against someone on the path who can alter the traffic. On Linux, MySQL and MariaDB take the CA file or the system's store, /etc/ssl/certs, and which client library is loaded matters: of Oracle's clients only 8.x is given TLS options (above). On PostgreSQL, verify-full with no TlsCaFile is sslrootcert=system on Linux, with libpq 16 and later. What each mode does, the refusals (HM7530, HM7531), what each backend did before 6.3.4 and does now, and what is thinner than it could be are in Choosing a database, creating it, encrypting the link. The server cannot make the database server refuse plaintext: that is REQUIRE SSL on the MySQL account, or hostssl lines only in pg_hba.conf.

Try it before the service is given it. --check-database makes one connection with the TLS settings in the configuration, prints the result, and exits 0 connected, 1 not, 2 not configured. It changes nothing, so it runs beside a running service:

$ sudo -u hmailserver hmailserver --check-database
Configuration: /etc/hmailserver/hMailServer.ini
Database: MySQL/MariaDB on 192.0.2.20:3306, database hmailserver, as hmailserver
TLS: TlsMode verify-full, the certificate checked against /etc/hmailserver/db-ca.pem
Connected. MySQL/MariaDB: encrypted with TLS (TLS_AES_256_GCM_SHA384), the server's certificate checked (TlsMode verify-full)

The application log says the same of the running service's connection, in a line that begins Database connection:.

In a container (no image of 6.3.4 is published, so one you build from the .deb) the entrypoint writes [Database] from the environment at every start, so there HM_DB_TLS_MODE and HM_DB_TLS_CA set these, not a line in the file.

Upgrading the schema, and the floor

On a package upgrade the maintainer scripts run hmailserver --upgrade-database as the service user, only when a database is configured, then try-restart the service if it was running. A failure of the schema upgrade is reported and does not fail the package, so read what it printed. The verb is safe to run by hand, and says "nothing to do" when done:

sudo -u hmailserver hmailserver --upgrade-database

The chain is applied one step at a time from whatever version the database reports, which select value from hm_dbversion answers. A database from a newer build is refused, and a server refuses to start on a database that is too old or too new for it. Take the dump first, with pg_dump or mysqldump, and the data directory with it: the upgrade is one-way, and rolling back is restoring that dump and reinstalling the older package (Upgrading from 5.x, 6.2 or 6.3: rolling back).

From 6.3.3 to 6.3.4 the schema moves from 6040 to 6104 in 64 steps. On PostgreSQL step 6102 only moves the version. On MySQL and MariaDB it converts every table to utf8mb4 under utf8mb4_general_ci, InnoDB and ROW_FORMAT=DYNAMIC:

What	Detail
Time and disk	It takes as long as copying the database. Each table is rebuilt in turn, and `hm_messages`, `hm_message_metadata`, `hm_messageindexterms`, `hm_messagerecipients` and `hm_audit` take the time: one to five seconds on an empty schema, minutes or more on a large installation. Have free disk space of the order of the largest table
The floor, error 1071	Steps 6041, 6101 and 6102 each open with a statement that needs a long key; 6041 and 6101 make and drop a probe table, `hm_upgradeprobe`. On a server older than MySQL 5.7.9 or MariaDB 10.2.2 the upgrade stops at 6040 to 6041 with error 1071, and nothing has been changed. Upgrade the database server, then the schema
The privilege, error 1044	The step changes the database's default character set, which needs ALTER on the database itself, not only on its tables
A duplicate, error 1062	Two rows that the old collation told apart and the new one does not, under one unique index, stop the step; the error names the index and the value. The wiki's examples are `straße` and `strase` in one address book on MariaDB 11.8, and on MySQL 8.4 two names that differ only in an emoji. Rename or remove one and run the upgrade again: every statement of the step can run twice
Afterwards	A `text` column may read `mediumtext`, and a `mediumtext` one `longtext`. The server reads both alike

Until 26 September 2026 steps 6041 and 6101 made their first tables before the index that failed, so an unreleased build from before that day could leave a database half done at 6040 or 6100. No upgrade path repairs that; Upgrading.md gives the DROP TABLE statements that do.

Two nodes on one database are serialised step by step if they start together: on PostgreSQL by the version row, which each step locks for the length of its transaction, and on MySQL and MariaDB by a named lock, GET_LOCK('hmailserver_schema_upgrade'), held until the step is over and waited on for up to four hours. A server that refuses GET_LOCK altogether, MariaDB Galera with wsrep on, still upgrades with the row lock alone, and the output says so for each step: there, upgrade from one node at a time.

Where to read more

Installing on Linux and Before You Install, section 3.5, on the wiki.
DatabaseTls.md, with the Linux lab run of 25 September 2026 line by line; Upgrading.md for the 6102 step and the repair of a half-done one; MigratingDatabaseBackend.md for why utf8mb4.
The documentation page: 3.5 A database, 35.4 Creating the database and 35.13 Upgrading and rollback.
On this forum: First install on Linux, Upgrading to 6.3.4 and Moving to new hardware; choosing a database and encrypting the link for TLS on every backend, and the hmailserver command line on Linux for the other verbs.