Moving from PostgreSQL to MariaDB

In short. Up to version 0.2.0, Agora kept its Hub's data (members, forum, applications, settings, license) in a PostgreSQL database. Later versions use MariaDB. If you have an existing installation, the data is taken over during the first update to the MariaDB version: Agora reads the old database without changing it, copies everything into the new one, checks the copy, then starts. Your old database stays intact and, if the new version does not start, Agora puts the previous version back on it by itself. On Docker there is one thing to do first: run the installation command again. On a panel (egg) or a Node.js server, you update as usual.

Before you start #

• You are concerned if your installation dates from a version that used PostgreSQL. A new installation starts directly on MariaDB: this page is not about you, go to The two Agora databases.
• Make an Agora backup first, as a habit: Administration → Agora → Backups → Download a dump now, then keep the file off the server. That .sql taken on the old version cannot be restored on MariaDB (the format is not the same): it is a record, not a plan B. The real plan B is the old database, which the conversion does not touch (see below).
• Plan for a short outage: the site restarts during the update. Pick a quiet moment.
• Keep some disk space free: the old database stays in place and the new one is added next to it.
• The words (database, migration, dump) are in the glossary.

Steps #

Docker with install.sh #

1. Run the installation command again, as root (it keeps your key, your .env and your volumes):

   Copy

   curl -fsSL https://agorapanel.com/install.sh | sh

   The script prepares the move and says so: Passage à MariaDB préparé : /opt/agora/.env réécrit (l'ancien est gardé dans /opt/agora/.env.avant-mariadb). Ton site reste sur PostgreSQL jusqu'à la mise à jour vers la version MariaDB… ("Move to MariaDB prepared: … your site stays on PostgreSQL until the update to the MariaDB version"). Concretely, it adds an agora-mariadb container (new volume agora_mariadb), keeps your old agora-db container and its agora_db volume, and writes the old database's address into AGORA_CONVERT_FROM. If the launcher (boot.mjs) changed, it restarts Agora (Amorçage mis à jour : redémarrage d'Agora…). Your site keeps running on the old version and the old database.

2. Update Agora from Administration → Agora (see Update Agora): this is when the data is copied.

3. Watch the console (docker logs -f agora) during the update: see "You know it worked when…".

4. Do not cut the server during the copy. If it happens anyway, nothing is lost: see "If it does not work".

Panel (egg) or Node.js server #

1. Start the update from Administration → Agora (see Update Agora). The panel or server keeps its embedded PostgreSQL database (the .agora/pg folder); Agora copies it into its new embedded MariaDB database.
2. First pass with an old launcher. If your boot.mjs (or agora.mjs on a Node.js server) predates MariaDB, the update stops once while replacing it itself (signature checked): the console says cette version d'Agora tourne sur MariaDB : ton amorçage (boot.mjs) était trop ancien pour copier les données, il vient d'être mis à jour… Redémarre le serveur, puis relance la mise à jour ("this Agora version runs on MariaDB: your launcher was too old to copy the data and has just been updated… Restart the server, then run the update again"). Do it: restart, then run the update again. Nothing was changed in the meantime.
3. Watch the console during the copy: see "You know it worked when…".

Docker with a hand-written compose file #

The new compose file starts MariaDB in a new volume (mariadb_data). The old PostgreSQL volume (db_data) is neither read nor erased, except to take the data over, through a temporary service of the legacy profile.

1. Replace your compose file with the new one, and in .env replace POSTGRES_PASSWORD with a MariaDB password: MARIADB_ROOT_PASSWORD (long, letters and digits only).

2. Add to .env the old PostgreSQL password and the address of the old database:

   Copy

   POSTGRES_PASSWORD=<old PostgreSQL password>
   AGORA_CONVERT_FROM=postgresql://agora:<old PostgreSQL password>@db-pg:5432/agora

3. Start the old database as well as the new one, then Agora:

   Copy

   cd /opt/agora && docker compose --profile legacy up -d

4. Follow the log: docker compose logs -f agora. The conversion runs once, on the first start.

External database (advanced option) #

If you had brought your own PostgreSQL database through DATABASE_URL:

1. Create an empty MariaDB or MySQL 8 database, reserved for Agora, in utf8mb4, and its user.
2. Replace DATABASE_URL with its address: mysql://user:password@host:3306/database.
3. Add AGORA_CONVERT_FROM with the old PostgreSQL address (postgresql://…), then restart or run the update: the takeover happens as above. The old database is only read.

You know it worked when… #

• The console shows, in this order: ✓ schéma à jour, reprise des données de l'ancienne base PostgreSQL (elle n'est pas modifiée)…, the list of copied tables, vérification…, reprise terminée : 59 tables, … lignes, … membre(s) — l'ancienne base est intacte., then ✓ données reprises … and finally ✓ Agora … répond : mise à jour confirmée (the console is in French).
• Your Hub's /api/health address answers "ok": true and "db": "ok".
• In Administration → Members, you find all your members, with the same number of administrators as before; the forum, applications and your settings are there.
• Your license is active without doing anything: the installation keeps the same identity for the license server, no seat is taken (see Free a license seat only if the "seats taken" message shows up anyway).

