Can't use pgloader to migrate sqlite + Feature request: Migrate from sqlite to postgresql

[starsareintherose]

Use case

What problem you are trying to solve? Migrate from sqlite to postgresql

Note alternatives you considered and why they are not useful.

Your idea for a solution

How your solution would work in general? Maybe just a script to help Note that some overly complicated solutions may be rejected because maddy is meant to be simple.

• [x] I'm willing to help with the implementation

[starsareintherose]

I have tried this

pgloader /var/lib/maddy/imapsql.db postgres://dnuser:dnuser_passwd@localhost/db_name
2022-06-01T23:36:04.080002+01:00 LOG pgloader version "3.6.62d83f2"
2022-06-01T23:36:04.100002+01:00 LOG Data errors in '/tmp/pgloader/'
2022-06-01T23:36:04.460012+01:00 LOG Migrating from #<SQLITE-CONNECTION sqlite:///var/lib/maddy/imapsql.db {10071B9393}>
2022-06-01T23:36:04.460012+01:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://mail@localhost:5432/maddy {1007381A63}>
2022-06-01T23:36:05.100030+01:00 ERROR Database error 42704: type "longtext" does not exist
QUERY: CREATE TABLE msgs 
(
  mboxid        bigint,
  msgid         bigint,
  date          bigint,
  bodylen       bigint,
  mark          bigint default '0',
  bodystructure longtext,
  cachedheader  longtext,
  extbodykey    text default NULL,
  seen          bigint default '0',
  compressalgo  text
);
2022-06-01T23:36:05.100030+01:00 FATAL Failed to create the schema, see above.
2022-06-01T23:36:05.110031+01:00 LOG report summary reset
       table name     errors       rows      bytes      total time
-----------------  ---------  ---------  ---------  --------------
            fetch          0          0                     0.000s
  fetch meta data          0         20                     0.130s
   Create Schemas          0          0                     0.000s
 Create SQL Types          0          0                     0.020s
    Create tables          0          0                     0.000s
-----------------  ---------  ---------  ---------  --------------
-----------------  ---------  ---------  ---------  --------------

there is a clear error, and when I use it, I must make sslmode disable and I can't see nothing in this PostgreSQL db

[foxcpp]

LONGTEXT seems to be a remmant of MySQL compatibility in go-imap-sql.

[foxcpp]

Looks like you need to create a pgloader.load file to define a custom CAST rule to get rid of LONGTEXT type:

Something like this:

CAST type LONGTEXT to TEXT drop typemod

I will remove the incorrect definition but that won't affect existing databases.

[starsareintherose]

Could I know which db can be the ppstgresql, there are two db, and I want to solve the problems thatwhen I open the webmail, it responses quite slow to load emails.