Operate PostgreSQL

This page is for the operator: how the server connects to its two databases, which roles it needs, what a world save is, and what it is not. Entity mapping and data access are in Entities and data access; schema changes are in Migrations. The two databases and their target names are listed in Two databases, four names each.

Connections

Each database has one connection_string. It holds a PostgreSQL URI or a reference to an environment variable. A newly generated configuration uses:

[persistence]
auto_sync_schema = false

[persistence.accounts]
connection_string = "postgres://moongate:moongate@localhost:5432/auth"

[persistence.realm]
connection_string = "postgres://moongate:moongate@localhost:5432/world"

These defaults are for local development. Existing TOML files are never rewritten.

For a deployment, replace the Accounts value with "$MOONGATE_ACCOUNTS_DATABASE" and the Realm value with "${MOONGATE_REALM_DATABASE}", and set each variable to a URI such as postgres://runtime:password@db:5432/moongate_realm?sslmode=require through your service manager or secret provider. Merely exporting those variables does not override a literal URI in the file. $NAME and ${NAME} references expand once, without treating the result as a filesystem path or re-expanding the substituted value. An undefined variable fails initialization for every configured connection, including a database without entities or SQL files.

URI rules:

• Both postgres:// and postgresql:// are accepted. The port defaults to 5432. Bracket IPv6 hosts: postgres://runtime@[::1]/moongate_realm.
• Percent-encode reserved characters in usernames, passwords and database names: @ becomes %40, # becomes %23, a literal $ becomes %24. Values are decoded once; a + remains a literal plus.
• Query options include sslmode, connect_timeout, application_name, search_path and Npgsql option names. Unsupported options or SSL modes fail validation.
• Native Npgsql key=value; strings are also accepted, for direct library use.

Startup checks

Normal startup opens each database and runs SELECT 1, regardless of server mode or registered entities. A success logs Postgres connection successful with the target and endpoint, without credentials. A connection or ping failure aborts startup before other services start. Moongate does not create databases.

After the pings, startup validates the versioned SQL history of each active database, including data-only migrations, and then compares the registered entity mappings with the schema. Pending, changed or missing applied files, or an entity that needs DDL no migration provides, prevent services from starting. Apply the reviewed SQL with the migration runner while the server is stopped; see Migrations.

A connectivity ping does not activate entity mappings or migration checks for a database that has nothing registered. Run the runner’s status --target ... during deployment to check the history of a database that has become empty. A registered entity always activates its database and its migration checks.

auto_sync_schema defaults to false and should stay false. When true, startup applies unversioned schema changes directly and records no migration history. Use it only for a disposable development database.

Separate DDL and runtime roles

Give normal processes a runtime connection. A one-shot schema job uses the same connection_string setting with a schema-role URI for the same database. Its separate TOML can reference a schema-only environment variable:

[persistence.realm]
connection_string = "$MOONGATE_REALM_SCHEMA_DATABASE"

Only that administrative process receives the schema credential; normal hosts receive the runtime credential. There is no separate schema-connection setting in server configuration.

The schema role owns the entity schemas and performs DDL. The runtime role needs database CONNECT, entity-schema USAGE, SELECT, INSERT, UPDATE and DELETE on entity tables, and USAGE and SELECT on the entity sequences (SELECT lets a backup read their values). Configure default privileges for later plugin tables. For moongate_migrations, the runtime role needs only schema USAGE and SELECT on moongate_migrations.history; never grant it history writes. Set these grants as the owner after the first apply, or pre-provision the schema and default SELECT privileges before it. Moongate does not grant privileges silently. The one login and two game instances example demonstrates separate roles, grants and schema jobs.

World saves

A world save captures the state the game loop owns and upserts it, one transaction per active database. It is how the server persists what happened since the last save; it does not delete rows that left the snapshot.

Periodic saves default to every 300 seconds:

[world_save]
enabled = true
interval_seconds = 300

A save does not stop the game

Players keep walking, talking and fighting while the world is saved: no “the world is saving, please wait”, no frozen screen. A save runs in three steps, and only the first one holds the game loop:

1. Capture, on the loop. Each live entity is copied into a detached snapshot, in memory. On a development world of 173,000 entities (144,000 items, 29,000 NPCs) this takes about 0.1 seconds, once every five minutes.
2. Fingerprint, in the background. Each snapshot gets a fingerprint, compared with the one of the last committed save; about a second for the same world, while the game goes on.
3. Write, in the background. Only the entities that changed are written to PostgreSQL, in one transaction per database. With the world quiet that is a few hundred rows instead of 173,000; every twelfth save (once an hour) writes everything again, about 14 seconds on that world, still in the background.

A save that fails rolls back as a whole and is retried in full by the next one; see Live world snapshots for the rules.

enabled = false disables the periodic request while keeping explicit and final saves available. Concurrent requests join the active save; cancellation stops only that caller’s wait. An eligible shutdown runs a final capture after accepted work drains, which is why Ctrl+C must be allowed to finish. A failed startup or a faulted game loop cannot promise that final save, and a critical persistence failure blocks later saves until the host is restarted; see Live world snapshots for that rule.

Database backups

A world save is an application snapshot, not a database backup. For backups Moongate writes SQL exports of the databases a process owns:

Server mode	Files
standalone	auth_<date>.sql and world_<date>.sql
login	auth_<date>.sql
game	world_<date>.sql

<date> is the UTC time of the backup, yyyyMMdd_HHmmss; a second backup in the same second gets _2 after the date. The files go to backups under the server root, and only the newest five of each database are kept. All of this is set in [sql_backup].

