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
Choose a route#
| Route | Description |
|---|---|
| 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 import | Moves projects, environments, segments, flags and custom roles only. People, keys, history and metrics stay behind. |
| Start fresh | Point a new server at an empty PostgreSQL, apply the migrations and set it up from scratch. |
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 restartBefore you start#
- An empty PostgreSQL database and its connection strings:
DATABASE_URLfor the app andDIRECT_URL(not a pooler) for migrations. - The MongoDB connection string the server uses today, with the database named in it (
MONGODB_URI, orMONGODB_DB). - A way to stop writes to the old server for a few minutes at the end.
- The same
JWT_SECRETandAPI_KEY_ENCRYPTION_KEYon the new setup.
Passing --yes to an npm script
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#
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.Create the tables
Terminal npx prisma generate npx prisma migrate deployYou should see. Every migration is applied and the database is otherwise empty.Do a dry run
Terminal npm run db:copy-mongo-to-postgresYou should see. The PostgreSQL column is all zeros and the last line saysDry run. Nothing was written.It stops if PostgreSQL already holds more rows than MongoDB.Stop writes, then copy
Terminal npm run db:copy-mongo-to-postgres -- --yesIt 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 showscopied N of N, thenChecking…, thenDone. Counts match and every reference resolves.A skipped document is listed with the reason, and the script exits with an error so you notice.Switch the server over
Set
DB_TYPE=postgres,DATABASE_URLandDIRECT_URL, restart and sign in. Rate-limit counters were not copied on purpose.
Options#
| Option | Description |
|---|---|
| --yes | Write the data. Without it the script only counts and checks. |
| --only=a,b | Copy just these models, named as in the dry run output. |
| --skip=a,b | Leave these models out, for example --skip=exposure,evaluation_metric. |
| --include-rate-limits | Also 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.