Skip to main content
This guide migrates the three Prisme.ai structured-data stores — users, permissions and collections — from MongoDB to PostgreSQL. It applies when you already run Prisme.ai on Mongo and want to move to Postgres (the recommended engine for new deployments). The approach is a data reimport, not a live replication: you stand up a fresh Postgres-backed install, dump the Mongo databases, and load each dump into its Postgres counterpart with a migration script that upserts every document by id.
The schemas come from the platform, not from the migration. The relational schema of all three databases is provisioned the clean way — the backends create the users and permissions tables on startup, and the collections schema is generated by the MongoDB platform runtime and applied as collections.sql. The migration script only loads data into those existing tables and adapts each value to their real column types; it does not design the schema.
Prisme.ai’s three stores run on a single relational engine — you cannot mix Postgres and Mongo across users, permissions and collections. This migration moves all three at once. See Databases / PostgreSQL.

​
Overview

1

Deploy the Postgres-backed platform

Configure and deploy the core & apps Helm charts against PostgreSQL, and let the backends create their own tables.
2

Import the collections SQL schema

Generate the collections schema from the source runtime and apply it so every collection table exists with the correct types and constraints.
3

Dump the MongoDB databases

mongodump the users, permissions and collections databases.
4

Run the migration script

Load each dump into its Postgres database with mongo_to_postgres.py, once per database.

​
Prerequisites

  • A running Postgres-backed Prisme.ai install (see Step 1) — the target databases must exist and their connection URLs be known.
  • MongoDB Database Tools for mongodump — the source dumps.
  • The PostgreSQL client (psql) — see installing the client.
  • Python 3.9+ with the migration dependencies:
  • The migration assets:
    • scripts/mongo_to_postgres.py — the import script (shipped with the platform repository).
    • collections.sql — the collections schema as SQL DDL, generated from your source (Mongo-backed) runtime (see Generating the collections schema). It carries every collection table with its real types, constraints and defaults.
Run the whole migration against an empty or freshly-initialized Postgres target. The script is idempotent (safe to re-run), but the source of truth here is the Mongo dump — do not point it at a Postgres database that already holds diverging data.

​
1. Deploy the Postgres-backed platform

Configure and deploy the core and apps Helm charts with the three storage drivers set to postgresql (see Databases / PostgreSQL — Configuration and Helm install — PostgreSQL). Then verify the platform is healthy before importing any data:
  • Every service is up & running — prismeai-api-gateway, prismeai-workspaces, prismeai-events, prismeai-runtime, and the apps.
  • The users and permissions schemas are initialized. The backends create their own tables on first startup:
    • prismeai-api-gateway auto-creates the users tables (accounts, organizations, memberships, roles, API keys…).
    • prismeai-workspaces auto-creates the permissions tables (workspace-level RBAC).
Let the backends fully boot so these tables exist. The migration script adapts to the real column types and defaults of the target tables — those must be in place first.

​
2. Import the collections SQL schema

Unlike users and permissions, the collections tables are not created by a backend on startup — they are provisioned lazily as platform workspaces are imported. To get the schema the clean way without replaying that whole path, generate it from your source runtime and apply it to the target.

​
Generating the collections schema

The source platform — the runtime still running on MongoDB — exposes an internal endpoint that emits the full collections schema as SQL DDL. Generate collections.sql from a running runtime pod:
1

Exec into a runtime pod

2

Fetch the schema

$INTERNAL_API_KEY is the runtime’s internal API key — already present in the pod’s environment, which is why the call is made from inside the runtime. The /sys/collections/schema route is internal-only and rejects any request without the matching key. Copy the resulting collections.sql out of the pod to where you will run psql (e.g. kubectl cp <pod>:/path/collections.sql ./collections.sql).
The route only exports the schema of collections whose workspace has been loaded at least once since the runtime started. The runtime builds the collections schema lazily: a workspace’s collection schemas are known only after that workspace has been loaded into the runtime — and as soon as it is loaded, all of its collection schemas become available. A freshly (re)started runtime that hasn’t served any traffic will therefore return an empty or partial collections.sql, silently dropping tables for workspaces that were never touched.Before calling the endpoint, warm up the runtime: go to the platform and actually use the modules and workspaces you care about — send a message, browse statistics, open the important/desired workspaces, etc. — so each one is loaded and appears in the exported schema. Then generate collections.sql. When in doubt, exercise more workspaces rather than fewer.Why this matters: for any table missing from collections.sql, the migration script falls back to inferring the schema from the data itself (column types guessed from the Mongo values it sees). This inference is best-effort and lossy — it can pick a type too narrow or too loose for what the platform actually expects, which surfaces as errors either at insertion (a value that won’t coerce into the guessed column) or, worse, at read time (rows load fine but the platform later fails to query or deserialize them, because the column types/constraints don’t match what the ORM expects). Always prefer initializing the schema yourself, as complete as possible, on the important tables — i.e. make sure their workspaces are loaded so their real schema is exported in collections.sql — rather than relying on the script’s inference.

