How to migrate users with workflows and credentials links?

[JulienDelRio]

Hi,

I want to migrate from sqlite to postgres.

First, I tried to recreate my users by my own.

Then, I try to export/import with reusing the encryption key like described here :

CLI commands - n8n Documentation

It’s working well… but only for the main user… not the others.

I checked here too How to migrate from SQLite to PostgreSQL? - #5 by MutedJam
But not more solution.

I tried with pgloader… but there are errors…

Do you have any solution ?

Thank you

[MutedJam]

Hi @JulienDelRio, you’re correct I am afraid, the CLI commands would only export the workflows/credentials themselves, but not the sharing details.

There is no official migration path unfortunately that would keep the aforementioned details. So a “safe” way could be to simply import all workflows/credentials via the CLI first, then update the sharing settings as needed manually.

The old thread you have linked brings back memories, though of course a lot has changed since I posted this (many more tables would be a change for example). Still, I would have thought the basic idea of manually moving data from SQLite tables into PostgreSQL tables and taking care of any sequences would work in principle. Which error exactly are you seeing?

[JulienDelRio]

Hi,

Thanks for the reply.

Here the script :

load database
     from sqlite://./database.pro.sqlite
     into pgsql://user:password@localhost/n8n

 with include drop, create tables, create indexes, reset sequences

  set work_mem to '16MB', maintenance_work_mem to '512 MB';

here the command :

install@p2lxtsan8n01:~/test-jdr$ pgloader fromSQLITEtoPGSQL.load
2023-04-25T15:39:44.017000Z LOG pgloader version "3.6.2"
2023-04-25T15:39:44.114000Z LOG Migrating from #<SQLITE-CONNECTION sqlite:///home/install/test-jdr/./database.pro.sqlite {1005F99BF3}>
2023-04-25T15:39:44.115000Z LOG Migrating into #<PGSQL-CONNECTION pgsql://user:password@localhost/n8n {1005F9A8A3}>
2023-04-25T15:39:44.316000Z ERROR Database error 22007: syntaxe en entre invalide pour le type timestamp with time zone :  STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')
QUERY: CREATE TABLE tag_entity
(
  id        bigserial,
  name      text,
  createdat timestamptz default 'STRFTIME(''%Y-%m-%d %H:%M:%f'', ''NOW'')',
  updatedat timestamptz default 'STRFTIME(''%Y-%m-%d %H:%M:%f'', ''NOW'')'
);
2023-04-25T15:39:44.318000Z FATAL Failed to create the schema, see above.
2023-04-25T15:39:44.322000Z LOG report summary reset
       table name     errors       rows      bytes      total time
-----------------  ---------  ---------  ---------  --------------
            fetch          0          0                     0.000s
  fetch meta data          0         63                     0.084s
   Create Schemas          0          0                     0.000s
 Create SQL Types          0          0                     0.009s
    Create tables          0          0                     0.000s
-----------------  ---------  ---------  ---------  --------------
-----------------  ---------  ---------  ---------  --------------

I don’t understand the issue

[MutedJam]

Looks like your tool is trying to create the tables during the migration which fails due to an invalid syntax. I am not familiar with pgloader though, so this might be something you want to check with their team.

Perhaps you instead want to have n8n create your tables by starting n8n with your fresh PostgreSQL database? Afterwards you can then copy the data exported from your old SQLite database into the existing tables of your new PostgreSQL database without having to recreate them.

[JulienDelRio]

Hi,

I am not familiar with sqlite to postgre migration, that’s why I tried this tool.

I will try a more “manual” migration.

[MutedJam]

Hi @JulienDelRio, to be fair, this is not a standard operation so I don’t think anyone really is familiar with this. It will be a bit of trail and error.

But letting n8n generate the schema before importing actual data seems like a good first step to me

[JulienDelRio]

Thank you for your help !

[samaritan]

Hi @MutedJam ,

I believe before removing support for MySQL and MariaDB, ability to migrate users with their relations with workflows and credentials would be great.

Support for MySQL/MariaDB has been deprecated and will be removed with an upcoming version of n8n. Please migrate to PostgreSQL.

[Jon]

Hey @samaritan,

You are not wrong there and that is why v1.0 won’t drop support fully now.

[samaritan]

Hi @Jon,

Thank you for clear explanation.

I investigated the code of both import and export, currently importing workflows and credentials with given userId to attach users to them is possible. I believe, exporting workflows and credentials based on their userIds would be enough.

I’ve tried to do that in this PR Can you please review it?

Thanks,
Taha

[Jon]

Hey @samaritan,

I can add it to the list but I don’t know when someone will be available to review it or if it would be the sort of thing we would be planning to do in a different way. We have recently changed some parts of how n8n works which is being used by the new source control feature which will be out soon so no doubt part of that will come into play for this.