This guide explains how to migrate the data from an n8n instance (version 1.76.2) running with SQLite to PostgreSQL while preserving the table structure and ensuring that the relationships are well maintained.

Prerequisites Link to heading

  • An n8n instance under Docker running with SQLite
  • A PostgreSQL instance with a user and a dedicated database for n8n
  • The necessary rights to access the SQLite database data

Migration steps Link to heading

1. Stop n8n Link to heading

docker stop n8n

2. Export data from SQLite Link to heading

sqlite3 database.sqlite

.headers on 
.mode csv 

.output credentials_entity.csv 
SELECT * FROM credentials_entity;

.output workflow_entity.csv 
SELECT * FROM workflow_entity;

.quit

3. Convert CSV files to PostgreSQL Link to heading

Before importing the files into PostgreSQL, it is recommended to modify the data format to match the columns and avoid problems related to the order of fields.

Reformatting credentials Link to heading

Reorganize the columns to ensure consistency with the table structure in PostgreSQL:

awk -F',' '{
    OFS=",";
    print $2, $3, $4, $5, $6, $1, $7
}' credentials_entity.csv > credentials_entity_clean.csv

This command moves certain columns to respect the correct order.

Reformatting workflows Link to heading

It is possible to use csvkit to select only the necessary columns:

sudo apt install csvkit  # Install csvkit if not present
csvcut -c name,active,nodes,connections,createdAt,updatedAt,settings,staticData,pinData,versionId,triggerCount,id,meta workflow_entity.csv > workflow_entity_clean.csv

4. Configure n8n for PostgreSQL Link to heading

If you use environment variables to configure n8n, you can modify them to use PostgreSQL or pass them as parameters when starting the container.

[...] -e DB_TYPE=postgres -e DB_HOST=localhost -e DB_PORT=5432 -e DB_USER=n8n_user -e DB_PASSWORD=postgres -e DB_DATABASE=n8n [...]

5. Restart n8n Link to heading

docker start n8n

At this point, n8n should use PostgreSQL as the database and generate all necessary tables. It will then be necessary to create an admin account to be able to access the instance.

6. Restore data Link to heading

Connect to the PostgreSQL database:

psql -U n8n_user -d n8n

Import credentials Link to heading

COPY credentials_entity FROM '/path/to/credentials_entity_clean.csv' DELIMITER ',' CSV HEADER;

Import workflows Link to heading

COPY workflow_entity FROM '/path/to/workflow_entity_clean.csv' DELIMITER ',' CSV HEADER ENCODING 'UTF8';

The data exported from sqlite3 will be encoded in UTF-8 and it is necessary to indicate the encoding when importing as above to ensure that the data is correctly imported.

7. Establish relationships between workflows and projects Link to heading

Once the data has been imported, you must create relationships between the workflows and the projects:

INSERT INTO public.shared_workflow ("workflowId", "projectId", "role", "createdAt", "updatedAt")
SELECT 
    w.id AS "workflowId",
    p.id AS "projectId",
    'workflow:owner' AS "role",
    NOW() AT TIME ZONE 'UTC' AS "createdAt",
    NOW() AT TIME ZONE 'UTC' AS "updatedAt"
FROM public.workflow_entity w
CROSS JOIN public.project p;

This query associates each workflow with a project as an owner.

8. Establish relationships between credentials and projects Link to heading

INSERT INTO public.shared_credentials ("credentialsId", "projectId", "role", "createdAt", "updatedAt")
SELECT c.id as "credentialsId",
       (SELECT id FROM project LIMIT 1) as "projectId",
       'credential:owner' as "role",
       NOW() AT TIME ZONE 'UTC' AS "createdAt",
       NOW() AT TIME ZONE 'UTC' AS "updatedAt"
FROM public.credentials_entity c;

This query associates each credentials with a project as an owner.

9. Verify the migration Link to heading

Verify that the data has been migrated by checking that the workflows and credentials exist in the PostgreSQL database and are visible in the n8n web interface. Then launch the workflows to check that they work correctly.

Conclusion Link to heading

The migration of data from an n8n instance running with SQLite to PostgreSQL is a relatively simple process, but it requires following the steps in order.

This method allows you to migrate your n8n base cleanly to PostgreSQL, while maintaining the consistency of data and relationships.

Need help? Link to heading

If you encounter difficulties or want a migration without any hassle, contact me for technical assistance tailored to your needs. 🚀

👉 Contact me directly.