> ## Documentation Index
> Fetch the complete documentation index at: https://docs.prisme.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# MongoDB → PostgreSQL migration

> Migrate an existing Prisme.ai installation from MongoDB to PostgreSQL for the three structured-data stores (users, permissions, collections).

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](/self-hosting/databases/postgresql) 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.

<Info>
  **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.
</Info>

<Info>
  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](/self-hosting/databases/postgresql).
</Info>

## Overview

<Steps>
  <Step title="Deploy the Postgres-backed platform">
    Configure and deploy the core & apps Helm charts against PostgreSQL, and let the backends create their own tables.
  </Step>

  <Step title="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.
  </Step>

  <Step title="Dump the MongoDB databases">
    `mongodump` the `users`, `permissions` and `collections` databases.
  </Step>

  <Step title="Run the migration script">
    Load each dump into its Postgres database with `mongo_to_postgres.py`, once per database.
  </Step>
</Steps>

## 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](https://www.mongodb.com/docs/database-tools/)** for `mongodump` — the source dumps.
* **The PostgreSQL client (`psql`)** — see [installing the client](/self-hosting/databases/postgresql).
* **Python 3.9+** with the migration dependencies:

  ```bash theme={null}
  pip install "pymongo>=4" psycopg2-binary
  ```
* **The migration assets**:
  * [`scripts/mongo_to_postgres.py`](https://gitlab.com/prisme.ai/prisme.ai/-/raw/main/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](#generating-the-collections-schema)). It carries every collection table with its real types, constraints and defaults.

<Warning>
  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.
</Warning>

## 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](/self-hosting/databases/postgresql#configuration) and [Helm install — PostgreSQL](/self-hosting/kubernetes/helm#6-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).

<Note>
  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.
</Note>

## 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:

<Steps>
  <Step title="Exec into a runtime pod">
    ```bash theme={null}
    kubectl exec -it deploy/prismeai-runtime -- sh
    ```
  </Step>

  <Step title="Fetch the schema">
    ```bash theme={null}
    curl -H "x-prismeai-api-key: $INTERNAL_API_KEY" \
      http://localhost/sys/collections/schema > collections.sql
    ```
  </Step>
</Steps>

`$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`).

<Warning>
  **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.
</Warning>

### Applying the schema

```bash theme={null}
psql "$COLLECTIONS_PG_URL" -f ./collections.sql
```

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.

<Info>
  `$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](#psql-connects-to-the-wrong-server).
</Info>

## 3. Dump the MongoDB databases

Dump the three source databases from your existing Mongo cluster into a single directory:

```bash theme={null}
mongodump --uri="$MONGO_URI" --db users       --out=./mongodump
mongodump --uri="$MONGO_URI" --db permissions --out=./mongodump
mongodump --uri="$MONGO_URI" --db collections --out=./mongodump
```

This produces `./mongodump/<db>/<collection>.bson` (plus `.metadata.json` sidecars) — exactly the layout the migration script expects.

<Tip>
  Add `--readPreference=secondary` when dumping a production cluster so the dump reads from a replica instead of the primary.
</Tip>

## 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:

<CodeGroup>
  ```bash users theme={null}
  python ./scripts/mongo_to_postgres.py \
    --dump-dir ./mongodump \
    --mongo-db users \
    --pg-dsn "$PG_URL" \
    --pg-database users \
    --pg-schema public
  ```

  ```bash permissions theme={null}
  python ./scripts/mongo_to_postgres.py \
    --dump-dir ./mongodump \
    --mongo-db permissions \
    --pg-dsn "$PG_URL" \
    --pg-database permissions \
    --pg-schema public
  ```

  ```bash collections theme={null}
  python ./scripts/mongo_to_postgres.py \
    --dump-dir ./mongodump \
    --mongo-db collections \
    --pg-dsn "$PG_URL" \
    --pg-database collections \
    --pg-schema public
  ```
</CodeGroup>

`--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.

<Check>
  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.
</Check>

### How the script works

<AccordionGroup>
  <Accordion title="Loads into the existing schema">
    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](#2-import-the-collections-sql-schema)). Provision the real schema for every important table instead of relying on this fallback.
  </Accordion>

  <Accordion title="Idempotent — safe to re-run">
    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.
  </Accordion>

  <Accordion title="Primary key detection">
    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.
  </Accordion>

  <Accordion title="camelCase → snake_case column names">
    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.
  </Accordion>

  <Accordion title="NOT NULL defaults">
    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.
  </Accordion>

  <Accordion title="Excluding rows or whole collections">
    `--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.
  </Accordion>
</AccordionGroup>

### Options reference

| Flag              | Purpose                                                                                                                                                        |
| ----------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `--dump-dir`      | Path to the `mongodump` output directory.                                                                                                                      |
| `--mongo-db`      | Source Mongo database(s) — the `<dump-dir>/<db>` sub-directory to import. Comma-separated.                                                                     |
| `--pg-dsn`        | Target Postgres DSN (or `PG_DSN` env var).                                                                                                                     |
| `--pg-database`   | Target Postgres database, overrides the `/dbname` of `--pg-dsn`.                                                                                               |
| `--pg-schema`     | Target Postgres schema (default `public`).                                                                                                                     |
| `--collections`   | Import only these collections (comma-separated).                                                                                                               |
| `--exclude`       | Skip data: `collection:field=value` skips matching documents, a bare `collection` skips the whole collection. Repeatable; added to the platform-seed defaults. |
| `--column-naming` | `snake` (default) or `preserve`.                                                                                                                               |
| `--skip-invalid`  | Skip non-empty collections that have no `_id`/`id` instead of aborting.                                                                                        |
| `--batch-size`    | Rows per upsert batch (default `1000`).                                                                                                                        |

## Verification

After importing all three databases:

* **Row counts** — spot-check a few collections against Mongo:

  ```bash theme={null}
  # Mongo
  mongosh "$MONGO_URI/users" --eval 'db.Users.countDocuments()'
  # Postgres
  psql "${PG_URL%/*}/users" -c 'SELECT count(*) FROM "Users";'
  ```
* **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.

<Note>
  Table and column identifiers are case-sensitive in Postgres and quoted by the ORM (e.g. `"Users"`, `"AccessTokens"`). Quote them in `psql` queries.
</Note>

## Troubleshooting

<AccordionGroup>
  <Accordion title="psql connects to the wrong server">
    `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:

    ```bash theme={null}
    psql "postgres://user:pass@host:5432/collections?sslmode=require" -f ./collections.sql
    ```

    Also confirm the variable is set (`echo "$COLLECTIONS_PG_URL"`) — an empty value produces the same fallback.
  </Accordion>

  <Accordion title="null value in column … violates not-null constraint">
    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.
  </Accordion>

  <Accordion title="duplicate key value violates unique constraint">
    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`).
  </Accordion>

  <Accordion title="Aborting — invalid collection(s)">
    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`.
  </Accordion>
</AccordionGroup>

<Warning>
  **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.
</Warning>