How to Choose and Configure Your Mango Database (MySQL, PostgreSQL, MariaDB)

Overview

Mango by Radix IoT uses two databases: a SQL database for configuration data (users, roles, data sources, data point definitions, event detectors, events, audit history) and a separate time-series point value store for point values: by default Mango's own NoSQL store, or TimescaleDB or ClickHouse through the TSL module (see Where Point Values Are Stored below). This article covers choosing and configuring the SQL database. Changing db.type does not move or affect the point value store.

Prerequisites

• Admin access to the Mango host and the ability to edit mango.properties
• A database server you can administer, or the ability to have one provisioned
• A maintenance window — Mango must be stopped while the database type changes
• A full backup, including the existing H2 files, before changing anything on a system that holds real configuration

Database Types Mango Defines

Mango Core defines its SQL database types explicitly. The value you put in db.type has to be one of them, and the name is matched case-insensitively:

Core's type list carries a fifth entry, derby, and it is dead: nothing constructs a Derby connection, no Derby driver ships, and setting db.type=derby stops startup with an internal "Unknown/unsupported database type" error rather than a helpful message. It is named here only so that nobody who finds it in the source treats it as an option.

H2 (Embedded)

Mango ships with H2 as the default database. It requires no separate installation — it runs embedded inside Mango's Java process, which is exactly why it is the default: a new install works immediately with no external dependency.

What it gives you: zero setup, nothing to operate, and a database that moves with the Mango data directory.

What it does not give you: access from external tools while Mango holds the files, replication, or the operational tooling your organisation already has for a standalone database server. Backups mean taking a file copy with Mango stopped, or using Mango's own backup.

How to decide: the question is not a point count, it is whether you need any of the things H2 cannot do — external access, replication, independent backup and restore, or a database operated by a team that already runs MySQL or PostgreSQL. If you need them, move. If you genuinely do not, run H2 with a tested backup and restore procedure and revisit the decision when your requirements change.

MySQL Configuration (also the MariaDB path)

Step 1: Install the Database Server

Install MySQL using your OS package manager or official installer. Confirm the version is 5.7.8 or later.

Step 2: Create the Mango Database

CREATE DATABASE mango CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'mango'@'localhost' IDENTIFIED BY 'your_secure_password';
GRANT ALL PRIVILEGES ON mango.* TO 'mango'@'localhost';
FLUSH PRIVILEGES;

Mango creates its own tables, but it does not create the database or the user. Both must exist and be reachable from the Mango host before you start.

Step 3: Configure Mango

Edit mango.properties in the overrides directory:

db.type=mysql
db.url=jdbc:mysql://localhost:3306/mango?useSSL=false&allowPublicKeyRetrieval=true
db.username=mango
db.password=your_secure_password

Mango adds useUnicode and UTF-8 character encoding to the MySQL URL itself, so you do not need to set those. You can pass any other Connector/J property as a URL query parameter.

Step 4: Restart Mango

Mango creates the required tables automatically on first startup against a new, empty database.

PostgreSQL Configuration

Step 1: Install PostgreSQL

Install PostgreSQL using your OS package manager or official installer.

Step 2: Create the Database

CREATE USER mango WITH PASSWORD 'your_secure_password';
CREATE DATABASE mango OWNER mango ENCODING 'UTF8';
GRANT ALL PRIVILEGES ON DATABASE mango TO mango;

Step 3: Configure Mango

db.type=postgres
db.url=jdbc:postgresql://localhost:5432/mango
db.username=mango
db.password=your_secure_password

PostgreSQL JDBC driver properties can be set as URL query parameters. As with MySQL, the connector is bundled — there is nothing to download.

Step 4: Restart Mango

Mango creates the required tables automatically on first startup against a new, empty database.

Moving an Existing System

Everything above describes a new database. If the installation already holds configuration you care about, pointing db.* at an empty database and restarting is not a migration — Mango finds no schema, creates a fresh one, and starts as a brand-new installation with only the default admin.

To carry the existing contents across, Mango has a built-in conversion: configure the empty target under db.*, point convert.db.* at the existing H2 database, and Mango copies the Core and module schema tables during startup. Conversion runs only when convert.db.type is set and the target has no existing schema, and it must not be combined with a Mango upgrade. See How to Migrate from H2 to a Production Database for the full procedure.