​
Applying the schema

This creates every collection table with the exact types, constraints and defaults the platform expects — so the migration only has to load rows into them.
$COLLECTIONS_PG_URL must include the database name in the URL (e.g. postgres://user:pass@host:5432/collections?sslmode=require). With psql, a separate -d name flag replaces the whole connection string from the URL — put the database in the URL instead. See psql troubleshooting.

​
3. Dump the MongoDB databases

Dump the three source databases from your existing Mongo cluster into a single directory:
This produces ./mongodump/<db>/<collection>.bson (plus .metadata.json sidecars) — exactly the layout the migration script expects.
Add --readPreference=secondary when dumping a production cluster so the dump reads from a replica instead of the primary.

​
4. Run the migration script

Load each dump into its Postgres database, once per database. Point --mongo-db at the source Mongo database (a sub-directory of the dump) and --pg-database at the target Postgres database:
--pg-database overrides the database name in --pg-dsn, so you can keep a single generic $PG_URL (host / user / password) and only change the target database between runs.
Each run prints, per collection, the primary key used, document count and the number of rows upserted — e.g. -> upserted 1240 row(s). Empty and excluded collections are reported as skipped.

​
How the script works

When a table already exists — created by the backends (users, permissions) or by collections.sql — the script reads its real column types, defaults and constraints from information_schema and coerces every Mongo value to match: strings to timestamptz, epoch numbers to dates, objects to jsonb, string arrays to text[], and so on. This is the reliable path, and the reason you should provision collections.sql up front.For a collection whose table is missing, the script falls back to inferring a schema from the data it reads and creating the table itself. Treat this as a last resort: the inferred types are guessed from sample values and can be wrong, causing failures at insertion or at read time (see the warning in Step 2). Provision the real schema for every important table instead of relying on this fallback.
Rows are UPSERTed on their primary key (INSERT … ON CONFLICT (pk) DO UPDATE), so ids are preserved and re-running never duplicates rows nor errors. If a run is interrupted, just run it again.
The primary key is _id if present, otherwise id, decided per collection. A non-empty collection with neither is treated as invalid and aborts the run (override with --skip-invalid). Empty collections are skipped.
Mongo field names are converted to snake_case to match the platform ORM (e.g. authData → auth_data, expiresAt → expires_at); _id is kept as-is. Use --column-naming preserve to keep the original Mongo names instead.
When a NOT NULL column has no value in the Mongo document, the script applies the table’s real Postgres DEFAULT (any type), falling back to [] for arrays and false for booleans — matching the ORM defaults that Mongo documents don’t store.
--exclude skips data at two levels:
  • collection:field=value — skip matching documents. Some rows are seeded by the API at startup and would collide with the dump on a secondary unique constraint; the defaults skip those platform-workspace rows.
  • collection (bare name) — skip an entire collection, before it is even read. Useful for temp/cache collections you don’t want in Postgres (e.g. --exclude '<workspaceId>/atlasReportsTemp', using the decoded name).
Both forms are repeatable and comma-separated; anything you pass is added to the defaults.

​
Options reference

​
Verification

After importing all three databases:
  • Row counts — spot-check a few collections against Mongo:
  • Log in to the platform as an existing user and confirm organizations, workspaces and permissions resolve correctly.
  • Open a workspace that uses collections and confirm its data is present.
Table and column identifiers are case-sensitive in Postgres and quoted by the ORM (e.g. "Users", "AccessTokens"). Quote them in psql queries.

​
Troubleshooting

connection to server on socket "/tmp/.s.PGSQL.5432" failed means psql ignored your connection URL and fell back to the local socket. This happens when you pass both a URL and a bare -d dbname — the -d replaces the whole connection string. Put the database in the URL instead:
Also confirm the variable is set (echo "$COLLECTIONS_PG_URL") — an empty value produces the same fallback.
A NOT NULL column received no value and has no usable default. The script auto-fills DB defaults, empty arrays and false booleans; anything else means a genuinely missing required field or a type that couldn’t be coerced. The script prints a one-line diagnostic naming the field, column and reason just before the failure — use it to identify the field.
A row already exists in Postgres (seeded by the API at startup) that collides with the dump on a secondary unique constraint (not the primary key). Exclude the seeded rows with --exclude. The platform-workspace roles are excluded by default (roles:subjectId=platform).
A non-empty collection has neither _id nor id, so no stable key exists to upsert on. Inspect the collection; if it is genuinely disposable, re-run with --skip-invalid.
Order matters. Deploy and let the backends create the users/permissions tables (Step 1) and import the collections schema (Step 2) before running the script (Step 4). The importer adapts to the real column types and defaults of the target tables, so those tables must exist first.