How to migrate users with workflows and credentials links?

Questions
,
1

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 :

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

2

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?

2 Likes
3

Hi,

Thanks for the reply.

Here the script :

load database
     from sqlite://./database.pro.sqlite
     into pgsql://user:[email protected]/n8n

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

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

here the command :

[email protected]:~/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:[email protected]/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 :frowning:

4

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.

5

Hi,

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

I will try a more “manual” migration.

6

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 :slight_smile:

1 Like
7

Thank you for your help !

1 Like