A backup runs on the schedule when sql_backup.enabled is true, and at once with the sql_backup command.

What a backup does

1. With the game role, it saves the world and waits for the save to finish. If the save fails, no file is written.
2. It exports each database in one read-only transaction, so a file is one consistent picture even when another save commits meanwhile.
3. It writes the file as .tmp and renames it when complete: a .sql file is always whole.
4. It deletes the oldest files of that database beyond keep.

The export runs off the game loop. Only the world save before it touches the loop.

A database that fails is logged and does not stop the other one; its older files are left alone. Files in the directory that Moongate did not write are never deleted.

What is in a file

Data only: a TRUNCATE of the tables, one COPY ... FROM stdin block for each table, and the current value of each sequence, all in one transaction. The file holds no schema. Every table the runtime role can read is included, also the tables of plugins. A table it cannot read is skipped and named in a comment at the top of the file and in a log warning. This covers a table in a schema the role may not use. When a skipped table references an exported one, the file and the log also warn that the table must be emptied before the restore, or PostgreSQL refuses the TRUNCATE. A sequence the role cannot read (USAGE without SELECT) is left out in the same way, named in the comment and in a log warning; after a restore it keeps the value the migrations gave it, so grant SELECT on the sequences.

The migration history (moongate_migrations) is never in the file: it describes the schema of the database it is in, and the restore procedure rebuilds it.

A file of auth holds the password hashes of the accounts. Keep the backup directory readable only by the user that runs the server.

Restore

Restore with the Moongate version that wrote the backup.

1. Stop the server.

2. Create an empty database and apply the migrations of that version with mgctl.

3. Run the file with a role that owns the tables:

   psql "postgres://moongate:<password>@localhost:5432/world" -v ON_ERROR_STOP=1 -f world_20261002_113000.sql

4. Start the server. To move to a newer version, upgrade after the restore: the newer migrations then run on the restored data as in any upgrade. A backup written before the catalogs became one file each is the exception: see Moving to the one-file catalogs.

The file runs in one transaction: if it fails, the database is left as it was. Restore auth and world from the same backup run, so characters and accounts match.

What it does not do

The files are not compressed, not copied anywhere else and not restored by the server. Moongate has no automatic reverse migration, so a file does not load onto an older schema, and a newer schema may have columns the file cannot fill. For point-in-time recovery or off-site copies, use PostgreSQL’s own tools next to this.

Moving to the one-file catalogs

The auth catalog was 4 files and the world catalog 28 while the schemas were taking shape; each is now one file, auth/0001_auth.sql and world/0001_world.sql, which builds the same schema. A root and its databases made before that cannot be upgraded the usual way: mgctl init stops with Existing migration '0001_mobiles.sql' for World conflicts with bundled '0001_world.sql', and mgctl migrate and the server stop with Applied migration 'core/0001_mobiles.sql' is missing. (or the same about 0001_account_id_sequence.sql for auth). Do not restore the old files: make the two schemas again.

1. To keep the accounts and the world, write a backup with .sql_backup while the old version still runs, with every old file applied. The files hold the rows only, and the new schemas are the same, so they load into the new databases.

2. Stop the server and install the new version.

3. Delete the old files of the root, <root>/migrations/auth/0001_account_id_sequence.sql to 0004_account_admin_api_access.sql and <root>/migrations/world/0001_mobiles.sql to 0028_guild_member_name.sql, and any NNNN_auto_schema.sql draft beside them, then run mgctl init <root> again. A Docker or Kubernetes volume has them under /data/migrations/.

4. Drop each schema and its history, with a role that owns them: auth in the accounts database, world in the world database of every realm:

   DROP SCHEMA auth CASCADE;
   DELETE FROM moongate_migrations.history WHERE target = 'auth';

   DROP SCHEMA world CASCADE;
   DELETE FROM moongate_migrations.history WHERE target = 'world';

   The tables of the plugins in a schema go with it, and their SQL is applied again in the next step. With separate DDL and runtime roles, create the schema again and give the runtime role its grants as when the database was first made.

5. Run mgctl migrate apply --root-directory <root> --target auth and the same with --target world.

6. To bring the accounts and the world back, run the backup files of step 1 as in Restore, then start the server.

Letter attachment claims

Letter rewards are frozen in the item’s book.attachments string prop. The world.book_attachment_claims table holds an insert-only receipt keyed by letter serial; it has no live world-save source. The receipt and newly materialized reward rows commit with the saved letter and required containment parents in one world transaction. Receipt insertion locks and checks its referenced letter. Deletion cleanup runs at transaction completion: destroying the letter removes its receipt, while a save that temporarily deletes and restores a moved letter preserves it. Receipts never become live world-save snapshots.

Claims share the persistence-operation barrier with world saves. They briefly reserve the claimant’s inventory, run database work off the game loop and apply the delivered objects on the loop before releasing the reservation. Logout waits for settlement before capturing inventory, and shutdown drains claims before its final save. Claims do not retry their transaction after an exception: a fresh read reconciles the exact receipt and expected item snapshots.

A confirmed rollback leaves the entitlement available. A matching committed batch applies once. An uncertain or inconsistent result, failed reconciliation or failed postcommit loop application keeps inventory excluded and faults the shared barrier; final capture must fail instead of saving stale inventory over committed rewards. Inspect the server log and durable receipt/item rows before recovery. Never regenerate an issued payload from a changed TOML source.

The receipts table and its triggers are part of the world schema (0001_world.sql).