SQLite to Postgres migration

Questions
1

So I need to migrate from sqlite to Postgres. I´m running n8n installed with npm and pm2.

Workflow export works, credential export works aswell, credential clear output doesn´t work (It says wrong encryption key, even though it never changed).

Stopping n8n, adding new database connections and starting again with pm2 works. Tables are created, etc.

Now the weird. By using command line to import workflows, everything seems to be successful, BUT instead of importing workflows into Postgres a new sqlitedb is created. O_0

Describe the problem/error/question

Any Idea what is causing this?

What is the error message (if any)?

Please share your workflow

(Select the nodes on your canvas and use the keyboard shortcuts CMD+C/CTRL+C and CMD+V/CTRL+V to copy and paste the workflow.)

Share the output returned by the last node

Information on your n8n setup

  • **n8n version:1.44.1
  • **Database (default: SQLite):SQLite to Postgres (Aurora)
  • n8n EXECUTIONS_PROCESS setting (default: own, main):
  • **Running n8n via (Docker, npm, n8n cloud, desktop app):npm/pm2
  • **Operating system:AWS AMI
2

Hey @Kool_Baudrillard,

Have you added the env options for the new database before doing the import? PM2 can also be tricky and doesn’t always pick up new env options so you need to force it.

3

Hi Jon,

yes, I´m adding the env option to ecosystem.config.js, than I perform a pm2 update. As said - creating the new tables in Postgres is fine. Performing the import of workflow and credentials creates a new sqlitedb.

4

Hi @Kool_Baudrillard,

That sounds very odd, When you run the import command though are you setting the same env options on your machine before running the n8n command as pm2 only handles the env options as a service not a one off command.

5

It´s the same machine.

1: Export Workflows & Credentials

2: Second:Stopping n8n with pm2 stop ecosystem.config.js, adding new env option to ecosystem.config.js. Than pm2 update to pick up new option, starting with pm2 start ecosystem.config.js.

This works fine, n8n starts properly, I can setup new user etc aka fresh install on Postgres.

3: Import Workflows and credentials via n8n import.

4: Restart n8n with pm2 restart ecosystem.config.js. N8N starts again with setup screen due to new created sqlitedb. No workflow imported to Postgres and aswell none imported to sqlite (I guess no user, no workflow).

6

Hey @Kool_Baudrillard,

It doesn’t matter if it is the same machine, PM2 will only set the env options for what it is running so if you were running the n8n import command outside of pm2 it won’t have those env options available to it so you would need to make sure you set them in your shell before running the n8n command.

7

You mean, something like this?

pm2 n8n export:credentials --all --output=backup/credentials/file.json

pm2 n8n import:credentials --separate --input=backup/credentials/onefile/allcreds.json

8

Hey @Kool_Baudrillard,

I am not a user of PM2 so I don;t know if that is a valid command or if it would pick up the values from the ecosystem file.

I would probably just do a bunch of export commands then run the n8n command.

9

Ok,

I tried without pm2:

  1. Set every option in config file within .n8n (added DB, etc)
  2. n8n start
  3. Imported credentials / workflows
  4. restarted n8n
  5. Logged in, everything there
  6. Verify install on Postgres - no tables added
  7. .n8n again a new sqlitedb :smiley:

I´m officially lost :smiley:

10

Don’t use the config file in the .n8n folder, Instead you would need to use a differnet config file and specify it with another env option.

The export option is the best option although I would still recommend docker as this would be a lot easier.

11

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.