Connection Pool and Query Settings

Mango's own database settings live in mango.properties and default conservatively:

• db.pool.maxActive / db.pool.minIdle — write connection pool (defaults 10 and 1)
• db.read.pool.maxActive / db.read.pool.minIdle — read connection pool (defaults 20 and 1)
• db.useMetrics and db.metricsThreshold — log slow queries above a threshold in milliseconds, which is the cheapest way to find out whether the database is actually your problem
• db.fetchSize, db.batchSize, db.batchDeleteSize — row fetch and batching behaviour, with type-dependent defaults

Mango does not publish required server-side tuning values, and the right settings for innodb_buffer_pool_size, shared_buffers, work_mem and friends depend on your hardware, dataset size, and query pattern. Size them from your own measurements and your database vendor's documentation, change one at a time, and confirm the effect with db.useMetrics before making the next change. Whatever you set, keep the server's connection limit above the total of Mango's main and read pools.

Read Replicas

Mango can direct reads to a read-only replica: set db.read.url and Mango sends reads there while writes continue to go to db.url. The read type and credentials default to the primary's values.

Where Point Values Are Stored

Point values never go in the SQL database. By default they go in Mango's own NoSQL time-series store. With the TSL module (Radix IoT's time-series database integration) they can go in TimescaleDB or ClickHouse instead. Mango uses one point value store at a time, chosen in mango.properties separately from db.type:

# TimescaleDB. ClickHouse uses the same keys under db.tsl.clickhouse. (default port 8123)
db.tsl.timescale.enabled=true
db.tsl.timescale.host=localhost
db.tsl.timescale.port=5432
db.tsl.timescale.db=radix_tsl
db.tsl.timescale.username=mango
db.tsl.timescale.password=<password>

Each store also has an order setting, and Mango initializes only the one with the highest priority. To confirm which store is in use, look for the line Mango writes to ma.log at startup, Time series database … initialized, which names it (for example MangoNoSqlPointValueDaoDefinition). If your history lives in TimescaleDB or ClickHouse, back that database up with its own tools: Mango's NoSQL backup settings cover only the NoSQL store.

Database Backup

Mango can back up the SQL database by shelling out to the database's own dump tool:

• db.backup.dump sets the dump executable — mysqldump for MySQL, pg_dump for PostgreSQL — and db.backup.extraDumpArgs passes extra arguments
• db.restore.path and db.restore.extraArgs do the same for restore
• The executable has to exist on the Mango host and be runnable by the Mango process
• Configure database-level backups with your organization's own tooling as well, and test a restore before you rely on either

The SQL database is only half the picture. Back up the point value store separately: the NoSQL store with How to Configure NoSQL Time-Series Backup Settings, or TimescaleDB and ClickHouse with their own tools.

Decision Guide

Choose on operational fit rather than on which database is more popular — Mango does not favour one over the other, and this article has no data on which is more widely deployed.

Verification

• ☐ ma.log shows startup completing with no database errors
• ☐ The database contains the Mango tables — users, roles, dataSources, dataPoints, eventDetectors, events, audit — plus module tables
• ☐ You can log in to Mango and create, save, and reload a configuration object
• ☐ Data sources are polling and points are receiving new values
• ☐ Historical data is still queryable — confirming the point value store was not disturbed
• ☐ No JDBC driver JAR was added to lib
• ☐ A backup runs successfully and a restore has been tested somewhere disposable
• ☐ The old H2 files are retained until you are confident

Troubleshooting

Summary

Mango defines h2, mysql, and postgres as its practical SQL database types; MariaDB rides on the MySQL type as a compatibility option that you should confirm and test rather than assume. H2 is the zero-setup default and is the right choice until you need something it cannot do — external access, replication, or independent backup and restore. For MySQL or PostgreSQL, create a dedicated database and user, set the four db.* properties, and restart; the connectors are already bundled, so there is no driver to download. If the installation already holds real configuration, use Mango's built-in convert.db.* conversion instead of simply repointing db.*. Tune from measurement, not from published numbers, and test a restore before going live.