Skip to content
FlagSwiftDocs

Switch from MongoDB to PostgreSQL

Create the tables in PostgreSQL, copy everything from MongoDB, check the result and switch the server over.

Time
About 20 min
Who can do it
Whoever operates the server
Where
A terminal in the server's folder, your two databases and your environment variables
You finish with
The same organization running on PostgreSQL, with the counts checked

What and why#

This is the mirror image of Switch from PostgreSQL to MongoDB. The copy only reads from MongoDB, writes only into empty PostgreSQL tables, can be run again if it stops part way, and skips (and lists) any document it cannot place instead of guessing.

What changes for people

MongoDB ObjectIds become UUIDs, and every reference to one (organization, project, flag, member, who created or approved something) converts in the same way, so records stay linked to each other. A record that came from PostgreSQL in the first place gets a new UUID, not its original one. Sessions issued before the switch carry the old ids, so people sign in again.

Choose a route#

Three ways to end up on PostgreSQL
RouteDescription
Copy script (this guide)Moves everything: people and roles, keys, flags with their history, segments, releases, audit log, metrics and analytics. Needs a short stop in writes.
Export and importMoves projects, environments, segments, flags and custom roles only. People, keys, history and metrics stay behind.
Start freshPoint a new server at an empty PostgreSQL, apply the migrations and set it up from scratch.
Terminal
npx prisma migrate deploy                          # 1. create the tables on the target
npm run db:copy-mongo-to-postgres                  # 2. dry run: counts and checks only
# stop writes to the old server
npm run db:copy-mongo-to-postgres -- --yes         # 3. copy, then it verifies itself
# set DB_TYPE=postgres and restart

Before you start#

  • An empty PostgreSQL database and its connection strings: DATABASE_URL for the app and DIRECT_URL (not a pooler) for migrations.
  • The MongoDB connection string the server uses today, with the database named in it (MONGODB_URI, or MONGODB_DB).
  • A way to stop writes to the old server for a few minutes at the end.
  • The same JWT_SECRET and API_KEY_ENCRYPTION_KEY on the new setup.

Passing --yes to an npm script

Write npm run db:copy-mongo-to-postgres -- --yes, with the extra --. Without it npm keeps --yes for itself and the script runs as a dry run.

Walk through it#

  1. Set both connection strings

    Environment
    MONGODB_URI="mongodb+srv://…mongodb.net/fs-enterprise"   # the source
    DATABASE_URL="postgresql://…"                             # the target
    DIRECT_URL="postgresql://…"                               # the target, direct (for migrations)
    You should see. The script prints the MongoDB database name it read. Check it is the one that holds your data.
  2. Create the tables

    Terminal
    npx prisma generate
    npx prisma migrate deploy
    You should see. Every migration is applied and the database is otherwise empty.
  3. Do a dry run

    Terminal
    npm run db:copy-mongo-to-postgres
    You should see. The PostgreSQL column is all zeros and the last line says Dry run. Nothing was written. It stops if PostgreSQL already holds more rows than MongoDB.
  4. Stop writes, then copy

    Terminal
    npm run db:copy-mongo-to-postgres -- --yes

    It copies in dependency order (users, organizations, projects, environments, flags and so on). If it stops, run the same command again: rows that already arrived are skipped.

    You should see. Each table shows copied N of N, then Checking…, then Done. Counts match and every reference resolves. A skipped document is listed with the reason, and the script exits with an error so you notice.
  5. Switch the server over

    Set DB_TYPE=postgres, DATABASE_URL and DIRECT_URL, restart and sign in. Rate-limit counters were not copied on purpose.

Options#

Options of the copy script
OptionDescription
--yesWrite the data. Without it the script only counts and checks.
--only=a,bCopy just these models, named as in the dry run output.
--skip=a,bLeave these models out, for example --skip=exposure,evaluation_metric.
--include-rate-limitsAlso copy rate-limit counters. They are skipped by default.
  • Dates and JSON fields are copied unchanged. Fields PostgreSQL does not have are dropped, and empty values take the column default.
  • Nothing is deleted. MongoDB stays as it was, so you can go back by setting DB_TYPE=mongodb, losing only changes made on PostgreSQL in the meantime.

If something goes wrong#

It ran as a dry run after I added --yes

Why: npm took --yes for itself.

Fix: Use npm run db:copy-mongo-to-postgres -- --yes, or npx tsx scripts/copy-mongo-to-postgres.ts --yes.

PostgreSQL has more rows than MongoDB in …

Why: The target is not empty, or it is the wrong database.

Fix: Check DATABASE_URL and use a new empty database.

N skipped: points at a record that does not exist

Why: A MongoDB document refers to something that was deleted. MongoDB does not enforce references, PostgreSQL does.

Fix: Those documents are left out and listed. Usually they are leftovers and safe to ignore. If one matters, restore the record it points at in MongoDB and run again.

N skipped: required … is empty

Why: An old document is missing a field PostgreSQL requires and has no default for.

Fix: Fill the field in MongoDB for the document named, then run again.

Set MONGODB_URI (source) and DATABASE_URL (target)

Why: One connection string is not in the environment the script runs in.

Fix: Export both or put them in .env. DB_TYPE is not needed by the scripts.

Questions#

Will my API keys and SDKs keep working?
Yes. Keys are copied as they are, and the SDKs keep the keys they already have, as long as API_KEY_ENCRYPTION_KEY is unchanged.
Can I try it first?
Yes. Copy into a throwaway PostgreSQL database as often as you like. Nothing in MongoDB changes.