How to migrate from SQLite to PostgreSQL?

Questions
5

:warning: EDIT 29 Oct 24: This post was written a long time ago when n8n was using a very different database structure and did not have the functionality to export/import data stored in the database.

So please do not follow these steps anymore. Instead, refer to the CLI instructions at CLI commands | n8n Docs for information on how to export data from one n8n instance and import it to another. :warning:

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.

  1. Copy the config file with the encryptionKey from the .n8n directory of the old instance to the new one
  2. In the .n8n directory of the old instance, open database.sqlite with sqlite3: sqlite3 database.sqlite
  3. Export the tables credentials_entity and 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):
.headers on
.mode csv
.output credentials_entity.csv
SELECT * FROM credentials_entity;
.output workflow_entity.csv
SELECT * FROM workflow_entity;
.quit
  1. 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)
  2. 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;
  1. 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)
  2. 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.

14 Likes