If it does not work #

In every case below, your old database was not modified, the new database is emptied before starting again, and the previous version stays (or comes back) in service: the console says mise à jour impossible : … then la version … reste en place.

Going further #

Finding the duplicate values #

The refusal message names the table and the column (for example User(email)) and only shows the beginning of the values. To find the pair in the old database (replace User and email with what the message says):

docker exec agora-db psql -U agora -d agora -c 'SELECT lower(email), count(*) FROM "User" GROUP BY 1 HAVING count(*) > 1;'

Without Docker, run the same query in your PostgreSQL. Fix one of the two values, then run the update again: until that is done, nothing is copied.

What happens during the conversion #

1. Agora first checks, before copying anything, that no value hides another one for MariaDB (the case, accent or trailing-space duplicates described above).
2. It opens the old database read-only and freezes it at a given instant: even if someone were still writing, it copies a consistent state.
3. It copies the tables in the right order (the links between tables are respected).
4. It checks the copy: row count of each table, fingerprint of each row, no orphan row, same number of members. It also carries over the database's identity (db_instance_id), which keeps the license fingerprint.
5. It leaves a mark in the new database so as to never redo the conversion on the next start, then launches Agora.

If a step fails, Agora empties the new database, puts the previous version back on the old database and says so; the old one was never written to.

How long it takes #

Measured on the test bench: a few seconds for an ordinary installation (59 tables, a few hundred rows). The duration follows the number of messages and rows; we have not measured a very large forum, so allow more time and keep the console open. The site is unavailable during the update. Do not cut the server: if you do, the copy starts over from zero, losing nothing.

Going back #

• During the update: it is automatic. If the copy fails, or if the new version does not start, Agora puts the previous version back on its old database by itself (message repli vers … : la version … n'a pas démarré). What was written before the update is intact there, and if you then run the update again, the copy is redone from scratch.
• Once the new version is confirmed (mise à jour confirmée), there is no automatic way back: what you write afterwards exists only in MariaDB, it is not copied back into the old database. If you really must go back, open a ticket (Open a support ticket): the old database and your old .env are kept for that.

What is kept, and for how long #

Agora never deletes the old database by itself: it stays until you delete it.

• Docker with install.sh: the agora-db container and its agora_db volume (the old database), and the /opt/agora/.env.avant-mariadb file (your old .env, with the old password: readable by root only).
• Docker with a hand-written compose: the db_data volume, and the POSTGRES_PASSWORD and AGORA_CONVERT_FROM lines of your .env.
• Panel or Node.js: the .agora/pg folder (the old database) and .agora/pg-portable (the PostgreSQL program). Do not delete .agora/pg-converted.json: it is the "already converted" mark.
• Pre-update backups (pre-update-…): Agora keeps the last five.

When to delete the old database #

Once you have lived a few days on MariaDB without trouble and made an Agora backup of the new database (Administration → Agora → Backups, downloaded off the server). Then:

• Docker with install.sh: remove the AGORA_CONVERT_FROM and POSTGRES_PASSWORD lines from /opt/agora/.env, run the installation command again (it rewrites the compose file without the old database), then cd /opt/agora && docker compose up -d --remove-orphans. When you are sure, delete the volume: docker volume ls gives its full name (it ends in agora_db), then docker volume rm <name>. Also delete .env.avant-mariadb.
• Docker with a hand-written compose: docker compose --profile legacy down, remove the AGORA_CONVERT_FROM and POSTGRES_PASSWORD lines from .env, then delete the db_data volume (docker volume ls gives you its full name).
• Panel or Node.js: delete the .agora/pg and .agora/pg-portable folders to free the space.

What changes for you after the conversion #

• The forum search finds whole words or word beginnings ("dealer" finds "dealership"), ignoring accents and capitals. It no longer reduces words to their root: if you type the full plural "cars", it will not find "car".
• Usernames are compared without accents or capitals: "Élise" and "Elise" are the same name. This avoids look-alike usernames.
• Agora's backups are made without any external program and are in a different format: your old PostgreSQL .sql files can no longer be restored. See Backups and restore.
• The license does not move: same identity, same seat.
• Nothing else: the Hub, MDT, live map, dealership and the rest work as before. Your game server's database is never touched by this conversion.