So turns out it wasn’t too hard in the end. Here’s what I did.
Scenario: You have two instances of n8n running, an old one using SQLite with a couple of active workflows and a new one using PostgreSQL without any data yet. You want to move over existing workflows and credentials without having your users re-create them or copy them over manually.
- Copy the config file with the encryptionKey from the .n8n directory of the old instance to the new one
- In the .n8n directory of the old instance, open database.sqlite with sqlite3:
- Export the tables
workflow_entity to CSV by running the following commands (Note that there is also webhook_entity which sounds relevant but was empty in my case. You might want to include this as well should you have data in there):
SELECT * FROM credentials_entity;
SELECT * FROM workflow_entity;
- Now connect to the PostgreSQL database connected to your new n8n instance through psql:
psql -h localhost -p 5432 -U n8nuser n8ndatabase (replace
localhost with your actual hostname,
5432 with the database port,
n8nuser with the database user and
n8ndatabase with the database name)
- Import the CSV files created in the previous step like so and then reset the respective sequences for these tables so new credentials and workflows get proper IDs (again I have skipped webhook_entity here):
\copy credentials_entity from '/path/to/old/n8n/data/credentials_entity.csv' delimiter ',' csv header;
\copy workflow_entity from '/path/to/old/n8n/data/workflow_entity.csv' delimiter ',' csv header;
SELECT setval(pg_get_serial_sequence('credentials_entity', 'id'), MAX(id)) FROM credentials_entity;
SELECT setval(pg_get_serial_sequence('workflow_entity', 'id'), MAX(id)) FROM workflow_entity;
- Restart the new n8n instance to load the newly added workflows and credentials (not sure this step is necessary but it’s what I did)
- Now in the last step, redirect requests goinig to the old instance to the new one. In my case this was pretty straight forward as I had set up nginx as a reverse proxy. I therefore just needed to replace the port the old n8n instance was listening on with the port the new n8n instance was listening on in the respective server block (e.g. in my case replacing
proxy_pass http://localhost:5678/; with
proxy_pass http://localhost:6789/; etc. inside the n8n file of
/etc/nginx/sites-available). However the exact steps here will depend on your setup, so this will vary. If you are using docker you could also just stop the old n8n instance and configure the new one to listen on the port of the old one going forward.
That’s it. After running through the above steps, n8n is now storing data in PostgreSQL instead of SQLite.