Backend migration#

/vg migrate-db copies the audit catalog from the active backend to another one. It is a console-only, one-way, confirmed operation.

When to migrate#

Migrate when SQLite is the bottleneck — /vg status shows a persistent queue depth with zero drops on a healthy backend — or when you want the database on a managed server with its own backups and replication.

Do not migrate as a routine maintenance step. Copying a large catalog takes time, blocks audit writes while it runs, and gains nothing if SQLite is keeping up.

Prepare the destination#

1. Create the empty database#

The target must exist, be reachable, and be empty — it must contain no VonixGuardian data. The migration job initialises the schema itself.

-- MySQL / MariaDB
CREATE DATABASE vonixguardian CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'guardian'@'127.0.0.1' IDENTIFIED BY 'CHANGE-ME';
GRANT ALL PRIVILEGES ON vonixguardian.* TO 'guardian'@'127.0.0.1';
FLUSH PRIVILEGES;

-- PostgreSQL
CREATE DATABASE vonixguardian;
CREATE USER guardian WITH ENCRYPTED PASSWORD 'CHANGE-ME';
GRANT ALL PRIVILEGES ON DATABASE vonixguardian TO guardian;

2. Add database.migrationTarget#

The command reads the destination credentials from config, not from the command line. Add the block without changing database.type — the server stays on its current backend.

"database": {
  "type": "sqlite",
  "file": "vonixguardian.db",
  "migrationTarget": {
    "type": "postgresql",
    "jdbcUrl": "jdbc:postgresql://127.0.0.1:5432/vonixguardian",
    "user": "guardian",
    "password": "CHANGE-ME"
  }
}

migrationTarget.type must match the type you will pass on the command line. A mismatch is refused rather than attempted.

3. Back up the source#

The migration reads from the live database. Take a backup first — if the copy fails partway the source is untouched, but a backup is what lets you retry without nervousness.

sqlite3 vonixguardian.db ".backup '/backup/vg-pre-migration-$(date +%F).db'"

Run the migration#

From the console (not in-game — the command refuses a player source):

/vg migrate-db postgresql

Without the confirm token this is a dry run. It prints what it is about to do and stops:

[VonixGuardian] migrate-db: about to copy every audit row from 'sqlite' to 'postgresql'.
[VonixGuardian] This is a one-way operation — the target must be at schema v9 and empty.
[VonixGuardian] To proceed, run: /vg migrate-db postgresql CONFIRM

Then confirm:

/vg migrate-db postgresql CONFIRM

Progress is printed per table:

[VonixGuardian] migrate-db: blocking new audit writes and waiting for the async pipeline to idle...
[VonixGuardian] migrate-db: opening destination backend (postgresql)...
[VonixGuardian]   vg_users                        4210 /     4210 rows  (312 ms elapsed)
[VonixGuardian]   vg_worlds                          12 /       12 rows  (318 ms elapsed)
[VonixGuardian]   vg_actions                    1834220 /  1834220 rows  (41220 ms elapsed)
[VonixGuardian] migrate-db: DONE. Copied 1838442 rows in 41530 ms.
[VonixGuardian] Update config.database.type to 'postgresql' (and matching connection fields) and restart the server to use the new backend.

Rows copy in 1,000-row chunks.

What "blocking writes" means#

During the copy the command enters a maintenance write-block: new audit events are refused admission, and it waits for the ring buffer, the worker's local batch, and any in-flight sink transaction to idle before it starts reading.

This is a best-effort snapshot barrier, not a global database freeze. It exists so the copy does not race rows being written underneath it. In practice it means:

• Events occurring during the migration are not recorded. Players can keep playing; their actions in that window will not appear in the log.
• Expect roughly 40 seconds per 1.8 million rows on local storage, longer over a network.

Preflight refusals, each with its own message:

The 30-second idle timeout is the one to plan for. On a busy server, run the migration during a quiet period or a maintenance window. If it refuses, the source database is unchanged and you can retry.

Switch over#

After DONE, the server is still running on the old backend. Switch it:

1. Edit config.json and set database.type plus the matching connection fields:

"database": {
  "type": "postgresql",
  "jdbcUrl": "jdbc:postgresql://127.0.0.1:5432/vonixguardian",
  "user": "guardian",
  "password": "CHANGE-ME"
}

2. Restart the server. database.* is restart-only; /vg reload will not apply it.

3. Verify on the new backend:

/vg status

The Storage section should report the new backend and health OK, and the Writer queue section should show a low depth with zero drops.

Rolling back the migration#

If the new backend turns out to be worse, going back is another migration in the other direction — not a simple config flip.

1. Point database.type back at the old backend and restart.
2. The old database still holds everything up to the moment the migration began.
3. Anything logged on the new backend since then is not in the old one.

That data gap is why the migration is described as one-way. Decide before you cut over, and keep the source backup until the new backend has proven itself for a few days.

Notes per backend pair#

SQLite BLOB columns become LONGBLOB on MySQL and BYTEA on PostgreSQL. NBT payloads larger than MySQL's 16 MB MEDIUMBLOB limit are why LONGBLOB is used rather than the smaller type.