---
title: "Databases on Linux: PostgreSQL or MariaDB, the client, TLS and the upgrade floor"
url: https://www.hmailserver.co.uk/topic/28247/databases-on-linux-postgresql-or-mariadb-the-client-tls-and-the-upgrade-floor
author: "Progressiverobot"
published: 2026-09-27T19:29:13.731Z
updated: 2026-09-27T19:29:13.731Z
category: "Linux"
tags: ["linux","database","postgresql","mariadb","tls"]
site: hMailServer Community Forum
---

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

*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](/topic/28277). 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](/topic/28278).*



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](/topic/28190).

### 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](https://gitlab.com/hmailserver/hmailserver/-/blob/v6.3.4/hmailserver/docs/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

1. **Create the role.** With `--createdb` the server creates the database itself in step 3:

   ```sh
   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:

   ```sh
   sudo -u postgres createuser --pwprompt hmailserver
   sudo -u postgres createdb --owner hmailserver hmailserver
   ```

2. **Write `[Database]`**: `Type=PostgreSQL`, the server, the database, the role, its password, and `Port=5432`.
3. **Create the schema**, as the service user:

   ```sh
   sudo -u hmailserver hmailserver --create-database
   ```

4. **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

1. **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".
2. **Install the client library** from the table above.
3. **Create the database**, or let the verb create it, and give the account the server connects with every privilege on it:

   ```sql
   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.
4. **Write `[Database]`**: `Type=MySQL`, the server, `Port=3306`, the database, the account and its password.
5. **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](/topic/28241).

### 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](/topic/28228). 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](/topic/28246) (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:

```sh
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](/topic/28235)).

**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](https://gitlab.com/hmailserver/hmailserver/-/blob/v6.3.4/hmailserver/docs/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](https://gitlab.com/hmailserver/hmailserver/-/wikis/Installing-on-Linux) and [Before You Install](https://gitlab.com/hmailserver/hmailserver/-/wikis/Before-You-Install), section 3.5, on the wiki.
- [DatabaseTls.md](https://gitlab.com/hmailserver/hmailserver/-/blob/v6.3.4/hmailserver/docs/DatabaseTls.md), with the Linux lab run of 25 September 2026 line by line; [Upgrading.md](https://gitlab.com/hmailserver/hmailserver/-/blob/v6.3.4/hmailserver/docs/Upgrading.md) for the 6102 step and the repair of a half-done one; [MigratingDatabaseBackend.md](https://gitlab.com/hmailserver/hmailserver/-/blob/v6.3.4/hmailserver/docs/MigratingDatabaseBackend.md) for why `utf8mb4`.
- The documentation page: [3.5 A database](https://www.progressiverobot.com/hmailserver-documentation/#35-a-database), [35.4 Creating the database](https://www.progressiverobot.com/hmailserver-documentation/#354-creating-the-database) and [35.13 Upgrading and rollback](https://www.progressiverobot.com/hmailserver-documentation/#3513-upgrading-and-rollback).
- On this forum: [First install on Linux](/topic/28190), [Upgrading to 6.3.4](/topic/28193) and [Moving to new hardware](/topic/28194); [choosing a database and encrypting the link](/topic/28228) for TLS on every backend, and [the hmailserver command line on Linux](/topic/28245) for the other verbs.